Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved PB 2019 and Informix Database Connection

  1. How-to
0
Votes
Undo
  1. Jeff Nesler
    Jeff Nesler
  2. PowerBuilder
  3. Monday, 24 August 2020 02:04 AM UTC

Hello,

Environment info:
Powerbuilder 2019 R2 Build 2353
Database: Informix 12.10.FC13 running on Linux server
Informix client: 4.10.TC14 (32 bit) and 4.10.FC12W1 (64 bit)
PB App and Informix Client running on Windows 2008 Server R2 (Service Pack 1)

EDIT:

2 part question:

1. Does anyone have any advice about switching from the native PB Informix driver I10 INFORMIX-10.0 to using ODBC to connect? Any pitfalls, things to look out for, settings that are important or different between the two? We've been using the native PB Informix driver for 20 years and are contemplating switching because we cannot get PB to work properly with the latest releases of the Informix Client software. Not a lot of ODBC experience in our shop and we're hoping the switch will be relatively painless if we have to do it.

It turns out that ODBC driver sees Informix booleans as either a '1' or a '0', not as a 't' or 'f' as does PB's native driver, so it looks like ODBC is not an option for us as it would require code changes.

If anyone else is still using Informix and Powerbuilder together and using the native PB driver, have you experienced any problems upgrading to never versions of the Informix client, specifically issues with boolean data types in Informix? If so, how did you resolve those issues?

I posted this issue late last year and worked with PB support on it but was not able to find a resolution on our end. The problem is that Powerbuilder sees the Informix boolean as a char(1), a 't' or an 'f'. PB and the older Informix client software did this translation seamlessly whereas with newer versions of the client we get strange/garbled data back for booleans and any columns that come after them in the result set. Connecting PB apps through ODBC doesn't produce the garbled data in the result set and behaves the same way regardless of Informix client (it returns '1' or '0' instead of 't' or 'f').  Our .Net apps work fine through an OleDB connection, so it doesn't seem to be a general problem with the Informix client software. We've tried setting things like db_locale, client_locale, different versions of the client software, all to no avail when connecting using the native PB Informix driver.

Any suggestions or info anyone can provide will be greatly appreciated.

Thanks,

Jeff

 

UPDATE 10/8/2020:

We did some additional research and testing and found that an older version of the Informix client, 4.10.TC2, does work properly with booleans and allows us to implement TLS 1.2.  That is our current workaround until PB Support can come up with a resolution that allows use of the latest Informix client.

  1. Database Driver
  2. ODBC
  3. PB Classic
How do I set the default printer?
How to save a MailMessage Object to the file syste...
Responses (3)
mike S
mike S Accepted Answer Pending Moderation
  1. Tuesday, 25 August 2020 15:08 PM UTC
  2. PowerBuilder
  3. # 1

"Our .Net apps work fine through an OleDB connection"

did you try oleDB with powerbuilder?

Comment
Jeff Nesler
  1. Jeff Nesler
  2. Tuesday, 25 August 2020 17:16 PM UTC
Mike,



I tried and could not get connected successfully using Ole DB. Got a message saying "DBMS is not supported in your current installation". Googled around a bit and saw it might have something to do with a 64 bit driver?
  1. Helpful
mike S
  1. mike S
  2. Tuesday, 25 August 2020 18:11 PM UTC
you also have to have the drivers installed that you are using. you also have to have matching bitness in your drivers with any application (PB or anything else).



  1. Helpful
There are no comments made yet.
Kai Zhao @Appeon
Kai Zhao @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 25 August 2020 07:46 AM UTC
  2. PowerBuilder
  3. # 2

Hi Jeff,

Thanks for reporting this problem! We reproduced the boolean data type issue you reported in ticket #3518 in the support portal and will do further research to figure it out. BTW, PB 12.6 has the same issue.

We will keep you posted on the progress in the support portal.

Regards,
ZhaoKai

Comment
There are no comments made yet.
Stuart Macandrew
Stuart Macandrew Accepted Answer Pending Moderation
  1. Tuesday, 25 August 2020 02:53 AM UTC
  2. PowerBuilder
  3. # 3

Hi Jeff,

You have a similar environment to us - we run PB 2017 R3 against Informix 12.10.FC10.

We ran ODBC for many years, until we migrated our database to UTF8 about 5 years ago -  the ODBC driver we were using then was not UTF8 capable, so we changed to the native driver. So for the last five years or so we have been running the native Informix driver with no issues.

In relation to booleans, I can recall having some minor considerations. 't' or 'f' are the underlying types for Informix boolean. Nothing that could not be easily worked around though.

Checking our source as to what changes we made in our utf8 migration (this is from memory);

- RPC functions (Local External Functions) in SQLCA handle booleans with no issue e.g. as a return value

- Stored procedures declared in Powerscript SQL did not handle boolean. We changed the calling code to expect a char as a return code ( 't' or 'f' ) and interpreted accordingly. Or we moved the proc declarations to SQLCA RPC declarations. Or we altered the stored procedure and removed the boolean.

- Procedures declared in Powerscript SQL - the syntax is slightly different as it expects ( ) characters surrounding procedure arguments in the declarations. We changed all the declarations accordingly.

- I can't see any datawindow source changes, so I am guessing that boolean was handled fine as a datatype in the result set. Or maybe we just do not do that - I cannot recall.

I have not noticed any differences in boolean handling between Powerbuilder versions using the Informix native driver. I have not tested PB 2019 though.

It sounds like you just need reassurance that the issues you are having with the native driver can be worked around. I think they can be.

Feel free to ask further questions.

Stuart

Comment
Jeff Nesler
  1. Jeff Nesler
  2. Tuesday, 25 August 2020 17:31 PM UTC
Stuart,



Thanks for the reply, good info especially the UTF8 info, I'll have to check if anything like that has changed on our side. We also recently moved from Solaris to Linux on the server that hosts Informix.



Are you using the IBM Informix Client SDK or SetNet on your machines where your apps are deployed? If so, do you mind sharing what version you are using? Our problems started when we migrated to version 4.10.TC13 and above.



I have confirmed that if we use a CAST() function to explicitly cast booleans to char(1) in the select statement, it seems to work fine, no data loss and/or garbled data after those columns. That is our final workaround if we can't resolve it without code changes.



Thanks,

Jeff
  1. Helpful
Stuart Macandrew
  1. Stuart Macandrew
  2. Thursday, 27 August 2020 20:26 PM UTC
I don't understand what you mean by the Informix client-sdk or setnet32 - the setnet32 util is part of the sdk. Anyway our version in prod use is clientsdk.4.10.TC6DE - that is the 32-bit version. That seems to date from early 2017. It is only that old as we have not reviewed recently, and not because I am aware of any problems with later revisions. The Informix client sdks have been fairly solid in the past, but I know we have skipped troublesome revisions in the past and we test all our db interactions if we change revisions. I have taken aboard your warning re TC13.

Our Informix hosts are still Solaris.

  1. Helpful
Jeff Nesler
  1. Jeff Nesler
  2. Friday, 28 August 2020 05:00 AM UTC
Thanks, that's exactly what I needed to know. I am currently testing with 4.10.TC2 and it seems to be working with booleans but will look into trying TC6DE and see if suits our needs. Our main issue is finding a version that will support TLS 1.2 and still works with the booleans. I agree the SDK's have been solid in the past, we've been using them for the better part of 20 years and have not had many issues until recently with the newest versions of the SDK.



Thanks again for the helpful response and information.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.