COBOL and Dynamic SQL
Last Post 15 Mar 2010 10:31 AM by heronfisher. 9 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
adayers
New Member
New Member
Posts:10

--
12 Jan 2010 03:01 PM
I have an issue. I am trying to build a Dynamic Insert based on the filename passed into this program. Basically I want to do Insert into InputTable Select x1, x2, x3, t4...tn from FromTable t, xTable x Where t1 ='01' and t2 = '01' and t3 = '04' of course t4-tn will be different depending on which tablename is passed in. I am using SYS2.SYSCOLUMNS to get the field names and building the SQL stmt. Then Prepare it. I get a SQLCode=-104 invalid token. So I thought I would put a simple sql stmt in "SELECT * from Table Where field1 = '01'" and still get the same code. Exec SQL Prepare SQL_STATEMENT FROM :WS-SQL-STATEMENT. End-Exec I do not have a SQL declare for SQL_STATEMENT do I need one? Any help would be appreciated. Thanks
B.Hauser
Veteran Member
Veteran Member
Posts:2043

--
12 Jan 2010 03:52 PM Accepted Answer
Hi, Change your string as follows: [PHP] Insert into InputTable Select x1, x2, x3, t4...tn from FromTable t, xTable x Where t1 =''01'' and t2 = ''01'' and t3 = ''04'' [/PHP] Instead of a single apostrophe you need to write 2 apostrophes within a string! BTW for an Insert or Update statement you don't need Prepare and Execute, you may simply use EXECUTE IMMEDIATE :MyString Birgitta
Lynne Noll
Senior Member
Senior Member
Posts:6567

--
12 Jan 2010 04:45 PM Accepted Answer
The choice between prepare /execute and execute immediate is mostly whether you want to run it more than once. If you were writing a generic update program with a screen, you might want to prepare once and then run the update based on user input with a execute using statement, changing row selection or update values each time. In this caes, you probably are only going to run it once, and execute immediate saves a step.
adayers
New Member
New Member
Posts:10

--
13 Jan 2010 02:01 PM Accepted Answer
Hi, Change your string as follows: [PHP] Insert into InputTable Select x1, x2, x3, t4...tn from FromTable t, xTable x Where t1 =''01'' and t2 = ''01'' and t3 = ''04'' [/PHP] Instead of a single apostrophe you need to write 2 apostrophes within a string! BTW for an Insert or Update statement you don't need Prepare and Execute, you may simply use EXECUTE IMMEDIATE :MyString Birgitta
I did this and got past the -104 invalid token and now I am getting an -113 invalid character. This is my build sql stmt when I get it. And once I get this to prepare correctly I will change it to a Execute immediate. INSERT INTO INTOTABLE SELECT XLOC, XMCO, XPCO, AUADCD, AUADTX, AUAGTX, AUANTX, AUAOTX, AUDTST, AUCITX, AUBTTX, AUDCST, AUBZNB, AUBUTX, AUDDST, AUB0NB, AUBVTX, AUDEST, AUB1NB, AUBWTX, AUDFST, AUB2NB, AUBXTX, AUDGST, AUB3NB, AUBYTX, AUDHST, AUB4NB, AUBZTX, AUDIST, AUB5NB, AUB0TX, AUDJST, AUB6NB, AUB1TX, AUAUCD, AUDNST, AUMUTX, AUAAVN, AUAKDT, AUABTM FROM FROMTABLE, (SELECT XLOC, XMCO, XPCO FROM VLKLMPL1 WHERE XLOC >= ''01'' AND XMCO >= ''01'' AND XPCO >= ''01'') XREC WHERE AUAACD < ''99'' AND AUABCD < ''99'' AND AUACCD < ''99''
adayers
New Member
New Member
Posts:10

--
13 Jan 2010 04:51 PM Accepted Answer
I copied the sql into DBvisualizer and ran it.. That worked I copied the sql into interactive SQL on iSeries and ran it that worked as well Of course both times I had to remove the double single quotes and replace with one single quote. and change the table names to the actual names. But the SQL worked.
SMITH RICK
Basic Member
Basic Member
Posts:169
Avatar

--
13 Jan 2010 06:12 PM Accepted Answer
I did this and got past the -104 invalid token and now I am getting an -113 invalid character. This is my build sql stmt when I get it. And once I get this to prepare correctly I will change it to a Execute immediate. INSERT INTO INTOTABLE SELECT XLOC, XMCO, XPCO, AUADCD, AUADTX, AUAGTX, AUANTX, AUAOTX, AUDTST, AUCITX, AUBTTX, AUDCST, AUBZNB, AUBUTX, AUDDST, AUB0NB, AUBVTX, AUDEST, AUB1NB, AUBWTX, AUDFST, AUB2NB, AUBXTX, AUDGST, AUB3NB, AUBYTX, AUDHST, AUB4NB, AUBZTX, AUDIST, AUB5NB, AUB0TX, AUDJST, AUB6NB, AUB1TX, AUAUCD, AUDNST, AUMUTX, AUAAVN, AUAKDT, AUABTM FROM FROMTABLE, (SELECT XLOC, XMCO, XPCO FROM VLKLMPL1 WHERE XLOC >= ''01'' AND XMCO >= ''01'' AND XPCO >= ''01'') XREC WHERE AUAACD < ''99'' AND AUABCD < ''99'' AND AUACCD < ''99''
I'm assuming that the insert statement shown comes directly from the WS-SQL-STATEMENT variable. If the statement is different I would like to see what is directly placed in the WS-SQL-STATEMENT variable, otherwise I don't think I could be much help. If the insert statement above is what is in the variable I would attempt to place the statement in the program as shown below:

