RE: Preparing dynamic SQL and parameter markers -- RPG400-L

Vernon, Robert

Thanks for the advice, I never knew you could do that. I'll try it out
first thing in the morning.

All the best

Jonathan

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Vernon Hamberg
Sent: 04 February 2008 13:34
To: RPG programming on the AS400 / iSeries
Subject: Re: Preparing dynamic SQL and parameter markers

Jonathan

According to the reference manual, SELECT INTO cannot be dynamically
prepared.

Let me recommend VALUES INTO - this CAN be dynamically prepared, and
you can use a SELECT statement to feed it.

C Eval wSQL = 'Values (Select SUBSTR(CIFLD, 3, 8) ' +
C 'From ' + %Trim(wFileName) + ' ' +
C 'Where SUBSTR(CIFLD, 1,2)=''CI'' ' +
C 'and SUBSTR(CIFLD,19,18)=' +wQuote +
C MobileNo + wQuote) +
C ' Into ?'

See the docs for more info - like how the SELECT must return only a
single row - how you can use more than one host variable, etc.
At 07:00 AM 2/4/2008, you wrote:

Hi List

I'm trying to select a value from a flat file using embedded SQL and
because the filename is dynamic I believe I have to use a PREPARE
statement and provide a parameter marker for the variable I want to
select the value into.

My SQL statement is being built as:

C Eval wSQL = 'Select SUBSTR(CIFLD, 3, 8) '
+
C 'Into ? '
+
C 'From ' + %Trim(wFileName) + '
' +
C 'Where SUBSTR(CIFLD, 1,
2)=''CI'' ' +
C 'and SUBSTR(CIFLD,19,18)=' +
wQuote +
C MobileNo + wQuote

However when I run the PREPARE statement:

C/Exec SQL
C+ Prepare MySql from :wSQL
C/End-Exec

I am getting an error returned that says:

Message ID . . . . . . : SQL0104 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 04/02/08 Time sent . . . . . . :
12:07:31

Message . . . . : Token ? was not valid. Valid tokens: : <IDENTIFIER>.
Cause . . . . . : A syntax error was detected at token ?. Token ? is
not a
valid token. A partial list of valid tokens is : <IDENTIFIER>. This
list
assumes that the statement is correct up to the token. The error may
be
earlier in the statement, but the syntax of the statement appears to
be
valid up to this point.
Recovery . . . : Do one or more of the following and try the request
again:
-- Verify the SQL statement in the area of the token ?. Correct the
statement. The error could be a missing comma or quotation mark, it
could
be a misspelled word, or it could be related to the order of clauses.
-- If the error token is <END-OF-STATEMENT>, correct the SQL
statement
because it does not end with a valid clause.

Bottom

Am I wrong in thinking that this can be done? I came across an example
in the archives that looked like it was possible. I was hoping to avoid
having to use a cursor because there should only ever be one record
returned.

Thanks

Jonathan

_______________________________________________________
This message was sent using NOCC v1.14 webmail software
_______________________________________________________

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.