Re: SQL Error- SQLBindColumn and TimeStamp Field

Specifically I am trying to execute a query like
Insert into Table(field1, field2, field3) values(?,?,?)
Where field3 is Postgres type 11, or Timestamp. The application I have
written queries the database to determine field types in an attempt to
properly insert the record. Yes, I could execute the query you describe
below, except that means I would have to rewrite my application to not use
query parameters, which has it own issues.

And my apologies- I dropped a zero somewhere. The year is actually 2000, not
200.

I did find a reference online at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcdatetime_data_type_changes.asp
that talks about datetime-type variable changes between ODBC versions 1 and
2. I need to do some experimenting- I think I may need to change data type.

Ed
----- Original Message -----
From: "Greg Campbell" <greg(dot)campbell(at)us(dot)michelin(dot)com>
To: "Ed Brown" <ebrown(at)arcompanies(dot)net>
Cc: <pgsql-odbc(at)postgresql(dot)org>
Sent: Wednesday, November 17, 2004 12:17 PM
Subject: Re: [ODBC] SQL Error- SQLBindColumn and TimeStamp Field

> Please clarify "trying to insert a Timestamp field"
> Are you doing Data Definition -- creating a timestamp field
> or
> Data Manipulation -- populating data into an existing timestamp field.
>
> If data manipulation:
> I do not know Delphi, but couldn't you execute as query string against a
> connection -- a string like
> "INSERT INTO my_table (my_time) VALUES ('2004-11-17 12:10:35')"?
>
> and did you mean for the year value to be 200?
>
>
>
> Ed Brown wrote:
>
> > I have been trying to do an insert into a TimeStamp field in
> > Postgresql 7.34 (running on Suse 9.0, if it matters). The application
> > is in Delphi 7.0, but the code is similar enough that you C gurus
> > should be able to see what's happening. I get the text as a string,
> > parse it into the various elements, and populate the
> > SQL_TimeStamp_Structure. When I execute the SQLBindColumn function I
> > get the following message:
> >
> > Unable to Bind Parameter- State: S1003, Error # 0,
> > [Microsoft][ODBC Driver Manager] Program type out of range
> >
> > The values in the structure are reasonable, and other elements work
> > properly. Can anyone either tell me what I'm doing wrong, or show me
> > an example of setting a Timestamp value that works?
> >
> > Thanks very much. Code Follows
> >
> > Ed Brown
> > ------------------------------------------------------------------------
> >
> > New(pTimeStamp);
> > DecodeDate(dtThis, i1,i2,i3);
> > pTimeStamp^.year := i1;
> > pTimeStamp^.Month := i2;
> > pTimeStamp^.Day := i3;
> > DecodeTime(dtThis, i1,i2,i3,i4);
> > pTimeStamp^.Hour := i1;
> > pTimeStamp^.minute := i2;
> > pTimeStamp^.second := i3;
> > pTimeStamp^.fraction := i4;
> >
> > New(piTemp);
> > lIntegers.Add(piTemp);
> >
> > // Fails on next call
> > retcode := SQLBindParameter(hSTMT, i + 1, SQL_PARAM_INPUT,
> > SQL_C_TYPE_TIMESTAMP,
> > SQL_TYPE_TIMESTAMP,
> > SizeOf(SQL_TIMESTAMP_STRUCT), 0 ,
> > pTimeStamp,
> > SizeOf(SQL_TIMESTAMP_STRUCT), piTemp) ;
> > ------------------------------------------------------------------------
> > The values are:
> >
> > pTimeStamp^.year = 200
> > pTimeStamp^.Month = 7
> > pTimeStamp^.Day = 15
> > pTimeStamp^.Hour := 11
> > pTimeStamp^.minute := 15
> > pTimeStamp^.second := 0
> > pTimeStamp^.fraction := 0
> >
>
>

----------------------------------------------------------------------------
----

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match