SQL stored procedure returning @@@@@@@

The place for general PHP questions and hints for PHP on IBM i
Post Reply
jstagliano
Posts: 9
Joined: Fri Jan 20, 2012 11:25 pm

SQL stored procedure returning @@@@@@@

Post by jstagliano » Tue Nov 04, 2014 6:14 pm

I have a stored procedure that has 1 input variable (character(10)) and 1 output variable (numeric(9,0))

When I call the sp from ops navigator, it has the correct value for my output value.

when I call the stored procedure from php, it returns @@@@@@@.

Does anyone know what I may be doing wrong? Is there a setting somewhere?

<?php
require_once 'i5DBconn.php';
$KEYNM = 'RA10MQWKEY';
$KEYVAL = 0;
if (isset($_SESSION['customer'])) {
$CUSTOMER = $_SESSION['customer'];
}

/* Prepare, bind and execute the DB2 SQL statement */
$sqlKEY = 'call mqwlibr.Get_Next_Key(?, ?)';
$stmtKEY = db2_prepare($i5DBconn, $sqlKEY);

db2_bind_param($stmtKEY, 1, "KEYNM", DB2_PARAM_IN);
db2_bind_param($stmtKEY, 2, "KEYVAL", DB2_PARAM_OUT);

$result = db2_execute($stmtKEY);
if (!$result) {
echo 'The db2 execute failed. ';
echo 'SQLSTATE value: ' . db2_stmt_error();
echo ' Message: ' . db2_stmt_errormsg();
}

echo "Key Name = $KEYNM --- Key Value = $KEYVAL";
?>

scottgcampbell
Posts: 187
Joined: Wed Apr 22, 2009 2:29 pm
Location: Edmonton, AB, Canada

Re: SQL stored procedure returning @@@@@@@

Post by scottgcampbell » Tue Nov 04, 2014 10:37 pm

Try something like this

select 'ABCD',1 FROM library.table

And display the results for one row (use a where clause in the SQL)

Where library.table is the same table that the stored procedure is coming from.
If that returns "garbage" try

select CAST('ABCD' AS CHAR(4) CCSID 37), 1 FROM library.table

If that works it is a CCSID problem (DSPSYSVAL SYSVAL(QCCSID)), and you will either have to change your system CCSID or change the CCSID for the user that connects to the database (unless you use more than one).

If that isn't the issue then post the results.

I've changed the CCSID on our dev box (a few years ago I think, to 37) with no problems, but haven't had the guts to do it in production yet so I just changed the one user that we use to connect to the database.

CHGUSRPRF USRPRF(someone) CCSID(yourccsid_here)

Scott

jstagliano
Posts: 9
Joined: Fri Jan 20, 2012 11:25 pm

Re: SQL stored procedure returning @@@@@@@

Post by jstagliano » Thu Nov 06, 2014 8:45 pm

I found that if I "select field ...", (in a stored procedure or directly in my php), everything is good.

However, when I "select coalesce(field, ' ') ...", (in a stored procedure or directly in my php), it will return garbage.

Very strange!!!

scottgcampbell
Posts: 187
Joined: Wed Apr 22, 2009 2:29 pm
Location: Edmonton, AB, Canada

Re: SQL stored procedure returning @@@@@@@

Post by scottgcampbell » Fri Nov 07, 2014 5:14 pm

You can do the cast around the coalesce also

CAST(coalesce(comdes,' ') AS CHAR(100) CCSID 37)

If you need a work around. I have tested it on both of my environments and it works the same with or without the CAST so I would assume it has to do with your CCSID.

Scott

mark397
Posts: 87
Joined: Thu May 30, 2013 6:12 pm

Re: SQL stored procedure returning @@@@@@@

Post by mark397 » Fri Nov 21, 2014 10:27 pm

I had a similar issue and this was found in DBMON:
Message ID . . . . . . : SQL0332 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic

Message . . . . : Character conversion between CCSID 65535 and CCSID 1208
not valid.
Cause . . . . . : Character or graphic conversion has been attempted for
data that is not compatible. There is no conversion defined between CCSID
65535 and CCSID 1208.
If one CCSID is 65535, the other CCSID is a graphic CCSID. Conversion is
not defined between 65535 and a graphic CCSID.
Recovery . . . : Ensure that all character or graphic comparisons,
concatenation, or assignments are between columns or host variables with
compatible CCSID values.
The problem is string literals, whether it's in the COALESCE, a RIGHT or LEFT, or a concatenation. It's only an issue if the string literal is part of the SELECT, so WHERE or JOIN including literals is fine. As a workaround use the CAST with CCSID 37, or set the job, user, or systemwide CCSID to 37.

Post Reply