Thread: New to ODBC with postgresql

New to ODBC with postgresql

From
"Tim LoGrasso"
Date:

Hello,

 

I think I must be missing something fundamental but for the life of me I’m not able to figure it out.  Hopefully someone here will be kind enough to point me in the right direction.  Essentially I am trying to setup a mail merge between Microsoft Word and a postgresql database.  I’m on Windows XP with MS Word 2002.

 

I have setup a DSN for the database I’m trying to connect to with the following info: DATABASE: production, SERVER: ip address, PORT: 5432.  

Then I go into MS Word and choose Tools->Letters and Mailings->Mail Merge Wizard.

I then chose Browse for a database.

I’m then prompted to choose a data source and I chose “+Connect to new data source”

Next I choose ODBC DSN and choose the data source I’d earlier created.

Then I enter user name and password and choose test.

I get the following error:  “Test connection failed because of an error in initializing provider. blank

 

The other variation I try is instead of choosing “Connect to new data source” I click the “New Source…” button.

It then starts with connect string instead of data source name chosen. 

I click build next to the connect string field and fill in the database, server ip address, port and password info.

I then get the following error: “Operation cannot be executed because an error occurred during connection to the driver.”

 

 

I figured out how to trace the ODBC drivers and I’ve attached the SQL.LOG file.  I just downloaded the window’s driver from the postgres site on Friday so I should have the most recent versions.

 

I’ve configured the postgres server to allow host connections from all IP’s on my subnet.  I’ve also set tcpip_socket=true in postgresql.conf. 

 

This is all the info I can think of attaching at the moment.  Please let me know if you need anything else to make this work.

 

Thanks for your time.

 

Sincerely,

 

Tim LoGrasso

Attachment

Re: New to ODBC with postgresql

From
"Tim LoGrasso"
Date:

Some additional debug actions by me:

 

I checked the mirror and realized I may not have the latest version of the ODBC driver.  So, I downloaded from the msi directory the following file: psqlodbc-07_03_0100.zip

 

I then uninstalled the existing driver and installed the 07_03_0100.zip driver.

 

I now get the following error in MS Word: “Unable to obtain list of tables from the datasource.”

 

I tried to connect via MS Access using the same DSN and I get the following error:

 “ODBC—call failed.

 

                          ERROR: current transaction is aborted, queries ignored until end of transaction block (#7)”

 

This implies there is an error in the request for a list of tables.  I then changed the driver configuration to return system tables.  I got a list of tables.  In testing this further, I created a new DB and tried the same thing and got the #7 error.  I know when I mistype a command at the psql prompt I cannot perform any further commands until I do a rollback.  This seems very similar which begs the question, how do I either perform that rollback or prevent this error from being generated in the first place.  Any ideas would be appreciated.

 

--Tim

 

 

 

 

 

 

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Tim LoGrasso
Sent:
Monday, October 20, 2003 3:28 PM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] New to ODBC with postgresql

 

Hello,

 

I think I must be missing something fundamental but for the life of me I’m not able to figure it out.  Hopefully someone here will be kind enough to point me in the right direction.  Essentially I am trying to setup a mail merge between Microsoft Word and a postgresql database.  I’m on Windows XP with MS Word 2002.

 

I have setup a DSN for the database I’m trying to connect to with the following info: DATABASE: production, SERVER: ip address, PORT: 5432. 

Then I go into MS Word and choose Tools->Letters and Mailings->Mail Merge Wizard.

I then chose Browse for a database.

I’m then prompted to choose a data source and I chose “+Connect to new data source”

Next I choose ODBC DSN and choose the data source I’d earlier created.

Then I enter user name and password and choose test.

I get the following error:  “Test connection failed because of an error in initializing provider. blank

 

The other variation I try is instead of choosing “Connect to new data source” I click the “New Source…” button.

It then starts with connect string instead of data source name chosen. 

I click build next to the connect string field and fill in the database, server ip address, port and password info.

I then get the following error: “Operation cannot be executed because an error occurred during connection to the driver.”

 

 

I figured out how to trace the ODBC drivers and I’ve attached the SQL.LOG file.  I just downloaded the window’s driver from the postgres site on Friday so I should have the most recent versions.

 

I’ve configured the postgres server to allow host connections from all IP’s on my subnet.  I’ve also set tcpip_socket=true in postgresql.conf. 

 

This is all the info I can think of attaching at the moment.  Please let me know if you need anything else to make this work.

 

Thanks for your time.

 

Sincerely,

 

Tim LoGrasso

Re: New to ODBC with postgresql

From
Mike Mascari
Date:
Tim LoGrasso wrote:

> Some additional debug actions by me:
>
> I checked the mirror and realized I may not have the latest version of
> the ODBC driver.  So, I downloaded from the msi directory the following
> file: psqlodbc-07_03_0100.zip
>
> I then uninstalled the existing driver and installed the 07_03_0100.zip
> driver.
>
> I now get the following error in MS Word: “Unable to obtain list of
> tables from the datasource.”
>
> I tried to connect via MS Access using the same DSN and I get the
> following error:
>
>  “ODBC—call failed.
>
>                           ERROR: current transaction is aborted, queries
> ignored until end of transaction block (#7)”

It may help in debugging the situation if you turned statement logging
on in postgresql.conf by setting:

log_statement = true

On RedHat, the initialization script would then need to be edited so
output is not directed to /dev/null but a file instead. That way you
could examine the SQL statements the ODBC driver is sending to the
database.

Mike Mascari
mascarm@mascari.com