ADONET64 vertica driver returns empty DataTable on Select count(*)

joergschaber

Hi,

sometimes my ADONET64 vertica driver returns an empty DataTable, when I run a "Select count(*) from " a View.
The c# code is the following:

            VerticaCommand command = _dbConnection.CreateCommand();
            command.Connection = _dbConnection;

           command.CommandText = "SELECT COUNT(*) FROM View";

            // Create the DataAdapter
            VerticaDataAdapter adapter = new VerticaDataAdapter {SelectCommand = command};

            // Fill the DataTable
            DataTable result = new DataTable();
            adapter.Fill(result);

After adapter.Fill(result) result should be a data table with one row, but sometimes it is just empty.
Any idea? Is that a bug in the driver?
vertica and driver version is v10.0.1-6

joergschaber

I think we found the problem. We figured that we probably had parallel queries using a async methods running the queries.
Thanks.

Jim_Knicely

Can you check to see if the query is reaching Vertica?

To do that, query the SYSTEMS tables.

Example...

verticademos=> CREATE VIEW d_view AS SELECT * FROM dual;
CREATE VIEW

verticademos=> \! vsql -c "SELECT COUNT(*) FROM d_view;"
 COUNT
-------
     1
(1 row)

verticademos=> SELECT user_name, transaction_id, statement_id, success FROM query_requests WHERE request = 'SELECT COUNT(*) FROM d_view;';z

 user_name |  transaction_id   | statement_id | success
-----------+-------------------+--------------+---------
 dbadmin   | 45035996274039952 |            1 | t
(1 row)

verticademos=> SELECT session_id, transaction_id, statement_id, processed_row_count FROM query_profiles WHERE query = 'SELECT COUNT(*) FROM d_view;';
              session_id              |  transaction_id   | statement_id | processed_row_count
--------------------------------------+-------------------+--------------+---------------------
 erticademos_node0001-1145916:0x97e5a | 45035996274039952 |            1 |                   1
(1 row)

Are there times whene the SUCCESS flag is false?

If so, take a peek at the DC_ERRORS Data Collector table for the related TRANSACTION_ID and STATEMENT_ID:

SELECT log_message FROM dc_errors WHERE transaction_id = 45035996274039952 AND statement_id = 1;

joergschaber

Hi,

yes, we checked and it seems that queries are not reaching Vertica and there are no queries with success = false.
This errors seems to occur, when we query data sets where count = 0 and/or we have several queries in a milliseconds time interval.