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
#include <windows.h> #include <stdio.h> #include <sql.h> #include <sqlext.h> 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.
REFERENCES
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
170380 How to display/pass TimeStamp value from/to SQL Server
Additional query words: time stamp
Keywords: kbdatabase kbhowto KB249819