Claudia Rocio Diaz Toro | 21 Apr 19:37 2014

Ayuda con el scrip de coneccion por ODBC vs progress

Ref: Ayuda con el scrip de coneccion por ODBC vs progress 

lo hago como se conecta excel y no lo realia que puedo tener mal 

ADO-CONNEC = "DSN=PostgreSQL35W;DATABASE=scclog;SERVER=localhost;PORT=5432;UID=postgres;;

Claudia Rocio Diaz Toro
Movistar  Ec 083421164
Movistar Col 3176800188
Tj Crawley | 21 Apr 10:14 2014

Access - utf8

Windows Server 2012
MS Access 2010
psqlODBC_x64 v9.2.1 (Unicode)
PostgreSQLx64 9.1.13, and 9.2.x
db: encoding=UTF8; collation and charater type = English_United States.1252

I have a database which has some composite primary keys that contain character varying fields.  Some of these fields have unicode characters from a foreign language.  I have used this configuration for about 1 year without any problems.  All of the table data were visible through an Access front end over the unicode 64 odbc connection, it worked great.  Yesterday I reinstalled only the postgresql software itself.  Neither the OS, nor the odbc driver, nor Access was changed.  I restored my database and within Pgadmin everything looks fine including my data.  Then I went back to my Access frontends and at first everything still looked fine - without having changed anything the data appeared.  However, then I saw that in the limited cases where my primary key does contain a unicode character, those rows now appear with the #deleted in all fields.

The database encoding is set to UTF8 and I have tried setting the client_encoding variable to UTF8 in the database, in the login and group roles and in the .conf file.  There is no change, I still get #deleted.  In fact, I've attempted to set the client_encoding variable on the database to sql_ascii, but running show client_encoding from any client still shows utf8, so I don't even know if that variable is having any effect.  This is all very reproducible; I took a new Windows 7 machine, did a fresh install of Access, postgresql 9.1.3, odbc x64 unicode, create a db with utf8 encoding, add table with a primary key of character varying and add some foreign characters, back in the Access linked table, same behavior.

My assumption is that the problem is that Access is assuming it needs to read the primary keys in utf16 or something similar.  When it goes back to requery the db to display the table, then it can no longer find these keys and I get the #deleted.   When I create an Access query on the same table and set the Recordset Type to Snapshot, all of the data are displayed without any problems.

I did see this same problem over a year ago when I first started using Access and Postgresql together.  I must have fixed it about a year ago, at which point the problem went away and I have been happily using this configuration ever since.  Unfortunately, I can neither remember nor find on the internet whatever solution I implemented back then.  It's probably something in postgresql itself since that is the only part of the system I changed, either in the server configuration or in the database configure that wasn't brought over through my backup/restore process.

Thank you to all for any help you can provide!

PG User | 17 Apr 01:24 2014

compiling ODBC on windows

Hi All,
I am following the following document for compiling the code on windows:

But I am finding it hard to compile. I have few questions:

1. I tried using visual studio express 12 and 10 without MFC. Is MFC required?
I am getting errors:
"afxres.h" does not exists
I got error as undefined IDC_STATIC

2. Even I tried nmake option, but I am getting an error as
c:\XXX\psqlodbc-09.01.0100>nmake /f win32.mak CFG=Release ALL

Microsoft (R) Program Maintenance Utility Version 10.00.30319.01
Copyright (C) Microsoft Corporation.  All rights reserved.

Building the PostgreSQL Unicode 3.5 Driver for Win32...

Using default PostgreSQL Include directory: C:\Program Files (x86)\PostgreSQL\9.
Using default PostgreSQL Library directory: C:\Program Files (x86)\PostgreSQL\9.
Linking static Multithread library
Using default OpenSSL Include directory: C:\OpenSSL\include
Using default OpenSSL Library directory: C:\OpenSSL\lib\VC
CPP_PROJ=/nologo /MT /O2 /D "NDEBUG" /W3 /EHsc /I "C:\Program Files (x86)\Postgr
eSQL\9.1\include" /I "C:\OpenSSL\include" /D "WIN32" /D "_WINDOWS" /D "_MBCS" /D
CVER=0x0351" /Fp".\Release\psqlodbc.pch" /Fo".\Release"\ /Fd".\Release"\ /FD
        if not exist ".\Release/" mkdir ".\Release"
        cl.exe <at> C:\Users\ADMINI~1\AppData\Local\Temp\2\nm9EB5.tmp
NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft Visual Studio 10.0
\VC\bin\cl.exe"' : return code '0xc0000135'

Do you know the reason?

Thanking you.

- Nachiket

张兴宇 | 16 Apr 10:52 2014

Does pgsql-odbc driver have a 32bit version?

Dear  friends:
        I'm a software engineer from beijing , China, Recently , my team need to connect postgresql database through cognos by odbc,  I checked the postgresql website and download a version:
psqlodbc-09.02.0100.tar.gz , But I don't if the driver has a 32bit or 64 bit version, cause cognos software requirs 3rd party connection tool driver must be 32bit , I compile and install the psqlodbc-09.02.0100 and unixodbc-2.3.2, it works fine when I using the isql command . but cognos test database connection failed because some library files are 64bit. the question is, Does pgsql-odbc driver have a 32bit version? or I can compile the driver to 32bit somehow 
        I'm looking forward to receive your reply, and your paying attention is much appreciated. thank you !

                                                                                                                                                                                    Best Wishes
                                                                                                                                                                                    kyle  from beijing China

Andrzej Jurzec | 16 Apr 09:11 2014

psql odbc and ado

I'm using latest version of psql osbc driver and postgres database.
I have mfc application that uses ADO to connect to database
I have table with serial type and if i add new record, value of serial column isn't reread to recordset.
Same thing is with default values. Do you know, if there is any solution for that kind of problem ?

Daniel Díaz | 14 Apr 22:06 2014

SSL slow when using libpq.dll


I'm using psqlodbc on Windows to perform a query that returns on the order of 1 million rows. 

I have noticed that, when the DSN is configured to use SSL, the query runs very slow, like three times slower. 

However, if I delete libpq.dll from the psqlodbc installation directory, the slowdown in the SSL-secured query is much less (only about 10-20% compared to the unsecured query). 

I have tried several of the latest versions of the driver, and the same thing happens in all. 

Several doubts: 

- I surmise that the absence of libpq forces the driver to use Windows' sspi service to handle the SSL, is that correct? 

- Is it normal that using sspi is that much faster? 

- Is there a way, other than deleting libpq.dll, to force the use of sspi on Windows when using the compiled distribution of the driver? 

- Can deleting libpq.dll cause problems? 

Thanks in advance, 
Daniel Díaz 
Heikki Linnakangas | 11 Apr 14:16 2014

UseDeclareFetch bugs, found & fixed

Hi all,

If you run the pgsql-odbc regression suite with UseDeclareFetch=1, you 
get two failures:

1. notice

> *** psqlodbc/test/expected/notice.out	2014-03-24 08:45:07.567976184 +0200
> --- psqlodbc/test/results/notice.out	2014-04-11 15:00:01.250084814 +0300
> ***************
> *** 1,7 ****
>   \! ./src/notice-test
>   connected
> - 00000=NOTICE: test notice: foo
> - 00000=
>   disconnecting
> --- 1,3 ----

In a nutshell, with UseDeclareFetch=1, NOTICE messages are lost. The 
server sends them to the client with the response to the DECLARE CURSOR 
statement, but the driver throws away the response to DECLARE CURSOR. It 
waits for the response to the FETCH statement, and returns that instead. 
To fix, before throwing away the response, copy any NOTICE messages from 
the Qresult for the DECLARE CURSOR forward to the next Qresult.

2. dataatexecution

> *** /home/heikki/git-sandbox-pgsql/psqlodbc/test/expected/dataatexecution.out	2014-03-24
08:45:07.567976184 +0200
> --- /home/heikki/git-sandbox-pgsql/psqlodbc/test/results/dataatexecution.out	2014-04-11
15:00:05.934040285 +0300
> ***************
> *** 3,12 ****
>   Result set:
>   2
>   3
> ! Parameter	Status
> ! Fetching result sets for array bound (2 results expected)
> ! 1: Result set:
> ! 4
> ! 2: Result set:
> ! 5
> ! disconnecting
> --- 3,8 ----
>   Result set:
>   2
>   3
> ! SQLParamData failed
> ! 42P03=ERROR: cursor "SQL_CUR0xe20060" already exists;
> ! Error while executing the query

