CODEWITHSUNDEEP
sqldb2
Geoff_S
Geoff_S

Reputation: 5105

Trying to query the database with an encoded (CCSID) web response

IN my post response on my website I'm getting an identifier 0213725502A33A32DAACAEB001 and passing it to a query in a where clause so that I can get other data about the record using it. THe database has the identifier column as Character 13, and the above is obviously 26 bytes.

The problem is that my sql returns a null value for this. If I copy it and paste it into the query locally it is null, but if I put x'0213725502A33A32DAACAEB001 ' then it returns the data I want.

How can I send this data from the web into my sql and get it to correctly match the encoded database version so that it returns my records?

This works:

select * from tableOne.testing where CAST(identifier AS character(26) CCSID 65535) = x'0213725502A33A32DAACAEB001'  limit 5;

This doesn't

select * from tableOne.testing where CAST(identifier AS character(26) CCSID 65535) = '0213725502A33A32DAACAEB001'  limit 5;

Upvotes: 1

Views: 362

Answers (1)

Paul Vernon
Paul Vernon

Reputation: 3901

This might be what you need

select * from tableOne.testing
where CAST(identifier AS character(26) CCSID 65535) = 
   CAST('0213725502A33A32DAACAEB001' AS character(26) CCSID 65535)
limit 5;

or 

select * from tableOne.testing
where CAST(identifier AS character(26) CCSID 65535) = 
   CAST('0213725502A33A32DAACAEB001' AS character(26) FOR BIT DATA)
limit 5;

You could also use a user defined function, as per https://stackoverflow.com/a/42371427/9525344

Upvotes: 1

Related Questions