how to do batch insert into table having "timestamp" column using c++ ODBC code

zachariam · August 2023

I am writing a c++ code in linux using ODBC , I have a table which is having GEN_TIME column of timestamp type, i need 8 data to insert at a time, i defined
array of SQL_TIMESTAMP_STRUCT lDateAndTime[10]; and filled with data like below

const int NUM_ENTRIES = 8;

lDateAndTime[0].year = 2023;
lDateAndTime[0].month = 8;
lDateAndTime[0].day = 10;
lDateAndTime[0].hour = 11;
lDateAndTime[0].minute = 30;
lDateAndTime[0].second = 0;

lDateAndTime[1].year = 2023;
lDateAndTime[1].month = 8;
lDateAndTime[1].day = 10;
lDateAndTime[1].hour = 11;
lDateAndTime[1].minute = 30;
lDateAndTime[1].second = 0;

And i set insert query like below

ret = SQLPrepare (hdlStmt, (SQLTCHAR*)"INSERT INTO test_evr_details (evr_event, evr_reason, evr_cause, TRANS_ID, GEN_TIME) VALUES (?, ?, ?, ?, ?)" , SQL_NTS) ;

and set the date filed like

            ret = SQLBindParameter(hdlStmt, 5, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TYPE_TIMESTAMP, 0, 0, (SQLPOINTER)lDateAndTime,  sizeof(SQL_TIMESTAMP_STRUCT) , NULL);

and i set the number of batch as

ret = SQLSetStmtAttr( hdlStmt, SQL_ATTR_PARAMSET_SIZE,
(SQLPOINTER)NUM_ENTRIES, 0 );

and executed query as

ret = SQLExecute(hdlStmt);

But it is getting error, if i remove GEN_TIME from the query, then it is inserting 8 rows.

SruthiA · August 2023

@zachariam : I got it working. I had to do couple of changes. Please find the attached code.

