home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 18 REXX
/
18-REXX.zip
/
orarex.zip
/
SAMPLE3.CMD
< prev
next >
Wrap
OS/2 REXX Batch file
|
1995-06-28
|
3KB
|
117 lines
/*** rexx ********************************************************************/
/* */
/* SAMPLE3.CMD - Oracle-REXX Interface Sample */
/* */
/* Sample REXX command file to demonstrate use of arrays. */
/* */
/*****************************************************************************/
CALL RXFUNCADD "SQLEXEC", "RXORA", "SQLEXEC"
/*---------------------------------------------------------------------------*/
SAY "Connecting as SCOTT/TIGER"
user = "SCOTT"
password = "TIGER"
connect = "2:"
CALL SQLEXEC "CONNECT :user IDENTIFIED BY :password USING :connect"
CALL CheckSQLReturn
/*---------------------------------------------------------------------------*/
SAY "Creating table TEMP"
CALL SQLEXEC "EXECUTE IMMEDIATE create table temp (empno number(4), ename varchar2(40), dept number(4))"
CALL CheckSQLReturn
/*--Executing with an array--------------------------------------------------*/
SAY "Adding data to table TEMP"
CALL SQLEXEC "DECLARE stmt1 STATEMENT"
CALL CheckSQLReturn
CALL SQLEXEC "PREPARE stmt1 FROM insert into temp(empno,ename,dept) values(:a,:b,:c)"
CALL CheckSQLReturn
empno.1 = 1021
ename.1 = 'David'
dept.1 = 20
empno.2 = 1022
ename.2 = 'Beverly'
dept.2 = 20
empno.3 = 1023
ename.3 = 'Tim'
dept.3 = 20
empno.4 = 1024
ename.4 = 'Lily'
dept.4 = 20
n = 4
CALL SQLEXEC "FOR :n EXECUTE stmt1 USING :empno, :ename, :dept"
CALL CheckSQLReturn
/*--Fetching with an array---------------------------------------------------*/
SAY "Query data from table TEMP"
CALL SQLEXEC "DECLARE stmt2 STATEMENT"
CALL CheckSQLReturn
CALL SQLEXEC "DECLARE csr2 CURSOR FOR stmt2"
CALL CheckSQLReturn
CALL SQLEXEC "PREPARE stmt2 FROM select empno, ename from temp where dept=:a"
CALL CheckSQLReturn
dept = 20
CALL SQLEXEC "OPEN csr2 USING :dept"
CALL CheckSQLReturn
n = 3
rows = 0
CALL SQLEXEC "FOR :n FETCH csr2 INTO :empno, :ename"
DO WHILE (result = 0) & (sqlca.sqlcode >= 0)
DO i=1 TO (SQLCA.SQLERRD.3 - rows)
SAY right(empno.i,8) ename.i
END
rows = SQLCA.SQLERRD.3
CALL SQLEXEC "FOR :N FETCH csr2 INTO :empno, :ename"
END
CALL SQLEXEC "CLOSE csr2"
CALL CheckSQLReturn
/*---------------------------------------------------------------------------*/
SAY "Drop table TEMP"
CALL SQLEXEC "EXECUTE IMMEDIATE drop table temp"
CALL CheckSQLReturn
/*---------------------------------------------------------------------------*/
SAY "Disconnect"
CALL SQLEXEC "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