CALL SQLEXEC "CONNECT TO ......."

[cbe...@gmail.com]

Hi all,

Is it possible to have two connects in the same rexx script to
different DB's?
I have to get data form on DB (with specifics selects and filter out
some values with RExx) and save the results into another DB?
I know from Wscripts and MS-Sql that you can build just two objects
but I did not see something like that in REXX.

Thanks in advance for your help

[markh]

With Rexx/SQL it certainly is (and to different database platforms),
but it might depend on what operating system you are on and what the
database platforms are. But based on the subject line it doesn't look
like you are using Rexx/SQL.

Cheers, Mark

[cbe...@gmail.com]

Hi Mark,

It is rexx (obj rexx) and DB2 on Windows I know in z/os it would look
like

"ADDRESS DSNREXX "CONNECT" SUBSYS
IF SQLCODE ^= 0 THEN CALL SQLCA "

[Gary Scott]

cbe...@gmail.com wrote:

Along that line, is it possible to initiate an application remotely.
For example, a DLL or EXE resides on a server and is accessible as a
network shared drive. Is there a method of initiating the DLL or EXE
from the client such that it executes on the server (normally, the OS
would copy it locally)?

--

Gary Scott
mailto:garylscott@sbcglobal dot net

Fortran Library: http://www.fortranlib.com

Support the Original G95 Project: http://www.g95.org
-OR-
Support the GNU GFortran Project: http://gcc.gnu.org/fortran/index.html

If you want to do the impossible, don't hire an expert because he knows
it can't be done.

-- Henry Ford

[Lee Peedin]

On 21 Apr 2007 02:44:32 -0700, markh <re...@users.sourceforge.net>
wrote:

And I can vouche for that ability. It will be part of one of my
Rexx/SQL presentations at this year's Symposium (next week). I'm not
dealing with different database platforms, but I do make connections
to 2 different MySql databases in the same application. If the
internet connection is good enough in the conference room, I'll even
demostrate connecting to MySQL databases on different servers in the
same application.

BTW: I will demostrate this ability using both procedural coding and
object oriented coding. If you've ever wondered exactly what
"Encapsulation" was in regards to ooRexx, this oo demo should clarify
it. :-)

Going to miss seeing you Mark! But be assured I've got nothing but
GOOD to say about Rexx/SQL - well maybe one thing I don't like, but
who's counting. :-)

Lee

[Ven Ilagan]

There are no limits to how many DB's you can connect to in the same rexx
script and there are two styles, ie
1. Using the native rexx interface provided with UDB, ie using
rc=RxFuncAdd('SQLEXEC','db2ar','SQLEXEC')
CALL SQLEXEC 'CONNECT TO 'dbname' USER' userid' USING 'passwd
CALL SQLEXEC 'PREPARE etc,etc
( see the samples provided in the SAMPLE\REXX directory that came with
the install of DB2 )
2. Using REXX/SQL - you download the RexxSQL package of Mark from
Sourceforge, unzip it and put the dll's and exe into your oorexx directory
(included in the Path)
Call RxFuncAdd 'SQLLoadFuncs','rexxsql','SQLLoadFuncs'
Call SqlLoadFuncs
If sqlconnect(id,usr,pwd,dbname,server)>0 then call Abort 'Connect'
sqlprepare, etc etc
( see the sample provided by the Rexx\SQL package )

Whichever method you use, you MUST Catalog your NODE and DB via the CATALOG
command. BTW, run your rexx in the DB2CLP window.

This is how I normally do it.
a. Create a generic rexx routine with 2 arguments, dbname and sql such that
the routine simply connects to the db (read a file containing passwords for
each db likely to be used so you know what password to pass to the Connect
statement). The generic routine would then either issue the prepare and
either EXECUTE IMMEDIATE depending on whether the sql is doing SELECT or
UPDATE/INSERT./DELETE). Result sets are saved onto stem variables which are
returned by the generic routine and lastly DISCONNECT is issued.
b. Any rexx program can then just call this multi-purpose generic routine
pointed to different databases irrespective of where they are and manipulate
the data accordingly. Using this method, it was so easy doing a COMPARE 2
databases script finding differences between dev and prod structures simply
by querying the sysibm catalogues with the same SQL on the different servers
saving the data onto different stems and then comparing the two stems for
variances....

HTH,
Ven

<cbe...@gmail.com> wrote in message
news:1177151608.5...@q75g2000hsh.googlegroups.com...

[cbe...@gmail.com]

Here my code:

CONNECTVAR.0 = 1 /* ONLY SETTING ONE OPTION */
CONNECTVAR.1 = 2 /* TYPE 1 CONNECT */

CALL RXFUNCADD 'SQLDBS',
'SQLAR','SQLDBS'
CALL RXFUNCADD
'SQLEXEC','SQLAR','SQLEXEC'
CALL RXFUNCADD
'SQLDB2','SQLAR','SQLDB2'
CALL SQLDBS 'START DATABASE
MANAGER'
/
*
// INITIALIZE GLOBAL
VARIABLES
*/
DBNAME = 'METADB1'
DBNAME1 = 'METADB'
SAY "SETTING CLIENT CONNECT TYPE 2 "
CALL SQLDBS 'SET CLIENT USING :CONNECTVAR'
CALL SQLEXEC 'CONNECT TO' DBNAME
CALL SQLEXEC 'CONNECT TO' DBNAME1

CALL SQLEXEC 'SET CONNECTION' DBNAME
SQLCMD = "SELECT *
"
SQLCMD = SQLCMD || "FROM USER01.GRP_MEMBERS
"

CALL SQLEXEC "DECLARE C1 CURSOR FOR
S1"
CALL SQLEXEC "PREPARE S1
FROM :SQLCMD"
CALL SQLEXEC "DESCRIBE S1
INTO :OUTSQLDA"
CALL SQLEXEC "OPEN
C1"
OUTSTEM.0 =
1
DO UNTIL(SQLCA.SQLCODE <>
0)
CALL SQLEXEC "FETCH C1 USING
DESCRIPTOR :OUTSQLDA"
IF SQLCA.SQLCODE = 0 THEN
DO
SAY SEP || OUTSQLDA.1.SQLDATA || SEP SEP || OUTSQLDA.
2.SQLDATA || SEP SEP ||OUTSQLDA.3.SQLDATA || SEP

END

END
CALL SQLEXEC "CLOSE
C1"

CALL SQLEXEC 'SET CONNECTION' DBNAME1

SQLCMD1 = "SELECT *
"
SQLCMD1 = SQLCMD || "FROM
USER01.TCEOCLASSES"

CALL SQLEXEC "DECLARE C1 CURSOR FOR
S1"
CALL SQLEXEC "PREPARE S1
FROM :SQLCMD1"
CALL SQLEXEC "DESCRIBE S1
INTO :OUTSQLDA"
CALL SQLEXEC "OPEN
C1"
OUTSTEM.0 =
1
DO UNTIL(SQLCA.SQLCODE <>
0)
CALL SQLEXEC "FETCH C1 USING
DESCRIPTOR :OUTSQLDA"
IF SQLCA.SQLCODE = 0 THEN
DO
say SEP || OUTSQLDA.1.SQLDATA || SEP SEP || OUTSQLDA.
2.SQLDATA || SEP SEP ||OUTSQLDA.3.SQLDATA || SEP

END

END
CALL SQLEXEC "CLOSE
C1"

CALL SQLEXEC "CONNECT RESET"
EXIT

I GOT THIS ERRORS...

SQL0843N The server name does not specify an existing connection.
SQLSTATE=08003

ANY IDEA?