Your dynamic SQL Statement is not prepared because the Table does not exist?
... you have to trap SQLSTATE 70003
If you want to check whether a table exists in a specific library / schema,
why not accessing the SYSTABLES catalog view?
You don't even need a dynamic SQL Statement.
Something like this
Select 1 into isFound
Where Table_Name = 'YOURTABLE' and Table_Schema = 'YOURLIB';
BTW are you sure you don't want to set the Error message within the Compound
Statement of the Handler?
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
dlclark@xxxxxxxxxxxxxxxx
Sent: Freitag, 9. Februar 2018 08:30
To: Midrange-L <midrange-l@xxxxxxxxxxxx>
Subject: Error Checking in SQL Stored Procedure is not Working
Based on my other post, I dumped my compound SQL statement into a
stored procedure so that I could run it through the SQL interactive
debugger. What I found is that a PREPARE statement is failing (based on
message SQL0204 in the joblog) but the SQL state is coming back as all zero.
Thus, my code goes on to EXECUTE the prepared statement and fails with SQL
state 07003 because the statement didn't actually get prepared.
How can I resolve this?
Basically, this code is checking to see if a file is in a particular
library, and if it is not, it is supposed to loop to the next company and
check again.
Begin
Declare Continue Handler for SQLSTATE '42704' Begin End; -- notfnd
Set ErrText = 'Checking for LIFO Details backup failed.';
Set CompanyNo3 = SubStr(CompanyNumber,3,3);
Set SqlStmt = 'Values('
||'Select 1 FROM LIFOLIB/IMLFDTL'
||CompanyNo3
||' Fetch First Row Only'
||') Into ?';
Prepare CheckBackup_Stmt
from SqlStmt;
Get Current Diagnostics Condition 1 CurState = Returned_SqlState;
If Substr(CurState,1,2) <> '00' Then
Set BackupPresent = NULL;
Else
Execute CheckBackup_Stmt
using BackupPresent;
Get Current Diagnostics Condition 1 CurState =
Returned_SqlState;
If Substr(CurState,1,2) <> '00' Then
Set BackupPresent = NULL;
End If;
End If;
Release CheckBackup_Stmt;
End;
If BackupPresent is NULL Then
Iterate Company_Loop;
End If;
Sincerely,
Dave Clark
--
int.ext: 91078
direct: (937) 531-6378
home: (937) 751-3300
Winsupply Group Services
3110 Kettering Boulevard
Dayton, Ohio 45439 USA
(937) 294-5331
****************************************************************************
*****************
This email message and any attachments is for use only by the named
addressee(s) and may contain confidential, privileged and/or proprietary
information. If you have received this message in error, please immediately
notify the sender and delete and destroy the message and all copies. All
unauthorized direct or indirect use or disclosure of this message is
strictly prohibited. No right to confidentiality or privilege is waived or
lost by any error in transmission.
****************************************************************************
*****************
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
