[ psqlodbc-Bugs-1010500 ] UUID not being returned correctly

Bugs item #1010500, was opened at 2008-10-28 14:09
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010500&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Philip Warner (pjw)
Assigned to: Nobody (None)
Summary: UUID not being returned correctly

Initial Comment:

I have the following in a postgres 8.3 DB:

Create Table test(f uuid);
insert into test values('e964684f-87fe-49d9-9b87-137cc83165ea');

I have the following VB.Net 2005 code that connects to this DB (using either ANSI or UNICODE driver); full connection string below. I use a DataAdapter as follows:

Dim c As New ODBC.ODBCConnection(My.Settings.ConnectionString)
Dim DA As New Odbc.OdbcDataAdapter("select f::text, f from test", c)
Dim Tbl As New DataTable
DA.Fill(zTbl)

When I inspect the values, they differ:

?tbl.Rows(0).Item(0).ToString
"e964684f-87fe-49d9-9b87-137cc83165ea"
?tbl.Rows(0).Item(1).ToString
"00390065-0036-0034-3600-380034006600"

The original can be stored using the ODBC driver, it just seems to have trouble retrieving it.

Full connection string:

Driver={PostgreSQL ANSI};database=time_log;server=xxx.yyy.zzz.ttt;port=5433;uid=root;sslmode=allow;readonly=0;protocol=7.4-1;fakeoidindex=0;showoidcolumn=0;rowversioning=0;showsystemtables=0;fetch=100;socket=4096;unknownsizes=0;maxvarcharsize=255;maxlongvarcharsize=8190;debug=0;commlog=0;optimizer=0;ksqo=1;usedeclarefetch=0;textaslongvarchar=1;unknownsaslongvarchar=0;boolsaschar=0;parse=0;cancelasfreestmt=0;extrasystableprefixes=dd_;lfconversion=1;updatablecursors=0;disallowpremature=0;trueisminus1=1;bi=0;byteaaslongvarbinary=0;useserversideprepare=0;lowercaseidentifier=0;xaopt=1

----------------------------------------------------------------------

>Comment By: Hiroshi Inoue (hinoue)
Date: 2012-03-28 03:28

Message:
Oops, I've misunderstood the log info.

Did you display all Mylog output?
ISTM the log only contains the connection phase.

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Nicolas Helleringer (nhe)
Date: 2012-03-27 13:41

Message:
This is what I did.

The log I posted is the content of the C:\Mylog_xxx.log file

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2012-03-27 11:36

Message:
At the PostgreSQL Unicode/ANSI ODBC Driver (psqlODBC) Setup
page, click the Datasource button and you can find the
MyLog (C:\mylog_xxxx.log) check box.

----------------------------------------------------------------------

Comment By: Nicolas Helleringer (nhe)
Date: 2012-03-26 09:02

Message:
I don't understand

The log is the Mylgog for the DSN UUIDTest created for the test.

I do not know what you mean by or how to "turn on per DSN Mylog option" sorry

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2012-03-24 15:09

Message:
Please turn on per DSN Mylog option as well.

regards,
Hiroshi inoue

----------------------------------------------------------------------

Comment By: Nicolas Helleringer (nhe)
Date: 2012-03-23 13:39

Message:
Here you are :

