home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 18 REXX
/
18-REXX.zip
/
orarex.zip
/
SAMPLE1.CMD
< prev
next >
Wrap
OS/2 REXX Batch file
|
1995-06-28
|
3KB
|
104 lines
/*** rexx ********************************************************************/
/* */
/* SAMPLE1.CMD - Oracle-REXX Interface Sample */
/* */
/* Sample REXX command file to demonstrate dynamic SQL methods 1,2 and 3. */
/* */
/*****************************************************************************/
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
/*--Dynamic SQL Method 1-----------------------------------------------------*/
SAY "Creating table TEMP"
CALL SQLEXEC "EXECUTE IMMEDIATE create table temp (empno number(4), ename varchar2(40), dept number(4))"
CALL CheckSQLReturn
/*--Dynamic SQL Method 2-----------------------------------------------------*/
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 = 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
/*--Dynamic SQL Method 3-----------------------------------------------------*/
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
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
/*---------------------------------------------------------------------------*/
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