Re: [sqlite] which func could get the number of rows
Yes, sqlite3_get_table() is good to handle the mission. and I am always using it. but I found that the prefomance was a little weak. and so I want to get some other API to handle the same mission. Kees Nuyt wrote: > > On Sat, 7 Mar 2009 01:09:28 -0800 (PST), liubin liu > <7101...@sina.com> wrote: > >>I'm very happy that my question have triggerred the wiki-FAQ's question. >> >>but if the next version could solve(settle?) the question, >>many guys will be happy, :) > > In my opinion, there is no problem, so there is nothing to > solve. > > By the way, there is new code in the works which will > improve the performance of COUNT(*) in some cases. > See the timeline: > http://www.sqlite.org/cvstrac/timeline > 2009-Feb-24 and 2009-Feb-25 > >>and now, does it mean that we have to use link-list struct to write such >>kind of codes when using "sqlite3_prepare_v2() + sqlite3_step() + >>sqlite3_column_*()"? > > Perhaps the sqlite3_get_table() API is what you are looking > for? http://www.sqlite.org/c3ref/free_table.html > >> >>Kees Nuyt wrote: >>> >>> On Fri, 6 Mar 2009 02:15:10 -0800 (PST), liubin liu >>> <7101...@sina.com> wrote: >>> which func could get the number of rows? >>> >>> There is no function to retrieve the number of rows in a >>> result set. SQLite doesn't know the number in advance, but >>> returns row by row while iterating through the tables. The >>> application can increment a row counter as needed at every >>> successful sqlite3_step() . >>> >>> Some wrappers are able to collect all rows in a resultset in >>> a in-memory table, so they can return the number of rows. >>> >>> You can always get the number of rows that a certain SELECT >>> statement would return at the cost of some performance: >>> >>>BEGIN IMMEDIATE TRANSACTION; >>>SELECT COUNT(*) FROM x WHERE y; >>>SELECT a,b,c FROM x WHERE y; >>>ROLLBACK TRANSACTION; >>> >>> You have to wrap this in a transaction to prevent other >>> connections from inserting / deleting rows between the two >>> SELECT statements. >>> >>> http://www.sqlite.org/lang_transaction.html >>> >>> I hope this helps and I added it to the wiki FAQ: >>> >>> http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/which-func-could-get-the-number-of-rows-tp22369246p22407568.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which func could get the number of rows
On Sat, 7 Mar 2009 01:09:28 -0800 (PST), liubin liu <7101...@sina.com> wrote: >I'm very happy that my question have triggerred the wiki-FAQ's question. > >but if the next version could solve(settle?) the question, >many guys will be happy, :) In my opinion, there is no problem, so there is nothing to solve. By the way, there is new code in the works which will improve the performance of COUNT(*) in some cases. See the timeline: http://www.sqlite.org/cvstrac/timeline 2009-Feb-24 and 2009-Feb-25 >and now, does it mean that we have to use link-list struct to write such >kind of codes when using "sqlite3_prepare_v2() + sqlite3_step() + >sqlite3_column_*()"? Perhaps the sqlite3_get_table() API is what you are looking for? http://www.sqlite.org/c3ref/free_table.html > >Kees Nuyt wrote: >> >> On Fri, 6 Mar 2009 02:15:10 -0800 (PST), liubin liu >> <7101...@sina.com> wrote: >> >>>which func could get the number of rows? >> >> There is no function to retrieve the number of rows in a >> result set. SQLite doesn't know the number in advance, but >> returns row by row while iterating through the tables. The >> application can increment a row counter as needed at every >> successful sqlite3_step() . >> >> Some wrappers are able to collect all rows in a resultset in >> a in-memory table, so they can return the number of rows. >> >> You can always get the number of rows that a certain SELECT >> statement would return at the cost of some performance: >> >>BEGIN IMMEDIATE TRANSACTION; >>SELECT COUNT(*) FROM x WHERE y; >>SELECT a,b,c FROM x WHERE y; >>ROLLBACK TRANSACTION; >> >> You have to wrap this in a transaction to prevent other >> connections from inserting / deleting rows between the two >> SELECT statements. >> >> http://www.sqlite.org/lang_transaction.html >> >> I hope this helps and I added it to the wiki FAQ: >> >> http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which func could get the number of rows
I'm very happy that my question have triggerred the wiki-FAQ's question. but if the next version could solve(settle?) the question, many guys will be happy, :) and now, does it mean that we have to use link-list struct to write such kind of codes? Kees Nuyt wrote: > > On Fri, 6 Mar 2009 02:15:10 -0800 (PST), liubin liu > <7101...@sina.com> wrote: > >>which func could get the number of rows? > > There is no function to retrieve the number of rows in a > result set. SQLite doesn't know the number in advance, but > returns row by row while iterating through the tables. The > application can increment a row counter as needed at every > successful sqlite3_step() . > > Some wrappers are able to collect all rows in a resultset in > a in-memory table, so they can return the number of rows. > > You can always get the number of rows that a certain SELECT > statement would return at the cost of some performance: > >BEGIN IMMEDIATE TRANSACTION; >SELECT COUNT(*) FROM x WHERE y; >SELECT a,b,c FROM x WHERE y; >ROLLBACK TRANSACTION; > > You have to wrap this in a transaction to prevent other > connections from inserting / deleting rows between the two > SELECT statements. > > http://www.sqlite.org/lang_transaction.html > > I hope this helps and I added it to the wiki FAQ: > > http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/which-func-could-get-the-number-of-rows-tp22369246p22385592.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which func could get the number of rows
On Fri, 6 Mar 2009 02:15:10 -0800 (PST), liubin liu <7101...@sina.com> wrote: >which func could get the number of rows? There is no function to retrieve the number of rows in a result set. SQLite doesn't know the number in advance, but returns row by row while iterating through the tables. The application can increment a row counter as needed at every successful sqlite3_step() . Some wrappers are able to collect all rows in a resultset in a in-memory table, so they can return the number of rows. You can always get the number of rows that a certain SELECT statement would return at the cost of some performance: BEGIN IMMEDIATE TRANSACTION; SELECT COUNT(*) FROM x WHERE y; SELECT a,b,c FROM x WHERE y; ROLLBACK TRANSACTION; You have to wrap this in a transaction to prevent other connections from inserting / deleting rows between the two SELECT statements. http://www.sqlite.org/lang_transaction.html I hope this helps and I added it to the wiki FAQ: http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which func could get the number of rows
On 6/03/2009 9:15 PM, liubin liu wrote: > which func could get the number of rows? select count(*) from your_table_name; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users