pyodbc always returns unicode instead of str

[GoogleCodeExporter]

What steps will reproduce the problem?

self.cursor.execute("create table t1(s varchar(2048))
self.cursor.execute("insert into t1 values(''XXX")
v = self.cursor.execute("select * from t1").fetchone()[0]

What is the expected output? What do you see instead?

type(v) should return <type 'str'>, but it returns <type 'unicode'>

What version of the product are you using? On what operating system?

MacOS 10.6.7
pyodbc 2.1.8
psqlodbc 09.00.0200
unixodbc 2.2.12

Please provide any additional information below.

I tried to use pyodbc.connect(connection_string, unicode_results=False), but it 
didn't help.

Original issue reported on code.google.com by sapoj...@gmail.com on 16 Oct 2011 at 5:13

[GoogleCodeExporter]

What is returned depends on your driver.  The library examines the results of 
the query.  For string types, there are only 2 ODBC types: SQLCHAR and 
SQLWCHAR.  SQLCHAR results are ANSI and are returned as 'str'.  SQLWCHAR 
results are UCS2 Unicode and are returned as 'unicode'.

The unicode_results flag is to force the conversion of ANSI to Unicode, but it 
is not really possible to convert the other way -- not all Unicode strings can 
be represented as ANSI ones.

I am going to close this since I believe it is working as designed.  To be 
sure, you can create an ODBC trace and see what data type is being returned to 
pyodbc.  If it is SQLCHAR, then there is a bug and you should reopen this.

Original comment by mkleehammer on 27 Oct 2011 at 6:48

• Changed state: NoFix

[GoogleCodeExporter]

Actually I have noticed in connection.cpp, Connect() - the connection is being 
established according to the fAsni flag. So if this flag is false, pyODBC is 
trying to establish a connection using SQLDriverConnectW first - which returns 
<unicode> results (instead of <str>).
If the fAnsi flag is true, OR if the SQLDriverConnectW fails, the connection is 
being established using SQLDriverConnect - which returns <str> results.
The fAnsi flag can be enabled by issuing: pyodbc.connect(connection_string, 
ansi=1).
The problem is that I have also switched from psqlODBC 08.04 to 09.00. In 08.04 
SQLDriverConnectW always failed, so pyODBC was automatically switching to 
SQLDriverConnect.
Regarding the trace - I wasn't able to produce it. Can you please assist me on 
this?

Original comment by sapoj...@gmail.com on 27 Oct 2011 at 9:28