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.campbell@us.michelin.com>
To: "Ed Brown" <ebrown@arcompanies.net>
Cc: <pgsql-odbc@postgresql.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