Re: Dynamic SQL question

My approach has the select statement built entirely
before the prepare without using parameter markers, so
I don't use 'USING' on the OPEN.  That way I can build
the select anyway I want and the SQL code will be the
same.

Another thing you could do is have multiple opens, and
condition which one to use.  (I have my doubts,
however, that the precompiler will let you do this.)

Phil

--- "mlazarus@ttec.com" <mlazarus@ttec.com> wrote:
> Phil,
>
>  Here's a snippet of code.  I'm building SQLString
> dynamically.  I will have from 1 to X parameter
> markers (determined at runtime) on the WHERE clause.
>
>  My question refers more to the Open Using
> statement.  In this case I have 4 host variables
> specified, but since I won't know until runtime how
> many I need, how would you code the Open Using
> dynamically?  Or is there another approach that I
> can take?  Thanks.
>
>  -mark
>
>
> C/Exec SQL
> C+  Prepare SQLStatement From :SQLString
> C/End-Exec
>
> C/Exec SQL
> C+  Declare C1 Cursor For
> C+             SQLStatement
> C/End-Exec
>
> C/Exec Sql
> C+     Open C1 Using :S$SESN, :S$RRCD, :PURC, :S$SRS
> C/End-Exec
>
> C/Exec Sql
> C+     Fetch C1 Into :HostStr
> C/End-Exec
>
>  *  DoW Not EOF
> C                   DOW       SQLStt <> '02000'
>
> C                   WRITE     FWSCHWKR
>
> C/Exec Sql
> C+     Fetch C1 Into :HostStr
> C/End-Exec
>
> C                   ENDDO
>
> C/Exec Sql
> C+     Close C1
> C/End-Exec
>
>
>
> ----------------
> below is a subprocedure which builds a host variable
> called SQLStmt (a 1024 varying character field),
> prepares the statement using that host variable,
> declares the cursor using the prepared statement,
> then
> opens the cursor.  Is this what you're looking for?
>
> Phil
>
>      P SQL_PrpStmt     b
>       /FREE
>        SQLStmt =
>            'select * from prmorg +
>            where companyNumber = ' + %editc(cpy:'X')
> +
>            ' order by companyNumber,
> organizationName';
>
>       /END-FREE
>      C/exec sql
>      C+ PREPARE @SELECT FROM :SQLStmt
>      C/end-exec
>      C
>      C/exec sql
>      C+ DECLARE @CURSOR DYNAMIC SCROLL CURSOR FOR
> @SELECT
>      C/end-exec
>      C
>      C/exec sql
>      C+ open @cursor
>      C/end-exec
>      P SQL_PrpStmt     e
> >
> > >>> mlazarus@ttec.com 03/18/02 06:21PM >>>
> >   I would like to run a dynamic SQL statement with
> > an undetermined (at
> > compile time) number of placeholder variables.
> >
> >   Building the PREPARE string w/ the placemarkers
> is
> > not a problem.
> > My
> > question is how do I specify the "OPEN cursor
> INTO"
> > dynamically?
>
>
>
--------------------------------------------------------------------
> mail2web - Check your email from the web at
> http://mail2web.com/ .
>
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries
> (RPG400-L) mailing list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com
> Before posting, please take a moment to review the
> archives
> at http://archive.midrange.com/rpg400-l.
>


__________________________________________________
Do You Yahoo!?
Yahoo! Sports - live college hoops coverage
http://sports.yahoo.com/