Page 1 of 1

executing sql query using rexx

PostPosted: Thu May 28, 2009 12:42 pm
by kuldeep negi
i have a sql query and i want to execute this using rexx.

SELECT * FROM
(SELECT
PRIMARY KEY OF TABLE1
FROM TABLE1) AS PARTA

LEFT OUTER JOIN

(SELECT
PRIMARY KEY OF TABLE2
FROM TABLE2) AS PARTB

ON PARTA.PRIMARY KEY OF TABLE1= PARTB.PRIMARY KEY OF TABLE2

WHERE PARTB.PRIMARY KEY OF TABLE2 IS NULL


PLEASE SUGGEST HOW TO EXECUTE IT USING REXX......

Re: executing sql query using rexx

PostPosted: Fri May 29, 2009 1:36 am
by dick scherrer
Hello,

What have you tried? What happened?

Re: executing sql query using rexx

PostPosted: Fri May 29, 2009 5:35 pm
by expat
"Application programming and SQL guide" can be read or downloaded from the IBM website.

It contains a whole section on using SQL with REXX

Re: executing sql query using rexx

PostPosted: Mon Jun 01, 2009 1:27 pm
by kuldeep negi
Below is my Rexx code:


"ISREDIT MACRO "
Clientid = ""

ADDRESS TSO "SUBCOM DSNREXX" /* DSN Rexx environment available

if rc then
do
s_rc= RXSUBCOM('ADD','DSNREXX','DSNREXX')
end

ARG DD1
CLS
IF DD1 = ' ' THEN
DO
SAY 'Enter the name of database'
PULL DD1

ADDRESS DSNREXX "CONNECT" DD1

if SQLCODE \= 0 then
Do
say 'Sql Error during connect to subsystem:' DD1
End

/* PREPARE SQL STATEMENTS */

address tso
sqlStmt = " SELECT * (SELECT FROM TABLE1) AS PARTA"
sqlStmt = " LEFT OUTER JOIN"
sqlStmt = " (SELECT FROM TABLE2) AS PARTB"
sqlStmt = sqlStmt ||" ON PARTA.PRIMARY KEY OF TABLE1 = PARTB.PRIMARY KEY OF TABLE2"
sqlStmt = sqlStmt ||"WHERE PARTB.PRIMARY KEY OF TABLE2 = NULL"

/* execute the sql statment */
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR WITH HOLD FOR S1"
ADDRESS DSNREXX "EXECSQL PREPARE S1 INTO :SQLDA FROM :SQLSTMT"
ADDRESS DSNREXX "EXECSQL OPEN C1"
If SQLCODE \= 0 then
do
say ' SQL error :' SQLCODE
EXIT 0
end

num=0
Do while sqlcode = 0

/*Fetch the cursor */

ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :SQLDA"
if sqlcode =0 then
do
num = num+1
PRIMARY KEY OF TABLE1 = sqlda.1.sqldata
PRIMARY KEY OF TABLE1 .num = PRIMARY KEY OF TABLE1
say ' PRIMARY KEY OF TABLE1 :' PRIMARY KEY OF TABLE1 .num
end
else
if num = 0 then
say ' No record found'
End

ADDRESS DSNREXX "EXECSQL CLOSE C1"

/* End of the program */
EXIT 0


In this code i am comparing two tables and finding out the record which is there in table1 but not there in table2. table1 is the child table and table2 is the parent table.

i want to take 4 inputs from the user : name of table1, name of table2, primary key of table1, primary key of table2

i tried my code and i am able to connect to the database. but the sql query is not getting executed. it is showing following error code: -514

Please suggest the corrections in my code so that i can get the desired output..

Re: executing sql query using rexx

PostPosted: Mon Jun 01, 2009 10:34 pm
by dick scherrer
Hello,

Did you refer to the -514 in the manual?
http://publibz.boulder.ibm.com/cgi-bin/ ... /2.1.6.223?

Suggest you need to check sqlcode after every execsql. . .

Re: executing sql query using rexx

PostPosted: Tue Jun 02, 2009 12:55 pm
by kuldeep negi
i checked sqlcode after every execsql statement like below. It worked fine for the "cursor declaration statement" but giving SQL error2 : -104 for the prepare statement.


/* execute the sql statment */
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR WITH HOLD FOR S1"
If SQLCODE \= 0 then
do
say ' SQL error1 :' SQLCODE
EXIT 0
end

ADDRESS DSNREXX "EXECSQL PREPARE S1 INTO :SQLDA FROM :SQLSTMT"
If SQLCODE \= 0 then
do
say ' SQL error2 :' SQLCODE
EXIT 0
end

ADDRESS DSNREXX "EXECSQL OPEN C1"
If SQLCODE \= 0 then
do
say ' SQL error3 :' SQLCODE
EXIT 0
end

Please suggest..

Re: executing sql query using rexx

PostPosted: Tue Jun 02, 2009 6:33 pm
by dick scherrer
Hello,

What did you learn when you looked up the -104?