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

i simple test vertica:

ha=> insert test(name, city) values( 'nan', 'nanjing');  output  --------       1 (1 row)  ha=> select node_name, wos_row_count, ros_row_count projection_storage anchor_table_name = 'test';    node_name   | wos_row_count | ros_row_count  ---------------+---------------+---------------  v_ha_node0001 |             1 |             3 (1 row)  ha=> select * 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 , rosall display).

then write simple program uses odbc:

ret = sqlexecdirect(stmt_handle, (sqlchar*)"select * 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 %d\n", row_num); 

but result is:

row number 3 

it doesn't count data in wos.

and dbvisualizer displays 3 rows of data:

does need special option using odbc? in advance!

by default, vsql in transaction mode. long keep session open, inside vsql, see expect, inside transaction.

as go outside of session (odbc, dbvis), transaction not (yet) visible. make visible other sessions, need issue 'commit;' inside vsql. (as confirmed) can access data odbc , dbvis.

you can set (vsql only) transaction autocommit with

\set autocommit on -- disable \set autocommit off 

to know if autocommit enabled, can use show:

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

you can on vsql call --set autocommit=on. idea or not question.

odbc lets set autocommit in different ways, see odbc doc.