VBA ADO Command Date Parameter Not Working

Hello,

I'm using the PSQL-ODBC driver from Excel 2002 VBA with a ADO Command
object. What is the correct way to pass a date into a parameter so it
gets into the database correctly? - everything I've tried results in
1900-05-07 added to the DB instead of the correct date. I also get
1900-05-07 in the DB if I leave the date empty. I've simplified my code
into the following test case - I've had no problems with other data
types. I've already tried changing adDBDate, passing Date objects or
strings.

Thanks,
Josh

Sub TestDateInsert()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command

Set conn = New ADODB.Connection
conn.Open "DSN=PRHTest"

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "INSERT INTO test_table (test_date) VALUES (?);"
cmd.Parameters.Append cmd.CreateParameter("@test_date", adDBDate,
adParamInput, , #11/23/2009#)
cmd.Execute , adExecuteNoRecords

Set cmd = Nothing
conn.Close
Set conn = Nothing

End Sub

Database Table definition:

CREATE TABLE test_table (
test_date date NOT NULL
);

From psqlodbc.log

[0.000]Driver Version='08.04.0100,200907060001' linking 1400 static
Multithread library
[0.000]Global Options: fetch=100, socket=4096, unknown_sizes=0,
max_varchar_size=255, max_longvarchar_size=8190
[0.016] disable_optimizer=0, ksqo=1, unique_index=1,
use_declarefetch=0
[0.016] text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
[0.016] extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding=''
[0.578] [ PostgreSQL version string = '8.3.8' ]
[0.578] [ PostgreSQL version number = '8.3' ]
[0.578]conn=03044168, query='select oid, typbasetype from pg_type where
typname = 'lo''
[0.594] [ fetched 0 rows ]
[0.594] [ Large Object oid = -999 ]
[0.594] [ Client encoding = 'UTF8' (code = 6) ]
[0.594]conn=03044168,
PGAPI_DriverConnect(out)='DSN=PRHTest;DATABASE=prhtest;SERVE R=localhost;PORT=5432;UID=xxx;PWD=xxxxxxxxxxx;SSLmode=disabl e;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;R owVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;So cket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSi ze=8190;Debug=1;CommLog=1;Optimizer=0;Ksqo=1;UseDeclareFetch =0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1 ;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFCon version=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus 1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;Lower CaseIdentifier=0;XaOpt=1'
[5.750]conn=03044168, query='INSERT INTO test_table (test_date) VALUES
('1900-05-07'::date);'
[7.203]conn=03044168, PGAPI_Disconnect

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: VBA ADO Command Date Parameter Not Working

on Mon, Nov 23, 2009 at 7:47 AM, Josh T wrote:
> =A0What is the correct way to pass a date into a parameter so it gets
> into the database correctly?

http://www.postgresql.org/docs/8.4/interactive/datetime-appe ndix.html

This is how Postgresql accepts dates by default. There is a way to change =
this:

http://www.postgresql.org/docs/8.4/interactive/datatype-date time.html#DATAT=
YPE-DATETIME-INPUT
http://www.postgresql.org/docs/8.4/interactive/runtime-confi g-client.html#G=
UC-DATESTYLE

> [5.750]conn=3D03044168, query=3D'INSERT INTO test_table (test_date) VALUES
> ('1900-05-07'::date);'

It looks like the ODBC driver is make the conversion here.
Rather than passing the date in with the #...#. Could you pass it in
as a string?

--=20
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: VBA ADO Command Date Parameter Not Working

Richard Broersma wrote:
> on Mon, Nov 23, 2009 at 7:47 AM, Josh T wrote:
>> What is the correct way to pass a date into a parameter so it gets
>> into the database correctly?
>
> http://www.postgresql.org/docs/8.4/interactive/datetime-appe ndix.html
>
> This is how Postgresql accepts dates by default. There is a way to change this:
>
> http://www.postgresql.org/docs/8.4/interactive/datatype-date time.html#DATATYPE-DATETIME-INPUT
> http://www.postgresql.org/docs/8.4/interactive/runtime-confi g-client.html#GUC-DATESTYLE
>
>> [5.750]conn=03044168, query='INSERT INTO test_table (test_date) VALUES
>> ('1900-05-07'::date);'
>
> It looks like the ODBC driver is make the conversion here.
> Rather than passing the date in with the #...#. Could you pass it in
> as a string?
>


Thanks, but trying as string gives me the same result

cmd.Parameters.Append cmd.CreateParameter("@test_date", adDBDate,
adParamInput, 4, "2009-11-23")
-> results in 1900-05-07 in the DB

I also get the same result in I leave the value out entirely

cmd.Parameters.Append cmd.CreateParameter("@test_date", adDBDate,
adParamInput)
-> also results in 1900-05-07 in the DB

Trying a string this way doesn't work at all

cmd.Parameters.Append cmd.CreateParameter("@test_date", adVarChar,
adParamInput, Len("2009-11-23"), "2009-11-23")
-> throws an error on cmd.Execute -- ERROR: invalid input syntax for
type date: "128"; Error while executing the query

Thanks,
Josh

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: VBA ADO Command Date Parameter Not Working

On Mon, Nov 23, 2009 at 9:24 AM, Josh T wrote:

> cmd.Parameters.Append cmd.CreateParameter("@test_date", adDBDate,
> adParamInput, 4, "2009-11-23")

Does anything change is you set the byte size from 4 to something like 10 or 12?

> cmd.CommandText = "INSERT INTO test_table (test_date) VALUES (?);"

Another thought would be to rewrite the insert expression to:

INSERT INTO Test_table( test_date )
VALUES( CAST( ? AS DATE ));

Although it probably wont help much.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: VBA ADO Command Date Parameter Not Working

Richard Broersma wrote:
> On Mon, Nov 23, 2009 at 9:24 AM, Josh T wrote:
>
>> cmd.Parameters.Append cmd.CreateParameter("@test_date", adDBDate,
>> adParamInput, 4, "2009-11-23")
>
> Does anything change is you set the byte size from 4 to something like 10 or 12?

Does not make a difference, nor does leaving empty.

>
>> cmd.CommandText = "INSERT INTO test_table (test_date) VALUES (?);"
>
> Another thought would be to rewrite the insert expression to:
>
> INSERT INTO Test_table( test_date )
> VALUES( CAST( ? AS DATE ));
>
> Although it probably wont help much.
>
>

No luck either - I get 1900-05-07 if I use adDBDate, an error if I use
adVarChar.

Thanks again,
Josh


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: VBA ADO Command Date Parameter Not Working

Josh T wrote:
> Hello,
>
> I'm using the PSQL-ODBC driver from Excel 2002 VBA with a ADO Command
> object. What is the correct way to pass a date into a parameter so it
> gets into the database correctly? - everything I've tried results in
> 1900-05-07 added to the DB instead of the correct date. I also get
> 1900-05-07 in the DB if I leave the date empty. I've simplified my code
> into the following test case - I've had no problems with other data
> types. I've already tried changing adDBDate, passing Date objects or
> strings.
>
> Thanks,
> Josh
>
> Sub TestDateInsert()
> Dim conn As ADODB.Connection
> Dim cmd As ADODB.Command
>
> Set conn = New ADODB.Connection
> conn.Open "DSN=PRHTest"
>
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = conn
> cmd.CommandType = adCmdText
> cmd.CommandText = "INSERT INTO test_table (test_date) VALUES (?);"
> cmd.Parameters.Append cmd.CreateParameter("@test_date", adDBDate,
> adParamInput, , #11/23/2009#)


> cmd.Execute , adExecuteNoRecords

It seemd 2 commas are needed, i.e.

cmd.Execute ,, adExecuteNoRecords

regards,
Hiroshi Inoue

> Set cmd = Nothing
> conn.Close
> Set conn = Nothing
>
> End Sub
>
> Database Table definition:
>
> CREATE TABLE test_table (
> test_date date NOT NULL
> );
>

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: VBA ADO Command Date Parameter Not Working

Hiroshi Inoue wrote:
>
> It seemd 2 commas are needed, i.e.
>
> cmd.Execute ,, adExecuteNoRecords

Thank you very much, that was it. (A stupid typo on my part, figures...)

Thanks again,
Josh


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc