RE: Embedded SQL --> Prepare

Phil,

I have a feeling is that the table name and the column lengths, etc may be
mandatory. I agree with David.  Instead of using the parameter marker, I
think you can just build the complete Create Table statement and then
execute it using the EXECUTE IMMEDIATE.

I think the following code may work:

C                   eval      SQLStmt =
C                             'create table qtemp/'+
C                             %trim(file) +
C                             ' (record character (' +
C                             %editc(length:'X') +
C                             ') not null with +
C                              default)'
C                   eval      prepared = *on
C                   endif
C/exec sql
C+ execute immediate :Sqlstmt
C/end-exec

HTH.

-----Original Message-----
From: Phil Groschwitz [mailto:sublime78ska@yahoo.com]
Sent: Friday, February 01, 2002 12:28 PM
To: rpg400-l@midrange.com
Subject: Re: Embedded SQL --> Prepare


That's what I'm doing.  I'm hoping to find out whether
it is permissible or not, mainly to improve my
knowledge of SQL.

PHil


--- David Morris <David.Morris@plumcreek.com> wrote:
> Phil,
>
> Are you creating thousands of tables? If not I would
> just
> use execute immediate and pass the full statement
> that
> would be built using RPGIV's swell string support.
> As I
> recall, you do have to pass a fixed length string
> (no
> varying) to execute immediate. It may not be a hard
> requirement, but I am certain there is a bug related
> to
> VARYING fields and RPGLE SQL.
>
> David Morris
>
> >>> sublime78ska@yahoo.com 02/01/02 09:25AM >>>
> I am trying to do prepare a CREATE TABLE statement
> (code is below) using parameter markers for the
> table
> name and the record length.  I get a runtime error
> that the token ? is invalid.
>
> Have I done something wrong here or is it not
> permissible to use parameter markers for these
> values?
>
> Thanks,
>
> Phil
>
> P SQLCrtTable    b
> D SQLCrtTable     pi
> D  file                         10    value
> D  length                        3  0 value
> D len             s              3
> D SQLStmt         s            256
> D prepared        s               n   static
> C                   if        prepared = *off
> C                   eval      len =
> %editc(length:'X')
> C                   eval      SQLStmt =
> C                             'create table qtemp/?
> +
> C                             (record character ( ?
> )
> +
> C                              not null with +
> C                              default)'
> C/exec sql
> C+ prepare crtTblStmt from :SQLStmt
> C/end-exec
> C                   eval      prepared = *on
> C                   endif
> C/exec sql
> C+ execute crtTblStmt
> C+    using :file, :len
> C/end-exec
> C                   return
> P SQLCrtTable     e
> _______________________________________________
> 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!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.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.