[5928-0.000]CC_connect: entering...
[5928-0.001]sslmode=disable
[5928-0.001]original_CC_connect: entering...
[5928-0.003]Driver Version='09.01.0100,201112290002' linking 1400 dynamic Multithread library
[5928-0.011]original_CC_connect: DSN = 'UUIDTest', server = 'X.X.X.X', port = '5432', database = 'UUIDTest', username = 'access', password='xxxxx'
[5928-0.013]connecting to the server socket...
[5928-0.015](0)(null) ERRNO=0
[5928-0.016]connection to the server socket succeeded.
[5928-0.017]!!! usrname=access server=X.X.X.X
[5928-0.017]startup client_encoding=UTF8
[5928-0.018]sizeof startup packet = 97
[5928-0.019]sent the authentication block successfully.
[5928-0.019]gonna do authentication
[5928-0.019]read -1, global_socket_buffersize=4096
[5928-0.019]Lasterror=10035
[5928-0.070]read 13, global_socket_buffersize=4096
[5928-0.070]auth got 'R'
[5928-0.070]areq = 5 salt=b576b1d400
[5928-0.071]in AUTH_REQ_MD5
[5928-0.071]read -1, global_socket_buffersize=4096
[5928-0.071]Lasterror=10035
[5928-0.097]read 324, global_socket_buffersize=4096
[5928-0.097]auth got 'R'
[5928-0.097]areq = 0 salt=0000000000
[5928-0.097]auth got 'S'
[5928-0.097]auth got 'S'
[5928-0.097]auth got 'S'
[5928-0.097]auth got 'S'
[5928-0.098]auth got 'S'
[5928-0.098]auth got 'S'
[5928-0.098]auth got 'S'
[5928-0.098]auth got 'S'
[5928-0.098]Got the PostgreSQL version string: '9.0.3'
[5928-0.098]Extracted PostgreSQL version number: '9.0'
[5928-0.100]auth got 'S'
[5928-0.100]auth got 'S'
[5928-0.100]standard_conforming_strings=off
[5928-0.101]auth got 'S'
[5928-0.101]auth got 'K'
[5928-0.101]auth got 'Z'
[5928-0.101]CC_send_settings: entering...
[5928-0.101]PGAPI_AllocStmt: entering...
[5928-0.102]**** PGAPI_AllocStmt: hdbc = 00000000001BC460, stmt = 00000000001BF820
[5928-0.102]CC_add_statement: self=00000000001BC460, stmt=00000000001BF820
[5928-0.102]PGAPI_FreeStmt: entering...hstmt=00000000001BF820, fOption=1
[5928-0.103]QResult: enter DESTRUCTOR
[5928-0.103]SC_init_Result(1bf820)[5928-0.103]SC_Destructor: self=00000000001BF820, self->result=0000000000000000, self->hdbc=00000000001BC460
[5928-0.104]APD_free_params: ENTER, self=00000000001BF9C8
[5928-0.104]IPD_free_params: ENTER, self=00000000001BFA38
[5928-0.104]PDATA_free_params: ENTER, self=00000000001BFB00
[5928-0.105]SC_Destructor: EXIT
[5928-0.105]CC_lookup_lo: entering...
[5928-0.105]CC_send_query: conn=00000000001BC460, query='select oid, typbasetype from pg_type where typname = 'lo''
[5928-0.106]send_query: done sending query 63bytes flushed
[5928-0.107]in QR_Constructor
[5928-0.107]exit QR_Constructor
[5928-0.107]read -1, global_socket_buffersize=4096
[5928-0.107]Lasterror=10035
[5928-0.133]read 79, global_socket_buffersize=4096
[5928-0.133]send_query: got id = 'T'
[5928-0.133]QR_fetch_tuples: cursor = '', self->cursor=0000000000000000
[5928-0.134]num_fields = 2
[5928-0.134]READING ATTTYPMOD
[5928-0.134]CI_read_fields: fieldname='oid', adtid=26, adtsize=4, atttypmod=-1 (rel,att)=(1247,65534)
[5928-0.135]READING ATTTYPMOD
[5928-0.135]CI_read_fields: fieldname='typbasetype', adtid=26, adtsize=4, atttypmod=-1 (rel,att)=(1247,24)
[5928-0.135]QR_fetch_tuples: past CI_read_fields: num_fields = 2
[5928-0.136]MALLOC: tuple_size = 100, size = 3200
[5928-0.136]QR_next_tuple: inTuples = true, falling through: fcount = 0, fetch_number = 0
[5928-0.136]end of tuple list -- setting inUse to false: this = 00000000001BF820 SELECT 0
[5928-0.137]_QR_next_tuple: 'C' fetch_total = 0 & this_fetch = 0
[5928-0.137]QR_next_tuple: backend_rows < CACHE_SIZE: brows = 0, cache_size = 0
[5928-0.138]QR_next_tuple: reached eof now
[5928-0.138]_QR_next_tuple: 'C': DONE (fcount == 0)
[5928-0.138]send_query: got id = 'Z'
[5928-0.139]QResult: enter DESTRUCTOR
[5928-0.139]QResult: in QR_close_result
[5928-0.139]QResult: free memory in, fcount=0
[5928-0.139]QResult: free memory out
[5928-0.139]QResult: enter DESTRUCTOR
[5928-0.140]QResult: exit close_result
[5928-0.140]QResult: exit DESTRUCTOR
[5928-0.140]Got the large object oid: -999
[5928-0.140]CC_lookup_characterset: entering...
[5928-0.141]conn->unicode=1
[5928-0.141]CC_connect: returning...1

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2012-03-22 23:48

Message:
Hi Nicolas,

I cannot reproduce the case with MS Access 2002.
Could you attach the Mylog output of your case?

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Nicolas Helleringer (nhe)
Date: 2012-03-20 15:26

Message:
Done the same on a x86 32 bits host / Access / ODBC driver

Same results, GUID are not well transfered.

----------------------------------------------------------------------

Comment By: Nicolas Helleringer (nhe)
Date: 2012-03-20 14:10

Message:
Same bug here with the x64 latest driver version (2011-12-30 version)

Context :
- From MS Access 2010 64 bits
- Replication number field GUID
- Export table to ODBC tool

With the unicode driver the tables are created correctly i-e fields are mapped to uuid fields (with ansi version they are mapped to bytea fields) but data in them is not ok.

Source is GUID like : {7AB02082-2242-4A65-A7E3-6B624BF4873E}

Transfered data is like : 00410037-0042-0030-3200-300038003200

----------------------------------------------------------------------

Comment By: Philip Warner (pjw)
Date: 2008-11-01 12:14

Message:
Thanks for the quick response!

I tried the ASCII driver, and it worked, which is great!

How stable are the beta drivers? and/or any schedule for
releasing a production version?

Thanks again.

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2008-10-31 22:04

Message:
Could you try the drivers on testing for 8.3.0300 dll at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?

----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010500&group_id=1000125