Re: [cx-oracle-users] cx_Oracle 5.2

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

On 7/23/15 12:24 PM, Doug Henderson wrote:
> Try changing your test case to use a byte string instead of a unicode
> string. Change
>
> cursor = session.execute('SELECT "SCHEMA_lot_lot_key",
> "SCHEMA_lot_lot_id" \nFROM (SELECT "SCHEMA".lot.lot_key AS
> "SCHEMA_lot_lot_key", "SCHEMA".lot.lot_id AS "SCHEMA_lot_lot_id"
> \nFROM "SCHEMA".lot) \nWHERE ROWNUM <= :param_1', {'param_1':1})
>
> to
>
> cursor = session.execute(b'SELECT "SCHEMA_lot_lot_key",
> "SCHEMA_lot_lot_id" \nFROM (SELECT "SCHEMA".lot.lot_key AS
> "SCHEMA_lot_lot_key", "SCHEMA".lot.lot_id AS "SCHEMA_lot_lot_id"
> \nFROM "SCHEMA".lot) \nWHERE ROWNUM <= :param_1', {b'param_1':1}
>
>
> I suspect you may have a problem with mixing unicode and byte strings.
> You don't mention which Python version you use but the string prefix
> suggests you are using 3.x. Are you using a recent version, e.g. 3.4?
>
> Note that the log does not show the results of the SA internal queries
> that try to find out how strings are returned.

Hi Doug -

Thanks very much for responding.   I should note that this is not my own 
test environment and I can't reproduce it with any Oracle environment I 
have access to.  I'm channeling one of my users who I think has just 
downgraded to cx_oracle 5.1.3 to solve the issue and may or may not 
still be paying attention (I will ping), but if you look at the logs the 
user posted you can see that we are sending bytestring based queries 
along and by the presence of the "b''" you know this is Python 3 at 
least (and likely 3.3 or 3.4).   That is, SQLAlchemy is encountering 
this error using the latter form you've posted.

Below are logs posted by this user where the failure of a row to be 
returned is present.  In SQLAlchemy, when you see queries sent as b'', 
that means the flag "supports_unicode_statements" is set to False on the 
dialect, which may or may not be the right choice for cx_oracle on 
Python 3, though in our own test suite we pass our suite 100% with this 
flag set to False for cx_oracle + py3k.  The parameter key is also a 
bytestring.

So in one case we have this: where we see the cursor.description comes 
back, but no rows:

2015-07-20 13:49:41,436 INFO sqlalchemy.engine.base.Engine b'SELECT USER FROM DUAL'
2015-07-20 13:49:41,437 INFO sqlalchemy.engine.base.Engine {}
2015-07-20 13:49:41,438 DEBUG sqlalchemy.engine.base.Engine Col ('USER',)
2015-07-20 13:49:41,439 DEBUG sqlalchemy.engine.base.Engine Row ('DPUSER',)
2015-07-20 13:49:41,439 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
2015-07-20 13:49:41,439 INFO sqlalchemy.engine.base.Engine {}
2015-07-20 13:49:41,440 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
2015-07-20 13:49:41,440 INFO sqlalchemy.engine.base.Engine {}
2015-07-20 13:49:41,443 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-07-20 13:49:41,444 INFO sqlalchemy.engine.base.Engine b'SELECT "SCHEMA_lot_lot_key", "SCHEMA_lot_lot_id" \nFROM (SELECT "SCHEMA".lot.lot_key AS "SCHEMA_lot_lot_key", "SCHEMA".lot.lot_id AS "SCHEMA_lot_lot_id" \nFROM "SCHEMA".lot) \nWHERE ROWNUM <= :param_1'
2015-07-20 13:49:41,444 INFO sqlalchemy.engine.base.Engine {b'param_1': 1}
2015-07-20 13:49:41,445 DEBUG sqlalchemy.engine.base.Engine Col ('SCHEMA_lot_lot_key', 'SCHEMA_lot_lot_id')

the user also pastes the identical series of steps on a different run, 
however there *is* a result.  The user observes that whether or not a 
result is returned is a 50% random decision.  If they downgrade to 
cx_oracle 5.1.3, the failures disappear:

2015-07-20 13:52:27,579 INFO sqlalchemy.engine.base.Engine b'SELECT USER FROM DUAL'
2015-07-20 13:52:27,579 INFO sqlalchemy.engine.base.Engine {}
2015-07-20 13:52:27,580 DEBUG sqlalchemy.engine.base.Engine Col ('USER',)
2015-07-20 13:52:27,581 DEBUG sqlalchemy.engine.base.Engine Row ('DPUSER',)
2015-07-20 13:52:27,581 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
2015-07-20 13:52:27,581 INFO sqlalchemy.engine.base.Engine {}
2015-07-20 13:52:27,583 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
2015-07-20 13:52:27,583 INFO sqlalchemy.engine.base.Engine {}
2015-07-20 13:52:27,586 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-07-20 13:52:27,587 INFO sqlalchemy.engine.base.Engine b'SELECT "SCHEMA_lot_lot_key", "SCHEMA_lot_lot_id" \nFROM (SELECT "SCHEMA".lot.lot_key AS "SCHEMA_lot_lot_key", "SCHEMA".lot.lot_id AS "SCHEMA_lot_lot_id" \nFROM "SCHEMA".lot) \nWHERE ROWNUM <= :param_1'
2015-07-20 13:52:27,587 INFO sqlalchemy.engine.base.Engine {b'param_1': 1}
2015-07-20 13:52:27,588 DEBUG sqlalchemy.engine.base.Engine Col ('SCHEMA_lot_lot_key', 'SCHEMA_lot_lot_id')
2015-07-20 13:52:27,588 DEBUG sqlalchemy.engine.base.Engine Row (1581, 'S9343.S')

what would be most helpful here would be someone with very detailed 
knowledge of cx_oracle's changes between 5.1.3 and 5.2 which could in 
any way result in the result rows for a statement going missing.
>
> You mention trying older versions of SA, but have you tried newer versions?
>
> Do you env variable for LANG or LC_*? Also, is the Oracle server
> version the same as the client version? You might also check the
> values of the NLS character set parameters.
>
> Hope this helps.
> Doug
>
> On 22 July 2015 at 19:13, Mike Bayer <mi...@zz...> wrote:
>>
>> On 6/20/15 11:40 PM, Anthony Tuininga wrote:
>>
>> What is cx_Oracle?
>>
>> cx_Oracle is a Python extension module that enables access to Oracle for
>> Python 2.x and 3.x and conforms to the Python database API 2.0
>> specifications with a number of enhancements.
>>
>>
>> Where do I get it?
>>
>> http://cx-oracle.sourceforge.net
>>
>>
>>
>> What's new?
>>
>> http://cx-oracle.readthedocs.org/en/latest/releasenotes.html
>>
>>
>> One of my users is seeing issues with SQL result sets returning zero rows
>> when upgrading to cx_Oracle 5.2 from 5.1.3.   Unfortunately we don't have a
>> simple reproduction case, and it also involves SQLAlchemy which I know rules
>> out it being a simple test case for cx_Oracle developers.   However, I'm
>> hoping that some other cx_Oracle users eventually can confirm some similar
>> issue to shed some light on it, so consider this post to be at least a flag
>> that something has changed for at least one user:
>>
>> https://bitbucket.org/zzzeek/sqlalchemy/issues/3491/queryfirst-sometimes-returns-none
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>>
>>
>>
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>>
>> ------------------------------------------------------------------------------
>>
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
>