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
