ODBC insert of SQL_TYPE_TIMESTAMP

Hi,
I'm trying to insert a time stamp into SQL Server (2005) and can't figure
out the right combination of column type and arguments to SQLBindParameter()
.
My C code currently looks like this:
rc = SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 10, 0,
(SQLPOINTER)&tstamp, 0, NULL);
I've tried different values for the ColumnSize and DecimalDigits parameters,
including 10, 19, 22, 23, 26, and SQL_TIMESTAMP_LEN.
As for the table definition, I've tried DateTime, char(10), char(19), etc.
Different combinations either give me return codes of 22008 or 42S02.
I definitely need to insert into a single column, as I'm trying to maintain
compatibility with an equivalent DB2 database (which, if it helps, uses the
DB2 TIMESTAMP data type, and has no problem with the above code).
Info on the web for this problem is surprisingly scarce. I've found a few
things but nothing concrete - so I'd really appreciate any help.
Thanks in advance!
Regards,
Josh"LDJ" <LDJ@.discussions.microsoft.com> wrote in message
news:37FA6F9F-2352-4F86-B79C-6C262D8099A0@.microsoft.com...
> Hi,
> I'm trying to insert a time stamp into SQL Server (2005) and
> can't figure
> out the right combination of column type and arguments to
> SQLBindParameter().
> My C code currently looks like this:
> rc = SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT,
> SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 10, 0,
> (SQLPOINTER)&tstamp, 0, NULL);
> I've tried different values for the ColumnSize and
> DecimalDigits parameters,
> including 10, 19, 22, 23, 26, and SQL_TIMESTAMP_LEN.
> As for the table definition, I've tried DateTime, char(10),
> char(19), etc.
> Different combinations either give me return codes of 22008 or
> 42S02.
This is probably a little more than you want to see, but ...
We use our own DB objects to encapsulate ODBC access to Oracle,
DB2 and SQL Server (2000). Therefore, some extra code you won't
recognize. But, I think you're just looking for the Bind.
Please see below.
////////////////////////////////////////////////////////////////////////////
/
//
void VParameter::PutDateTime( VDateTime dateTime )
{
SQLRETURN rc;
USHORT msec;
m_LenInd = sizeof( m_timeStamp );
dateTime.DecodeDate( m_timeStamp.year, m_timeStamp.month,
m_timeStamp.day );
dateTime.DecodeTime( m_timeStamp.hour, m_timeStamp.minute,
m_timeStamp.second, msec );
m_timeStamp.fraction = 0;
rc = SQLBindParameter( m_hStmt, m_paramNumber,
SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TIMESTAMP,
sizeof( "yyyy-mm-dd hh:mm:ss" ), 0,
&m_timeStamp, sizeof( m_timeStamp ), (SQLLEN *) &m_LenInd);
CHECK_STMT( m_hStmt, rc );
}
m_timeStamp is just the SQL timestamp struct. A VDateTime is our
own object, based on midnight Dec. 30, 1899 (that's a standard).
m_* are class variables.
Good luck,
- Arnie|||Hi,
This is what I used for my SQLBindParameter and it's working, so hope it'll
help you!
SQLBindParamenter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP,
0, 0, (SQLPOINTER)&value, 0, &cbSize);
Note: columnSize and bufferLength are ignored when the type is
SQL_TIMESTAMP, cbSize is just an SQLINTEGER but asyou know it's also ignored
for timestamps...
I'm not sure whether it makes a difference if you use SQL_C_TIMESTAMP as
opposed to SQL_C_TYPE_TIMESTAMP, but I've been using the former
Hope this helps,
Isabella
P.S. you can check out the following on the function if you haven't already:
http://msdn.microsoft.com/library/d...ndparameter.asp
"Arnie" wrote:

> "LDJ" <LDJ@.discussions.microsoft.com> wrote in message
> news:37FA6F9F-2352-4F86-B79C-6C262D8099A0@.microsoft.com...
> This is probably a little more than you want to see, but ...
> We use our own DB objects to encapsulate ODBC access to Oracle,
> DB2 and SQL Server (2000). Therefore, some extra code you won't
> recognize. But, I think you're just looking for the Bind.
> Please see below.
> //////////////////////////////////////////////////////////////////////////
///
> //
> void VParameter::PutDateTime( VDateTime dateTime )
> {
> SQLRETURN rc;
> USHORT msec;
> m_LenInd = sizeof( m_timeStamp );
> dateTime.DecodeDate( m_timeStamp.year, m_timeStamp.month,
> m_timeStamp.day );
> dateTime.DecodeTime( m_timeStamp.hour, m_timeStamp.minute,
> m_timeStamp.second, msec );
> m_timeStamp.fraction = 0;
> rc = SQLBindParameter( m_hStmt, m_paramNumber,
> SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TIMESTAMP,
> sizeof( "yyyy-mm-dd hh:mm:ss" ), 0,
> &m_timeStamp, sizeof( m_timeStamp ), (SQLLEN *) &m_LenInd);
> CHECK_STMT( m_hStmt, rc );
> }
>
> m_timeStamp is just the SQL timestamp struct. A VDateTime is our
> own object, based on midnight Dec. 30, 1899 (that's a standard).
> m_* are class variables.
> Good luck,
> - Arnie
>
>
>|||Oops sorry LDJ
My columnsize is set to sizeof(TIMESTAMP_STRUCT), not 0... and i was
incorrect, ColumnSize is not ignored for SQL_TIMESTAMP!
Sorry for the confusion.
Isabella
"Isa" wrote:
[vbcol=seagreen]
> Hi,
> This is what I used for my SQLBindParameter and it's working, so hope it'l
l
> help you!
> SQLBindParamenter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAM
P,
> 0, 0, (SQLPOINTER)&value, 0, &cbSize);
> Note: columnSize and bufferLength are ignored when the type is
> SQL_TIMESTAMP, cbSize is just an SQLINTEGER but asyou know it's also ignor
ed
> for timestamps...
> I'm not sure whether it makes a difference if you use SQL_C_TIMESTAMP as
> opposed to SQL_C_TYPE_TIMESTAMP, but I've been using the former
> Hope this helps,
> Isabella
> P.S. you can check out the following on the function if you haven't alread
y:
> http://msdn.microsoft.com/library/d...ndparameter.asp
>
> "Arnie" wrote:
>|||Apologies to Josh and Arnie! I realised the reason why my code worked was
because I was just inserting a date with no time...
Josh, Arnie was correct, the column size should be 19 as it actually
represents the number of bytes needed to display the yyyy-mm-dd hh:mm:ss
format.. If you wanted to show the fraction, you should use 20 + s as the
columnSize, where s is the precision
http://msdn.microsoft.com/library/d.../>
mn_size.asp
Sorry for my unhelpful posts b4
Isabella
"Isa" wrote:
[vbcol=seagreen]
> Oops sorry LDJ
> My columnsize is set to sizeof(TIMESTAMP_STRUCT), not 0... and i was
> incorrect, ColumnSize is not ignored for SQL_TIMESTAMP!
> Sorry for the confusion.
> Isabella
> "Isa" wrote:
>