[dbadmin@node3 ~]$ g++ -o test10 test1.cpp -lodbc
[dbadmin@node3 ~]$ ./test10
Allocated an environment handle.
Set application to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disabling autocommit.
Creating prepared statement
Created prepared statement.
Bound EvrEvent array to prepared statement
Bound DateAndTime array to prepared statement
Set numbers of rows
successful.
Row 1 inserted successsfully
Row 2 inserted successsfully
Row 3 inserted successsfully
Committing transaction
Committed transaction
Free handles.
[dbadmin@node3 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

Bad terminal type: "xterm". Will assume vt100.
test=> select * from test_table;
evr_event | gen_time
-----------+---------------------
1 | 2023-08-10 11:30:11
2 | 2023-08-10 11:30:01
3 | 2023-08-10 11:30:01
(3 rows)

test=>

shrutiChougule1008 · August 2023

I am also looking for the same. Can someone please help on this?

Bryan_H · August 2023

I'll need more info to figure this out. Can you post the complete example? What was the exact error message?

zachariam · August 2023

@Bryan_H said:
I'll need more info to figure this out. Can you post the complete example? What was the exact error message?

i create a test table like below

I Create a test code like below to insert 3 rows to this test table

include <stdio.h>

include <stdlib.h>

include <time.h>

include <unistd.h>

include <sql.h>

include <sqltypes.h>

include <sqlext.h>

include <string.h>

int main()
{
// Number of data rows to insert
const int NUM_ENTRIES = 3;

    // Set up the ODBC environment
    SQLRETURN ret;
    SQLHENV hdlEnv;
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv);
    if(!SQL_SUCCEEDED(ret)) {
            printf("Could not allocate a handle.\n");
            exit(EXIT_FAILURE);
    } else {
            printf("Allocated an environment handle.\n");
    }
    // Tell ODBC that the application uses ODBC 3.
    ret = SQLSetEnvAttr(hdlEnv, SQL_ATTR_ODBC_VERSION,
                    (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
    if(!SQL_SUCCEEDED(ret)) {
            printf("Could not set application version to ODBC3.\n");
            exit(EXIT_FAILURE);
    } else {
            printf("Set application to ODBC 3.\n");
    }
    // Allocate a database handle.
    SQLHDBC hdlDbc;
    ret = SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlDbc);
    if(!SQL_SUCCEEDED(ret)) {
            printf("Could not allocate database handle.\n");
            exit(EXIT_FAILURE);
    } else {
            printf("Allocated Database handle.\n");
    }

    // Connect to the database
    printf("Connecting to database.\n");
    const char *dsnName = "Vertica";
    const char* userID = "rp_prs";
    const char* passwd = "rp_pr5";
    ret = SQLConnect(hdlDbc, (SQLCHAR*)dsnName,
                    SQL_NTS,(SQLCHAR*)userID,SQL_NTS,
                    (SQLCHAR*)passwd, SQL_NTS);
    if(!SQL_SUCCEEDED(ret)) {
            printf("Could not connect to database.\n");
            exit(EXIT_FAILURE);
    } else {
            printf("Connected to database.\n");
    }

    // Disable AUTOCOMMIT
    printf("Disabling autocommit.\n");
    ret = SQLSetConnectAttr(hdlDbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF,
                    SQL_NTS);
    if(!SQL_SUCCEEDED(ret)) {
            printf("Could not disable autocommit.\n");
            exit(EXIT_FAILURE);
    }

    // Set up a statement handle
    SQLHSTMT hdlStmt;
    SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);

    printf("Creating prepared statement\n");


    ret = SQLPrepare (hdlStmt, (SQLTCHAR*)"INSERT INTO test_table (evr_event, GEN_TIME) VALUES (?, ?)" , SQL_NTS) ;

    if(!SQL_SUCCEEDED(ret)) {
            printf("Could not create prepared statement\n");
            exit(EXIT_FAILURE);
    } else {
            printf("Created prepared statement.\n");
    }

    SQLINTEGER lEvrEvent[10]={0};
    SQL_TIMESTAMP_STRUCT lDateAndTime[10];

    lEvrEvent[0] = 1;
    lEvrEvent[1] = 2;
    lEvrEvent[2] = 3;

    lDateAndTime[0].year = 2023;
    lDateAndTime[0].month = 8;
    lDateAndTime[0].day = 10;
    lDateAndTime[0].hour = 11;
    lDateAndTime[0].minute = 30;
    lDateAndTime[0].second = 0;

    lDateAndTime[1].year = 2023;
    lDateAndTime[1].month = 8;
    lDateAndTime[1].day = 10;
    lDateAndTime[1].hour = 11;
    lDateAndTime[1].minute = 30;
    lDateAndTime[1].second = 0;

    lDateAndTime[2].year = 2023;
    lDateAndTime[2].month = 8;
    lDateAndTime[2].day = 10;
    lDateAndTime[2].hour = 11;
    lDateAndTime[2].minute = 30;
    lDateAndTime[2].second = 0;


    ret = SQLBindParameter(hdlStmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER,
                    0, 0, (SQLPOINTER)lEvrEvent, sizeof(SQLINTEGER) , NULL);
    if(!SQL_SUCCEEDED(ret)) {
            printf("Could not bind EvrEvent array\n");
            exit(EXIT_FAILURE);
    } else {
            printf("Bound EvrEvent array to prepared statement\n");
    }

    ret = SQLBindParameter(hdlStmt, 2, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TYPE_TIMESTAMP,
                    0, 0, (SQLPOINTER)lDateAndTime, sizeof(SQL_TIMESTAMP_STRUCT), NULL);

    if(!SQL_SUCCEEDED(ret)) {
            printf("Could not bind DateAndTime array\n");
            exit(EXIT_FAILURE);
    } else {
            printf("Bound DateAndTime array to prepared statement\n");
    }

    // Tell the ODBC driver how many rows we have in the
    // array.
    ret = SQLSetStmtAttr( hdlStmt, SQL_ATTR_PARAMSET_SIZE,
                    (SQLPOINTER)NUM_ENTRIES, 0 );
    if(!SQL_SUCCEEDED(ret)) {
            printf("Could not bind set parameter size\n");
            exit(EXIT_FAILURE);
    } else {
            printf("Set numbers of rows\n");
    }

    ret = SQLExecute(hdlStmt);
    if(!SQL_SUCCEEDED(ret))
    {
            printf("not successful!\n");
    }
    else
    {
            printf("successful.\n");
    }


    // Done with batches, commit the transaction
    printf("Committing transaction\n");
    ret = SQLEndTran(SQL_HANDLE_DBC, hdlDbc, SQL_COMMIT);
    if(!SQL_SUCCEEDED(ret)) {
            printf("Could not commit transaction\n");
    }  else {
            printf("Committed transaction\n");
    }

    // Clean up
    printf("Free handles.\n");
    ret = SQLDisconnect( hdlDbc );
    if(!SQL_SUCCEEDED(ret)) {
            printf("Error disconnecting. Transaction still open?\n");
            exit(EXIT_FAILURE);
    }
    SQLFreeHandle(SQL_HANDLE_STMT, hdlStmt);
    SQLFreeHandle(SQL_HANDLE_DBC, hdlDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv);
    exit(EXIT_SUCCESS);

}

I compiled the code like

g++ -o TEST_BatchInsert TestBatchInsert.cc -lodbc

I run the executable like below

[prs@v110 VerticaTestCode]$ ./TEST_BatchInsert
Allocated an environment handle.
Set application to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disabling autocommit.
Creating prepared statement
Created prepared statement.
Bound EvrEvent array to prepared statement
Bound DateAndTime array to prepared statement
Set numbers of rows
not successful!
Committing transaction
Committed transaction
Free handles.

