PowerBuilder

[husseinhussein]

We are currently doing a research to determine the impact of upgrading our POSSE applications from using cx_Oracle version 5.1.2 to 7.1.1 with Python version 2.7.1 and Oracle version 11.2.0.4 & 12.1.0.2. We have determined the following:

1. When a connection is initialized from a PowerBuilder application and passed to cx_Oracle connection using a handle, cx_Oracle raises the following error message when trying to fetch data from the database using the connection cursor. DatabaseError: ORA-00911: invalid character
2. The default character set used by the PowerBuilder application is Unicode.
3. The cx_Oracle encoding character set for the connection initialized from the PowerBuilder application is U.
4. Setting the PowerBuilder application NLS character set to Local, resolves the issue.

Do you mind giving us a quick brain dump of why cx_Oracle version 7.1.1 acts as described above?

Note that we are using PowerBuilder version 11.2.

Thank You So Much, Hussein Hussein Computronix Canada.

[cjbj]

Some of that may make sense to @anthony-tuininga given his previous life at Computronix, but for me what does "U" mean in point 3?

[husseinhussein]

Thanks for a quick reply.

Just to be clear, we modified cx_Oracle code to throw some debug lines and found out that the connection that was initialized from the PowerBuilder application has the value of "U" for encoding.

I think "U" stands for Unicode since it is the default character set for PowerBuilder applications.

[anthony-tuininga]

Is it possible that you are using "UTF-16" and you only see the first character as you are using %s in your print statement? Note that since you are providing a handle you also need to ensure that the character set that you provide cx_Oracle (via the encoding and nencoding parameters or via setting the environment variable NLS_LANG) matches the character set that PowerBuilder initially provided Oracle. If you don't you will get something like what you see. I'm not sure what "Local" means -- but if it means use NLS_LANG then that would make sense. :-)

[anthony-tuininga]

Sample code that demonstrates the problem is as follows:

import cx_Oracle

conn = cx_Oracle.connect("pythontest/welcome@localhost/T183",
        encoding="UTF-16", nencoding="UTF-16")

conn2 = cx_Oracle.connect(handle=conn.handle, encoding="UTF-8",
        nencoding="UTF-8")
cursor = conn2.cursor()
cursor.execute("select * from TestStrings")
for row in cursor:
    print(row)

This generates an error for me: cx_Oracle.DatabaseError: ORA-00900: invalid SQL statement which looks similar to what you are experiencing -- so I bet that's the source of your issue!

[husseinhussein]

Thank you for your response.

The print statement is printing correctly because when we set the PowerBuilder application to “Local” it printed the correct NLS_LANG environment variable.

The character set that the PowerBuilder application provides to Oracle is different than the one used by cx_Oracle when we are passing the handle for the connection.

Could you please help us understand why it works with cx_Oracle version 5.1.2?

Also, when was this change introduced?

Thanks, Hussein Hussein

[anthony-tuininga]

I took a closer look at the code as it existed in 5.1.2 and as it exists today. The main difference is that the code today uses the handle directly, but with a different environment handle -- and if the character sets differ you get strange errors. The code in 5.1.2 created a new service context handle and simply attached the server and session handles from the original service context handle to the new one. That way the new environment handle (with a potentially different character set) would work as expected.

This change was introduced in version 6 when the OCI code was moved from cx_Oracle to ODPI-C. It works fine when you're careful with the character sets -- but falls apart when they are different, as you noticed. I agree that the original behaviour is better and so I have a patch in ODPI-C to correct this which appears to work fine using the sample I posted earlier. Are you able to build your own version of cx_Oracle to test for yourself? If it works to your satisfaction I'd be happy to include this patch along with any other minor issues corrected in a 7.1.2 release. Please advise!

[husseinhussein]

Thank you so much Anthony,

Personally, I cannot build my own version of cx_Oracle. Can you send me the one you build? I will use it to validate if it fixes the issue we are experiencing.

Also, when will 7.1.2 be available?

Thanks

[cjbj]

Sorry, we can only distribute binaries via PyPI. The timing of 7.1.2 is TBD.

[husseinhussein]

Makes sense.

When will the ODPI-C patch be available?

Please update us when you guys have determined the release date of 7.1.2?

Thanks, Hussein

[anthony-tuininga]

The ODPI-C patch is already available -- see just above Chris' comments! We're aiming for early next week for the new releases but that is completely subject to change! I'll add another note to this issue when the release is made. If you have a chance to test yourself, let me know.

[anthony-tuininga]

The source on GitHub now contains this patch along with a couple of other issues that were discovered.

[anthony-tuininga]

This was addressed in cx_Oracle 7.1.2 which was just released.

[husseinhussein]

Thanks Anthony.

We appreciate your help.

[anthony-tuininga]

@husseinhussein, we discovered that the method used in the past, and the one that I started using again for 7.1.2 involves potential memory corruption. Its generally not noticeable if you close your connection as the last thing that you do before terminating your application, but if you attempt to attach an external service context handle multiple times (and close it after doing so) you will very likely get a segfault at some point. I believe for PowerBuilder it hasn't been an issue, but in discussions internally it has become clear that this approach is not supported. As a result, I have made changes to instead use the external service context handle and associated environment handle. As result, the same encodings will be used in the new connection created from the external service context handle. You can see what they are by looking at connection.encoding and connection.nencoding. This method does not involve any memory corruption. Before you close the external service context handle you should ensure that the connection created from it and any objects that were created from that connection have been destroyed.

Can you try the new code and make sure that it is going to work for you? If you have any problems, please let me know. Thanks!

[anthony-tuininga]

@husseinhussein, can you let me know if you've had a chance to test this? The changes will be part of cx_Oracle 7.3.

[husseinhussein]

My apologies, I did not have a change to test the new changes.

I will give you an answer before the end of next week.

Is that OK with you?

Thanks, Hussein

[anthony-tuininga]

Ideally I'd like to release the changes next week, but it can wait a bit longer yet if needed. Thanks for getting back to me!

[husseinhussein]

Greetings,

My apologies for a late reply....

I would like to let you know the changes you made fixed the problem.

Thanks, Hussein

[anthony-tuininga]

Great! Thanks, Hussein.

[anthony-tuininga]

cx_Oracle 7.3 was released with the newest changes implemented in it.