Dynamic SQL prepare question...

[nxm150]

When I code my SQL like -

STRING 'SELECT PHYS_CYCLE, DATA_RQST_ID, QUE_CONTRL_ID
'FROM DBA$DSY.DSY_PHYSIC_EXTRACT
'WHERE PHYS_CYCLE = ? FOR FETCH ONLY'
DELIMITED BY SIZE INTO WS-SQL-STATEMENT-TEXT
WITH POINTER WS-SQL-STATEMENT-LEN
END-STRING.

and then prepare the statement, it works fine.

But when I code it like

MOVE 1 TO WS-TEST-STRING-LEN
WS-SQL-STATEMENT-LEN.
MOVE SPACES TO WS-TEST-STRING-TEXT
WS-SQL-STATEMENT-TEXT
MOVE 'SELECT PHYS_CYCLE, DATA_RQST_ID, QUE_CONTRL_ID
'FROM DBA$DSY.DSY_PHYSIC_EXTRACT WHERE PHYS_CYCLE = ?
' FOR FETCH ONLY' TO WS-TEST-STRING.
STRING WS-TEST-STRING DELIMITED BY SIZE
INTO WS-SQL-STATEMENT-TEXT
WITH POINTER WS-SQL-STATEMENT-LEN

I get a -104. I need to code my program the second way because I will not know all the column names right away. I am building my select statement. Any help on how to correct this problem would be appreciated.

 

[nxm150]

I figured out the problem!!!

STRING WS-TEST-STRING-TEXT DELIMITED BY SIZE
INTO WS-SQL-STATEMENT-TEXT
WITH POINTER WS-SQL-STATEMENT-LEN.

The size of WS-TEST-STRING and WS-SQL-STATEMENT were defined both as 300. When I displayed WS-SQL-STATEMENT-LEN it showed a value of 301. I made WS-TEST-STRING a size of 200 and it worked fine!

 

[senthiljith]

Hi

can u give an idea on how to prepare dynamic SQL queries
Thanx

 

[nxm150]

I am coding dynamic SQL for the first time and am learning as I go. What I do know is that there are 2 types of dyanmic SQL. Fixed list and variable list. I am coding variable list right now. What you need to do is to string your SQL statment together. Then you will need to issue a PREPARE statment against your string. You will need to set up a input SQLDA area and output SQLDA area. Your pointers will be in the SQLDA. You open your cusrsor using the input SQLDA. You fetch using your output SQLDA.