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

Nan_Xiao

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!

id10t

Hi!

GOTO???  Very interesting.

How row_num variable is initialized?  If its initialized to ZERO so you got correct answer - you counts rows starts from ZERO:

0 => row 1 1 => row 2 2 => row 3 ... n => row [n-1] 

EXAMPLE

>>> rows = [
...     'row 01',
...     'row 02',
...     'row 03',
...     'row 04']
>>> 
>>> for (row_index, row) in enumerate(rows):
...     print 'Row index is: {:0>3}\t but row order is: {}'.format(row_index, row)
... 
Row index is: 000  but row order is: row 01
Row index is: 001  but row order is: row 02
Row index is: 002  but row order is: row 03
Row index is: 003  but row order is: row 04

About DBViz:
Check against Vertica system tables what query DBViz sends to Vertica.

System Tables:
--------------
* sessions
* query_requests


Also you can find DBViz queries in a ODBC log file.

Best, Idiot.

Nan_Xiao

Thanks for your comment!
The root cause has been found: http://stackoverflow.com/questions/27164238/why-does-vsql-can-return-all-the-records-while-program-u....