IBProvider Developer's Blog

The support of DBTIMESTAMPOFFSET

The following example demonstrates the support of DBTIMESTAMPOFFSET datatype for non-FB4 servers.

This simple code connects to FB3 and asks IBProvider to translate ‘2023-08-09 21:53 Europe/Moscow’ string into binary presentation of TIMESTAMP WITH TIME ZONE datatype — DBTIMESTAMPOFFSET.

Provider will do it with help ICU library.

#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=LCPI.IBProvider.5;Location=inet4://HOME4:3060/d:\\Database\\Ram\\FB30_PORT3060\\IBP_TEST_FB30_D3.GDB;")

   _OLEDB_LIB_T
    ("User ID=GAMER;Password=vermut;dbclient_type=fb.direct;")

   _OLEDB_LIB_T
    ("icu_library=icuuc63.dll;icuin_library=icuin63.dll;");

 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 '2023-08-09 21:53 Europe/Moscow' from DUAL");

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

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

 row.columns(0).set_bind_type(oledb_lib::oledb_typeid__DBTIMESTAMPOFFSET);

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

 _THROW_OLEDB_FAILED(cmd,execute(nullptr));

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

 while(cmd.fetch(row)==S_OK)
 {
  cout<<"FIELD: "<<row[0].get_string()<<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: "<<structure::ansi_to_oem(e.what())<<endl;
 }//catch
 
 return 0;
}//main

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

Output:

GO!
Connect to database …
Start transaction …
Prepare [select ‘2023-08-09 21:53 Europe/Moscow’ from DUAL] …
Execute …
FIELD: 09.08.2023 21:53:00 +03:00
Commit …

Let’s try to use a string with unknown time zone region — ‘2023-08-09 21:53 Europe/Moscow2’.

We will get the following error message:

(in Russian)

GO!
Connect to database …
Start transaction …
Prepare [select ‘2023-08-09 21:53 Europe/Moscow2’ from DUAL] …
Execute …
ERROR: Getting columns data
1. [LCPI.IBProvider.5]: [Подсистема: icu.v063] Неизвестное название часового пояса [Europe/Moscow2].
2. [LCPI.IBProvider.5]: Ошибка конвертирования типа данных [DBTYPE_STR] в [DBTYPE_DBTIMESTAMPOFFSET].
3. [LCPI.IBProvider.5]: Ошибка получения данных колонки [номер: 1][CONSTANT]. Bind DbType: DBTYPE_DBTIMESTAMPOFFSET. Статус: DBSTATUS_E_CANTCONVERTVALUE.

COM Error Code: DB_E_ERRORSOCCURRED

(In English)

GO!
Connect to database …
Start transaction …
Prepare [select ‘2023-08-09 21:53 Europe/Moscow2’ from DUAL] …
Execute …
ERROR: Getting columns data
1. [LCPI.IBProvider.5]: [Subsystem: icu.v063] The unknown timezone name [Europe/Moscow2].
2. [LCPI.IBProvider.5]: Conversion from datatype [DBTYPE_STR] into datatype [DBTYPE_DBTIMESTAMPOFFSET] failed.
3. [LCPI.IBProvider.5]: Failed to obtain column data [number: 1][CONSTANT]. Bind DbType: DBTYPE_DBTIMESTAMPOFFSET. Status: DBSTATUS_E_CANTCONVERTVALUE.

COM Error Code: DB_E_ERRORSOCCURRED

This test was using IBProvider v5.31.0.43923.

—
PS. It’s a real miracle 🙂