Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/249819

From BetaArchive Wiki
Knowledge Base


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