{"action":"created","issue":{"url":"https://api.github.com/repos/mkleehammer/pyodbc/issues/154","repository_url":"https://api.github.com/repos/mkleehammer/pyodbc","labels_url":"https://api.github.com/repos/mkleehammer/pyodbc/issues/154/labels{/name}","comments_url":"https://api.github.com/repos/mkleehammer/pyodbc/issues/154/comments","events_url":"https://api.github.com/repos/mkleehammer/pyodbc/issues/154/events","html_url":"https://github.com/mkleehammer/pyodbc/issues/154","id":188775346,"number":154,"title":"Parameterized queries not working for me","user":{"login":"Pentusha","id":1904496,"avatar_url":"https://avatars.githubusercontent.com/u/1904496?v=3","gravatar_id":"","url":"https://api.github.com/users/Pentusha","html_url":"https://github.com/Pentusha","followers_url":"https://api.github.com/users/Pentusha/followers","following_url":"https://api.github.com/users/Pentusha/following{/other_user}","gists_url":"https://api.github.com/users/Pentusha/gists{/gist_id}","starred_url":"https://api.github.com/users/Pentusha/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Pentusha/subscriptions","organizations_url":"https://api.github.com/users/Pentusha/orgs","repos_url":"https://api.github.com/users/Pentusha/repos","events_url":"https://api.github.com/users/Pentusha/events{/privacy}","received_events_url":"https://api.github.com/users/Pentusha/received_events","type":"User","site_admin":false},"labels":[{"id":78342295,"url":"https://api.github.com/repos/mkleehammer/pyodbc/labels/Hold","name":"Hold","color":"fad8c7","default":false},{"id":207139526,"url":"https://api.github.com/repos/mkleehammer/pyodbc/labels/Need%20More%20Info","name":"Need More Info","color":"fbca04","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":6,"created_at":"2016-11-11T14:47:47Z","updated_at":"2016-11-30T05:08:51Z","closed_at":null,"body":"Hello, I've got an issue with Oracle database and parameterized queries.\r\n```sql\r\nSELECT * FROM V$VERSION\r\nOracle Database 11g Release 11.2.0.1.0 - 64bit Production\r\nPL/SQL Release 11.2.0.1.0 - Production\r\nCORE\t11.2.0.1.0\tProduction\r\nTNS for 64-bit Windows: Version 11.2.0.1.0 - Production\r\nNLSRTL Version 11.2.0.1.0 - Production\r\n```\r\n\r\n```bash\r\npython --version\r\nPython 3.5.1\r\n\r\npip freeze | grep odbc\r\npyodbc==3.0.10\r\n```\r\n\r\n```python\r\nconnection = pyodbc.connect(connection_string)\r\ncursor = connection.cursor()\r\nformat_query = '''\r\n    SELECT *\r\n    FROM USER_ARGUMENTS\r\n    LEFT OUTER JOIN USER_TYPES ON USER_ARGUMENTS.TYPE_NAME = USER_TYPES.TYPE_NAME\r\n    LEFT OUTER JOIN USER_TYPE_ATTRS ON USER_ARGUMENTS.TYPE_NAME = USER_TYPE_ATTRS.TYPE_NAME\r\n    WHERE\r\n      PACKAGE_NAME = upper('{package_name}') AND\r\n      OBJECT_NAME = upper('{routine_name}')\r\n    ORDER BY SEQUENCE, POSITION, ATTR_NO;\r\n'''.format(\r\n    package_name=package_name,\r\n    routine_name=routine_name,\r\n)\r\nparameterized_query = '''\r\n    SELECT *\r\n    FROM USER_ARGUMENTS\r\n    LEFT OUTER JOIN USER_TYPES ON USER_ARGUMENTS.TYPE_NAME = USER_TYPES.TYPE_NAME\r\n    LEFT OUTER JOIN USER_TYPE_ATTRS ON USER_ARGUMENTS.TYPE_NAME = USER_TYPE_ATTRS.TYPE_NAME\r\n    WHERE\r\n      PACKAGE_NAME = upper(?) AND\r\n      OBJECT_NAME = upper(?)\r\n    ORDER BY SEQUENCE, POSITION, ATTR_NO;\r\n'''\r\ncursor.execute(format_query, [])\r\nassert cursor.fetchall()  # ok and some results\r\ncursor.execute(parameterized_query, [package_name, routine_name])\r\nassert cursor.fetchall()  # failed, no results\r\ncursor.execute(parameterized_query, package_name, routine_name)\r\nassert cursor.fetchall()  # failed, no results\r\n```"},"comment":{"url":"https://api.github.com/repos/mkleehammer/pyodbc/issues/comments/263784509","html_url":"https://github.com/mkleehammer/pyodbc/issues/154#issuecomment-263784509","issue_url":"https://api.github.com/repos/mkleehammer/pyodbc/issues/154","id":263784509,"user":{"login":"mkleehammer","id":27421,"avatar_url":"https://avatars.githubusercontent.com/u/27421?v=3","gravatar_id":"","url":"https://api.github.com/users/mkleehammer","html_url":"https://github.com/mkleehammer","followers_url":"https://api.github.com/users/mkleehammer/followers","following_url":"https://api.github.com/users/mkleehammer/following{/other_user}","gists_url":"https://api.github.com/users/mkleehammer/gists{/gist_id}","starred_url":"https://api.github.com/users/mkleehammer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mkleehammer/subscriptions","organizations_url":"https://api.github.com/users/mkleehammer/orgs","repos_url":"https://api.github.com/users/mkleehammer/repos","events_url":"https://api.github.com/users/mkleehammer/events{/privacy}","received_events_url":"https://api.github.com/users/mkleehammer/received_events","type":"User","site_admin":false},"created_at":"2016-11-30T05:08:51Z","updated_at":"2016-11-30T05:08:51Z","body":"I'm still looking, but I find this most interesting:\r\n\r\n```text\r\n[ODBC][10487][1480409611.119633][SQLFetch.c][348]\r\n\t\tExit:[SQL_NO_DATA]\r\n```\r\n\r\nIf [SQLFetch](https://msdn.microsoft.com/en-us/library/ms712424(v=vs.85).aspx) returns SQL_NO_DATA then there are no rows returned by the DB.  I think that would make it more likely that the parameters are not being passed correctly.\r\n\r\nThe ODBC specification specifies that Unicode is to be UCS-2 encoded (extended to UTF-16 later apparently).  Now, many drivers do not pay attention to this and I am in the process of figuring out how to make the encodings configurable.  In the meantime, perhaps pyodbc is passing UTF-16 data and Oracle is expecting UTF-8.  Can you check that?  Is that something you can configure on the client easily, at least to test?\r\n\r\nAnd kudos on the traces!  Usually when I need one I don't get a response.\r\n"}}
{"actor":{"display_login":"mkleehammer"}}