Reply-To: <smacedo@calmetric.pt>
From: "Silvio Macedo" <smacedo@calmetric.pt>
To: <pgsql-interfaces@postgresql.org>
Subject: LabView and PostgreSQL ; and RecordSet GetString performance
Date: Thu, 25 Aug 2005 04:59:40 +0100
Organization: Calmetric Lda
Message-ID: <000401c5a929$6c0cf930$0302a8c0@plexus>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Importance: Normal

Hi,

A week or two ago, Fisher Ulrich asked this list about interfaces from
LabView to PostgreSQL. (hope he's reading as I couldn't find his
original email in the list archive).

For LabView on Windows, you can use LabSQL, which is a simple, but
convenient, ADO wrapper to Postgres. You then can use native OLEDB
(undergoing development) or PostgreSQL OLE/ODBC driver.
LabSQL - google search it
OLEDB and ODBC come with standard dist (windows, of course)

To save you time, DSN-less connection strings are as follows:
For ODBC:
DRIVER=3D{PostgreSQL};SERVER=3D127.0.0.1;port=3D5432;DATABASE=3DIT2CalMet=
er200
5;UID=3DIT2CalMeter;PWD=3Dxxxxx;

For OLEDB:
Provider=3DPostgreSQL; Location=3DIT2CalMeter2005; User =
ID=3DIT2CalMeter;
Password=3Dxxxxx;

change db, user and pass as needed, obviously.


Now, I'm having two issues with these:
- in native OLEDB, if an error happens when executing an SQL
statement, I cannot close the connection (ADO Connection.Close won't
actually close it) and the backend will start to have lots of zombie
connections (well, Idle at least).

- on the other hand, in OLE/ODBC, RecordSet.GetString is about 10
times slower than native OLEDB to get 20K records (tstamp,value).=20

Anybody had this sort of performance hit on GetString on PostgreSQL
ODBC?
Any tips? Any special connection string tuning?
Would GetRows be much better?

Thanks a lot
Silvio