MSSQL For Real Man

When I started adding the support of a new FB4 datatype TIMESTAMP WITH TIME ZONE, I did a small test with MSSQL 2008, where this datatype is presented as datetimeoffset:

create table TBL__TS_WITH_TZ
(
 COL_TS_WITH_TZ datetimeoffset(4)
);

insert into TBL__TS_WITH_TZ (COL_TS_WITH_TZ) VALUES ('2023-04-27 11:18:00.0000 +03:30')
insert into TBL__TS_WITH_TZ (COL_TS_WITH_TZ) VALUES ('2023-04-27 11:19+04:30')
insert into TBL__TS_WITH_TZ (COL_TS_WITH_TZ) VALUES ('2023-06-21 13:19:57+04:37')

It was a couple months ago.

Today it was needed to read these records through MSSQL OLE DB provider (MSOLEDBSQL) to check this data. Provider returns datetimeoffset in DBTIMESTAMPOFFSET structure.

Ok, I was thought and wrote the following utility on C++: 

#include <_pch_.h>
#pragma hdrstop

#include <ole_lib/oledb/oledb_client_lib.h>
#include <ole_lib/com_initialize.h>
#include <iostream>

////////////////////////////////////////////////////////////////////////////////
using namespace std;
////////////////////////////////////////////////////////////////////////////////

void Execute()
{
 cout<<"GO!"<<endl;

 const ole_lib::TComInitialize2 __co_init(COINIT_MULTITHREADED);

 oledb_lib::t_db_data_source ds;

 const oledb_lib::t_oledb_char* const c_cn_str
  =_OLEDB_LIB_T
    ("Provider=MSOLEDBSQL;Data Source=VW7X64\\SQLEXPRESS, 49253;Initial Catalog=test;")

   _OLEDB_LIB_T
    ("User ID=sa;Password=mssql;Prompt=4;");

 cout<<"Connect to database ..."<<endl;

 _THROW_OLEDB_FAILED(ds,initialize(c_cn_str))

 oledb_lib::t_db_session session;

_THROW_OLEDB_FAILED(session,create(ds))

 cout<<"Start transaction ..."<<endl;

_THROW_OLEDB_FAILED(session,start_transaction());

 //---------------------------
 oledb_lib::t_db_command cmd;

 _THROW_OLEDB_FAILED(cmd,create(session))

 //---------------------------
 oledb_lib::t_db_row row;

 const oledb_lib::t_oledb_char* const c_sql
  =_OLEDB_LIB_T("select COL_TS_WITH_TZ from TBL__TS_WITH_TZ");

 cout<<"Prepare ["<<structure::tstr_to_str(c_sql)<<"] ..."<<endl;

 _THROW_OLEDB_FAILED(cmd,prepare_ex(c_sql,&row,0));

 //---------------------------
 cout<<"Execute ..."<<endl;

 _THROW_OLEDB_FAILED(cmd,execute(nullptr));

 //---------------------------
 _THROW_OLEDB_FAILED(cmd,bind(row));

 while(cmd.fetch(row)==S_OK)
 {
  cout<<"FIELD: "<<print(row[0])<<endl;
 }//while

 _THROW_OLEDB_FAILED(cmd,m_last_result)

 //---------------------------
 cout<<"Commit ..."<<endl;

_THROW_OLEDB_FAILED(session,commit());
}//Execute

////////////////////////////////////////////////////////////////////////////////

int main()
{
 try
 {
  Execute();
 }
 catch(const std::exception& e)
 {
  cout<<"ERROR: "<<e.what()<<endl;
 }//catch
 
 return 0;
}//main

////////////////////////////////////////////////////////////////////////////////

After I had gotten all the necessary information from this test, I thought — it would be stupid to do not share this code 🙂

Dmitry Kovalenko on 21 июня, 2023 | Filed Under MS SQL Server

Leave a Comment