Re: Dynamic SQL using Parameter Markers

[R Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>]

Parameter markers are not supported for the schema and table names. That 
would require a prepare before each execution.


Ellsberry, Steve wrote:
> 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
>
>  
>
>