home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 18 REXX
/
18-REXX.zip
/
orarex.zip
/
SAMPLE4.CMD
< prev
Wrap
OS/2 REXX Batch file
|
1995-06-28
|
5KB
|
173 lines
/*** rexx ********************************************************************/
/* */
/* SAMPLE1.CMD - Oracle-REXX Interface Sample */
/* */
/* Sample REXX command file to demonstrate multiple database sessions. */
/* */
/*****************************************************************************/
CALL RXFUNCADD "SQLEXEC", "RXORA", "SQLEXEC"
/*--Connect using session1---------------------------------------------------*/
SAY "Connecting session1 as SCOTT/TIGER"
user = "SCOTT"
password = "TIGER"
connect = "2:"
CALL SQLEXEC "DECLARE session1 DATABASE"
CALL CheckSQLReturn
CALL SQLEXEC "CONNECT :user IDENTIFIED BY :password AT session1 USING :connect"
CALL CheckSQLReturn
/*---------------------------------------------------------------------------*/
SAY "Creating table TEMP"
CALL SQLEXEC "AT session1 EXECUTE IMMEDIATE create table temp (empno number(4), ename varchar2(40), dept number(4))"
CALL CheckSQLReturn
/*--Add data from session1---------------------------------------------------*/
SAY "Adding data to table TEMP"
CALL SQLEXEC "AT session1 DECLARE stmt1 STATEMENT"
CALL CheckSQLReturn
CALL SQLEXEC "PREPARE stmt1 FROM insert into temp(empno,ename,dept) values(:a,:b,:c)"
CALL CheckSQLReturn
empno = 1021
ename = 'David'
dept = 20
CALL SQLEXEC "EXECUTE stmt1 USING :empno, :ename, :dept"
CALL CheckSQLReturn
empno = 1022
ename = 'Beverly'
dept = 20
CALL SQLEXEC "EXECUTE stmt1 USING :empno, :ename, :dept"
CALL CheckSQLReturn
CALL SQLEXEC "AT session1 COMMIT"
CALL CheckSQLReturn
/*--Connect using session2---------------------------------------------------*/
SAY "Connecting session2 as SCOTT/TIGER"
user = "SCOTT"
password = "TIGER"
connect = "2:"
CALL SQLEXEC "DECLARE session2 DATABASE"
CALL CheckSQLReturn
CALL SQLEXEC "CONNECT :user IDENTIFIED BY :password AT session2 USING :connect"
CALL CheckSQLReturn
/*--Add data from session1---------------------------------------------------*/
SAY "Adding more data to table TEMP from session1"
empno = 1023
ename = 'Tim'
dept = 20
CALL SQLEXEC "EXECUTE stmt1 USING :empno, :ename, :dept"
CALL CheckSQLReturn
empno = 1024
ename = 'Lily'
dept = 20
CALL SQLEXEC "EXECUTE stmt1 USING :empno, :ename, :dept"
CALL CheckSQLReturn
/*--Fetch data from session2-------------------------------------------------*/
SAY "Query data from table TEMP from session2"
SAY "Should only see two entries"
CALL SQLEXEC "AT session2 DECLARE stmt2 STATEMENT"
CALL CheckSQLReturn
CALL SQLEXEC "DECLARE csr2 CURSOR FOR stmt2"
CALL CheckSQLReturn
CALL SQLEXEC "PREPARE stmt2 FROM select empno, ename from temp"
CALL CheckSQLReturn
CALL SQLEXEC "OPEN csr2"
CALL CheckSQLReturn
CALL SQLEXEC "FETCH csr2 INTO :empno, :ename"
DO WHILE (result = 0) & (sqlca.sqlcode = 0)
SAY right(empno,8) ename
CALL SQLEXEC "FETCH csr2 INTO :empno, :ename"
END
CALL SQLEXEC "CLOSE csr2"
CALL CheckSQLReturn
/*--Fetch data from session1-------------------------------------------------*/
SAY "Query data from table TEMP from session1"
SAY "Should see four entries"
CALL SQLEXEC "AT session1 DECLARE stmt3 STATEMENT"
CALL CheckSQLReturn
CALL SQLEXEC "DECLARE csr3 CURSOR FOR stmt3"
CALL CheckSQLReturn
CALL SQLEXEC "PREPARE stmt3 FROM select empno, ename from temp"
CALL CheckSQLReturn
CALL SQLEXEC "OPEN csr3"
CALL CheckSQLReturn
CALL SQLEXEC "FETCH csr3 INTO :empno, :ename"
DO WHILE (result = 0) & (sqlca.sqlcode = 0)
SAY right(empno,8) ename
CALL SQLEXEC "FETCH csr3 INTO :empno, :ename"
END
CALL SQLEXEC "CLOSE csr3"
CALL CheckSQLReturn
/*---------------------------------------------------------------------------*/
SAY "Disconnect Session2"
CALL SQLEXEC "AT session2 ROLLBACK RELEASE"
CALL CheckSQLReturn
/*---------------------------------------------------------------------------*/
SAY "Drop table TEMP"
CALL SQLEXEC "AT session1 EXECUTE IMMEDIATE drop table temp"
CALL CheckSQLReturn
/*---------------------------------------------------------------------------*/
SAY "Disconnect Session1"
CALL SQLEXEC "AT session1 ROLLBACK RELEASE"
CALL CheckSQLReturn
EXIT
/*---------------------------------------------------------------------------*/
CheckSQLReturn:
IF result <> 0 THEN
SAY "Invalid embedded SQL statement ("result")."
ELSE IF sqlca.sqlcode <> 0 THEN
SAY sqlca.sqlerrmc
IF (result <> 0) | (sqlca.sqlcode <> 0) THEN
EXIT
RETURN