Even though it's the dataatexecution regression test that fails, this 
isn't related to data-at-execution, but array-binding of parameters. 
With array-bound parameters, the driver will execute the same statement 
multiple times, once for each set of parameters. If the statement is a 
SELECT-query, it will try to execute the "DECLARE CURSOR" many times, 
without closing the cursor inbetween, which fails.

To fix, I just disabled using cursors with array-bound parameters. I'm 
not sure if there would be a better fix - like close the cursor between 
the calls - but this is good enough for me.

I pushed a fix for those.

- Heikki


Sent via pgsql-odbc mailing list (pgsql-odbc <at>
To make changes to your subscription:

Heikki Linnakangas | 10 Apr 21:01 2014

SQLGetDiagRec should not clear error.

A colleague of mine pointed out that calling SQLGetDiagRec on a 
connection, after the connection has failed, clears the current error. 
The consequence is that if you call SQLGetDiagRec twice, and the first 
call returns an error, the second call might return no error. According 
to the ODBC spec:

> Calls to SQLGetDiagRec are nondestructive to the header and record
> fields. The application can call SQLGetDiagRec again at a later time to
> retrieve a field from a record as long as no other function, except
> SQLGetDiagRec, SQLGetDiagField, or SQLError, has been called in the interim.

Digging into the commit log, the code line that clears the error message 
in CC_get_error has been there since the very first commit.

I pushed a patch to fix that, removing the line in CC_get_error that 
clears the error number. I also added a regression test that 
demonstrates the issue.

- Heikki


Sent via pgsql-odbc mailing list (pgsql-odbc <at>
To make changes to your subscription:

pg_gg | 5 Apr 20:50 2014

Postgresql ODBC Truncates Timestamp second fractions


We are trying to replicate data from an Oracle database to PG 9.3 (tried 9.2 as well, but that doesn't really make a difference) on Windows 2008R2 64-bit using Oracle GoldenGate (OGG), but running into an issue with the ODBC driver psqlodbc-09.03.0210. The problem is that timestamp values coming from Oracle are truncated and mili and micro second data is lost. For example if data is '2014-04-05 11:12:13.123456000' what is insertedin the PG table is '2014-04-05 11:12:13', even when the field is defined as timestamp or timestamp (6). We logged the queries and it appears that the parameter that is prepared is already truncated to seconds only. We only see this behaviour with OGG, and not through other methods of inserting data using the same ODBC connection. However, th e ODBC driver that ships with OGG, which is from DataDirect, doesn't have this problem and passes the timestamp values appropriately. However we cannot use that driver, as it doesn't handle unicode null character, i.e. 0x00, which the official PG ODBC driver somehow handles. So basically none of the drivers work properly, but each has it's own separate issue. We are trying to figure out where in the psqlodbc codes the preparation and conversion of parameters happen to find out why the timestamp values are truncated if the data is coming from OGG and not truncated from other mechansims. Any help with this regard is greatly appreciated.

Thank you

PG User | 2 Apr 06:13 2014

Communication error while getting a tuple

Hi All,
I am connecting to postgres cluster using ODBC postgres driver and I am getting error as 

Description: 'Communication error while getting a tuple; 

Error fetching next row' 


What can be possible reason for the same?

I am using the following script to get the tuples:

import pyodbc

cnxn = pyodbc.connect('DRIVER={PostgreSQL UNICODE};SERVER=hostname;DATABASE=testdb;PORT=port;UID=test;PWD=password;UseDeclareFetch=1;Fetch=999;sslmode=prefer')

cursor = cnxn.cursor()


  if sz == 0:
  print "read =" + str(ressize)

The result size for a query is huge around 10M rows.

Thanking you.

- pguser
Hiroshi Inoue | 31 Mar 05:23 2014

Re: FW: ODBC 9.3.0210 not commiting, Discard ALL

Hi Barry,

My reply seems to have been rejected.
So I reply to ML.

(2014/03/29 1:10), Barry Bell wrote:
> Only "ini" I could find was ""
> [Bootstrap]
> Hope this helps,  Connection string:
> "Driver={PostgreSQL ANSI};;Port=65432;Database=db;Uid=u;Pwd=p;BI=2;TextAsLongVarchar=1;UnknownSizes=2;UseServerSidePrepare=1;B0=254;B7=1;C5=0;AB=8;"
> For the ODBC driver log:
> vfp9            2928-f338	ENTER SQLExecDirect
> 		HSTMT               0x009C8C08
> 		UCHAR *             0x053B3568 [      -3] "\ 9\ 9\ 9Insert into autorunlog (
recordid,program,description,starttime,endtime,records ) values (
"nextval"('autorunlog_seq'),? ,? ,? ,? ,?  )\ 0"
> 		SDWORD                    -3
> vfp9            2928-f338	EXIT  SQLExecDirect  with return code 0 (SQL_SUCCESS)
> 		HSTMT               0x009C8C08
> 		UCHAR *             0x053B3568 [      -3] "\ 9\ 9\ 9Insert into autorunlog (
recordid,program,description,starttime,endtime,records ) values (
"nextval"('autorunlog_seq'),? ,? ,? ,? ,?  )\ 0"
> 		SDWORD			  -3

Hmm what I really wanted was Mylog output.
You can take the log by adding ;Debug=1 or ;B2=1 to your
connection string.

> 2014-03-27 15:55:55.336 EDT,"","",2566,"localhost:51398",53348244.a06,50,"PARSE",2014-03-27
15:55:48 EDT,9/417,0,LOG,00000,"duration: 0.241 ms  parse <unnamed>: 			Insert into autorunlog (
recordid,program,description,starttime,endtime,records ) values (
""nextval""('autorunlog_seq'),$1 ,$2 ,$3 ,$4 ,$5  )",,,,,,,,"exec_parse_message, postgres.c:1391",""
> 2014-03-27 15:55:55.336
EDT,"hits","hits",2566,"localhost:51398",53348244.a06,51,"BIND",2014-03-27 15:55:48
EDT,9/417,0,LOG,00000,"duration: 0.040 ms  bind <unnamed>: 			Insert into autorunlog (
recordid,program,description,starttime,endtime,records ) values (
""nextval""('autorunlog_seq'),$1 ,$2 ,$3 ,$4 ,$5  )","parameters: $1 = 'DOEFUELPRICES', $2 = 'DOE Fuel
Prices Update', $3 = '2014-03-27 15:55:47', $4 = '2014-03-27 15:55:55', $5 =
'0'",,,,,,,"exec_bind_message, postgres.c:1771",""
> 2014-03-27 15:55:55.337
EDT,"","",2566,"localhost:51398",53348244.a06,52,"INSERT",2014-03-27 15:55:48
EDT,9/417,7419304,LOG,00000,"duration: 0.233 ms  execute <unnamed>: 			Insert into autorunlog (
recordid,program,description,starttime,endtime,records ) values (
""nextval""('autorunlog_seq'),$1 ,$2 ,$3 ,$4 ,$5  )","parameters: $1 = 'DOEFUELPRICES', $2 = 'DOE Fuel
Prices Update', $3 = '2014-03-27 15:55:47', $4 = '2014-03-27 15:55:55', $5 =
'0'",,,,,,,"exec_execute_message, postgres.c:1991",""
> 2014-03-27 15:55:55.346 EDT,"","",2566,"localhost:51398",53348244.a06,53,"DISCARD
ALL",2014-03-27 15:55:48 EDT,9/0,0,LOG,00000,"duration: 8.486 ms  statement:  DISCARD
ALL",,,,,,,,"exec_simple_query, postgres.c:1125",""

Unfortunately I can find no problem in the above log.
Do some errors occur after this?

Hiroshi Inoue


Sent via pgsql-odbc mailing list (pgsql-odbc <at>
To make changes to your subscription: