Обсуждение: 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;SERVER=localhost;PORT=5432;UID=xxx;PWD=xxxxxxxxxxx;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=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
on Mon, Nov 23, 2009 at 7:47 AM, Josh T <mortonjt@rochester.rr.com> 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-appendix.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-datetime.html#DATATYPE-DATETIME-INPUT http://www.postgresql.org/docs/8.4/interactive/runtime-config-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? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Richard Broersma wrote: > on Mon, Nov 23, 2009 at 7:47 AM, Josh T <mortonjt@rochester.rr.com> 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-appendix.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-datetime.html#DATATYPE-DATETIME-INPUT > http://www.postgresql.org/docs/8.4/interactive/runtime-config-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
On Mon, Nov 23, 2009 at 9:24 AM, Josh T <mortonjt@rochester.rr.com> 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
Richard Broersma wrote:
> On Mon, Nov 23, 2009 at 9:24 AM, Josh T <mortonjt@rochester.rr.com> 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
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
> );
>
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