Thanks Birgitta (and Alan and R.Bruce and Nick),
Funny, I actually have used cursors with prepared statements before, but it
just didn't occur to me to use a cursor for a single value. I tend to think of
those in terms of multi-row use.
I had read about the SQLDA in the reference manual, but I didn't understand
what I was reading. I think it's beginning to make more sense now.
And of course, the OVRDBF idea is elegantly simple.
Maybe I'll try them all. Now that we can do it in /Free, it's almost fun.
G
|-----Original Message-----
|From: midrange-l-bounces@xxxxxxxxxxxx [
mailto:midrange-l-
|bounces@xxxxxxxxxxxx] On Behalf Of BirgittaHauser
|Sent: Tuesday, July 03, 2007 1:01 AM
|To: 'Midrange Systems Technical Discussion'
|Subject: AW: Dynamic SQL
|
|Hi,
|
|Select ... Into is not allowed in dynamic SQL.
|If you want to execute this SQL statement dynamically you have to define a
|cursor:
|
|/Free
| MySQLStm = 'Select Count(*) from ' + MyTable;
| Exec SQL Prepare DynSQLCsr1 From :MySQLStm;
| //Check for SQLState/SQLSTT or SQLCODE/SQLCOD
|
| Exec SQL Declare CsrC1 Cursor For DynSQLCsr1;
| //Check for SQLState/SQLSTT or SQLCODE/SQLCOD
|
| Exec SQL Open CsrC1;
| //Check for SQLState/SQLSTT or SQLCODE/SQLCOD
|
| Clear w_Count;
| Exec SQL Fetch Next From CsrC1 into :w_Count;
| //Check for SQLState/SQLSTT or SQLCODE/SQLCOD
|
| Exec SQL Close CsrC1;
| //Check for SQLState/SQLSTT or SQLCODE/SQLCOD
|/End-Free
|
|An other way would be to use static SQL in combination with executing an
|OVRDBF immediately before the SELECT ... Into statement.
|
|/Free
| MyCLStm = 'OVRDBF X ....'
| QCmdExc(MyCLStm: %Len(MyCLStm));
| Clear w_Count;
| Exec SQL Select Count(*) into :w_Count from x;
| //Check for SQLState/SQLSTT or SQLCODE/SQLCOD
|/End-Free
|
|Mit freundlichen Grüßen / Best regards
|
|Birgitta Hauser
|
|"Shoot for the moon, even if you miss, you'll land among the stars." (Les
|Brown)
|"If you think education is expensive, try ignorance." (Derek Bok)
|"What is worse than training your staff and losing them? Not training them
|and keeping them!"
|
|-----Ursprüngliche Nachricht-----
|Von: midrange-l-bounces@xxxxxxxxxxxx
|[
mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Fleming, Greg (ED)
|Gesendet: Monday, July 02, 2007 23:06
|An: Midrange Systems Technical Discussion
|Betreff: Dynamic SQL
|
|
|I'm trying to prepare a dynamic SQL statement, and the "prepare" statement
|is giving me an error "host variable not defined or not usable".
|
|Here's the statement:
|
|<snip>
|
|w_SQLString = 'Select Count(*) into :w_Count From MKCUSW';
|
|Exec SQL Prepare w_SQLStmnt from :w_SQLString;
|
|Exec SQL Execute w_SQLStmnt;
|
|</snip>
|
|Ultimately, I'm planning to substitute a variable for the file name in when
|I populate my string, but since it wasn't working I simplified it to this
|version, and it still doesn't work.
|
|I know my :w_count variable is defined OK, because if I make it static SQL,
|this statement works:
|
|<snip>
|
|Exec SQL Select Count(*) into :w_Count from MKCUSW;
|
|</snip>
|
|Can I not use host variables in dynamic SQL ?
|
|
|
|Thanks
|
|
|
|Greg Fleming
|
|Senior Programmer/Analyst
|
|Everglades Direct, Inc.
|
|
|
|--
|This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
|To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
|or change list options,
|visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
|or email: MIDRANGE-L-request@xxxxxxxxxxxx
|Before posting, please take a moment to review the archives
|at
http://archive.midrange.com/midrange-l.
|
|
|
|
|
|--
|This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
|To post a message email: MIDRANGE-L@xxxxxxxxxxxx
|To subscribe, unsubscribe, or change list options,
|visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
|or email: MIDRANGE-L-request@xxxxxxxxxxxx
|Before posting, please take a moment to review the archives
|at
http://archive.midrange.com/midrange-l.
midrange.com
