Обсуждение: 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
>

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

----- Original Message -----

From: Ed Brown

To: pgsql-odbc@postgresql.org

Sent: Wednesday, November 17, 2004 11:03 AM

Subject: [ODBC] SQL Error- SQLBindColumn and TimeStamp Field

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