Table content after execute

TSSDB=> select * from test_table;
EVR_EVENT | GEN_TIME
-----------+---------------------
1 | 2023-08-10 11:30:00
(1 row)

Problem

It is inserting only 1 time, i coded it to insert 3 times. if i remove the code for GEN_TIME and put only EVR_EVENT in query, then it is working fine and inserting 3 rows.

Bryan_H · August 2023

I was able to compile your code on my test cluster, and it runs successfully inserting 3 rows with ODBC driver v23.3.0. I will see if this was a known issue, but please obtain the latest driver from https://www.vertica.com/download/vertica/client-drivers/
ODBC driver is backwards compatible to Vertica 8 so the latest client should still work with your server.

SruthiA · August 2023

@zachariam : I am able to reproduce the issue locally

[dbadmin@node3 ~]$ g++ -o test9 test.cpp -lodbc
[dbadmin@node3 ~]$ ./test9
Allocated an environment handle.
Set application to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disabling autocommit.
Creating prepared statement
Created prepared statement.
Bound EvrEvent array to prepared statement
Bound DateAndTime array to prepared statement
Set numbers of rows
not successful!
Committing transaction
Committed transaction
Free handles.
[dbadmin@node3 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

Bad terminal type: "xterm". Will assume vt100.
test=> select * from test_table;
evr_event | gen_time
-----------+---------------------
1 | 2023-08-10 11:30:00
(1 row)

test=> select version();

version

Vertica Analytic Database v23.3.0-1
(1 row)

I added more debugging with a helper function and found that insertion is failing because of datetime overflow.

[dbadmin@node3 ~]$ g++ -o test10 test1.cpp -lodbc
[dbadmin@node3 ~]$ ./test10
Allocated an environment handle.
Set application to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disabling autocommit.
Creating prepared statement
Created prepared statement.
Bound EvrEvent array to prepared statement
Bound DateAndTime array to prepared statement
Set numbers of rows

error record 1
sqlstate: 22008
detailed msg: [Vertica][Support] (40520) Datetime field overflow resulting from invalid datetime.
native error code: 40520

not successful!
Committing transaction
Committed transaction
Free handles.
[dbadmin@node3 ~]

zachariam · August 2023

@Bryan_H said:
I was able to compile your code on my test cluster, and it runs successfully inserting 3 rows with ODBC driver v23.3.0. I will see if this was a known issue, but please obtain the latest driver from https://www.vertica.com/download/vertica/client-drivers/
ODBC driver is backwards compatible to Vertica 8 so the latest client should still work with your server.

I updated vertica driver to 23.3, but still i am getting error . i compiled code using /usr/lib/libodbc.so -> libodbc.so.2.0.0 , and i run the executable in another server having vertica driver/client 23.3, where is the issue i am not getting.

[prs@v112 ~]$ rpm -qa | grep vertica-client
vertica-client-23.3.0-0.x86_64
[prs@v112 ~]$
[prs@v112 ~]$
[prs@v112 ~]$ ./TEST_BatchInsert
Allocated an environment handle.
Set application to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disabling autocommit.
Creating prepared statement
Created prepared statement.
Bound EvrEvent array to prepared statement
Bound DateAndTime array to prepared statement
Set numbers of rows
not successful!
Committing transaction
Committed transaction
Free handles.

zachariam · August 2023

@SruthiA said:
@zachariam : I am able to reproduce the issue locally

[dbadmin@node3 ~]$ g++ -o test9 test.cpp -lodbc
[dbadmin@node3 ~]$ ./test9
Allocated an environment handle.
Set application to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disabling autocommit.
Creating prepared statement
Created prepared statement.
Bound EvrEvent array to prepared statement
Bound DateAndTime array to prepared statement
Set numbers of rows
not successful!
Committing transaction
Committed transaction
Free handles.
[dbadmin@node3 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

Bad terminal type: "xterm". Will assume vt100.
test=> select * from test_table;
evr_event | gen_time
-----------+---------------------
1 | 2023-08-10 11:30:00
(1 row)

test=> select version();

version

Vertica Analytic Database v23.3.0-1
(1 row)

I added more debugging with a helper function and found that insertion is failing because of datetime overflow.

[dbadmin@node3 ~]$ g++ -o test10 test1.cpp -lodbc
[dbadmin@node3 ~]$ ./test10
Allocated an environment handle.
Set application to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disabling autocommit.
Creating prepared statement
Created prepared statement.
Bound EvrEvent array to prepared statement
Bound DateAndTime array to prepared statement
Set numbers of rows

error record 1
sqlstate: 22008
detailed msg: [Vertica][Support] (40520) Datetime field overflow resulting from invalid datetime.
native error code: 40520

not successful!
Committing transaction
Committed transaction
Free handles.
[dbadmin@node3 ~]

How to solve this issue, how to set the dates properly, can you help. how you are able to print some debugging information, can you tell how to enable it, my executable just says not successful.

[prs@v112 ~]$ rpm -qa | grep vertica-client
vertica-client-23.3.0-0.x86_64
[prs@v112 ~]$
[prs@v112 ~]$
[prs@v112 ~]$ ./TEST_BatchInsert
Allocated an environment handle.
Set application to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disabling autocommit.
Creating prepared statement
Created prepared statement.
Bound EvrEvent array to prepared statement
Bound DateAndTime array to prepared statement
Set numbers of rows
not successful!
Committing transaction
Committed transaction
Free handles.

zachariam · August 2023

@SruthiA said:
@zachariam : I got it working. I had to do couple of changes. Please find the attached code.

[dbadmin@node3 ~]$ g++ -o test10 test1.cpp -lodbc
[dbadmin@node3 ~]$ ./test10
Allocated an environment handle.
Set application to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disabling autocommit.
Creating prepared statement
Created prepared statement.
Bound EvrEvent array to prepared statement
Bound DateAndTime array to prepared statement
Set numbers of rows
successful.
Row 1 inserted successsfully
Row 2 inserted successsfully
Row 3 inserted successsfully
Committing transaction
Committed transaction
Free handles.
[dbadmin@node3 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

Bad terminal type: "xterm". Will assume vt100.
test=> select * from test_table;
evr_event | gen_time
-----------+---------------------
1 | 2023-08-10 11:30:11
2 | 2023-08-10 11:30:01
3 | 2023-08-10 11:30:01
(3 rows)

test=>

@SruthiA , Thanks , it worked .

zachariam · August 2023

@zachariam said:

@SruthiA said:
@zachariam : I got it working. I had to do couple of changes. Please find the attached code.

[dbadmin@node3 ~]$ g++ -o test10 test1.cpp -lodbc
[dbadmin@node3 ~]$ ./test10
Allocated an environment handle.
Set application to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disabling autocommit.
Creating prepared statement
Created prepared statement.
Bound EvrEvent array to prepared statement
Bound DateAndTime array to prepared statement
Set numbers of rows
successful.
Row 1 inserted successsfully
Row 2 inserted successsfully
Row 3 inserted successsfully
Committing transaction
Committed transaction
Free handles.
[dbadmin@node3 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

Bad terminal type: "xterm". Will assume vt100.
test=> select * from test_table;
evr_event | gen_time
-----------+---------------------
1 | 2023-08-10 11:30:11
2 | 2023-08-10 11:30:01
3 | 2023-08-10 11:30:01
(3 rows)

test=>

I will try this and see.

I followed Vertica documentation page, can you edit/add example having DATETIME batch insert also, current example is having only integer and text field batch insert. since most of the tables having datetime column so it will help many vertica c++ application developers. based on below example only i tried but in that datetime example not available

https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/ConnectingToVertica/ClientODBC/UsingBatchInserts.htm

zachariam · August 2023

@SruthiA said:
@zachariam : I got it working. I had to do couple of changes. Please find the attached code.

[dbadmin@node3 ~]$ g++ -o test10 test1.cpp -lodbc
[dbadmin@node3 ~]$ ./test10
Allocated an environment handle.
Set application to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disabling autocommit.
Creating prepared statement
Created prepared statement.
Bound EvrEvent array to prepared statement
Bound DateAndTime array to prepared statement
Set numbers of rows
successful.
Row 1 inserted successsfully
Row 2 inserted successsfully
Row 3 inserted successsfully
Committing transaction
Committed transaction
Free handles.
[dbadmin@node3 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

Bad terminal type: "xterm". Will assume vt100.
test=> select * from test_table;
evr_event | gen_time
-----------+---------------------
1 | 2023-08-10 11:30:11
2 | 2023-08-10 11:30:01
3 | 2023-08-10 11:30:01
(3 rows)

test=>

actual issue with the code was some junk value was going as input , i added memset and now my code is working without this modifications. i added below code before filling date and time to the structure. Thanks for your help

memset(&lDateAndTime, 0, sizeof(lDateAndTime));

SruthiA · August 2023

@zachariam : Good to know it worked and thanks for sharing your feedback. I have raised a documentation JIRA .

how to do batch insert into table having "timestamp" column using c++ ODBC code

Best Answer

Answers

i create a test table like below

I Create a test code like below to insert 3 rows to this test table

include <stdio.h>

include <stdlib.h>

include <time.h>

include <unistd.h>

include <sql.h>

include <sqltypes.h>

include <sqlext.h>

include <string.h>

I compiled the code like

I run the executable like below

Table content after execute

Problem

version

version

Leave a Comment