View previous topic :: View next topic
|
Author |
Message |
Elixir
Active User
Joined: 08 Feb 2009 Posts: 116 Location: CHENNAI/NEW JERSEY - INDIA/USA
|
|
|
|
Hello Friends,
I am trying to obtain the information from a DB2 table by providing the Unique key field.
I am able to extract only that many row whose total character count is <= 250. (COL_1,COL_2 ---count is 10)
A_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX') is used.
When I try to obtain the details of additional rows from the table am getting below error after "ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'"
"ADDRESS DSNREXX 'column fetched variable names"
"+++ S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')"
"IRX0030I Error running DB2, line 200: Name or string > 250 characters"
Please let me know how its possible to get all the colums from a table. |
|
Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
That message is coming from Rexx, not DSNREXX or DB2. Notice the unbalanced quotes in your ADDRESS statement. |
|
Back to top |
|
|
Elixir
Active User
Joined: 08 Feb 2009 Posts: 116 Location: CHENNAI/NEW JERSEY - INDIA/USA
|
|
|
|
i dont think so.
I have just removed one column from the db2 select statement and am able to get the information successfully. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Code: |
"IRX0030I Error running DB2, line 200: Name or string > 250 characters" |
Did removing the column cause the length to become less 250?
What is the entire line 200? |
|
Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
Use HILITE REXX and the cause of the problem will become obvious. |
|
Back to top |
|
|
Elixir
Active User
Joined: 08 Feb 2009 Posts: 116 Location: CHENNAI/NEW JERSEY - INDIA/USA
|
|
|
|
SQLSTMT ="SELECT COL_1,COL_2,....." <end>
SQLSTMT1 =",COL_14,..... FROM TABLE"<end>
SQLSTMT2 =" WHERE COL_1 ='
SQLSTMT3 = ""COLVAL"" --> Taken from the user
SQLSTMT = SQLSTMT SQLSTMT1 SQLSTMT2 SQLSTMT3
ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'
failing at this point if the count > 250 as mentioned above.
Dick S,
removing the column had made the string character count < 250.
line 200 is "ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'
" |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
You are missing a closing " on SQLSTMT2 but if this just a typo then do not retype things - cut and paste them. And use the code tags to enhance the readability of your code. |
|
Back to top |
|
|
Elixir
Active User
Joined: 08 Feb 2009 Posts: 116 Location: CHENNAI/NEW JERSEY - INDIA/USA
|
|
|
|
Code: |
CLRSCRN
address ISREDIT "MACRO"
"SUBCOM DSNREXX"
say 'Please Enter KEY '
PULL COLVAL
S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT <db2 test region>"
SQLSTMT ="SELECT COL_1,COL_2,....." <end>
SQLSTMT1 =",COL_14,..... FROM TABLE"<end>
SQLSTMT2 =" WHERE COL_1 ="
SQLSTMT3 = ""COLVAL"" --> Taken from the user
SQLSTMT = SQLSTMT SQLSTMT1 SQLSTMT2 SQLSTMT3
ADDRESS DSNREXX 'EXECSQL PREPARE A1 FROM :SQLSTMT'
ADDRESS DSNREXX 'EXECSQL DECLARE B1 CURSOR FOR A1'
ADDRESS DSNREXX 'EXECSQL OPEN B1'
ENDIND = 'N'
FORTHEFIRST = 'Y'
DO UNTIL ENDIND = 'Y'
ADDRESS DSNREXX 'EXECSQL FETCH B1 INTO :C1,:C2,...,:C14,.."
IF SQLCODE = 0
DO
SAY 'COL_1' C1
..<display other columns>
FORTHEFIRST = 'N'
ENDIND= 'N'
END
ELSE
IF FORTHEFIRST = 'Y' & SQLCODE =100
DO
SAY 'NO DATA FOUND'
ENDIND = 'Y'
END
ELSE
DO
SAY 'SQL ERRORCODE =' SQLCODE
ENDIND = 'Y'
END
END
ADDRESS DSNREXX "DISCONNECT"
S_RC=RXSUBCOM('DELETE','DSNREXX','DSNREXX')
EXIT(0) |
|
|
Back to top |
|
|
Elixir
Active User
Joined: 08 Feb 2009 Posts: 116 Location: CHENNAI/NEW JERSEY - INDIA/USA
|
|
|
|
pasted the entire snippet |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
You may have pasted it but you did not use the code tags (hint: highlight the code to be 'coded' and click on the Code button). I am not going to waste time downloading your code to my editor to try and see it properly. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
post edited to add the code tags, anyway
the snippet as posted will never run...
a couple of THEN are missing |
|
Back to top |
|
|
Elixir
Active User
Joined: 08 Feb 2009 Posts: 116 Location: CHENNAI/NEW JERSEY - INDIA/USA
|
|
|
|
yes..THEN's are the only things missing....
The issue is only with the below because if string length is greater than 250 the IF statements are not getting executed at all.
<code - start>
S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT <db2 test region>"
SQLSTMT ="SELECT COL_1,COL_2,....." <end>
SQLSTMT1 =",COL_14,..... FROM TABLE"<end>
SQLSTMT2 =" WHERE COL_1 ="
SQLSTMT3 = ""COLVAL"" --> Taken from the user
SQLSTMT = SQLSTMT SQLSTMT1 SQLSTMT2 SQLSTMT3
ADDRESS DSNREXX 'EXECSQL PREPARE A1 FROM :SQLSTMT'
<code - end>
after executing the last line of code shown above, IF the total string length in SQLSTMT(SQLSTMT + SQLSTMT1 + SQLSTMT2 + SQLSTMT3) > 250 ,REXX is failing with
"ADDRESS DSNREXX 'column fetched variable names"
"+++ S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')"
"IRX0030I Error running DB2, line 200: Name or string > 250 characters"
I feel S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX') is not able to handle more than 250 characters. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
RXSUBCON has nothing to do with it - it is only making the Rexx/DB2 interface available (if it is not already - did you first check as per the documentation?). |
|
Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
Try coding it like this (notice code tag being used):
Code: |
SQLSTMT = "SELECT ",
" COL_1, ",
" COL_2, ",
" COL_3, ",
" COL_4, ",
" COL_5, ",
" COL_6, ",
" COL_7 ",
" FROM TABLE_A ",
" WHERE COL_1 = '"COLVAL"'"
ADDRESS DSNREXX "EXECSQL PREPARE S2 FROM :SQLSTMT"
|
I have SQL longer than 1000 characters that work. |
|
Back to top |
|
|
Elixir
Active User
Joined: 08 Feb 2009 Posts: 116 Location: CHENNAI/NEW JERSEY - INDIA/USA
|
|
|
|
It worked for me. |
|
Back to top |
|
|
|