EXEC SQL
     INSERT INTO INTOTABLE SELECT XLOC, XMCO, XPCO, AUADCD, AUADTX,
AUAGTX, AUANTX, AUAOTX, AUDTST, AUCITX, AUBTTX, AUDCST, AUBZNB, 
AUBUTX, AUDDST, AUB0NB, AUBVTX, AUDEST, AUB1NB, AUBWTX, AUDFST, 
AUB2NB, AUBXTX, AUDGST, AUB3NB, AUBYTX, AUDHST, AUB4NB, AUBZTX, 
AUDIST, AUB5NB, AUB0TX, AUDJST, AUB6NB, AUB1TX, AUAUCD, AUDNST, 
AUMUTX, AUAAVN, AUAKDT, AUABTM FROM FROMTABLE, (SELECT XLOC, 
XMCO, XPCO FROM VLKLMPL1 WHERE XLOC >= ''01'' AND XMCO >= ''01'' AND 
XPCO >= ''01'') XREC WHERE AUAACD < ''99'' AND AUABCD < ''99'' AND 
AUACCD < ''99''
END-EXEC.
Basically, make it static SQL and recompile the program. You should get the same error and a better description of where the SQL the error is at. I hope that helps.
adayers
New Member
New Member
Posts:10

--
13 Jan 2010 07:52 PM Accepted Answer
I'm assuming that the insert statement shown comes directly from the WS-SQL-STATEMENT variable. If the statement is different I would like to see what is directly placed in the WS-SQL-STATEMENT variable, otherwise I don't think I could be much help. If the insert statement above is what is in the variable I would attempt to place the statement in the program as shown below:

EXEC SQL
     INSERT INTO INTOTABLE SELECT XLOC, XMCO, XPCO, AUADCD, AUADTX,
AUAGTX, AUANTX, AUAOTX, AUDTST, AUCITX, AUBTTX, AUDCST, AUBZNB, 
AUBUTX, AUDDST, AUB0NB, AUBVTX, AUDEST, AUB1NB, AUBWTX, AUDFST, 
AUB2NB, AUBXTX, AUDGST, AUB3NB, AUBYTX, AUDHST, AUB4NB, AUBZTX, 
AUDIST, AUB5NB, AUB0TX, AUDJST, AUB6NB, AUB1TX, AUAUCD, AUDNST, 
AUMUTX, AUAAVN, AUAKDT, AUABTM FROM FROMTABLE, (SELECT XLOC, 
XMCO, XPCO FROM VLKLMPL1 WHERE XLOC >= ''01'' AND XMCO >= ''01'' AND 
XPCO >= ''01'') XREC WHERE AUAACD < ''99'' AND AUABCD < ''99'' AND 
AUACCD < ''99''
END-EXEC.
Basically, make it static SQL and recompile the program. You should get the same error and a better description of where the SQL the error is at. I hope that helps.
Yes this was in the WS-SQL-STATEMENT. I ran it but had to change the '' to " and it worked fine. is there perhaps a SET OPTION I need to do or a pre-compile option I may need to change?
Titus Aguilar
Veteran Member
Veteran Member
Posts:576
Avatar

--
14 Jan 2010 09:14 AM Accepted Answer
Insert the PROCESS QUOTE compiler directive to your CBL source to allow the use of single quotes. AFAIK, It must be the first line of the program. Another alternative is to set OPTION(*QUOTE) in the CRTSQLCBL at compile time. Whenever I do this dynamic SQL statement build, the quotes tend to be messy. What works for me is to temporarily use another character (I use underscore) during the STRING operations and then in the end I do an INSPECT statement to replace the underscore with the quote. That makes the STRING statements cleaner and easier to read. Hth, Titus
adayers
New Member
New Member
Posts:10

--
14 Jan 2010 12:48 PM Accepted Answer
Guess I needed the right combination. First Compiling with the option *QUOTESQL Then instead of using two apost's I use just one quote. Also remove the semicolon at the end of the statement. This worked. Thanks everyone for your help.
heronfisher
New Member
New Member
Posts:3

--
15 Mar 2010 10:31 AM Accepted Answer
One thing I try to make sure developers do when they do put dynamic sql in a program is that they check to see if a flag is on or off, in some code table. If the flag is on, then insert the dynamic query to a dynamic query table. This is just so that you can easily look at exactly what your program generated and correct it if need be. Also, allows the DBA to see what is being generated for researching indexes and the usage.
You are not authorized to post a reply.

Acceptable Use Policy