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

["Steve Cavill" <steve.cavill@infoclarity.com.au>]

Hi Manuel,
 I think your problem is that you are getting 2 error messages from DB2 and
SQR is only displaying the last of the 2.
The FIRST error is that the SQL won't compile:

(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
 
The SECOND error is that the SQL can't run (because it is not prepared)
(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

The $sql-error does contain that whole message (although I don't know why it
seems to cut off the "t" in "statement".

Because there are two errors, the $sql-error from the first error gets
overwritten by the 2nd error before SQR gets a chance to report the error.
This is normal behaviour for SQR.  $sql-error only ever contains the most
recent error message.

Can you trap the first (prepare) error?  I doubt it.  If you want to see the
full error messages you could use the -s flag which sends to sql to the .err
file for later analysis.

Note that for runtime error checking I think you are better off using
#sql-status, as the messages themselves can contain a lot of variable text
which is hard to check for.
In your case error -518 indicates the sql could not be compiled.  This is
not really a "runtime" error as the "[" in your sql will never compile.  But
you can still print out the values of all the variables yourself to
determine what is wrong with the SQL, and keep processing further records.

Regarding your actual SQL, I assume that $Incr_Effdt and #Job_Insert_Effseq
are hardcoded values you want to use in your select.  In which case you
should be able to just use the variable names without the [] brackets.  The
[] syntax only applies in begin-select.

And you are right the &variable are fine and the from, where formatting is
all irrelevant in a begin-sql block, the special sql formatting rules only
apply in BEGIN-SELECT.

Cheers, Steve.


-----Original Message-----
From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org
[mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] On
Behalf Of Manuel Basil Arakkal
Sent: Sunday, 2 January 2005 7:20 AM
To: bstone@fastenal.com
Cc: sqr-users@sqrug.org
Subject: RE: [sqr-users] In DB2 $sql-error not displaying the complete
errormessage

I had basically added the code with an error in it in my email to display
the fact that $sql-error does not give the full description when an error is
encountered.

You have said that the error message gives enough information. However the
error message with the full information was obtained without using the
$sql-error. If we use the $sql-error then the error message is not displayed
fully and that is my problem. We would like to have $sql-error display the
full error message so that we could take action based on the error message.

We could use on-error to get information on which row is being processed by
the SQR. However since $sql-error does not display the full error in DB2
this is not possible.

There should be no problem with using &variables within the SQL statement. 
These variables are used to pick up values from other SQL statements in the
same SQL without assigning them to $variables. There should also be no
problem in indenting FROM and WHERE clauses.

Manuel



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

----------------------------------------------------------------------------
----

Subject: RE: [sqr-users] In DB2 $sql-error not displaying the complete error
message
From: Bob Stone <bstone@fastenal.com>
Date: Thu, 2 Dec 2004 08:39:49 -0600
Delivery-date: Thu, 02 Dec 2004 09:40:58 -0500
List-id: "This list is for discussion about the SQR database
reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>

----------------------------------------------------------------------------
----

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



----------------------------------------------------------------------------
----

Prev by Date: [sqr-users] Printing data in next page using on-break Next by
Date: RE: [sqr-users] MAX() Query Previous by thread: [sqr-users] Printing
data in next page using on-break Next by thread: [sqr-users] How to extract
data in an SQR
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page



_______________________________________________
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