RE: Strange SQL

[MWalter@xxxxxxxxxxxxxxx]

Here is the code that seems to be failing.


?    C     @prepare_stmt BEGSR
?
?     * what is the sort order
?    C                   SELECT
?    C                   WHEN      sortorder = '1'
     C                   EVAL      sortField = 'a.itnbr'
?    C                   WHEN      sortorder = '2'
     C                   EVAL      sortField = 'a.itdsc'
?    C                   WHEN      sortorder = '3'
     C                   EVAL      sortField = 'b.itcls'
?    C                   ENDSL
?
?     * build the statement
     C                  EVAL      statement = 'SELECT
a.itnbr,b.itdsc,a.edate,'
     C                  EVAL      statement = %trim(statement) +
     C                             'a.itcls, b.lstum, a.price,
b.unmsr,b.itcls'
     C                  EVAL      statement = %trim(statement) +
     C                             ' FROM Prcmst a'
     c                  eval      statement = %trim(statement) +
     c                            ' LEFT OUTER JOIN Itemasa b'
     C                  EVAL      statement = %trim(statement) +
     C                             ' ON a.itnbr = b.itnbr'
     C                  EVAL      statement = %trim(statement) +
     C                             ' WHERE cusno = ' + %editc(cusno:'Z')
     C                  EVAL      statement = %trim(statement) +
     C                             ' ORDER BY ' + sortField


     C/exec sql
     C+ prepare stmt from :statement
     C/end-exec
?    C                   ENDSR
?     /eject

?    C     @open_cursor  BEGSR
?    C/exec sql
     C+ declare C1 cursor for stmt
     C/end-exec
      /eject

     C/exec sql
     C+ open C1
     C/end-exec
?    C                   ENDSR
?     /eject

?    C     @fetch_record BEGSR
?    C/exec sql
     C+ fetch C1 into :sitnbr, :itdsc27, :date8, :clswrk, :lstum,
     C+  :sprice, :ituom, :itclsm
     C/end-exec
     C                                 ENDSR

If I run the code in debug and stop it directly above, At the ENDSR
statement, I can view the SQL_000XX variable and it contains the correct
value but there is a  statement placed by the precompiler that says that;
C                                           IF                    SQL_00009
= '1'
... move the host variables
C                                           ENDIF

the SQL_00009 is = '0' Here is where I checked the SQLCOD variable it was a
-305 and the SQLSTT was '22022'

Thanks,

Mark


Mark Walter
Sr. Programmer/Analyst
Hanover Wire Cloth a div of CCX, Inc.
mwalter@hanoverwire.com
http://www.hanoverwire.com
717.637.3795 Ext.3040



                    Buck Calabro
                    <Buck.Calabro@com        To:     rpg400-l@midrange.com
                    msoft.net>               cc:
                    Sent by:                 Subject:     RE: Strange SQL
                    rpg400-l-admin@mi
                    drange.com


                    09/12/01 04:46 PM
                    Please respond to
                    rpg400-l






>there is code that the precompiler inserts that
>will not allow the results to be placed in my
>host variables. I can see the value that I need
>using debug. It is one of those SQL_000XX variables.

Perhaps you could post that section of code.  and the error it throws at
run
time.  It looks like a mis-match between CCSIDs on the two libraries.  Does
a comparison of DSPFFDs give you any clue?

Buck
_______________________________________________
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.