If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Execute String as SQL Command

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 51
Execute String as SQL Command

In SQl Server its possible to concoct a string representing a sql command and then execute it within a stored procedure. This overcomes the problem of receiving 'peices' of a command as a parameter, such as a list of values for an IN phrase, because it allows you to merge them with the string version of the command.

Does DB2 enable this?

Thanks,

Kimball Johnson
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,099
Yes,
Look at the PREPARE and EXECUTE SQL commands.

Andy
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 51
The DB2 help file says this about EXECUTE and EXECUTE IMMEDIATE:

"This statement can be embedded only in an application program."

My question was about using such a command within a 'stored procedure'.

Is the answer still yes?

Thanks,

Kimball Johnson
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,099
Yes, This will work in an SP:

DECLARE stmt VARCHAR(2000);
...

SET STMT = 'SELECT * from MyTable where ' || colname || ' in ' || list;
PREPARE S1 FROM stmt;
EXECUTE S1;
...

Andy
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 51
Great! Thanks!
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 51
Wait a minute

What about the need for cursors?

When you EXECUTE the string version of the command, where do the records appear?

Do you have to declare a cursor for return to client? And then Open it?

Does the execute command return a table so that you could say,

SET something = EXECUTE "select * from mytable" - ?

?

Thanks,

Kimball Johnson
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,099
Sorry, forgot about cursor.

DECLARE MyCursor CURSOR for S1;
...
DECLARE stmt VARCHAR(2000);
...
SET STMT = 'SELECT * from MyTable where ' || colname || ' in ' || list;
PREPARE S1 FROM stmt;

Open MyCursor;
...

Andy
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: London, UK
Posts: 31
Should be something like this:-

DECLARE stmt VARCHAR(2000);
DECLARE vchar1 varchar(100);
...

SET STMT = 'SELECT vchar1 from MyTable where ' || colname || ' in ' || list;
PREPARE S1 FROM stmt;
DECLARE C1 CURSOR FOR S1;
OPEN C1;
FETCH C1 INTO vchar1;
....

Just google for some examples
__________________
jdey@macehill.co.uk
http://www.macehill.co.uk
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On