executing sql query using rexx



IBM's Command List programming language & Restructured Extended Executor

executing sql query using rexx

Postby kuldeep negi » Thu May 28, 2009 12:42 pm

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......
kuldeep negi
 
Posts: 21
Joined: Mon Apr 27, 2009 3:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: executing sql query using rexx

Postby dick scherrer » Fri May 29, 2009 1:36 am

Hello,

What have you tried? What happened?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: executing sql query using rexx

Postby expat » Fri May 29, 2009 5:35 pm

"Application programming and SQL guide" can be read or downloaded from the IBM website.

It contains a whole section on using SQL with REXX
expat
 
Posts: 459
Joined: Sat Jun 09, 2007 3:21 pm
Has thanked: 0 time
Been thanked: 8 times

Re: executing sql query using rexx

Postby kuldeep negi » Mon Jun 01, 2009 1:27 pm

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..
kuldeep negi
 
Posts: 21
Joined: Mon Apr 27, 2009 3:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: executing sql query using rexx

Postby dick scherrer » Mon Jun 01, 2009 10:34 pm

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. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: executing sql query using rexx

Postby kuldeep negi » Tue Jun 02, 2009 12:55 pm

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..
kuldeep negi
 
Posts: 21
Joined: Mon Apr 27, 2009 3:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: executing sql query using rexx

Postby dick scherrer » Tue Jun 02, 2009 6:33 pm

Hello,

What did you learn when you looked up the -104?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to CLIST & REXX

 


  • Related topics
    Replies
    Views
    Last post