Informix query against en_us.918 database from utf databse connection through DRDA - error code: 4220, SQL state is null

1
votes

We are connecting to a Informix database of Unicode through DRDA port.

And we have another database whose locale is English locale (en_us.819).

We are executing a query on English database using the connection to Unicode database (like dbname:'informix'.tablename).

We identified that this is happening when query try to retrieve 'XIC¢LILY NINETH' value which is in English locale database.

And same did worked fine with ODBC connection. 

Error code : -4220, SQL State: null

Any help/pointers/workarounds please!

Information from the comments

I am working with two databases with different db locale, say:

  • Database1 -> en_us.819 (English)
  • Database2 -> en_us.57372 (UTF8)

I am using DB2 common server client to connect to database (means DRDA connection).

  • Connect to Database1. Select * from user
    execution successful.
  • Connect to Database 2 Select * from Database1:’Informix’.User
    throws exception and it crashes the database.
1
utf-8informixremote-access
sorry, but your question is little confuse for me and missing important information. Like: what is the locale of your unicode database? what is the locale used at client? can you put a better example how is this sql? if you connect directly at the unicode database the sql works? or this test is this odbc connection mentioned? – ceinmart
Hi @ceinmart, I am working with two databases with different db locale, say: Database1-> en_us.189 (English) Database2 ->en_us.57372 (UTF8) I am using DB2 common server client to connect to database (means DRDA connection): Here are the queries and results: Connect to Database1. Select * from user - execution successful. Connect to Database 2 Select * from Database1:’Informix’.User – throws exception and it crashes the database. Please let me know if you need more details. Thanks, Phani – Phani Krishna Vemireddy
ok,now you mentioned something very very important: "crashes the database". This for sure is a bug/defect, if you have active support should open a PMR (check this question about IBM Support, which is a similar situation: stackoverflow.com/q/21047333/469192 ) – ceinmart
@ceinmart, Thank you. We did that, and applied patch on server. Now no crashes with the same query. but fields which have special characters like '¢' giving null results. Any ideas please? – Phani Krishna Vemireddy
We found that it is Java library(JCC) giving error. When we tried with C# program, '¢' character converted to '?' character and rest of the characters coming correctly. Even now we are getting few more errors, I'll keep posted once we get specific details on them. Thank you @ceinmart. – Phani Krishna Vemireddy

1 Answers

0
votes

It seems when connecting to Informix DB suing DRDO connection (through DB2 Data Server Client), it is not checking for any special characters that are not configured for Database. But, if you use Informix drivers, this will not allow you execute queries if query has few special characters like "small quotes", '¢' etc.

We have installed new patches on Database serves, it solved the problem.

Thanks!