0

I have a stored procedure where I"m passing a value (from CHARACTER(13) with CSSID 65535 and trying to return an ID by it

The procedure is: 

BEGIN

DECLARE GET_ID_BY_WEB_IDENTIFIER_C1 CURSOR WITH RETURN FOR
SELECT ID FROM TABLE . ITEMT WHERE WEB_IDENTIFIER = P_WEB_IDENTIFIER ;

OPEN GET_ID_BY_WEB_IDENTIFIER_C1 ;
END

and the parameter P_WEB_IDENTIFIER Is a CHARACTER(26) with a matching cssid. I've also tried this with it being a character 13 with same results.

When I call this:

CALL PROGRAM . GET_ID_BY_WEB_IDENTIFIER (x'0213725501A421B9A457123001')

I get back my ID, but when I call like this (as it is called in production through the web):

 CALL PROGRAM . GET_ID_BY_WEB_IDENTIFIER ('0213725501A421B9A457123001')

I get a conversion error

How can I make sure that passing only the string '0213725501A421B9A457123001' will return my ID this way?

Geoff_S
  • 4,917
  • 7
  • 43
  • 133

1 Answers1

0

This x'0213725501A421B9A457123001' is a hexadecimal string literal.
From a quick glance it represents mostly non-printable characters.

'0213725501A421B9A457123001' is an actual string.
It's hexadecimal equivalent on db2 would be x'F0F2F1F3F7F2F5F5F0F1C1F4F2F1C2F9C1F4F5F7F1F2F3F0F0F1'

Big difference...

Your web app needs to pass it as a hex literal also, be sure to define the stored proc parm as CCSID 65535 (Binary/no-translation)

Optionally you could convert from the string value to the hex(binary) value inside the stored proc. The OS has some built in functions to do so, for an RPG example, look here https://stackoverflow.com/a/58997608/2933177

Charles
  • 21,637
  • 1
  • 20
  • 44
  • so how can I call the procedure like I need to then? If I call it with the 'x' in front of it, it finds the record and returns the ID – Geoff_S Feb 21 '20 at 22:54
  • well I"m calling through PHP but I'd prefer to do it in the stored procedure in DB2 on the 400 – Geoff_S Feb 21 '20 at 23:00