Microsoft KB Archive/249819

= How to pass a timestamp value to or from SQL Server with ODBC API =

Article ID: 249819

Article Last Modified on 7/31/2006

-

APPLIES TO


 * Microsoft ODBC Driver for Microsoft SQL Server 3.6
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q249819



SUMMARY
The SQL Server data type timestamp is a column value that is automatically updated every time a row is inserted or updated. Values in timestamp columns are not date/time data. By default, they are defined as binary(8) (for non nullable columns) or varbinary(8) (for nullable columns), which indicates the sequence of Microsoft SQL Server activity on the row. A table can have only one timestamp column.

From an ODBC application you can retrieve the value stored in a timestamp column and you can also send a timestamp parameter. You can not bind the timestamp column to SQL_TYPE_TIMESTAMP from an ODBC application. SQL_TYPE_TIMESTAMP is a totally different data type that is actually used for date/time data, and it does not correspond to the timestamp data type in SQL Server.

To pass a parameter of type timestamp to SQL Server, you have to bind a parameter of type SQL_BINARY with a column size of 8. You can also bind the timestamp value with a SQL_CHAR parameter.



MORE INFORMATION
The following ODBC example illustrates the functionality described in the "Summary" section. In this example you bind the timestamp parameter as SQL_BINARY. The example retrieves some data from a table that contains a timestamp column, and then queries the same table and passes a timestamp parameter.

The example assumes the following:  You have a data source name (DSN) named LocalServer.

  You have run the following SQL script to create a table and insert a value:

if exists (select * from sysobjects where id = object_id('dbo.TTimeStamp') and sysstat & 0xf = 3) drop table dbo.TTimeStamp GO

CREATE TABLE dbo.TTimeStamp (   col1 char (10) NOT NULL,    myTimeStamp timestamp NOT NULL ) GO

Insert into TTimestamp (col1) values("hello") GO 

Sample Code

 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 

int main(int argc, char* argv[]) {   SQLCHAR*               theDiagState = new SQLCHAR[50]; SQLINTEGER            theNativeState; SQLCHAR*              theMessageText  = new SQLCHAR[255]; SQLSMALLINT           iOutputNo;

SQLHENV               m_SQLEnvironment; SQLHDBC               m_SQLConnection; SQLHSTMT              m_SQLStatement;

SQLRETURN             iReturn; SQLINTEGER            iData; SQLCHAR*              cData = new SQLCHAR[20]; SQLCHAR*              cData1 = new SQLCHAR[20]; SQLCHAR*              cMyTimeStamp = new SQLCHAR[20];

//Connect //Allocate Environment Handle iReturn = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);

//Set environment to ODBC_3 iReturn = SQLSetEnvAttr(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);

//Allocate connection handle iReturn = SQLAllocHandle(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);

//Connect to the database. //In this example we have used the following: //LocalServer as the DSN name. //LoginName is a placeholder for the login name. Password is a placeholder for the password to the LoginName. //CHANGE THE DSN NAME and UserId and Password here. iReturn = SQLConnect(m_SQLConnection,(SQLCHAR*) "LocalServer",SQL_NTS,(SQLCHAR*)"LoginName",SQL_NTS,(SQLCHAR*)"Password",SQL_NTS); if (iReturn != SQL_ERROR) {       //Run the query. //Allocate the statement handle. iReturn = SQLAllocHandle(SQL_HANDLE_STMT,m_SQLConnection,&m_SQLStatement);

//CHANGE THE TABLE/COLUMN NAME HERE. //In this case we have used a table named TTimeStamp in SQL Server 6.5 or SQL Server 7.0 with two fields. //Col1 = Char(10) Primary //myTimeStamp = TimeStamp

//Execute the statement to get some timestamp value.

iReturn = SQLExecDirect(m_SQLStatement,(SQLCHAR*) "Select * from TTimeStamp",SQL_NTS); iReturn = SQLBindCol(m_SQLStatement,2,SQL_C_CHAR,cData,17,&iData); while( TRUE) {           iReturn = SQLFetch(m_SQLStatement); if (!((iReturn == SQL_SUCCESS) || (iReturn == SQL_SUCCESS_WITH_INFO))) break;

}       //Copying timestamp data to another location. This saves the last time stamp value. We will use this //to query the table a second time. strcpy((char*)cMyTimeStamp, (const char*) cData); iReturn = SQLCancel(m_SQLStatement);

iData = SQL_NTS; iReturn = SQLBindParameter(m_SQLStatement,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_BINARY,8,0,cMyTimeStamp,0,&iData); //Executing select statement with the timestamp as parameter. iReturn = SQLExecDirect(m_SQLStatement,(SQLCHAR*) "Select * from tTimeStamp where myTimeStamp = ?",SQL_NTS); if (iReturn != SQL_SUCCESS) {           SQLGetDiagRec(SQL_HANDLE_STMT,m_SQLStatement,1,theDiagState,&theNativeState,theMessageText,100,&iOutputNo);

}       //Bind the column again to see the data. iReturn = SQLBindCol(m_SQLStatement,2,SQL_C_CHAR,cData1,17,&iData); while( TRUE) {           //We will get only one record this time. iReturn = SQLFetch(m_SQLStatement); if (!((iReturn == SQL_SUCCESS) || (iReturn == SQL_SUCCESS_WITH_INFO))) break;

}

//DISCONNECT

iReturn = SQLFreeHandle(SQL_HANDLE_STMT,m_SQLStatement); iReturn = SQLDisconnect(m_SQLConnection); iReturn = SQLFreeHandle(SQL_HANDLE_DBC,m_SQLConnection); iReturn = SQLFreeHandle(SQL_HANDLE_ENV,m_SQLEnvironment);

m_SQLStatement = NULL; m_SQLConnection = NULL; m_SQLEnvironment = NULL; }   else {       //If it fails to connect theMessageText contains the reason for the failure. SQLGetDiagRec(SQL_HANDLE_DBC,m_SQLConnection,1,theDiagState,&theNativeState,theMessageText,100,&iOutputNo);

}   delete cData; delete cData1; delete cMyTimeStamp; delete theMessageText; delete theDiagState; return 1;

}

The preceding example retrieves some data from a table named TTimeStamp. It then runs a query that retrieves a particular record from the table depending upon a timestamp value.

