Re: Setting the sql state and message in an external stored procedure...

Hi Alan,

It think I have the parameter lengths right.

From the V7.1 manual SQL Reference

(CREATE PROCEDURE)...
PARAMETER STYLE
v A CHAR(5) output parameter for SQLSTATE.
v A VARCHAR(517) input parameter for the fully qualified procedure name.
v A VARCHAR(128) input parameter for the specific name.
v A VARCHAR(1000) output parameter for the message text.

Could this have changed with releases? I checked my V5R4 SQL Reference
manual and the lengths are the same as above.

Also, I do definite the parameters as varying.

Thanks,

Robert Rogerson

On Fri, Aug 26, 2011 at
6:10 PM, Alan Campin <alan0307d@xxxxxxxxx> wrote:

I wonder if you are defining the parameters correctly.

Here is the definition from my iDate function.

p IDAT_Packed_Default...
p b Export
d pi
d InPackedValue...
d 8p 0
d OutDate...
d Like(StdDat)
d InDataNull...
d Like(StdIntSml)
d OutNull...
d Like(StdIntSml)
d OutSQLState...
d Like(StdSQLState)
d InFunctionName...
d 139a Varying
d InSpecificName...
d 128a Varying
d OutDiagnosticMessage...
d 70a Varying

I am assuming that you do not have a return value so the Out fields would
not be there. I think the fields need to be Varying,, not *VARSIZE.

Also you might want to think about defining your programs as service
programs. They are a lot faster.

Create Function iDATE(Decimal(8,0))
Returns Date
Language RPGLE
External Name 'IL/XVIDAT(IDAT_PACKED_DEFAULT)'
Specific IDAT_D8
Deterministic
No SQL
Parameter Style SQL
Allow Parallel
No External Action;


On Fri, Aug 26, 2011 at 3:35 PM, Robert Rogerson <rogersonra@xxxxxxxxx

wrote:

Hi All,

When I call an external stored procedure I want to set the SQL state

and

error text when an error occurs. Currently my program is setting the

SQL

state correctly but it is cutting off the error text.

CALL RROGERSON1/GETSALES('A')

SQL State: 38I99
Vendor Code: -443
Message: [SQL0443] SPR0055: An unexpecte

I can't see why the text is being cut off. Any suggestions?

This is the code for my SP
CREATE OR REPLACE PROCEDURE RROGERSON1/GETSALES (
IN LISTTYPE VARCHAR(1) )
DYNAMIC RESULT SETS 1
LANGUAGE RPGLE
SPECIFIC RROGERSON1/SPR0055
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'RROGERSON1/SPR0055 '
PARAMETER STYLE SQL ;

And this the code to the external program.
Hoption(*nodebugio:*srcstmt)
D SPR0055 pr
D pr_ListType 1a varying const
D pr_ListType_ni...
D 5i 0 const
* Define the parameters required to throw an SQL Error
d pr_SQL_State 5a
d pr_SQL_Function...
d 517a const varying

options(*varSize)

d pr_SQL_Spec...
d 128a const varying

options(*varSize)

d pr_SQL_Msg 70a varying options(*varSize)

D SPR0055 pi
D pr_ListType 1a varying const
D pr_ListType_ni...
D 5i 0 const
* Define the parameters required to throw an SQL Error
d pr_SQL_State 5a
d pr_SQL_Function...
d 517a const varying

options(*varSize)

d pr_SQL_Spec...
d 128a const varying

options(*varSize)

d pr_SQL_Msg 70a varying options(*varSize)
*
D psDs sds
D PROGNAME *PROC
D jobid 244 269

/free
pr_SQL_State = '38I99';
pr_SQL_Msg = %Trim(PROGNAME) +
': An unexpected Program error occurred.';
*inlr = *on;
/end-free

Thanks all,

Robert Rogerson
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.