View previous topic :: View next topic
|
Author |
Message |
sakrat
Active User
Joined: 05 Feb 2014 Posts: 164 Location: India
|
|
|
|
Hi,
I want to write the FETCH statement dynamically in REXX that which is connected to DB2.
Like first time i might fetch records from the table which has 5 columns and next time when i use this tool, i might be in need to fetch another table which has 10 columns. So in that case FETCH has to take the host variable names dynamically.
So, What i thought first was to use the macro separately in a different member that which uses the DCLGEN of the table and insert the columns to the main rexx code which is taken from the DCLGEN .
But the problem with this which i found was after FETCH statement line in the main REXX code , I would call this macro but the columns will be inserted only after the comand which calls the macro.
can some one please suggest me any idea....I hope i have written clearly with what is my doubt....
Thanks |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Do you need Dynamic Fetch or Dynamic SQL? |
|
Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014 Posts: 164 Location: India
|
|
|
|
Pandora-Box wrote: |
Do you need Dynamic Fetch or Dynamic SQL? |
columns in the Table will vary so while FETCHING the host variable will also vary....I need to know how can I write REXX-DB2 code for FETCH cursor INTO in that case.... |
|
Back to top |
|
|
Smita.t2
New User
Joined: 17 Apr 2012 Posts: 31 Location: Bangalore
|
|
Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014 Posts: 164 Location: India
|
|
|
|
But Here, when i try with DRAW command I get command not found. Also I want the query in REXX code where i am connecting to DB2 and fetching records from table. |
|
Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
Did you look at the code? DRAW connects to DB2 and DESCRIBES a table into the SQLDA. These techniques are what you need for when you want to write a general purpose query program.
I have used DRAW as a model for the many dynamic SQL Rexx applications that I have created. |
|
Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014 Posts: 164 Location: India
|
|
|
|
don.leahy wrote: |
Did you look at the code? DRAW connects to DB2 and DESCRIBES a table into the SQLDA. These techniques are what you need for when you want to write a general purpose query program.
I have used DRAW as a model for the many dynamic SQL Rexx applications that I have created. |
Can you please provide basic syntax of how to write dynamic fetch statement in in REXX using DRAW....I saw the code but it looks difficult for me to understand...If i start with basic may i will be able.... |
|
Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
As you need to fetch rows from different tables and these tables have different columns, you have to be able to built a valid WHERE statement.
This is one (small) problem by itself but I will put it aside for now.
Let's suppose that you want to fetch the whole contents of the table.
First, build the SQL statement:
Code: |
SQLStmt = "SELECT * FROM "tablename |
After CONNECTing to DB2, declare and prepare the cursor:
Code: |
"EXECSQL DECLARE C1 CURSOR FOR S1"
If SQLCode ¬= 0 Then Call DisplaySQLError('DECLARE C1')
"EXECSQL PREPARE S1 INTO :SQLDA_C1 FROM :SQLStmt"
If SQLCode ¬= 0 Then Call DisplaySQLError('PREPARE S1') |
At this stage, you can already know which columns to expect:
Code: |
do I = 1 to SQLDA_C1.SQLD
Say 'Column 'I' is 'SQLDA_C1.I.SqlName
end |
This may be useful, for example, if you want to create an ISPF table to store the table values.
Now, you can start to fetch your data. It can be done with a very simple loop:
Code: |
"EXECSQL OPEN C1"
If SQLCode ¬= 0 Then Call DisplaySQLError('OPEN C1')
Do Forever
"EXECSQL FETCH C1 USING DESCRIPTOR :SQLDA_C1"
Select
When SqlCode = 0 Then Call Store_Row
When SqlCode = 100 Then Leave
Otherwise Call DisplaySQLError('FETCH C1')
End
End
"EXECSQL CLOSE C1"
If SQLCode ¬= 0 Then Call DisplaySQLError('CLOSE C1')
"EXECSQL COMMIT"
If SQLCode ¬= 0 Then Call DisplaySQLError('COMMIT') |
The Store_Row procedure look like this:
Code: |
Do I = 1 To SQLDA_C1.SQLD
ColName = SQLDA_C1.I.SqlName
ColValue = SQLDA_C1.I.SqlData
say 'Column 'ColName' contains <'ColValue'>'
End |
The '<' and '>' will only help you to see if there are spaces within ColValue.
If you want to set a variable named after the column (useful if you have this ISPF table I mentioned before), you can code:
Code: |
Interpret ColName' = "'ColValue'"' |
|
|
Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
If you want info about how to build the WHERE clause, just ask here... |
|
Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014 Posts: 164 Location: India
|
|
|
|
Marso wrote: |
As you need to fetch rows from different tables and these tables have different columns, you have to be able to built a valid WHERE statement.
This is one (small) problem by itself but I will put it aside for now.
Let's suppose that you want to fetch the whole contents of the table.
First, build the SQL statement:
Code: |
SQLStmt = "SELECT * FROM "tablename |
After CONNECTing to DB2, declare and prepare the cursor:
Code: |
"EXECSQL DECLARE C1 CURSOR FOR S1"
If SQLCode ¬= 0 Then Call DisplaySQLError('DECLARE C1')
"EXECSQL PREPARE S1 INTO :SQLDA_C1 FROM :SQLStmt"
If SQLCode ¬= 0 Then Call DisplaySQLError('PREPARE S1') |
At this stage, you can already know which columns to expect:
Code: |
do I = 1 to SQLDA_C1.SQLD
Say 'Column 'I' is 'SQLDA_C1.I.SqlName
end |
This may be useful, for example, if you want to create an ISPF table to store the table values.
Now, you can start to fetch your data. It can be done with a very simple loop:
Code: |
"EXECSQL OPEN C1"
If SQLCode ¬= 0 Then Call DisplaySQLError('OPEN C1')
Do Forever
"EXECSQL FETCH C1 USING DESCRIPTOR :SQLDA_C1"
Select
When SqlCode = 0 Then Call Store_Row
When SqlCode = 100 Then Leave
Otherwise Call DisplaySQLError('FETCH C1')
End
End
"EXECSQL CLOSE C1"
If SQLCode ¬= 0 Then Call DisplaySQLError('CLOSE C1')
"EXECSQL COMMIT"
If SQLCode ¬= 0 Then Call DisplaySQLError('COMMIT') |
The Store_Row procedure look like this:
Code: |
Do I = 1 To SQLDA_C1.SQLD
ColName = SQLDA_C1.I.SqlName
ColValue = SQLDA_C1.I.SqlData
say 'Column 'ColName' contains <'ColValue'>'
End |
The '<' and '>' will only help you to see if there are spaces within ColValue.
If you want to set a variable named after the column (useful if you have this ISPF table I mentioned before), you can code:
Code: |
Interpret ColName' = "'ColValue'"' |
|
Now I able to fetch records from the table that i specify, but there are some columns values where it is packed decimal format so after I fetch the records and write it in a PS file i get . (dot) displayed at the end of the value. is there any way i remove that and how can we unload the correct column value if it is the case of Packed decimal.... |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
The "dot" is just showing you that there is a non-displayable character. You can't get rid of it, as it is part of your (packed-decimal) data.
I think with the assistance you've been given already, you should at least be able to find out how to turn a packed-decimal into something human-readable. |
|
Back to top |
|
|
|