RE: RPG Embedded SQL connect to PC Websphere db

That's exactly what I do with embedded SQL programs that access remote
systems. It should be possible to trap this error and create the package
on the fly (I don't do this but it should be possible). Also, anytime
you recompile the program, you'll need to create new SQL packages.

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kurt Anderson
Sent: Tuesday, April 25, 2006 4:11 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: RPG Embedded SQL connect to PC Websphere db

So many combinations of things I've been trying...

Ok, it looks like if I compile the SQL module with RDB of *LOCAL and
Options(*XREF *SQL), and I create the SQL Package separately like you
said, then I should be all set with accessing multiple PC databases.

Thanks a ton for your help Matt,

Kurt Anderson
Application Developer
Highsmith Inc

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kurt Anderson
Sent: Tuesday, April 25, 2006 2:57 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: RPG Embedded SQL connect to PC Websphere db

Matt,

I created the package on the PC database.  My embedded SQL works.
That's great!

But now I'm a little confused.  I want the remote database to be
dynamic, but the way I'm doing it it looks like it'll be static.  Maybe
I'm doing something wrong (or misunderstanding something).

When I run CRTSQLRPGI with the parameters below, I'm linking the program
to one remote database, but I'm going to want to connect to more than
one.  From my testing it seemed like I needed to specify the RDB to make
it a distributed SQL program.
RDB(db1)
USER(userid)
PASSWORD('password')
OPTION(*XREF *SQL)

I then run CRTSQLPKG, again specifying:
RDB(db1)
USER(userid)
PASSWORD('password')

So this connection is to db1, is this limiting it to db1 only?  If not,
I'm not sure I understand it.  I figure I'd need to create a package on
each remote database, but it looks like the compile of the object with
the RDB parameter limits it to one remote relational database?

Thanks for your help,

Kurt Anderson
Application Developer
Highsmith Inc

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Matt.Haas@xxxxxxxxxxx
Sent: Tuesday, April 25, 2006 2:05 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: RPG Embedded SQL connect to PC Websphere db

Kurt,

Have you tried creating the package? The command you run from the box
your program runs on is CRTSQLPKG LIB/PGM REMOTEDB (if you need to log
in with a user name and password, prompt the CRTSQLPKG command). An SQL
package is used by the RBMS to store things like access path information
for the program and I think it also caches prepared statements. When you
run SQL interactively, that information is stored in a system level SQL
package that is automatically created if it doesn't exist.

Matt 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kurt Anderson
Sent: Tuesday, April 25, 2006 2:57 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: RPG Embedded SQL connect to PC Websphere db

Ok, by specifying RDB(*LOCAL) on the CRTSQLRPGI command, that appears to
make it a "Distributed SQL program."
With that, I can now connect to the PC Websphere database.
But, whenever I run a statement, I get:
SQL0805 - "SQL package TESTMSQL 0 in KANDERSON not found at DRDA
Server." 

With this new error code I found some questions about it in the
archives, but no answer in regard to connecting to a PC database.  (The
only answers out there seem to be to create the SQL package on the
remote iSeries.)  Any ideas?  How come interactive SQL from the iSeries
is connecting and performing fine but embedded isn't?

Kurt Anderson
Application Developer
Highsmith Inc

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kurt Anderson
Sent: Tuesday, April 25, 2006 11:54 AM
To: RPG programming on the AS400 / iSeries
Subject: RPG Embedded SQL connect to PC Websphere db

When I'm in interactive SQL (strsql), I can connect to the PC Websphere
db by doing:
       connect to webdev user username using 'password'
(I then see it change data refresh from *always to *forward, commit from
*none to *cs, allow copy data from *yes to *optimize, naming from *sys
to *sql, datfmt from *mdy to *usa, and timfmt from *hms to *jis.)
>From there I can then interact with the database.
 
But in RPG, I'm having issues connecting.
I'm attempting to emulate the parenthetical above (the allow copy data
is the compile default, and I don't know how to change the refresh to
*forward).
C/EXEC SQL

C+ Set Option COMMIT =*CS, NAMING =*SQL, DATFMT =*USA, TIMFMT =*JIS

C/END-EXEC
 
I issue the connect (the host variables are all defined as 10a):
C/EXEC SQL
C+ Connect To :rdb User :userid Using :pw
C/END-EXEC
I then get SQL0862
"Local program attempted to connect to a remote relational database."
The recovery says to use the RDB parameter on the compile.  I'd rather
not because the relational database I'm connecting to is different
depending on what box this program is running on.
 
v5r2
 
Thanks for any help, and feel free to ask me about any information I
forgot to mention.
 
Kurt Anderson
Application Developer
Highsmith Inc.
W5527 State Road 106, P.O. Box 800
Fort Atkinson, WI 53538-0800
TEL (920) 563-9571  FAX (920) 563-7395
EMAIL kjanderson@xxxxxxxxxxxxx
 
--
This is the RPG programming on the AS400 / iSeries (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 AS400 / iSeries (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 AS400 / iSeries (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.