How many records returned the select statement?

Jul 28, 2011 at 6:56 AM

Hello

early i use a special sqlite3-dll for vba.
But this dll can not read actuallay sqlite3-Databases with sqlite-version 3.7.x

So i have found this sqlite for excel.

In the special dll i had a function "number_of_rows_from_last_call" to get the row-count of the last select-statement.

In this sqlite for excel i have only found a function for changed records.

At the moment i get the count by loop the handle with RetVal = SQLite3Step(myStmtHandle) until RetVal = SQLITE_DONE.
I think this is very slow at selections with many records.

Is there also a simple (fast) function to get the row-count of the last select-statement?

Thanks for answer

 

Greets Sven

 

Coordinator
Jul 28, 2011 at 1:34 PM

Hi Sven,

I don't know of a SQLite function similar to sqlite_changes, that will return the number of rows returned in the last SELECT query. If you know of such a feature in SQLite that is not currently exposed by the SQLite for Excel wrapper, I'd be happy to add it.

If you are not actually retrieving the data, but just want the count, I guess you could say SELECT COUNT(*) FROM .....

If you are actually retrieving the data, I can't see how keeping a counter while you are looping through the data can make anything slow.

If you are trying to implement kind of paging for the results, consider SQLite's LIMIT and OFFSET clauses which you can add to the SELECT statement.

-Govert. 

Jul 29, 2011 at 6:27 AM

Hi Govert

Thanks for answer.

I Dont know how the other funtion "number_of_rows_from_last_call" is developed.
So i will use the Select Count-Statement.

 

Greets Sven

Aug 1, 2011 at 9:11 PM

Hello Govert,

i have found the c++ source-code for the old special vb-dll to connect sqlite3-Databases.
This VB-DLL included all SQLite-API's .
So i could use this DLL alone to connect the databases without the original SQLite3.DLL.

In the Sources i found the function to get the rowcount in the function sqlite_get_table.

Can i send you the sources to you per Mail? (to which adress?)
Perhaps you can find any ideas for your DLL.

Greets Sven

Coordinator
Aug 1, 2011 at 9:29 PM

Hi Sven,

sqlite_get_table is a utility function that returns all the data from a query as one block of memory. I think it will not be so easy to use from VBA, since all the conversion will need to happen when converting the data to VBA anyway. Internally, the SQLite code is doing the steps too, so I cannot see how it can be much faster than multiple calls to SQLite3Step. Have you tested on result sets of comparable size?

Regards,

Govert

Aug 2, 2011 at 1:20 PM

Hi Govert

I'm not the developer of the special VB-SQLite3-DLL. I have no VB-Studio to test/develop this Code.
I think this is the source-code for the SQLite3VB.dll from tannertech.net (but this website is now offline)
This SQLite3VB.dll is not compatible to sqlite-Databases 3.7.x
I have only found the source-Code for this dll in the internet and i can send this to you.

So you can perhaps find any helpful Code for your Project in the C++-Source-Code.

Should i send you this source-code ? (if yes, to which mail-Adress?)


Greets Sven

Coordinator
Aug 2, 2011 at 7:13 PM

Hi Sven,

I mean you can test the performance difference between calling sqlite_get_table through the other VB .dll you have, compared to calling SQLite3Step from your VBA code.

My email address is on the Home page, but I am not keen to look at other source code unless I know what the licence is.

-Govert