Re: using RPG to access MYSQL files -- RPG400-L

I expect that your problem is the quotes around accounts. If you do a DSPFD
on the file you are trying to query what does it say the table name is? Use
that name. I would guess it would be ACCOUNTS. You should be able to run the
statement within STRSQL. If it works there, it should work in the program.

Not sure what you mean by path name. From the AS/400 side there are no
paths, just libraries unless I am miss understanding the question.

On Fri, May 22, 2009 at 1:25 PM, dale janus <dalejanus@xxxxxxxxxxxxxxxxx>wrote:

I am new to this mailing list and I hope I am using it correctly. I did
not want to reply with all the quoted messages, so I tried to trim it down.

My problem is the lowercase file names. I was able to CRTDUPOBJ with
library "sugarcrm" and file "accounts". So now I have a starting point.

I will also try EXTFILE.

So my question has changed to:

What is the usual/acceptable/best method of using these long file and
path names with embedded quotes? Do I need a path inside my RPG if I
use embedded SQL? If not in RPG, where?

I am new to a lot of this stuff, PHP, SQL etc.
I gotta admit, it seems half the battle is figuring out where your files
and stuff is kept and how to get back to it.

For those who suggested IBM DB2 is better and more robust than MYSQL, I
agree wholeheartedly. For example, we have just started running
mediawiki internally to share information and when we tried a search,
MYSQL said the index is bad, please rebuild. After a rebuild, it would
still not find a page containing the search word until we saved the page
again. So I am in complete agreement that IBM DB2 is the best way.
That being said, there is a lot of free and interesting PHP stuff that
runs on mysql.

I tried to run the test program from the redbook with embedded SQL and
it says "column definitions not found".

d recordCount s 9
c*
c exsr countRecords
c dsply recordCount
c eval *inlr = *on
c return
c*-----------------------------------------------------
c countRecords begsr
c/exec sql
c+ select count(*) into :recordCount from "accounts"
c/end-exec
c endsr

---Dale

You are correct that the tables are just DB2 tables. The problem is

that

the table names from the storage engine have double quotes around the
table name. The RPG F spec does not support the double quote special
character in the file name.

The work around is to use OVRDBF before accessing the file. The OP had

a

problem doing a CRTDUPOBJ in order to compile his program.

Dale,

I don't have the storage engine loaded yet, but instead of doing the
duplicate object and OVRDF, have you tried using the EXTFILE keyword on
the F spec? I don't know if it would work, but it is logically the

same

thing.

Brian May
Project Lead
Management Information Systems
Garan, Incorporated
Starkville, Mississippi

Young i Professionals
http://www.youngiprofessionals.com

Alan Campin <alan0307d@xxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
05/22/2009 10:51 AM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>

To
"RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Re: using RPG to access MYSQL files

I am confused here.

If you are using the IBM DB2 engine doesn't that just mean that the

tables

are just ordinary AS/400 tables which you could access with embedded

SQL

or
File I/O. (Highly recommend using Embedded SQL so that you break your
program ever time the table gets changed by MYSQL)?

The link provided is to access MySql on another server via Java. I am
hearing that the data is stored on the AS/400 which makes them ordinary
AS/400 tables.

Am I missing something?

If you look in the schema (Library) what do you do see? You should see
physical files and if you display the physical file description you

should

see the short and the long name. If the long name is 10 characters or

less

you will just see the short name.

You could also look at the schema via Ops Nav.

On Fri, May 22, 2009 at 9:34 AM, Chamara Withanachchi
<chamaraw@xxxxxxxxxx>wrote:

You have to use MySQL drivers
check following link
http://systeminetwork.com/article/jdbc-rpg-enhancements

On Fri, May 22, 2009 at 5:13 PM, dale janus <

dalejanus@xxxxxxxxxxxxxxxxx

wrote:

I need help figure out how to use RPG or CL to access MYSQL data

created

using the new IBMDB2I engine in MYSQL version.

We have installed the latest version of MYSQL 5.1.34. Then we

installed

SUGARCRM with test data. PHPMYADMIN shows the test files using the
new IBMDB2I engine.

We followed the IBM redbook "using ibm db2 for i as a storage engine

of

mysql" .Everything seems to have worked as the redbook says it would.
But I am stuck on chapter 4, trying to get to the sugarcrm test

files

through rpg.

On page 78, it seems the redbook authors say all I need to do is

create

a duplicate object as a file template and compile my rpg program

using

it. But CRTDUPOBJ does not seem to know about QSYS2 and other such
things.

I like the idea of creating a dummy duplicate object for RPG

compiles.

For example, SUGARCRM has a masterfile called ACCOUNTS (probably

lower

case).

We know our catalog name is our I series serial number. The schema
(LIBRARY?)is sugarcrm (in lowercase). The table name is accounts

(in

lowercase, probably with " around it).

Can anyone point me in the right direction?

---Dale
--
This is the RPG programming on the IBM i / System i (RPG400-L)

mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
Regards,

Chamara Withanachchi
IBM Certified Power System Expert
RPG Programmer
(owner of www.rpgiv.info)

WWW.RPGIV.INFO
Mob: +971 50 5698644
Tel: +971 6 5595887
chamaraw@xxxxxxxxxx

i want to be future ready. i want control. i want an i.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.