PostgreSQL Date Field Trouble

[Dave Graybeal]

Hey all,

Has anyone used a Parameterized Query to Insert data into a PostgreSQL date Field? I'm getting an error returned when it tries to execute the query. The same code works when I try to talk to an Access database.

Thanks,

Dave

I'm currently using LabVIEW Database Connectivity Toolset for LabVIEW 8.2.1, PGADO client and PostgreSQL 8.2.

[xtal]

There isn't anything that jumps out to me as an immediate issue or resolution, so more debugging needs to be done to narrow down the problem.

What does the error say? I'm trying to see if it's an ActiveX error, and error in the driver, a LabVIEW error, or a syntax issue.

Does the non-parameterized version of that query work for PostgreSQL?

Have you tried a simpler version of the parameterized query with different datatypes?

Does the problem always occur with a date/time field or with any datatype?

[LAVA 1.0 Content]

I am working with Dave on this issue, and I know he doesn't read the fourm in the evening, so to help expedite things I will try to answer the questions.

QUOTE(xtaldaz @ May 3 2007, 07:27 PM)

What does the error say? I'm trying to see if it's an ActiveX error, and error in the driver, a LabVIEW error, or a syntax issue.

This is the error "Error -2147467259 occurred at Cmd Execute.vi->testdata.vi" and there is nothing under possible reasons.

QUOTE(xtaldaz @ May 3 2007, 07:27 PM)

Does the non-parameterized version of that query work for PostgreSQL?

Yes, several typical date formats work with single quotes.

QUOTE(xtaldaz @ May 3 2007, 07:27 PM)

Does the problem always occur with a date/time field or with any datatype?

If the column in the database table is defined as a Date time field the error occurs. It doesn't matter if you define the datatype in the parameters field as a string or date time, it fails. Only columns defined as date have a problem. The same code works in Access using the Micrsoft Jet 4.0 OLE DB Provider and in Sybase using the Sybase ASE OLE DB provider. Any help would be greatly appreciated.

Thanks,

Todd

[Ton Plomp]

A search on the NI forums gives this KB

Ton

[Dave Graybeal]

tcplomp,

Thanks for the link to the KB on NI's Forums. It looked like it only applied to MS access however. In our case we can connect to MS access and write all the data types fine. It just appears to be an issue when we try to write to PostgreSQL with a Date Column. I'll put together a VI that demonstrates the code that works as well as the code that does not work. If anyone's successfully done this before any help / advice would be appreciated

Thanks,

Dave Graybeal

[Dave Graybeal]

Alright,

Attached is a VI that I've created to demonstrate this problem. It uses an Enum to determine which Insert to Test.

The order they can be run in is

Access Non Parameterized --- This Returns No Error and Inserts Properly

PostgreSQL Non Parameterized --- This Returns No Error and Inserts Properly

Access Parameterized --- This Returns No Error and Inserts Properly

PostgreSQL Parameterized No Date --- This Returns No Error and Inserts Properly

PostgreSQL Parameterized Date --- This Returns An Error and Does Not Insert Data

http://forums.lavag.org/index.php?act=attach&type=post&id=5728

In order to run this VI, you'll need to Create a UDL file that points to an access DB (to test the access cases) and a UDL that points to a postgresql DB (to test the postgresql cases).

Also, there needs to be a table in each database named "example" with 2 columns ("id", "stdat"). "id" is of datatype integer (also used as the Primary Key). "stdat" is of datatype date.

I would appreciate any information that anyone has regarding this issue. I'm having a hard time finding new places to look for potential solutions to this.

Thanks,

Dave Graybeal

http://forums.lavag.org/index.php?act=attach&type=post&id=5727

[Dave Graybeal]

Alright,

Update: It would appear that the issue behind this is the ADO providers support for Parameterized Queries does not seem to include date and time stamps I believe. The ado provider for postgresql that I was using was PostgreSQL OLE DB Provider which I found on at PgFoundry . Does anyone know of another option that has worked for you?

Thanks again,

Dave Graybeal

[Michael Aivaliotis]

Have you tried this?

http://pgfoundry.org/projects/psqlodbc/

[Dave Graybeal]

I haven't actually tried the odbc for postgres yet. It's my understanding that the ADO (OleDB) method is a newer (and better?) method. Also, at least to this point everything that we have is written using an ADO (connections to Sybase and Access). If I can't seem to find anything that helps solve this issue I'm having with postgres and seemingly only available ado provider for it, then I'll probably have to venture into another connection method (probably odbc). I would obviously like to not have to go down the road of re-writing code thats already written using ADO however.

I'm hoping to discover a miracle cure i think. lol.

Thanks,

Dave

[Michael Aivaliotis]

I don't see why your fundamental code has to change. You just need to change the connection method. Actually, you don't even need a UDL. You can just connect with a string configuration parameter. This actually avoids having to setup a UDL/DSN connection on the machine.

[xtal]

Actually, ADO can communicate using either OLE DB or ODBC. OLE DB is the newer standard for database drivers, but each Database Management System (DBMS) implements their drivers differently. I've found that the Microsoft and Oracle OLE DB providers are of a much higher quality than their ODBC drivers. However, mySQL only provides ODBC drivers and they work fine. I've personally never used PostgreSQL, so I can't comment on which driver is better or supports more features for different kinds of queries. All I do know is that if one type of driver doesn't work, you use another.

Instead of creating a UDL file, you use the Data Sources (ODBC) utility from Control Panel >> Administrative Tools. You create a DSN (name) for that connection and make sure to define all the appropriate parameters. Then when you use the DB Tools Open Connection, you wire a string containing the DSN to the Connection input rather the UDL path. The rest of the code won't have to change.

[Dave Graybeal]

Alright, that doesn't actually sound all that bad. I've not done too much with the databases outside of ADO yet, so my speaking was purely from lack of experience. I'll tool around with getting my sample VI switched over to using ODBC and see how that works for me. I'll report back my findings! (or possibly more questions if i encounter a roadblock).

Thanks!

Dave Graybeal

[Dave Graybeal]

Just wanted to give a quick update to this. We went ahead and tried using the ODBC drivers for postgres(which were already installed when you install the newest version of PostgreSQL). We created a dns file (similar to the UDL except made for ODBC drivers instead of ADO) and it worked without making any changes to the code. It worked perfectly and I didn't even have to use a connection string. Thanks for pointing me in this direction.

I would highly recomend that anyone using LabVIEW to connect to a PostgreSQL database, should use the ODBC drivers as they seem to support more common SQL commands.

Thanks again,

Dave Graybeal

[xtal]

That's great news. Thanks for the update.