[sqlite] Compile error's on Visual Studio 2010
Hi, I am new to sqlite3 and just downloaded the code trying to test it from a C++ application using Visual Studio 2010. I just created a class and included the sqlite3.c and sqlite3.h from amalgamation-3070602 directly. The header compiles fine, but on the sqlite3.c I get typecast errors. This is because my compiler is set to a high warning level and sqlite3 code does things like converting from void* to struct pointers. Is there any way I can get around this without lowering the compiler's warning level? Jan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile error's on Visual Studio 2010
On Sun, May 8, 2011 at 6:50 AM, Jan Berger jan.ber...@video24.no wrote: Hi, I am new to sqlite3 and just downloaded the code trying to test it from a C++ application using Visual Studio 2010. I just created a class and included the sqlite3.c and sqlite3.h from amalgamation-3070602 directly. The header compiles fine, but on the sqlite3.c I get typecast errors. This is because my compiler is set to a high warning level and sqlite3 code does things like converting from void* to struct pointers. Is there any way I can get around this without lowering the compiler's warning level? No. See http://www.sqlite.org/testing.html#staticanalysis for additional information. Jan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17
Am Samstag, den 07.05.2011, 03:00 + schrieb sqlite-mana...@googlecode.com: Comment #6 on issue 608 by mrinal.k...@gmail.com: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17 http://code.google.com/p/sqlite-manager/issues/detail?id=608 This is the equivalent output on my computer; and Firefox 3.6.17 has sqlite 3.7.4 (as you have reported) user@user-office-laptop:~$ sqlite3 --version 3.7.4 user@user-office-laptop:~$ sqlite3 SQLite version 3.7.4 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select date('2011-04-29', quote(-3) || 'day'); Wait a minute. The previous sql produces 'NULL' output, yes? If so, it's an sqlite3 problem with that 3.7.4 version, because with my 3.6.16, it's correct as you can see in my previous mail: Weitergeleitete Nachricht Von: Thomas Mittelstaedt tmsta...@t-mittelstaedt.de Reply-to: tmsta...@t-mittelstaedt.de An: sqlite-mana...@googlecode.com Betreff: Re: Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17 Datum: Sat, 07 May 2011 04:40:17 +0200 Am Samstag, den 07.05.2011, 01:41 + schrieb sqlite-mana...@googlecode.com: Comment #4 on issue 608 by mrinal.k...@gmail.com: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17 http://code.google.com/p/sqlite-manager/issues/detail?id=608 sqlite manager in firefox uses the sqlite library which is included with firefox and not the one separately installed on the computer. It is possible that the firefox version 3.6.17 includes a higher version of sqlite library as compared to the earlier firefox versions. According to your report, firefox 3.6.17 includes sqlite 3.7.4 My firefox and Ubuntu have exactly this version of sqlite. tom@linux1:~$ sqlite3 --version 3.6.16 tom@linux1:~$ sqlite3 SQLite version 3.6.16 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select date('2011-04-29', quote(-3) || 'day'); 2011-04-26 sqlite -- thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17
On Sat, May 7, 2011 at 2:03 PM, Thomas Mittelstaedt tmsta...@t-mittelstaedt.de wrote: Am Samstag, den 07.05.2011, 03:00 + schrieb sqlite-mana...@googlecode.com: Comment #6 on issue 608 by mrinal.k...@gmail.com: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17 http://code.google.com/p/sqlite-manager/issues/detail?id=608 This is the equivalent output on my computer; and Firefox 3.6.17 has sqlite 3.7.4 (as you have reported) user@user-office-laptop:~$ sqlite3 --version 3.7.4 user@user-office-laptop:~$ sqlite3 SQLite version 3.7.4 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select date('2011-04-29', quote(-3) || 'day'); Wait a minute. The previous sql produces 'NULL' output, yes? If so, it's an sqlite3 problem with that 3.7.4 version, because with my 3.6.16, it's correct as you can see in my previous mail: You need a space between the 3 and the 'day'. Your code above is equivalent to: SELECT date('2011-04-29', '-3day'); This is apparently what you mean: SELECT date('2011-04-29', '-3 day'); Weitergeleitete Nachricht Von: Thomas Mittelstaedt tmsta...@t-mittelstaedt.de Reply-to: tmsta...@t-mittelstaedt.de An: sqlite-mana...@googlecode.com Betreff: Re: Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17 Datum: Sat, 07 May 2011 04:40:17 +0200 Am Samstag, den 07.05.2011, 01:41 + schrieb sqlite-mana...@googlecode.com: Comment #4 on issue 608 by mrinal.k...@gmail.com: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17 http://code.google.com/p/sqlite-manager/issues/detail?id=608 sqlite manager in firefox uses the sqlite library which is included with firefox and not the one separately installed on the computer. It is possible that the firefox version 3.6.17 includes a higher version of sqlite library as compared to the earlier firefox versions. According to your report, firefox 3.6.17 includes sqlite 3.7.4 My firefox and Ubuntu have exactly this version of sqlite. tom@linux1:~$ sqlite3 --version 3.6.16 tom@linux1:~$ sqlite3 SQLite version 3.6.16 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select date('2011-04-29', quote(-3) || 'day'); 2011-04-26 sqlite -- thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile error's on Visual Studio 2010
Hello Jan, Move it into it's own project, make it a static lib and turn down the warning level just for this project. That's what I do anyway. I'm not willing to touch the code but, that seems to be the only other solution. Sunday, May 8, 2011, 6:50:31 AM, you wrote: JB Hi, JB JB I am new to sqlite3 and just downloaded the code trying to test it from a JB C++ application using Visual Studio 2010. I just created a class and JB included the sqlite3.c and sqlite3.h from amalgamation-3070602 directly. JB JB The header compiles fine, but on the sqlite3.c I get typecast errors. This JB is because my compiler is set to a high warning level and sqlite3 code does JB things like converting from void* to struct pointers. JB JB Is there any way I can get around this without lowering the compiler's JB warning level? JB JB Jan JB ___ JB sqlite-users mailing list JB sqlite-users@sqlite.org JB http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17
Change that into: select date('2011-04-29', quote(-3) || ' day'); (note the space before day). Looks like a parsing change. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17
On Sun, 08 May 2011 09:36:43 -0400, Jean-Christophe Deschamps j...@antichoc.net wrote: Change that into: select date('2011-04-29', quote(-3) || ' day'); (note the space before day). Looks like a parsing change. Apparently, yes, between the 3.6 and 3.7 lineages: http://www.mail-archive.com/sqlite-users@sqlite.org/msg58853.html Very truly, SAMUEL ADAM ◊ http://certifound.com/ ◊ I read list mail sporadically. 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States April 15, 2011 Courtroom Video in re Adam v. Supreme Court of N.J.: http://www.youtube.com/watch?v=GPw2W2-Ujyc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Determining how many columns were returned in a query
How does one go about finding out how many rows a query returns? Is there a way to find out the id of a particular column? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
How does one go about finding out how many rows a query returns? This is the number of time sqlite3_step can be called successfully until it returns SQLITE_DONE. Is there a way to find out the id of a particular column? AFAICT column don't have ids. You can read column names or alias using sqlite3_column_name[16]. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
On 8 May 2011, at 4:00pm, Sam Carleton wrote: How does one go about finding out how many rows a query returns? This was asked earlier this week. There is no magic way. Step through the rows and count them. You can, of course, do a preliminary SELECT for 'count(*)' and see what answer is returned. Is there a way to find out the id of a particular column? It depends what you think a column's id is. But SQLite maintains a pseudo-column of INTEGERs called 'id' or 'rowid' (several other names) which you can ask for by name. For instance SELECT rowid,* FROM myTable Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
On 8 May 2011, at 4:09pm, Simon Slavin wrote: On 8 May 2011, at 4:00pm, Sam Carleton wrote: Is there a way to find out the id of a particular column? It depends what you think a column's id is. But SQLite maintains a pseudo-column of INTEGERs called 'id' or 'rowid' (several other names) which you can ask for by name. For instance SELECT rowid,* FROM myTable Whoops. Sorry I somehow understood that as 'rowid'. Columns don't have ids. See Jean-Christophe's reply for better details. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
On Sun, 08 May 2011 11:09:36 -0400, Simon Slavin slav...@bigfraud.org wrote: On 8 May 2011, at 4:00pm, Sam Carleton wrote: How does one go about finding out how many rows a query returns? This was asked earlier this week. There is no magic way. Step through the rows and count them. You can, of course, do a preliminary SELECT for 'count(*)' and see what answer is returned. How about: SELECT count() FROM (original query’s SELECT statement); Depending on the query, this might be possible and/or more obvious: SELECT count() FROM original query’s join-source WHERE original query’s WHERE clause; Quick test: sqlite CREATE TABLE Test (col1 INTEGER, col2 INTEGER); sqlite INSERT INTO Test VALUES (0, 1); sqlite INSERT INTO Test VALUES (1, 1); sqlite INSERT INTO Test VALUES (1, 2); sqlite SELECT count() FROM Test WHERE col2 = 1; 2 sqlite SELECT count() FROM Test WHERE col2 = 2; 1 sqlite SELECT count() FROM Test WHERE col2 = 0; 0 sqlite SELECT count() FROM ... (SELECT col1 FROM Test WHERE col2 = 1); 2 sqlite SELECT count() FROM ... (SELECT col1 FROM Test WHERE col2 = 3); 0 That looks like a fairly “magic way” to me—and I have actually used that method with nontrivial queries. Am I missing something? Too, I know how an aggregate function is made; and I don’t see how this *wouldn’t* work for an obvious implementation of a count() function. Very truly, SAMUEL ADAM ◊ http://certifound.com/ ◊ I read list mail sporadically. 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States April 15, 2011 Courtroom Video in re Adam v. Supreme Court of N.J.: http://www.youtube.com/watch?v=GPw2W2-Ujyc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
How about: SELECT count() FROM (original queryâs SELECT statement); You can do that (and variations) but this is a completely distinct statement. I meant that there is no possibility to recover the row count of a result set before it goes to completion (by iterating step), just because the SQLite engine has no idea itself. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
On May 8, 2011, at 11:09 AM, Jean-Christophe Deschamps j...@antichoc.net wrote: How does one go about finding out how many rows a query returns? This is the number of time sqlite3_step can be called successfully until it returns SQLITE_DONE. I had it wrong in the email body, I meant how many columns are in query? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
On May 8, 2011, at 11:46 AM, Sam Carleton wrote: On May 8, 2011, at 11:09 AM, Jean-Christophe Deschamps j...@antichoc.net wrote: How does one go about finding out how many rows a query returns? This is the number of time sqlite3_step can be called successfully until it returns SQLITE_DONE. I had it wrong in the email body, I meant how many columns are in query? Since, ostensibly, you are the one who queried in the first place, shouldn't you know that already? Maybe there is more to this question that you are not stating. Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
Sam Carleton scarle...@gmail.com wrote: I had it wrong in the email body, I meant how many columns are in query? sqlite3_column_count. Don't even need to execute the query for that, just prepare it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile error's on Visual Studio 2010
Thanks I finally managed to compile it. My challenge was that I am using a C++ project with default setting which uses C++ compiler setting, but to allow old C features you need to set the /TC option on the VC compiler - otherwise the typecasts are treated as errors etc. Jan -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Teg Sent: 8. mai 2011 13:30 To: General Discussion of SQLite Database Subject: Re: [sqlite] Compile error's on Visual Studio 2010 Hello Jan, Move it into it's own project, make it a static lib and turn down the warning level just for this project. That's what I do anyway. I'm not willing to touch the code but, that seems to be the only other solution. Sunday, May 8, 2011, 6:50:31 AM, you wrote: JB Hi, JB JB I am new to sqlite3 and just downloaded the code trying to test it from a JB C++ application using Visual Studio 2010. I just created a class and JB included the sqlite3.c and sqlite3.h from amalgamation-3070602 directly. JB JB The header compiles fine, but on the sqlite3.c I get typecast errors. This JB is because my compiler is set to a high warning level and sqlite3 code does JB things like converting from void* to struct pointers. JB JB Is there any way I can get around this without lowering the compiler's JB warning level? JB JB Jan JB ___ JB sqlite-users mailing list JB sqlite-users@sqlite.org JB http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17
On Sun, 08 May 2011 17:27:22 +0200, Thomas Mittelstaedt tmsta...@t-mittelstaedt.de wrote: Am Sonntag, den 08.05.2011, 10:08 -0400 schrieb Samuel Adam: On Sun, 08 May 2011 09:36:43 -0400, Jean-Christophe Deschamps j...@antichoc.net wrote: Change that into: select date('2011-04-29', quote(-3) || ' day'); (note the space before day). Looks like a parsing change. Apparently, yes, between the 3.6 and 3.7 lineages: http://www.mail-archive.com/sqlite-users@sqlite.org/msg58853.html --- firefox-3.6.17+build3+nobinonly.orig/debian/changelog +++ firefox-3.6.17+build3+nobinonly/debian/changelog @@ -0,0 +1,5526 @@ +firefox (3.6.17+build3+nobinonly-0ubuntu0.10.04.1) lucid-security; urgency=low + * Bump minimum version of sqlite to 3.7.1 after landing of (bmo: 583611) aka +Upgrade to SQLite 3.7.1 +- update debian/rules Looks like the regular update of the older ubuntu 9.10 release, karmic, of package firefox, now requires sqlite 3.7.xx, while the version installed on this system still is 3.6.16. Is that a problem? On many platforms, firefox installs its own libsqlite3.so, e.g. in /usr/lib/mps, so it doesn't require a specific system-wide sqlite3 version. I don't know about this specific Ubuntu / Firefox releases. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
On May 8, 2011, at 12:53 PM, Igor Tandetnik itandet...@mvps.org wrote: Sam Carleton scarle...@gmail.com wrote: I had it wrong in the email body, I meant how many columns are in query? sqlite3_column_count. Don't even need to execute the query for that, just prepare it. Ah, thank you! Is my impression current there is no function call to get the column index given a name? Like there is for getting the index of a binding. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
On Sun, May 08, 2011 at 11:00:29AM -0400, Sam Carleton scratched on the wall: How does one go about finding out how many rows a query returns? sqlite3_column_count() Is there a way to find out the id of a particular column? sqlite3_column_name() -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile error's on Visual Studio 2010
On Sun, May 08, 2011 at 12:50:31PM +0200, Jan Berger scratched on the wall: I am new to sqlite3 and just downloaded the code trying to test it from a C++ application using Visual Studio 2010. I just created a class and included the sqlite3.c and sqlite3.h from amalgamation-3070602 directly. The header compiles fine, but on the sqlite3.c I get typecast errors. This is because my compiler is set to a high warning level and sqlite3 code does things like converting from void* to struct pointers. Is there any way I can get around this without lowering the compiler's warning level? Use the correct compiler. SQLite is a C program and should be compiled with a C compiler. In C, you are allowed to assign to/from a void* without an explicit cast. That is the whole point of the void* type. Automatic casting is not supported in C++, however. There are strong arguments that allowing the automatic cast provides better type safety, but that's a different discussion. In most default setups, Visual Studio insists on compiling .c files with the C++ compiler, even though-- as this very example points out-- the languages are different enough that this behavior seems questionable. If you are sure then project is configured to use the correct C-only compiler, then the warnings being thrown are bogus, and I'd suggest you turn them off. Phantom warnings are not useful. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
On May 8, 2011, at 11:06 AM, Jay A. Kreibich j...@kreibi.ch wrote: On Sun, May 08, 2011 at 11:00:29AM -0400, Sam Carleton scratched on the wall: Is there a way to find out the id of a particular column? sqlite3_column_name() I want to go the other way: I have the string name, I need the index of the column, same concept as sqlite3_bind_parameter_index(). Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
Sam Carleton scarle...@gmail.com wrote: On May 8, 2011, at 11:06 AM, Jay A. Kreibich j...@kreibi.ch wrote: On Sun, May 08, 2011 at 11:00:29AM -0400, Sam Carleton scratched on the wall: Is there a way to find out the id of a particular column? sqlite3_column_name() I want to go the other way: I have the string name, I need the index of the column, same concept as sqlite3_bind_parameter_index(). You'll have to enumerate all columns, get the name of each, and compare it with the desired name. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
On 8 May 2011, at 8:04pm, Igor Tandetnik wrote: Sam Carleton scarle...@gmail.com wrote: I want to go the other way: I have the string name, I need the index of the column, same concept as sqlite3_bind_parameter_index(). You'll have to enumerate all columns, get the name of each, and compare it with the desired name. Out of interest, are you trying to analyse the results of a SELECT * ? Because since it's your query in the first place, you should know what columns you asked for. Generally, experienced programmers don't use SELECT * inside real applications, although it can be useful for utilities. The problem comes when you want to change your schema and find if difficult to find all the SELECTs you now have to modify. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
Sam Carleton scarle...@gmail.com wrote: On May 8, 2011, at 12:53 PM, Igor Tandetnik itandet...@mvps.org wrote: Sam Carleton scarle...@gmail.com wrote: I had it wrong in the email body, I meant how many columns are in query? sqlite3_column_count. Don't even need to execute the query for that, just prepare it. Ah, thank you! Is my impression current there is no function call to get the column index given a name? Indeed there is no such function. Note that there may be more than one column with the same name. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
On Sun, May 8, 2011 at 3:08 PM, Simon Slavin slav...@bigfraud.org wrote: Out of interest, are you trying to analyse the results of a SELECT * ? Because since it's your query in the first place, you should know what columns you asked for. Nope, I NEVER do SELECT *, very, very evil! Great for development and testing, but not in code! I happen to have a code path such that the select statement can return 1, 3 or 5 columns. I know I could go based on count, but if I could do it by name that would be safer. I had not considered the point that multiple columns could have the same name, though, so I fully understand why such a function does not exist. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
I happen to have a code path such that the select statement can return 1, 3 or 5 columns. I know I could go based on count, but if I could do it by name that would be safer. I had not considered the point that multiple columns could have the same name, though, so I fully understand why such a function does not exist. Also beware that the names returned by sqlite3_column_name are in fact the aliases used (if any) by the select statement, thus users can foil you without bribing anyone nor subverting the engine. Say you have a table T with (col_A, sigma), you can get the following: select max(col_A) as sigma, total(sigma) / count(*) as col_A from T; names returned by sqlite3_column_name will be 'sigma' and 'col_A'. Gotcha! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
what language are you using? usually there is a property for the resultset object that will supply the number of columns in the result set and another property that will return the number of rows. using the number of columns allows you to index into the columns in a loop retrieving each column name as well as some basic properties. in my projects i have two tables that define my reports, one table is just the sql code and a descriptor, the second table defines the parameters, names, types and any sql code to verify the parameter is correctly entered. the user scrolls thru a grid, picks the report to run, the code sees if there are parameters and what kind(date, int, etc, etc) build a screen to get the parameters, very them and then executes it. The Sql code for the report uses select column_name as Header_Name syntax so my actual report handling logic just has to get the number of columns, loop thru that to get the header names, and data types for output masking, then just loop thru each row till its done. The actual report handler code stays pretty simple, most of the actual report processing is done by sqllite. Woody Wizard, at large I'm in shape, round is a shape! --- On Sun, 5/8/11, Sam Carleton scarle...@miltonstreet.com wrote: From: Sam Carleton scarle...@miltonstreet.com Subject: Re: [sqlite] Determining how many columns were returned in a query To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: Sunday, May 8, 2011, 7:20 PM On Sun, May 8, 2011 at 3:08 PM, Simon Slavin slav...@bigfraud.org wrote: Out of interest, are you trying to analyse the results of a SELECT * ? Because since it's your query in the first place, you should know what columns you asked for. Nope, I NEVER do SELECT *, very, very evil! Great for development and testing, but not in code! I happen to have a code path such that the select statement can return 1, 3 or 5 columns. I know I could go based on count, but if I could do it by name that would be safer. I had not considered the point that multiple columns could have the same name, though, so I fully understand why such a function does not exist. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction triggers?
FWIW, I have parsing, automatic creation of the crutch views, and creation of the DB triggers working. Next up: firing of DB triggers. The changes so far are fairly trivial, adding very few branches, which means that writing tests for them should be relatively simple too. That said, it's taken quite a few hours to figure it all out, and I'm sure firing the triggers too will be hard to get right but simple looking in the end. Hopefully I can have something worth submitting to the SQLite3 dev community soon, and hopefully there will be interest. If not, I'll see if I can get the open source project I'm doing this for to accept keeping patches to SQLite3. sqlite select * from sqlite_master; sqlite create trigger main.test2 on database disconnect begin select raise(abort, 'Hey, this works!'); end;sqlite select * from sqlite_master; view|sqlite_intview_disconnect|sqlite_intview_disconnect|0|CREATE VIEW sqlite_intview_disconnect AS SELECT 0 AS nothing trigger|test2|sqlite_intview_disconnect|0|CREATE TRIGGER test2 on database disconnect begin select raise(abort, 'Hey, this works!'); end sqlite insert into sqlite_intview_disconnect values (1); Error: Hey, this works! sqlite The syntax I have a parser for is: CREATE TRIGGER ON db-event BEGIN ... END; where db-event is one of DATABASE CONNECT, DATABASE DISCONNECT, TRANSACTION START, TRANSACTION COMMIT, and TRANSACTION ROLLBACK. Cheers, Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction triggers?
On 8 May 2011, at 9:42pm, Nico Williams wrote: CREATE TRIGGER ON db-event BEGIN ... END; where db-event is one of DATABASE CONNECT, DATABASE DISCONNECT, TRANSACTION START, TRANSACTION COMMIT, and TRANSACTION ROLLBACK. Just asking to warn you it's tricky ... A) When do you consider that a transaction starts ? B) How do you deal with ATTACHed databases ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transaction speed too slow?
From http://www.sqlite.org/faq.html#q19 it says A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second. Using Linux/Ubuntu 10.04 on an otherwise idle Atom powered Nettop with a 5400RPM disk drive I was expecting circa 45 transactions per second. However using the basic python script below I seem to be getting a lot slower results, namely: ext3 partition: 16 transactions per second jfs partition: 9 transactions per second :memory: 15798 transaction per second Does anyone have an explanation for this? The script used is below. I'd be grateful if people could confirm whether it is just my hardware or a common result FULL RESULTS: nick@Haribo:~$ sudo hdparm -W 0 /dev/sda /dev/sda: setting drive write-caching to 0 (off) write-caching = 0 (off) nick@Haribo:~$ python write-transactions-1.py Run Number: 1, Location: :memory: 0:00:00.000108 0:00:00.58 0:00:00.57 0:00:00.57 0:00:00.56 0:00:00.56 0:00:00.57 0:00:00.57 0:00:00.57 0:00:00.56 Time Avg: 0.62 Trans/sec Avg: 16155 Run Number: 2, Location: write-transactions-1.db 0:00:00.099678 0:00:00.121630 0:00:00.110672 0:00:00.099599 0:00:00.110782 0:00:00.099542 0:00:00.121776 0:00:00.099599 0:00:00.121794 0:00:00.099624 Time Avg: 0.108470 Trans/sec Avg: 9 --- nick@Haribo:~$ sudo hdparm -W 1 /dev/sda /dev/sda: setting drive write-caching to 1 (on) write-caching = 1 (on) nick@Haribo:~$ python write-transactions-1.py Run Number: 1, Location: :memory: 0:00:00.000113 0:00:00.57 0:00:00.56 0:00:00.56 0:00:00.56 0:00:00.57 0:00:00.57 0:00:00.56 0:00:00.56 0:00:00.56 Time Avg: 0.62 Trans/sec Avg: 16129 Run Number: 2, Location: write-transactions-1.db 0:00:00.001438 0:00:00.000898 0:00:00.000989 0:00:00.000984 0:00:00.000982 0:00:00.001945 0:00:00.001059 0:00:00.001169 0:00:00.000914 0:00:00.001063 Time Avg: 0.001144 Trans/sec Avg: 874 -- SCRIPT # Test Benchmark for Transactions speed per second using built in SQLite import sqlite3 import datetime contype = [:memory:, write-transactions-1.db] for runidx, runtype in enumerate(contype): # Heading print Run Number: %d, Location: %s % (runidx + 1, runtype) con = sqlite3.connect(runtype) con.isolation_level = None con.execute(PRAGMA synchronous = FULL) times = [] # Create the table con.execute(drop table if exists person) con.execute(create table person(firstname, lastname)) # Measure 10 points for run in range(10): # BEGIN transaction con.execute(begin) # Fill the table con.execute(insert into person(firstname, lastname) values (?, ?), (alex,bob)) # Start the clock begin = datetime.datetime.now() # COMMIT transaction con.execute(commit) # Stop the clock end = datetime.datetime.now() # Record the time times.append(end - begin) # Measure 10 points for run in range(10): # Display the time results print times[run] # Print Average import operator td = reduce(operator.add, times) totalresultsec = (float(td.microseconds) + (td.seconds + td.days * 24 * 3600) * 10**6) / 10**6 avgsec = totalresultsec / 10.0 print Time Avg: %f % avgsec print Trans/sec Avg: %0.0f % (1.0/avgsec) con.close() ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction triggers?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/08/2011 01:46 PM, Simon Slavin wrote: Just asking to warn you it's tricky ... A) When do you consider that a transaction starts ? B) How do you deal with ATTACHed databases ? C) What about SAVEPOINT? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3HB8YACgkQmOOfHg372QQtdQCg2lP7iP6DaznL8W6oCtMDOyKW IkwAoOLhnFscg4RMCKpebduLUdN885vv =T42x -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile error's on Visual Studio 2010
Hello Jay, I haven't found this to be the case. I have numerous C only library's I compile and I don't have to change the defaults to compile them. There is an option to force C++ compiles but, I don't believe it's on by default. JAK In most default setups, Visual Studio insists on compiling .c files JAK with the C++ compiler, even though-- as this very example points JAK out-- the languages are different enough that this behavior seems JAK questionable. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction triggers?
Indeed, I have been thinking about when database connect fires. My current thought is: on the first non-pragma statement executed (not prepared), not at db open time. I only care about commit, really, but if I can I'll do the others too. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction triggers?
On May 8, 2011 4:14 PM, Roger Binns rog...@rogerbinns.com wrote: C) What about SAVEPOINT? Sounds useful... I should add that too. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction triggers?
As for attached DBs, each DB gets its own db triggers. DB connect trigger firing should be about the same (first non-pragma statement affecting the attached db). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction triggers?
The reason to delay connection trigger firing to the first non-pragma statement would be to allow one to enable or disable db triggers. DB triggers should also be disabled by default, and ahould have a separate set of pragmas to enable or disable them. The main utility of connect triggers is to load extensions needed by, e.g., views and triggers, to create temp tables and triggers, and so on. That's too powerful to permit in untrusted DBs, which is why connection triggers must be disabled by default. The shell could warn of their presence too... Regarding savepoint and rollback to a savepoint, I'll have to think about that some more. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile error's on Visual Studio 2010
Actually it does set C++ by default - look under Properties C/C++ Advanced - you have Compile As VC 2008 and 2010 set this to Yes by default. Jan -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Teg Sent: 8. mai 2011 23:48 To: General Discussion of SQLite Database Subject: Re: [sqlite] Compile error's on Visual Studio 2010 Hello Jay, I haven't found this to be the case. I have numerous C only library's I compile and I don't have to change the defaults to compile them. There is an option to force C++ compiles but, I don't believe it's on by default. JAK In most default setups, Visual Studio insists on compiling .c files JAK with the C++ compiler, even though-- as this very example points JAK out-- the languages are different enough that this behavior seems JAK questionable. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users