RE: [sqr-users] In DB2 $sql-error not displaying the complete error message

[Bob Stone <bstone@fastenal.com>]

Well, I think there are a few problems in your code that should help you fix
it.  The error you get gives you enough information, you just have to read
it.  It says it found a '[' and the SQR doesn't know what the heck that is.
So, you cant have the bracket in there...and, like the SQR, i'm not sure
what that is either.  It looks like you're trying to check a value, but dont
know if it will exists.  And if it does not exist, you want to stick the
variable in there.  Since I dont know DB2, i dont know what the fix is on
that.  In Oracle you can say 

select
nvl(col1, $var1)                ! <-- nvl - if no value (null value) of col1
then use $var1
from table where [$where_clause]

-------------the problem of your SQL
#ifdef MVS
,\$Incr_Effdt\
,\#Job_Insert_Effseq\
#else
,[$Incr_Effdt]
,[#Job_Insert_Effseq]
#end-if
--------------

Another problem in your SQR is at the bottom.  Recently someone reread the
rules on indentation, and I've already forgotten them, BUT, i'm gonna go
ahead and say that you shouldn't have indented the 'FROM' and 'WHERE'.  I
know its okay to indent the 'AND's (possibly the where too).  It maybe be
personal preference, but I like to keep my entire SQL statement flush to the
left margin...it makes for more easily readable SQR (in my opinion).

But the serious problem at the bottom are the &variables you're trying to
match against...You cant match against &variables in a SQL clause...you need
to have set them up as $variables. (let $var1 = &var1, etc).  If you're
trying to match them up as a 2 table where clause thing, then include both
tables in the FROM, and match them up in the WHERE.  

Once you figure out that # \\ [] problem up top, unindent your FROM/WHEREs,
and set up the variables as $variables, your SQR should work a lot better.

You dont necesarily need to get a better error message, you just need to
understand the one you got a bit better.  When it says 'An unexpected token
"[" was found following "" ' and you've got a [ character sitting in your
SQL, that should be a clue that it doesn't like that.  Also when it says
'SQL: ...WHERE EMPLID = ? AND EMPL_RCD = ? AND EFFDT = ? AND EFFSEQ = ?' you
might realize that it couldn't figure out what those &variable were supposed
to be.


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Manuel Basil Arakkal
Sent: Wednesday, December 01, 2004 5:35 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] In DB2 $sql-error not displaying the complete error
message


We have this code in our program:

Begin-Sql ON-ERROR=KP-SQL-ERROR('PS_JOB_EARNS_DIST - INSERT ',&JOB.EMPLID, 
#sql-status, $sql-error)
INSERT INTO PS_JOB_EARNS_DIST
SELECT
EMPLID
,EMPL_RCD
#ifdef MVS
,\$Incr_Effdt\
,\#Job_Insert_Effseq\
#else
,[$Incr_Effdt]
,[#Job_Insert_Effseq]
#end-if
,DEPTID
,JOBCODE
,POSITION_NBR
,GL_PAY_TYPE
,ACCT_CD
,SHIFT
,LOCATION
,ERNCD
,COMPRATE
,DIST_PCT
,STD_HOURS
,BUSINESS_UNIT

        FROM PS_JOB_EARNS_DIST
        WHERE EMPLID = &JOBI.EMPLID
        AND EMPL_RCD = &JOBI.EMPL_RCD
        AND EFFDT = &JOBI.EFFDT
        AND EFFSEQ = &JOBI.EFFSEQ
End-Sql

Begin-Procedure KP-SQL-ERROR($Table_Name, $Emplid, #sql-status, $sql-error)

    Let $sql-status = #sql-status
    Let $error_message = 'FATAL SQL ERROR. Table_Name:' || $Table_Name || 
'Emplid: '||$Emplid||'Error: '||$sql-error||' - '||$sql-status

    Show $error_message
    Stop

End-PROCEDURE

which produces the following message in the log file:

FATAL SQL ERROR. Table_Name:PS_JOB_EARNS_DIST - INSERT Emplid: KC0007     
Error: SQL0518N  The statement named in the EXECUTE statement is not in a 
prepared
state or is a SELECT or VALUES statemen

- -518.0000000000000000000000000000000000000000000000000

Error on line 3259:
   (SQR 3301) Program stopped by user request.

SQR for PeopleSoft: Program Aborting.

The $sql-error varaible does not give the complete error message.

When the ON-ERROR code is removed then we get the complete error message 
from DB2:

(SQR 5528) DB2 SQL PREPARE/DECLARE error -104 in cursor 37:
   SQL0104N  An unexpected token "[" was found following "".  Expected 
tokens may
include:  "( + - ? : CASE CAST USER <FLOAT> CURRENT <DECIMAL> <INTEGER> ".
SQLSTATE=42601

SQL: INSERT INTO PS_JOB_EARNS_DIST SELECT EMPLID ,EMPL_RCD ,'2000-12-16' ,[?
     ,DEPTID ,JOBCODE ,POSITION_NBR ,GL_PAY_TYPE ,ACCT_CD ,SHIFT ,LOCATION
     ,ERNCD ,COMPRATE ,DIST_PCT ,STD_HOURS ,BUSINESS_UNIT FROM
     PS_JOB_EARNS_DIST WHERE EMPLID = ? AND EMPL_RCD = ? AND EFFDT = ? AND
     EFFSEQ = ?
(SQR 5528) DB2 SQL OPEN/EXECUTE error -518 in cursor 37:
   SQL0518N  The statement named in the EXECUTE statement is not in a 
prepared
state or is a SELECT or VALUES statement.  SQLSTATE=07003

Error on line 2536:
   (SQR 3735) Could not execute SQL.

SQR for PeopleSoft: Program Aborting.

Is there some way I could get the complete error message from DB2 using 
ON-ERROR?

Thanks,
Manuel



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users