Re: Dynamic SQL using Parameter Markers

First of all, sorry for the length of this email but I wanted to give as
much detail as I could so as to head off a million questions afterwards.


Now to the problem.


Short version
Anybody have any idea what specific release/PTF level I need to be at
(if any) in order to use Parameter Markers with dynamic SQL? All our
boxes are at v5r3m0.


Long version


I've written a simple dynamic SQLRPGLE program that will be used to
insert data from a file within our current release to the same file in
our next release. Both files have the same name but reside in 2
different libraries. The library names will differ from market to
market.

We've added some additional fields but I couldn't use a simple CPYF w/
*MAP *DROP because we've changed one of the fields from numeric (2,0)
to alpha 5. We need that field to be formatted with a leading zero if
the current value is single digit - easily accomplished with casting.

The problem I have is the library names for both files will differ from
market to market. To get around that I figured I use parameters markers
and make it dynamic.

Here's the SQL statement as defined in my D specs

d SQLStatement s 1024 Inz('Insert into ? select
-

d CONBR , SITE , ACTNBR, SCRID
,-

d DIRNBR, TSEQNO, 0 ,
ELGTYP,-

d UPGSTS, UPGTYP, TRNDTE,
UPGDTE,-

d USRADD, CCODE , FUTDAT,
NOTSTS,-

d NOTDAT, SMSEML, NEWCHK,
SLCCHN,-

d
cast(substring(digits(QUALVL),1-

d ,2) as char(5)) as qualvl,
-

d QUADSC, QUAWUF, QUACON,
COMINT,-

d COMLEN, LVLCOD, MINMRC,
EQPDSC,-

d DSCAPL, CONLEN, WAIVEF,
ORSELP,-

d ACSELP, OVRRSC, OVRRST,
SOFUID,-

d UPGSEC, '' '' , '' '' , '' ''
,-

d '' '' , LSMODE, USCODE,
LSTPGM,-

d DTSTMPZ from ?')


The question marks are standard parameters markers for the from and to
library/file names which I build in the program. Those are defined as:

d FromPath s 21 Inz

d ToPath s 21 Inz


I have a variable called filename that I initialize as ('/custmast') I
pull the From/To libraries from a data area. I build the path names as:

FromPath = %Trim(Fromlibrary) + %Trim(FileName);
ToPath = %Trim(Tolibrary) + %Trim(FileName);


I Prepare the SQL

/End-Free

c/Exec Sql

c+ Prepare @SQLStatement from :SQLStatement

c/End-Exec

/Free

I execute the SQL

/End-Free

c/Exec Sql

c+ Execute @SQLStatement Using :FromPath, :ToPath

c/End-Exec

/Free



The program compiles with no errors. But I run it I get sqlcode = -104
(syntax error) and sqlerrmrc with a value of ? <IDENTIFIER>. If I run
it in debug and copy the sql statement and the variables built in the
program then run it interactively the sql runs to completion. That tells
me I'm not missing a comma so something like that. It also tells me I've
formatted the path name correctly.

I've read the DB2 UDB manual. I've read Kevin Forsythe's SQL for eServer
and iSeries. Both say I can do it but.....


Anybody have any suggestions?



Thanks,

S. Ellsberry