Why does vsql can return all the records, while program using ODBC driver can't?

Question

I do a simple test for Vertica:

ha=> insert into test(Name, City) values( 'Nan', 'Nanjing');
 OUTPUT 
--------
      1
(1 row)

ha=> select node_name, wos_row_count, ros_row_count from projection_storage where anchor_table_name = 'test';
   node_name   | wos_row_count | ros_row_count 
---------------+---------------+---------------
 v_ha_node0001 |             1 |             3
(1 row)

ha=> select * from test;
   ID   | Name |  City   
--------+------+---------
 250001 | Nan  | Nanjing
 250002 | Nan  | Nanjing
 250003 | Nan  | Nanjing
 250004 | Nan  | Nanjing
(4 rows)

The select operation displays OK (the data in WOS and ROSall display).

Then I write a simple program which uses ODBC:

ret = SQLExecDirect(stmt_handle, (SQLCHAR*)"select * from test", SQL_NTS);
if (!SQL_SUCCEEDED(ret))
{
    printf("Execute statement failed\n");
    goto ERR;
}

while ((ret = SQLFetch(stmt_handle)) == SQL_SUCCESS)
{
    row_num++;
}

printf("Row number is %d\n", row_num);

But the result is:

Row number is 3

It doesn't count the data in WOS.

And the DbVisualizer also displays 3 rows of data:

Does it need some special option for using ODBC? Thanks very much in advance!

Answer 1

By default, vsql is in transaction mode. As long as you keep your session open, inside vsql, you will see what you expect, as you are inside a transaction.

As soon as you go outside of your session (odbc, dbvis), the transaction is not (yet) visible. To make it visible to other sessions, you need to issue a 'COMMIT;' inside vsql. Then (as confirmed) you can access data from odbc and dbvis.

You can set (vsql only) your transaction to be autocommit with

\set AUTOCOMMIT on
-- disable with
\set AUTOCOMMIT off

To know if autocommit is enabled, you can use show:

show AUTOCOMMIT;
    name    | setting
------------+---------
 autocommit | off
(1 row)

You can even do it on your vsql call with --set autocommit=on. Is that a good idea or not is another question.

ODBC lets you set autocommit in different ways, see the odbc doc.