[sqlite] Question regarding Sqlite In-Memory DB Insert
HiI am using SQLite In-Memory DB for loading cache data into memory. I am using dotnet. The data I am inserting are .3 million. When I use the same number of records to insert in Console App it takes around 5 min. However If I host my app in IIS and Insert the same number of records It takes 60 minutes. No change in logic The DataSource is flat file. I am curious why the insert is so slow when hosted in IIS? I tried to search but did to get any answer. Any help here is highly appreciated. thanksNihar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question regarding Sqlite In-Memory DB Insert
I think either (a) your computer is broken; (b) your database is broken; or, (c) your methodology is broken. While I cannot speak to the inherent (ample) inefficiencies of dotnot, inserting three times the number of records (that is, ~1 million with multiple indexes) takes one-fifth the time (~1 minute) whether in the command line shell or using Python. Using an on-disk database rather than a :memory: database takes a little longer, but still many times faster than you report (~2 minutes or just under twice as long). You do not report the versions of anything you are using and this information may be somewhat helpful. For example, are you using Sqlite 1.0 on Windows 286 with an 40 Megabyte MFM Hard Disk and 512 KB of RAM, or something a little more modern? --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Nihar Dash Sent: Wednesday, 31 December, 2014 14:23 To: sqlite-users@sqlite.org Subject: [sqlite] Question regarding Sqlite In-Memory DB Insert HiI am using SQLite In-Memory DB for loading cache data into memory. I am using dotnet. The data I am inserting are .3 million. When I use the same number of records to insert in Console App it takes around 5 min. However If I host my app in IIS and Insert the same number of records It takes 60 minutes. No change in logic The DataSource is flat file. I am curious why the insert is so slow when hosted in IIS? I tried to search but did to get any answer. Any help here is highly appreciated. thanksNihar ___ 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] Question on locks
Thanks for the input so far. To clarify the whole setup works like this 1- bash scripts run every 10 minutes and generate information that is inserted into tables in a sqlite db, the tables are only accessed once simultaneously 2- PHP scripts read from those tables to display information on frontend. I did disable part two, so right now this is all about the write process to the database. 4 scripts run simultaneously, writing 500 entries each through a while loop to 500 tables each every 10 minutes. The relevant part is here sqlite3 websites.db PRAGMA busy_timeout=1500;insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2); But I still get frequent Error: database is locked , for further testing I did only run one script at one time, but the error still occurs at the same rate. Please advice. Regards On Sat, Oct 25, 2014 at 8:19 PM, Simon Slavin slav...@bigfraud.org wrote: On 25 Oct 2014, at 7:16pm, Ali Jawad alijaw...@gmail.com wrote: Thanks Simon, the create process is a one off. As for the table name I did use this approach as to not accumulate too much data in one table and instead split the data in multiple tables. From a design POV in sqlite is this a mistake. And will the pragma for php eliminate locks ? You should definitely execute the PRAGMA as a separate command, not as part of your SELECT command. I do not know for sure that, done as above, it will fix your lock. I'm not sure why you are getting the locks. But it is the next step for you to try, and if it doesn't fix them it will provide good diagnostic information. Simon. ___ 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] Question on locks
On 26 Oct 2014, at 9:27am, Ali Jawad alijaw...@gmail.com wrote: right now this is all about the write process to the database. 4 scripts run simultaneously, writing 500 entries each through a while loop to 500 tables each every 10 minutes. The relevant part is here sqlite3 websites.db PRAGMA busy_timeout=1500;insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2); First, a database with 500 tables in is probably badly organised and will lead to slow operations (and therefore locks !). Any time you find yourself using a 500-value data variable as a table name (in your case, $SITE) you're probably doing something wrong. It would be better to organise your table so that the $SITE name is a column in a table: CREATE TABLE samples (sitename TEXT,date TEXT,eu whatever,us whatever) insert into samples (sitename,date,eu,us) values ($SITE,$DATE,$DIFF,$DIFF2); It also means that you never have to worry about $SITE containing a character that is not legal in a table name. However, in the rest of this reply I will assume that you have good reasons for not wanting to reorganise your data in this way. Second, you are opening and closing the database 500 times, and opening and closing the database requires a huge amount of unique access, and is therefore keeping it busy, and therefore locked for a long time. So instead of running the SQLite shell tool 500 times, run it just once. In your script which runs the shell tool, instead of putting the data directly into the database, have it write the data to a text file. This text file should read PRAGMA busy_timeout=1500; BEGIN; insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2); insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2); insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2); ... all your other INSERTs here ... END; Once you've written the whole text file you can tell the shell tool to execute it using the following command sqlite3 websites.db .read commands.txt All the INSERTs will happen while the file is open once, and the BEGIN/END means that they'll all happen in the same transaction, which will also make everything far faster. In SQLite it's transactions that take time, not individual commands. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on locks
Thanks for the input, I did add PRAGMA busy_timeout=1500; before any query but I am still getting loads of locked database errors, please advice On Mon, Oct 20, 2014 at 3:44 AM, Keith Medcalf kmedc...@dessus.com wrote: Actually, you should set the timeout for each connection. The computer does not matter. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Sunday, 19 October, 2014 18:35 To: General Discussion of SQLite Database Subject: Re: [sqlite] Question on locks On 20 Oct 2014, at 1:15am, Ali Jawad alijaw...@gmail.com wrote: Thanks, I hope it works, I hate to use MySQL for this project. Pardon my ignorance but the example at hand below is for C, any ideas on how to set for Bash and PHP ? Or better yet globally for sqlite3 There's a PRAGMA which sets the same thing: http://www.sqlite.org/pragma.html#pragma_busy_timeout The time is in milliseconds and for testing purposes you want to set it to at least 1 milliseconds. Execute the pragma after you open the connection /on all the computers which are opening the database/. Simon. ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on locks
You did that per connection? On Sat, Oct 25, 2014 at 10:02 AM, Ali Jawad alijaw...@gmail.com wrote: Thanks for the input, I did add PRAGMA busy_timeout=1500; before any query but I am still getting loads of locked database errors, please advice ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on locks
Yes please see examples these are in for loops, but these are the only occurances of Sqlite in the files in question: bash script sqlite3 websites.db PRAGMA busy_timeout=1500;CREATE TABLE [$SITE] (DATE INT ,EU INT , US INT); php script $ret = $db-query(PRAGMA busy_timeout=1500;SELECT eu,us,date FROM [$site] ORDER BY date(DATE) DESC LIMIT 10); On Sat, Oct 25, 2014 at 4:12 PM, Stephen Chrzanowski pontia...@gmail.com wrote: You did that per connection? On Sat, Oct 25, 2014 at 10:02 AM, Ali Jawad alijaw...@gmail.com wrote: Thanks for the input, I did add PRAGMA busy_timeout=1500; before any query but I am still getting loads of locked database errors, please advice ___ 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] Question on locks
On 25 Oct 2014, at 3:31pm, Ali Jawad alijaw...@gmail.com wrote: bash script sqlite3 websites.db PRAGMA busy_timeout=1500;CREATE TABLE [$SITE] (DATE INT ,EU INT , US INT); Creating and destroying tables always involves a long lock. php script $ret = $db-query(PRAGMA busy_timeout=1500;SELECT eu,us,date FROM [$site] ORDER BY date(DATE) DESC LIMIT 10); This doesn't work. The query will process only the query command. You want something more like // do this just once, soon after creating the $db connection $ret = $db-exec(PRAGMA busy_timeout=1500); // do this when you need the result $ret = $db-query(SELECT eu,us,date FROM [$site] ORDER BY date(DATE) DESC LIMIT 10); By the way ... I notice you are creating a table with a variable name. This is usually a bad sign. It might make more sense to put your data into one table, and add a column which contains the $site . Then you don't need to create a new table when you have data for a new site. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on locks
Thanks Simon, the create process is a one off. As for the table name I did use this approach as to not accumulate too much data in one table and instead split the data in multiple tables. From a design POV in sqlite is this a mistake. And will the pragma for php eliminate locks ? On Oct 25, 2014 7:23 PM, Simon Slavin slav...@bigfraud.org wrote: On 25 Oct 2014, at 3:31pm, Ali Jawad alijaw...@gmail.com wrote: bash script sqlite3 websites.db PRAGMA busy_timeout=1500;CREATE TABLE [$SITE] (DATE INT ,EU INT , US INT); Creating and destroying tables always involves a long lock. php script $ret = $db-query(PRAGMA busy_timeout=1500;SELECT eu,us,date FROM [$site] ORDER BY date(DATE) DESC LIMIT 10); This doesn't work. The query will process only the query command. You want something more like // do this just once, soon after creating the $db connection $ret = $db-exec(PRAGMA busy_timeout=1500); // do this when you need the result $ret = $db-query(SELECT eu,us,date FROM [$site] ORDER BY date(DATE) DESC LIMIT 10); By the way ... I notice you are creating a table with a variable name. This is usually a bad sign. It might make more sense to put your data into one table, and add a column which contains the $site . Then you don't need to create a new table when you have data for a new site. Simon. ___ 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] Question on locks
On 25 Oct 2014, at 7:16pm, Ali Jawad alijaw...@gmail.com wrote: Thanks Simon, the create process is a one off. As for the table name I did use this approach as to not accumulate too much data in one table and instead split the data in multiple tables. From a design POV in sqlite is this a mistake. And will the pragma for php eliminate locks ? You should definitely execute the PRAGMA as a separate command, not as part of your SELECT command. I do not know for sure that, done as above, it will fix your lock. I'm not sure why you are getting the locks. But it is the next step for you to try, and if it doesn't fix them it will provide good diagnostic information. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question on locks
Hi I do have one sqlite DB, with multiple dbs, and multiple scripts writing to those tables, at one time only one script writes to one table, I.e. there is no simultaneous read/write access to one table at one time. However, I am getting DB locked errors, did I misunderstand Sqlite3 ? Is the lock per DB or per table ? Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on locks
On 18 Oct 2014, at 1:32pm, Ali Jawad alijaw...@gmail.com wrote: I do have one sqlite DB, with multiple dbs, and multiple scripts writing to those tables, at one time only one script writes to one table, I.e. there is no simultaneous read/write access to one table at one time. However, I am getting DB locked errors, did I misunderstand Sqlite3 ? Is the lock per DB or per table ? The lock is per database. You're bound to get locks occasionally if you don't set a timeout. Set yourself a timeout using https://www.sqlite.org/c3ref/busy_timeout.html or the PRAGMA. Set it to something like 1 minute. Does the problem go away ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on locks
Thanks, I hope it works, I hate to use MySQL for this project. Pardon my ignorance but the example at hand below is for C, any ideas on how to set for Bash and PHP ? Or better yet globally for sqlite3 int sqlite3_busy_timeout(sqlite3*, int ms); On Mon, Oct 20, 2014 at 2:00 AM, Simon Slavin slav...@bigfraud.org wrote: On 18 Oct 2014, at 1:32pm, Ali Jawad alijaw...@gmail.com wrote: I do have one sqlite DB, with multiple dbs, and multiple scripts writing to those tables, at one time only one script writes to one table, I.e. there is no simultaneous read/write access to one table at one time. However, I am getting DB locked errors, did I misunderstand Sqlite3 ? Is the lock per DB or per table ? The lock is per database. You're bound to get locks occasionally if you don't set a timeout. Set yourself a timeout using https://www.sqlite.org/c3ref/busy_timeout.html or the PRAGMA. Set it to something like 1 minute. Does the problem go away ? Simon. ___ 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] Question on locks
On 20 Oct 2014, at 1:15am, Ali Jawad alijaw...@gmail.com wrote: Thanks, I hope it works, I hate to use MySQL for this project. Pardon my ignorance but the example at hand below is for C, any ideas on how to set for Bash and PHP ? Or better yet globally for sqlite3 There's a PRAGMA which sets the same thing: http://www.sqlite.org/pragma.html#pragma_busy_timeout The time is in milliseconds and for testing purposes you want to set it to at least 1 milliseconds. Execute the pragma after you open the connection /on all the computers which are opening the database/. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on locks
Actually, you should set the timeout for each connection. The computer does not matter. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Sunday, 19 October, 2014 18:35 To: General Discussion of SQLite Database Subject: Re: [sqlite] Question on locks On 20 Oct 2014, at 1:15am, Ali Jawad alijaw...@gmail.com wrote: Thanks, I hope it works, I hate to use MySQL for this project. Pardon my ignorance but the example at hand below is for C, any ideas on how to set for Bash and PHP ? Or better yet globally for sqlite3 There's a PRAGMA which sets the same thing: http://www.sqlite.org/pragma.html#pragma_busy_timeout The time is in milliseconds and for testing purposes you want to set it to at least 1 milliseconds. Execute the pragma after you open the connection /on all the computers which are opening the database/. Simon. ___ 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] Question about coalesce and data types
Hello Clemens, thank you for your answer; i understand now why the where - condition returns 'false'. Also, the effect is independent of the function used. It seems the solution is to actually pass all bind variable values by their appropriate sqlite3_bind_* - function instead of just using sqlite3_bind_text. However, this means quite a lot of work for me. Is there a way to write my own coalesce-Function (or indeed any function) so that its result has an affinity? The documentation of the sqlite3_result_* family of functions suggests not. Thank you Martin Am 20.08.2014 12:03, schrieb Clemens Ladisch: Martin Engelschalk wrote: create table TestTable (col_a numeric); insert into TestTable (col_a) values (1); retrieve the row, as expected: select * from TestTable where col_a = '1'; do not retrieve the row: select * from TestTable where coalesce(col_a, 5) = '1' Can someone please explain this to me or point me to some documentation? The col_a column has numeric affinity, so the string '1' is converted into a number. http://www.sqlite.org/datatype3.html#affinity The return value of the function has NONE affinity, so no automatic conversion happens. http://www.sqlite.org/datatype3.html#expraff Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- *Codeswift GmbH * Kräutlerweg 20a A-5020 Salzburg Tel: +49 (0) 8662 / 494330 Mob: +49 (0) 171 / 4487687 Fax: +49 (0) 3212 / 1001404 engelsch...@codeswift.com www.codeswift.com / www.swiftcash.at Codeswift Professional IT Services GmbH Firmenbuch-Nr. FN 202820s UID-Nr. ATU 50576309 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about coalesce and data types
Martin Engelschalk wrote: It seems the solution is to actually pass all bind variable values by their appropriate sqlite3_bind_* - function instead of just using sqlite3_bind_text. However, this means quite a lot of work for me. Isn't it also work for you to converting your values to text? Is there a way to write my own Function so that its result has an affinity? No. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about coalesce and data types
Am 21.08.2014 11:39, schrieb Clemens Ladisch: Martin Engelschalk wrote: It seems the solution is to actually pass all bind variable values by their appropriate sqlite3_bind_* - function instead of just using sqlite3_bind_text. However, this means quite a lot of work for me. Isn't it also work for you to converting your values to text? Yes, but this would mean to change a LOT of SQL and then testing it, Also, the SQL is generic and has to work on Postgres and Oracle too. I will change the binding. Is there a way to write my own Function so that its result has an affinity? No. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Thank you very much, i see my way now. Martin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about coalesce and data types
Is there a way to write my own coalesce-Function (or indeed any function) so that its result has an affinity? The documentation of the sqlite3_result_* family of functions suggests not. No. But you can cast the result to whatever type you wish: cast(coalesce(a, 5) as TEXT) and it will then have a type affinity. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about coalesce and data types
Hello list, I checked the coalesce function and observed the follwoing results: I create a simple table with one column and one row: create table TestTable (col_a numeric); insert into TestTable (col_a) values (1); commit; The following statements retrieve the row, as expected: select * from TestTable where col_a = 1; select * from TestTable where col_a = '1'; Now when introducing coalesce, the following statements also retrieve the row: select * from TestTable where coalesce(col_a, 5) = 1 select * from TestTable where coalesce(col_a, '5') = 1 Bur the two next statements do not retrieve the row: select * from TestTable where coalesce(col_a, 5) = '1' select * from TestTable where coalesce(col_a, '5') = '1' When using coalesce, it seems to matter what is right of the = sign in the where clause. When comparing directly with the column, this dows not matter. The same effect can be observed if i replace the constant '1' to the right of the = with a bind variable that I bind with sqlite_bind_text. Can someone please explain this to me or point me to some documentation? Thank you Martin -- *Codeswift GmbH * Kräutlerweg 20a A-5020 Salzburg Tel: +49 (0) 8662 / 494330 Mob: +49 (0) 171 / 4487687 Fax: +49 (0) 3212 / 1001404 engelsch...@codeswift.com www.codeswift.com / www.swiftcash.at Codeswift Professional IT Services GmbH Firmenbuch-Nr. FN 202820s UID-Nr. ATU 50576309 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about coalesce and data types
Martin Engelschalk wrote: create table TestTable (col_a numeric); insert into TestTable (col_a) values (1); retrieve the row, as expected: select * from TestTable where col_a = '1'; do not retrieve the row: select * from TestTable where coalesce(col_a, 5) = '1' Can someone please explain this to me or point me to some documentation? The col_a column has numeric affinity, so the string '1' is converted into a number. http://www.sqlite.org/datatype3.html#affinity The return value of the function has NONE affinity, so no automatic conversion happens. http://www.sqlite.org/datatype3.html#expraff Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about opening database files
Hello Richard, thanks for the response and explanation. adil On Sun, Jun 22, 2014 at 04:51:27PM -0400, Richard Hipp wrote: On Sun, Jun 22, 2014 at 10:47 AM, Adil Hasan paradox2...@gmail.com wrote: Hello, I would like to add sqlite to a workflow. But, I cannot directly open the database in C using the filename as the application I'm using places a layer inbetween the filesystem and my workflow. I can open the file with a command from the middle-layer and get a file descriptor. Is there a way that I can just pass the file descriptor to the open command instead of the database filename? No. The SQLite transaction control and recovery mechanism depends on knowing the filename. So you cannot open an SQLite database file by passing just a file descriptor. Theoretically, if the file were immutable and known to be in a consistent state (it does not need recovery because the last writer did not crash) then it might be possible to open an SQLite database read-only using just a file descriptor. But no mechanism exists to do this at the current time. -- D. Richard Hipp d...@sqlite.org ___ 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
[sqlite] Question about opening database files
Hello, I would like to add sqlite to a workflow. But, I cannot directly open the database in C using the filename as the application I'm using places a layer inbetween the filesystem and my workflow. I can open the file with a command from the middle-layer and get a file descriptor. Is there a way that I can just pass the file descriptor to the open command instead of the database filename? I guess that maybe I should create a shim, but I'm not 100% clear on just how to do that. Does anyone have any suggestions? My naive guess is that I need to create some structure, but I can't figure out just how to do that (I'm afraid that my C isn't fantastic). Many thanks for your help, adil ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about opening database files
I’m working on accessing sqlite3 from a process/program that is not able to access sqlite3.a by using sockets through socat(1). Mike On Jun 22, 2014, at 9:47 AM, Adil Hasan paradox2...@gmail.com wrote: Hello, I would like to add sqlite to a workflow. But, I cannot directly open the database in C using the filename as the application I'm using places a layer inbetween the filesystem and my workflow. I can open the file with a command from the middle-layer and get a file descriptor. Is there a way that I can just pass the file descriptor to the open command instead of the database filename? I guess that maybe I should create a shim, but I'm not 100% clear on just how to do that. Does anyone have any suggestions? My naive guess is that I need to create some structure, but I can't figure out just how to do that (I'm afraid that my C isn't fantastic). Many thanks for your help, adil ___ 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] Question about opening database files
Hello Mike, thanks for the very quick response. That's very interesting. It looks (maybe I am wrong) as if you open a sqlite database file and then in your program you open a stream and use socat to connect the two together? I'm not sure if I can use such an approach in my situation. In my case I can within my workflow open a file using the middleware open command and get a file descriptor, but then I don't understand how I can pass that descriptor to sqlite. So, somehow doing something like: sqlite3_open_v2(filedescriptor, ...) instead of the existing approach of passing the filename. But, I'm a bit confused as to how to do that. hope all is well, adil On Sun, Jun 22, 2014 at 10:10:34AM -0500, mikeegg1 wrote: I’m working on accessing sqlite3 from a process/program that is not able to access sqlite3.a by using sockets through socat(1). Mike On Jun 22, 2014, at 9:47 AM, Adil Hasan paradox2...@gmail.com wrote: Hello, I would like to add sqlite to a workflow. But, I cannot directly open the database in C using the filename as the application I'm using places a layer inbetween the filesystem and my workflow. I can open the file with a command from the middle-layer and get a file descriptor. Is there a way that I can just pass the file descriptor to the open command instead of the database filename? I guess that maybe I should create a shim, but I'm not 100% clear on just how to do that. Does anyone have any suggestions? My naive guess is that I need to create some structure, but I can't figure out just how to do that (I'm afraid that my C isn't fantastic). Many thanks for your help, adil ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about opening database files
On Sun, Jun 22, 2014 at 10:47 AM, Adil Hasan paradox2...@gmail.com wrote: Hello, I would like to add sqlite to a workflow. But, I cannot directly open the database in C using the filename as the application I'm using places a layer inbetween the filesystem and my workflow. I can open the file with a command from the middle-layer and get a file descriptor. Is there a way that I can just pass the file descriptor to the open command instead of the database filename? No. The SQLite transaction control and recovery mechanism depends on knowing the filename. So you cannot open an SQLite database file by passing just a file descriptor. Theoretically, if the file were immutable and known to be in a consistent state (it does not need recovery because the last writer did not crash) then it might be possible to open an SQLite database read-only using just a file descriptor. But no mechanism exists to do this at the current time. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question : how to retrieve a byte array
Hi, I am currently working on a video game using sqlite3, and I am having an issue when saving the database to the platform im on. The save function for these platform use either a string or a byte array to save. So I need to be able to get a byte array of the database, and be able to rebuild the database with that byte array, or a string. So far I have not found anything that gets me the information I need, so I did my own Dump function, that dump all the SQL queries needed to rebuild the database from scratch, but it is pretty slow. I am working in c# under Unity engine. Thanks for any help I can get. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question : how to retrieve a byte array
I'm not sure this sounds like a SQLite issue but I think File.ReadAllBytes and File.WriteAllBytes in the System.IO namespace may do what you want. Cheers, On 28 April 2014 22:58, Denis Bezeau den...@hb-studios.com wrote: Hi, I am currently working on a video game using sqlite3, and I am having an issue when saving the database to the platform im on. The save function for these platform use either a string or a byte array to save. So I need to be able to get a byte array of the database, and be able to rebuild the database with that byte array, or a string. So far I have not found anything that gets me the information I need, so I did my own Dump function, that dump all the SQL queries needed to rebuild the database from scratch, but it is pretty slow. I am working in c# under Unity engine. Thanks for any help I can get. ___ 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
[sqlite] Question about checking table requirements on INSERT into a table
Hi sqlite-users, When I was toying around with sqlite in python, I ran into an interesting caveat when INSERTing into a table where an unsupported data type didn't throw an exception/error from sqlite's perspective. In the example below, I expected an insert of a text field to an integer column to throw an error, but the insert succeeded: $ sqlite3.exe ~/test.db 'CREATE TABLE bar2(foo INTEGER)' $ sqlite3.exe ~/test.db 'INSERT INTO bar2(foo) VALUES(6)' $ sqlite3.exe ~/test.db 'INSERT INTO bar2(foo) VALUES(a)' $ sqlite3.exe ~/test.db 'SELECT * FROM bar2' 6 a $ sqlite3.exe --version 3.8.4.3 2014-04-03 16:53:12 a611fa96c4a848614efe899130359c9f6fb889c3 Being able to INSERT and print out 'a' above caught me off guard a bit. Is this programmer error when I ran CREATE TABLE or the INSERT (this is the most likely answer, but I wanted to be sure), or is this by design? Thank you! -Garrett ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about checking table requirements on INSERT into a table
On 4/24/2014 1:03 PM, yaneurabeya . wrote: Being able to INSERT and print out 'a' above caught me off guard a bit. Is this programmer error when I ran CREATE TABLE or the INSERT (this is the most likely answer, but I wanted to be sure), or is this by design? By design. For details, see http://www.sqlite.org/datatype3.html . If you want strict typing, you can request it with a CHECK constraint: CREATE TABLE bar2(foo INTEGER CHECK (typeof(foo)='integer') ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about checking table requirements on INSERT into a table
On Thu, Apr 24, 2014 at 10:23 AM, Igor Tandetnik i...@tandetnik.org wrote: On 4/24/2014 1:03 PM, yaneurabeya . wrote: Being able to INSERT and print out 'a' above caught me off guard a bit. Is this programmer error when I ran CREATE TABLE or the INSERT (this is the most likely answer, but I wanted to be sure), or is this by design? By design. For details, see http://www.sqlite.org/datatype3.html . If you want strict typing, you can request it with a CHECK constraint: CREATE TABLE bar2(foo INTEGER CHECK (typeof(foo)='integer') ); Perfect -- thank you for the help :)!!! -Garrett ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] question for auto increament
Hello, all: I am using VS2013 + sqlite.net for 4.5.1 with 32 bit. I have question for a primary key with auto increment. I can't figure out how to do it in C#. when I use sqlite datasource designer in VS2013. It has option to add identity checking, however, this feature doesn't work even I check this option. Do you have anybody to help me out? Regards, yh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] question for auto increament
Hi Yang, Most designers are lacking a bit inside the dev environments. You could use the sqlite command-line tool available from the downloads page at http://sqlite.org/ or indeed any of the DB Management GUIs out there for SQLite such as these: http://www.sqliteexpert.com/ http://www.rifin.co.za/software/sqlc/ Note: Only the Command-line facility is officially supported, for support with the others you will need to contact their own lists/proprietors/etc. Alternately, you can simply directly execute some SQL to create your tables via the API, such as: CREATE TABLE SomeTableName ( IDKey INTEGER PRIMARY KEY AUTOINCREMENT /* Item ID */, ColX TEXT NOT NULL /* Some column */, ColY TEXT NOT NULL /* Some other column */, ColZ TEXT NOT NULL /* Some more columns etc. */ ); Which will do what you need. Good luck! On 2014/04/21 22:13, Yang Hong wrote: Hello, all: I am using VS2013 + sqlite.net for 4.5.1 with 32 bit. I have question for a primary key with auto increment. I can't figure out how to do it in C#. when I use sqlite datasource designer in VS2013. It has option to add identity checking, however, this feature doesn't work even I check this option. Do you have anybody to help me out? Regards, yh ___ 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] Question about how sqlite recovers after a power loss
Hi Simon, If I run my program step-by-step, I can see that the sqlite database is not modified when I do an INSERT. It is modified only when I do a COMMIT. I read the page on the sqlite website detailing how sqlite deals with crashes and untimely power losses, and it says that the journal file is created just before modifying the database and is a copy of the areas that are to be modified. Once the journal file is committed to the disk, the database is modified. But this is not what I can see. What I can see is that the database file is not modified when I do the INSERT, it is modified only when I do the COMMIT. And even then, that would not explain why the journal file lingers after re-opening the database. Could anyone shed some light on the above? Thank you very much for any help! Best regards, Fabrice --Original Message-- From: Simon Slavin To: Fabrice Triboix To: General Discussion of SQLite Database Subject: Re: [sqlite] Question about how sqlite recovers after a power loss Sent: 18 Feb 2014 08:57 On 18 Feb 2014, at 8:13am, Fabrice Triboix ftrib...@falcon-one.com wrote: Actually, the journal file does not get deleted when I do a SELECT just after opening the database. Depending on your journal mode (I think), the journal file should be deleted, but then it will immediately be recreated. Are you sure that this is not what's happening ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about how sqlite recovers after a power loss
On Wed, Feb 26, 2014 at 12:33 PM, Fabrice Triboix ftrib...@falcon-one.com wrote: And even then, that would not explain why the journal file lingers after re-opening the database. I remember asking a similar question. As long as I remember, the main logical implication is that journal file presence is not a mark to force database into some actions. It's rather a supplement for the error state of the base. So if the base is in error state and there's no journal file, it's bad. But reverse is ok and might appear with some scenarios. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about how sqlite recovers after a power loss
Hi Max, Many thanks for your input. This is probably how it works. Best regards, Fabrice --Original Message-- From: Max Vlasov To: Fabrice Triboix To: General Discussion of SQLite Database Subject: Re: [sqlite] Question about how sqlite recovers after a power loss Sent: 26 Feb 2014 10:24 On Wed, Feb 26, 2014 at 12:33 PM, Fabrice Triboix ftrib...@falcon-one.com wrote: And even then, that would not explain why the journal file lingers after re-opening the database. I remember asking a similar question. As long as I remember, the main logical implication is that journal file presence is not a mark to force database into some actions. It's rather a supplement for the error state of the base. So if the base is in error state and there's no journal file, it's bad. But reverse is ok and might appear with some scenarios. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about how sqlite recovers after a power loss
On Wed, Feb 26, 2014 at 3:33 AM, Fabrice Triboix ftrib...@falcon-one.comwrote: Hi Simon, If I run my program step-by-step, I can see that the sqlite database is not modified when I do an INSERT. It is modified only when I do a COMMIT. Correct. Changes are normally kept in memory (unless the change set grows too large) until you COMMIT. This allows other concurrent processes to continue reading the unmodified database file. I read the page on the sqlite website detailing how sqlite deals with crashes and untimely power losses, and it says that the journal file is created just before modifying the database and is a copy of the areas that are to be modified. Once the journal file is committed to the disk, the database is modified. But this is not what I can see. What I can see is that the database file is not modified when I do the INSERT, it is modified only when I do the COMMIT. Perhaps your mental model of INSERT and COMMIT is wrong. The abstraction that SQL (all SQL databases, not just SQLite) tries to present is this: INSERT does not modify an SQL database; it merely queues up changes to be made atomically when you COMMIT. And even then, that would not explain why the journal file lingers after re-opening the database. Simply calling sqlite3_open() does not re-open a database. It merely saves the filename so that the database can be opened later when needed. The re-open does not occur until you first try to read from the database file. The rollback journal is replayed and deleted at that point. Could anyone shed some light on the above? Thank you very much for any help! Best regards, Fabrice --Original Message-- From: Simon Slavin To: Fabrice Triboix To: General Discussion of SQLite Database Subject: Re: [sqlite] Question about how sqlite recovers after a power loss Sent: 18 Feb 2014 08:57 On 18 Feb 2014, at 8:13am, Fabrice Triboix ftrib...@falcon-one.com wrote: Actually, the journal file does not get deleted when I do a SELECT just after opening the database. Depending on your journal mode (I think), the journal file should be deleted, but then it will immediately be recreated. Are you sure that this is not what's happening ? Simon. ___ 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] Question about how sqlite recovers after a power loss
Hi Richard, Many thanks for your input. My ideas about how sqlite works were indeed incorrect. When I say re-opening the database, I mean opening it and doing a few SELECT to it (as described in a previous email). So I do read it (especially the sqlite_master table), and still the journal file stays there. Max suggested that it might be normal behaviour... Please also note that I now do a PRGMA to force the journal mode to DELETE (although I am pretty sure this is what it was by default...) Best regards, Fabrice -Original Message- From: Richard Hipp d...@sqlite.org Sender: drhsql...@gmail.com Date: Wed, 26 Feb 2014 05:40:27 To: ftrib...@falcon-one.com; General Discussion of SQLite Databasesqlite-users@sqlite.org Subject: Re: [sqlite] Question about how sqlite recovers after a power loss On Wed, Feb 26, 2014 at 3:33 AM, Fabrice Triboix ftrib...@falcon-one.comwrote: Hi Simon, If I run my program step-by-step, I can see that the sqlite database is not modified when I do an INSERT. It is modified only when I do a COMMIT. Correct. Changes are normally kept in memory (unless the change set grows too large) until you COMMIT. This allows other concurrent processes to continue reading the unmodified database file. I read the page on the sqlite website detailing how sqlite deals with crashes and untimely power losses, and it says that the journal file is created just before modifying the database and is a copy of the areas that are to be modified. Once the journal file is committed to the disk, the database is modified. But this is not what I can see. What I can see is that the database file is not modified when I do the INSERT, it is modified only when I do the COMMIT. Perhaps your mental model of INSERT and COMMIT is wrong. The abstraction that SQL (all SQL databases, not just SQLite) tries to present is this: INSERT does not modify an SQL database; it merely queues up changes to be made atomically when you COMMIT. And even then, that would not explain why the journal file lingers after re-opening the database. Simply calling sqlite3_open() does not re-open a database. It merely saves the filename so that the database can be opened later when needed. The re-open does not occur until you first try to read from the database file. The rollback journal is replayed and deleted at that point. Could anyone shed some light on the above? Thank you very much for any help! Best regards, Fabrice --Original Message-- From: Simon Slavin To: Fabrice Triboix To: General Discussion of SQLite Database Subject: Re: [sqlite] Question about how sqlite recovers after a power loss Sent: 18 Feb 2014 08:57 On 18 Feb 2014, at 8:13am, Fabrice Triboix ftrib...@falcon-one.com wrote: Actually, the journal file does not get deleted when I do a SELECT just after opening the database. Depending on your journal mode (I think), the journal file should be deleted, but then it will immediately be recreated. Are you sure that this is not what's happening ? Simon. ___ 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] Question about how sqlite recovers after a power loss
Hi Richard, Actually, the journal file does not get deleted when I do a SELECT just after opening the database. Why is that? NB: I do not set/modify any PRAGMA at all. Many thanks for your help! Best regards, Fabrice -Original Message- From: Richard Hipp d...@sqlite.org Sender: drhsql...@gmail.com Date: Sat, 15 Feb 2014 09:57:03 To: ftrib...@falcon-one.com; General Discussion of SQLite Databasesqlite-users@sqlite.org Subject: Re: [sqlite] Question about how sqlite recovers after a power loss On Sat, Feb 15, 2014 at 9:55 AM, Fabrice Triboix ftrib...@falcon-one.comwrote: Hi Richard, All right, many thanks for that. So if I do, say, a SELECT just after opening the database, that's when the recovery will actually take place and the journal file will be replayed and deleted. Is my understanding correct? yes. SELECT count(*) FROM sqlite_master is a good query to run for this. -- 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] Question about how sqlite recovers after a power loss
On 18 Feb 2014, at 8:13am, Fabrice Triboix ftrib...@falcon-one.com wrote: Actually, the journal file does not get deleted when I do a SELECT just after opening the database. Depending on your journal mode (I think), the journal file should be deleted, but then it will immediately be recreated. Are you sure that this is not what's happening ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC
Hi, all, Regarding SQLITE_DETERMINISTIC: http://www.sqlite.org/c3ref/create_function.html does specifying that flag guaranty that sqlite3 will only call my deterministic function one time during any given SQL statement, or must my function actually guaranty that deterministic behaviour itself? The canonical example is a now() function which returns time(NULL) (there was a long thread on this topic a few months back). My concrete questions: - Does such function need to make the guaranty itself or is marking it as SQLITE_DETERMINISTIC enough to keep sqlite from calling it twice in one statement? - Can within a single SQL statement be interpreted as within the lifetime of a given preparation of a given sqlite3_stmt handle without distorting the truth too much, or is there a more complex definition involving subselects and such? -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC
On Mon, Feb 17, 2014 at 11:47 AM, Stephan Beal sgb...@googlemail.comwrote: Hi, all, Regarding SQLITE_DETERMINISTIC: http://www.sqlite.org/c3ref/create_function.html does specifying that flag guaranty that sqlite3 will only call my deterministic function one time during any given SQL statement, or must my function actually guaranty that deterministic behaviour itself? The canonical example is a now() function which returns time(NULL) (there was a long thread on this topic a few months back). My concrete questions: - Does such function need to make the guaranty itself or is marking it as SQLITE_DETERMINISTIC enough to keep sqlite from calling it twice in one statement? The is a constraint on the function implementation, that allows SQLite to perform certain optimizations in the generated VDBE code that would otherwise not be valid. So the function must guarantee that it will always return the same values given the same inputs. - Can within a single SQL statement be interpreted as within the lifetime of a given preparation of a given sqlite3_stmt handle without distorting the truth too much, or is there a more complex definition involving subselects and such? In call to the function within any subquery or trigger must return the same value if it has the same inputs. But the return value can change after each sqlite3_reset(). -- 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] Question regarding guarantees of SQLITE_DETERMINISTIC
On 2014/02/17 18:47, Stephan Beal wrote: Hi, all, Regarding SQLITE_DETERMINISTIC: http://www.sqlite.org/c3ref/create_function.html does specifying that flag guaranty that sqlite3 will only call my deterministic function one time during any given SQL statement, or must my function actually guaranty that deterministic behaviour itself? The flag is telling SQLite that your function will behave determinsitcally, i.e. it won't change the output for the same inputs within a single query. SQLite then uses this information to maybe cache the output and re-use it, but there is no guarantee the optimisation is possible within every query situation, so it is very much possible SQLite can call your function again within the same query, you have to make your function behave deterministically if you tell SQLite that it is so - else query results can be undefined. The OP seems to have tested SQLite's determinism with adding a very indeterministic function, so what I was trying to point out is either his function isn't behaving deterministically and/or he did not specify the flag to let SQLite know - but to your question specifically, no the flag does not force determinism (AFAICT), it only allows the optimisation, or more specifically, NOT specifying the flag forces SQLIte to call the function every time and not expect determinsitic results (even if your return values are constant). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC
On Mon, Feb 17, 2014 at 6:23 PM, RSmith rsm...@rsweb.co.za wrote: The flag is telling SQLite that your function will behave determinsitcally, i.e. it won't change the output for the same inputs within a single query. i figured so, just wanted to be sure. SQLite then uses this information to maybe cache the output and re-use it, but there is no guarantee the optimisation is possible within every query situation, so it is very much possible SQLite can call your function again within the same query, you have to make your function behave deterministically if you tell SQLite that it is so - else query results can be undefined. But it would be really handy if sqlite3 could somehow guaranty that the optimization would apply :). (No, i'm not asking for that as a feature - it doesn't sound feasible to me for all query constructs.) The OP seems to have tested SQLite's determinism with adding a very indeterministic function, so what I was trying to point out is either his function isn't behaving deterministically and/or he did not specify the flag to let SQLite know - It was your response to that which lead me to that flag - i wasn't aware of it before and wanted to double-check before i go applying it to my local now() impl (which i now won't do, of course). But... i can set it on 5 or 6 others which do behave deterministically, so thank you for that :). but to your question specifically, no the flag does not force determinism (AFAICT), That coincides with Richard's answer (which i consider to be definitive ;). it only allows the optimisation But it would be cool if... ;) -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about how sqlite recovers after a power loss
Hello, I would like to understand better at which moment does sqlite recovers from a previous power loss. Let's take the following scenario: - sqlite3_open() - BEGIN TRANSACTION; - INSERT INTO SomeTable VALUES (blah, blah, blah); = Power loss! - sqlite3_open() What I notice, is that the 2nd sqlite3_open() succeeds, but the journal file is left intact and not deleted. I would like to know at what moment is the journal file actually deleted after a power loss. Many thanks! Fabrice ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about how sqlite recovers after a power loss
On Fri, Feb 14, 2014 at 10:13 AM, ftrib...@falcon-one.com wrote: Hello, I would like to understand better at which moment does sqlite recovers from a previous power loss. Let's take the following scenario: - sqlite3_open() - BEGIN TRANSACTION; - INSERT INTO SomeTable VALUES (blah, blah, blah); = Power loss! - sqlite3_open() What I notice, is that the 2nd sqlite3_open() succeeds, but the journal file is left intact and not deleted. I would like to know at what moment is the journal file actually deleted after a power loss. When you first access the database. sqlite3_open() does not actually open the database file. It just queues up the information. The real open is deferred until you actually try to read something out of the database. The delay of open gives the application time to run PRAGMA statements to further setup the connection. In particular, it allows the key PRAGMA to run to set the encryption key for encrypted database files, which would otherwise be unreadable. -- 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] Question about how sqlite recovers after a power loss
Hi Richard, All right, many thanks for that. So if I do, say, a SELECT just after opening the database, that's when the recovery will actually take place and the journal file will be replayed and deleted. Is my understanding correct? Many thanks for your help! Best regards, Fabrice -Original Message- From: Richard Hipp d...@sqlite.org Sender: sqlite-users-bounces@sqlite.orgDate: Sat, 15 Feb 2014 08:07:57 To: General Discussion of SQLite Databasesqlite-users@sqlite.org Reply-To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Question about how sqlite recovers after a power loss On Fri, Feb 14, 2014 at 10:13 AM, ftrib...@falcon-one.com wrote: Hello, I would like to understand better at which moment does sqlite recovers from a previous power loss. Let's take the following scenario: - sqlite3_open() - BEGIN TRANSACTION; - INSERT INTO SomeTable VALUES (blah, blah, blah); = Power loss! - sqlite3_open() What I notice, is that the 2nd sqlite3_open() succeeds, but the journal file is left intact and not deleted. I would like to know at what moment is the journal file actually deleted after a power loss. When you first access the database. sqlite3_open() does not actually open the database file. It just queues up the information. The real open is deferred until you actually try to read something out of the database. The delay of open gives the application time to run PRAGMA statements to further setup the connection. In particular, it allows the key PRAGMA to run to set the encryption key for encrypted database files, which would otherwise be unreadable. -- D. Richard Hipp d...@sqlite.org ___ 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] Question about how sqlite recovers after a power loss
On Sat, 15 Feb 2014 14:55:48 +, Fabrice Triboix ftrib...@falcon-one.com wrote: Hi Richard, All right, many thanks for that. So if I do, say, a SELECT just after opening the database, that's when the recovery will actually take place and the journal file will be replayed and deleted. Is my understanding correct? Yes. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about how sqlite recovers after a power loss
On Sat, Feb 15, 2014 at 9:55 AM, Fabrice Triboix ftrib...@falcon-one.comwrote: Hi Richard, All right, many thanks for that. So if I do, say, a SELECT just after opening the database, that's when the recovery will actually take place and the journal file will be replayed and deleted. Is my understanding correct? yes. SELECT count(*) FROM sqlite_master is a good query to run for this. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about using multiple sqlite3_stmt
Hi,
I'm checking just to make sure, if something like this is OK to do in SQLite.
My example works in practice but I wasn’t sure by reading the manual if I was
allowed to do ANY changes between the sqlite3_step() calls.
I’ve read from the mailing list there are problems if the UPDATE alters
something that is mentioned in the WHERE terms, which seems logical, but what
about this?
const char *sql = “SELECT rowid, foo FROM bar WHERE y = z;”;
sqlite3_stmt *stmt;
sqlite3_prepare_v2(conn,sql,-1,stmt, NULL);
while((step = sqlite3_step(stmt)) == SQLITE_ROW) {
//do some stuff with the row here
...
//make some changes to the row with new statement
sqlite3_exec(conn, “UPDATE bar SET foo=1 WHERE rowid = 123;”, NULL,
NULL, NULL);
}
Thanks!
Best regards,
Mikko Korkalo
mi...@korkalo.fi
+358509104329
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] question on WAL files
On 09/20/2013 11:30 PM, Carey, John wrote:
Hello,
I am trying to write a simple perl program (perl version 5.16.3). I am
accessing a SQLite db which is using WAL(write ahead logging). It took me a
while but I learned that my connection is only looking at the DB file and not
the WAL file. As a result, my queries are not returning the full data set.
the SQLite firefox addon shows 10 invoices in the invoice table. the following
program is returning only 8...
use DBD::SQLite;
use DBI;
use Tkx;
use File::Copy;
use PDF::API2;
use constant mm = 25.4 / 72;
use constant in = 1 / 72;
use constant pt = 1;
# Prompt user for database file location
$dbfile = Tkx::tk___getOpenFile(-initialdir='C:/events_p', -title='Please
select your current events database file (event.sqlite)');
$dbfile =~ s/\//\\/g;
# Initialiatize path variables
# parse out the db filename and path
$DbFilename=substr($dbfile,length($dbfile)-12,12);
$FolderPath=substr($dbfile,0,length($dbfile)-13);
$OriginalImages_Path=$FolderPath.\\OriginalImages;
$Internal_Path=$FolderPath.\\_internal;
$Carts_Path=$FolderPath.\\Carts;
$Orders_Path=$FolderPath.\\Orders;
$favorites=c:\favorites;
$Invoices_Folder=C:\\events_p_invoices;
# Error and exit if incorrect DB filename was selected
if ($DbFilename ne 'event.sqlite')
{
print \n ERROR , Invalid Database Filename Selected\n;
print You MUST select the filename called event.sqlite\n;
sleep(10);
exit;
}
# Connect to the Database
my $dbh2 = DBI-connect(dbi:SQLite:$dbfile, , ,{RaiseError = 1, AutoCommit
= 1}) or die $DBI::errstr;;
$dbh2-do('PRAGMA journal_mode=WAL');
my $sth = $dbh2-prepare(SELECT count(*) fromInvoice);
$sth-execute();
while (@data = $sth-fetchrow_array())
{
print count=$data[0]\n;
}
exit;
If I go into the firefox add on and run a checkpoint , the 2 new invoices get
flushed from the WL to the db file and then my program sees them
what am I doing wrong where my script is only looking at the db file and not
the WAL file?
Are there multiple links in the file-system to the database file?
More information in section 2.5 here:
http://www.sqlite.org/howtocorrupt.html
Dan.
thanks!
John
___
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
[sqlite] question on WAL files
Hello,
I am trying to write a simple perl program (perl version 5.16.3). I am
accessing a SQLite db which is using WAL(write ahead logging). It took me a
while but I learned that my connection is only looking at the DB file and not
the WAL file. As a result, my queries are not returning the full data set.
the SQLite firefox addon shows 10 invoices in the invoice table. the following
program is returning only 8...
use DBD::SQLite;
use DBI;
use Tkx;
use File::Copy;
use PDF::API2;
use constant mm = 25.4 / 72;
use constant in = 1 / 72;
use constant pt = 1;
# Prompt user for database file location
$dbfile = Tkx::tk___getOpenFile(-initialdir='C:/events_p', -title='Please
select your current events database file (event.sqlite)');
$dbfile =~ s/\//\\/g;
# Initialiatize path variables
# parse out the db filename and path
$DbFilename=substr($dbfile,length($dbfile)-12,12);
$FolderPath=substr($dbfile,0,length($dbfile)-13);
$OriginalImages_Path=$FolderPath.\\OriginalImages;
$Internal_Path=$FolderPath.\\_internal;
$Carts_Path=$FolderPath.\\Carts;
$Orders_Path=$FolderPath.\\Orders;
$favorites=c:\favorites;
$Invoices_Folder=C:\\events_p_invoices;
# Error and exit if incorrect DB filename was selected
if ($DbFilename ne 'event.sqlite')
{
print \n ERROR , Invalid Database Filename Selected\n;
print You MUST select the filename called event.sqlite\n;
sleep(10);
exit;
}
# Connect to the Database
my $dbh2 = DBI-connect(dbi:SQLite:$dbfile, , ,{RaiseError = 1,
AutoCommit = 1}) or die $DBI::errstr;;
$dbh2-do('PRAGMA journal_mode=WAL');
my $sth = $dbh2-prepare(SELECT count(*) fromInvoice);
$sth-execute();
while (@data = $sth-fetchrow_array())
{
print count=$data[0]\n;
}
exit;
If I go into the firefox add on and run a checkpoint , the 2 new invoices get
flushed from the WL to the db file and then my program sees them
what am I doing wrong where my script is only looking at the db file and not
the WAL file?
thanks!
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about begin/commit
Under normal circumstances only these stmts perform some DB activity: - BeginTransaction - ExecuteNonQuery - Commit SqliteCommand constructor as well as the following lines (setting parameters and command text) are memory constructs that prepare data for ExecuteNonQuery. If everything works correctly, then it is basically irrelevant whether these lines are inside or outside transaction block. However, if something goes wrong you have to think of the destructors. (i.e. Dispose() methods) Imagine this situation: - CommandText is a concatenation of several SQL statements. - Some of these statements (but not the last one) fail with an exception. Now SqliteCommand.Dispose() attempts to execute part of the CommandText that was not executed. In this case the order of C# using statements makes a difference. Your code, for example, makes sure that the whole action will be terminated by a rollback. On the other hand, if you exchanged the order of SQLiteCommand/SQLiteTransaction constructors, it might well happen that SqliteCommand.Dispose() will be run after rollback and thus perform unwanted data change. The code of System.Data.Sqlite wrapper is pretty complex (in my opinion too complex) and it is quite possible that more complications are possible. Placing the transaction at the uppermost level seems to be the safest and simplest procedure - you do not need to think of implementation details. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Question-about-begin-commit-tp71289p71365.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] Question about date time
Thanks for the reply. I understand. I am going to do some experimenting just to make sure... Regards, -Bill On 9/15/2013 3:13 PM, Petite Abeille wrote: On Sep 15, 2013, at 8:31 PM, William Drago wdr...@suffolk.lib.ny.us wrote: Thanks for the reply. Seconds since the epoch does make a good timestamp. Is that what is normally used to extract data between time periods? (Date Time seems to be a popular topic at the moment) There is nothing prescriptive in using epoch time. As SQLite doesn't have a dedicated date type, you are free to decide how you want to handle it. There are two main encoding: (1) As a number: Julian date, unix epoch, etc (2) As a string: ISO 8601 co.. Just make sure that your string representation sorts properly. http://www.sqlite.org/lang_datefunc.html The granularity of the date is up to you as well: day, hour, milliseconds, etc. This is more driven by what's convenient for your application. Ditto if this should be split between date time. Depending on the task at hand, you could even require a much more full fledge set of entities: create table if not exists date ( idinteger not null constraint date_pk primary key, year integer not null, month integer not null, day integer not null, day_of_year integer not null, day_of_week integer not null, week_of_year integer not null, constraintdate_uk unique( year, month, day ) ) create table if not exists time ( id integer not null constraint time_pk primary key, hourinteger not null, minute integer not null, second integer not null, constraint time_uk unique( hour, minute, second ) ) And then there are timezones, etc… ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.3408 / Virus Database: 3222/6667 - Release Date: 09/15/13 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about date time
All, Should I put date and time in separate columns if I want to select by time? For example: SELECT * FROM testresults WHERE (status != Pass) AND (23:00 = testtime) AND (testtime = 01:00). I have been reading the documentation, but it just isn't clear to me how I should handle this. Thanks, -Bill ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about date time
On 15 Sep 2013 at 18:13, William Drago wdr...@suffolk.lib.ny.us wrote: All, Should I put date and time in separate columns if I want to select by time? For example: SELECT * FROM testresults WHERE (status != Pass) AND (23:00 = testtime) AND (testtime = 01:00). I have been reading the documentation, but it just isn't clear to me how I should handle this. I convert everything to seconds since the epoch and have a column with that. All comparisons are done against that value. This is not too difficult in PHP. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about begin/commit
All,
In the following bit of code found in the help file
SQLite.NET.chm, I see that BeginTransaction() encloses
everything. I always thought that just the for-loop needed
to be enclosed by begin/commit.
What are the reasons for enclosing the other commands?
Thanks,
-Bill
using (SQLiteTransaction mytransaction =
myconnection.BeginTransaction())
{
using (SQLiteCommand mycommand = new
SQLiteCommand(myconnection))
{
SQLiteParameter myparam = new SQLiteParameter();
int n;
mycommand.CommandText = INSERT INTO [MyTable]
([MyId]) VALUES(?);
mycommand.Parameters.Add(myparam);
for (n = 0; n 10; n ++)
{
myparam.Value = n + 1;
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about date time
Hi Tim, Thanks for the reply. Seconds since the epoch does make a good timestamp. Is that what is normally used to extract data between time periods? Say for example, I want to know for the past month what my failure rate was between 11PM and 1AM every day. I'd figure out what 11PM and 1AM is in seconds since the epoch for the 1st of the month and then for each of the next 30 days, then figure out some SELECT statement to use that set of numbers. It seems very convoluted. Anyway, I have a feeling I'm asking this question in the wrong place. I'm not sure this is a SQLite specific question as the answer is probably going to be the same regardless of the DBMS. Thanks, -Bill On 9/15/2013 1:16 PM, Tim Streater wrote: On 15 Sep 2013 at 18:13, William Drago wdr...@suffolk.lib.ny.us wrote: All, Should I put date and time in separate columns if I want to select by time? For example: SELECT * FROM testresults WHERE (status != Pass) AND (23:00 = testtime) AND (testtime = 01:00). I have been reading the documentation, but it just isn't clear to me how I should handle this. I convert everything to seconds since the epoch and have a column with that. All comparisons are done against that value. This is not too difficult in PHP. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.3408 / Virus Database: 3222/6667 - Release Date: 09/15/13 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about date time
On Sep 15, 2013, at 8:31 PM, William Drago wdr...@suffolk.lib.ny.us wrote: Thanks for the reply. Seconds since the epoch does make a good timestamp. Is that what is normally used to extract data between time periods? (Date Time seems to be a popular topic at the moment) There is nothing prescriptive in using epoch time. As SQLite doesn't have a dedicated date type, you are free to decide how you want to handle it. There are two main encoding: (1) As a number: Julian date, unix epoch, etc (2) As a string: ISO 8601 co.. Just make sure that your string representation sorts properly. http://www.sqlite.org/lang_datefunc.html The granularity of the date is up to you as well: day, hour, milliseconds, etc. This is more driven by what's convenient for your application. Ditto if this should be split between date time. Depending on the task at hand, you could even require a much more full fledge set of entities: create table if not exists date ( idinteger not null constraint date_pk primary key, year integer not null, month integer not null, day integer not null, day_of_year integer not null, day_of_week integer not null, week_of_year integer not null, constraintdate_uk unique( year, month, day ) ) create table if not exists time ( id integer not null constraint time_pk primary key, hourinteger not null, minute integer not null, second integer not null, constraint time_uk unique( hour, minute, second ) ) And then there are timezones, etc… ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about aggregate functions used multiple times
James Powell wrote: SELECT MAX(X) AS MaxX, MAX(X)/MIN(X) AS RatioX does the MAX(X) get calculated twice, or does SQLite identify that it is the same number and do it only once? At least in version 3.8.0.2, it gets calculated only once. Please note that SQLite can optimize MIN/MAX calculations for indexed columns (http://www.sqlite.org/optoverview.html#minmax), but only for extremely simple queries. *If* your X column has an index, you could speed up your query by moving the MIN/MAX into simple subqueries: SELECT (SELECT MAX(X) FROM tab) AS MaxX, (SELECT MAX(X) FROM tab) / (SELECT MIN(X) FROM tab) AS RatioX; Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about index usage
Doug Nebeker wrote: CREATE UNIQUE INDEX Ind_DevProps_CompIDPropID on DevProps (CompID, PropID); When I check the query plan for this statement: SELECT CompID FROM DevProps WHERE PropID=33 it looks like it will be doing a table scan. My version of SQLite uses a covering index, but is still doing a full scan. Why is that? The index contains the values of the indexed columns, and for each index entry, a pointer back to the table record. In your case, it might look like this: CompID PropID rowid 1 11 * 1 22 * 1 33 * 2 22 * 3 33 * 3 44 * 4 11 * 4 22 * Please note that the index is sorted by CompID first, and then by PropID. How would this index be helpful for looking up entries with PropID=33, without doing a full scan? To be useful for lookups, the lookup column(s) must be the leftmost column(s) in the index. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about index usage
On 8/30/2013 12:22 PM, Doug Nebeker wrote: CREATE UNIQUE INDEX Ind_DevProps_CompIDPropID on DevProps (CompID, PropID); When I check the query plan for this statement: SELECT CompID FROM DevProps WHERE PropID=33 it looks like it will be doing a table scan. Why is that? Imagine that you have a phone book, sorted by last name then first name. Using this book, you can easily find all Smiths, and all John Smiths, but it's not helpful in finding all people named John. The order of columns in the index matters. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about index usage
On Fri, 30 Aug 2013 16:22:04 + Doug Nebeker ad...@poweradmin.com wrote: CREATE TABLE DevProps ( CompID INTEGER NOT NULL, PropID INTEGER NOT NULL ); CREATE UNIQUE INDEX Ind_DevProps_CompIDPropID on DevProps (CompID, PropID); ... SELECT CompID FROM DevProps WHERE PropID=33 it looks like it will be doing a table scan. Why is that? Because the first column of your index is not PropID. Your index is ordered by CompIDl. PropID 33 might belong to any CompID. SQLite has to look at every pair to find them all. It might as well scan the table. I would recomend adding , primary key (CompID, PropID) to the table for semantic reasons, and creating an index with just PropID for performance if you want to avoid the table scan. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] question about proper use of prepare_v2()
Hi, all,
i'm porting some code from one sqlite3-using project (Fossil SCM) to
another sqlite3-using project (a prototype for Fossil v2) and i came across
this code snippet:
while( rc==SQLITE_OK z[0] ){
pStmt = 0; // type=(sqlite3_stmt*)
rc = sqlite3_prepare_v2(g.db, z, -1, pStmt, zEnd);
if( rc!=SQLITE_OK ) break;
if( pStmt ){
...
}
z = zEnd;
}
My question is: is the if(pStmt) block there a case of too much error
handling, or can it really happen that prepare() returns OK but also leaves
pStmt as NULL (e.g. for an empty SQL statement)???
:-?
--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] question about proper use of prepare_v2()
On Sat, Jul 27, 2013 at 2:04 AM, Stephan Beal sgb...@googlemail.com wrote:
Hi, all,
i'm porting some code from one sqlite3-using project (Fossil SCM) to
another sqlite3-using project (a prototype for Fossil v2) and i came across
this code snippet:
while( rc==SQLITE_OK z[0] ){
pStmt = 0; // type=(sqlite3_stmt*)
rc = sqlite3_prepare_v2(g.db, z, -1, pStmt, zEnd);
if( rc!=SQLITE_OK ) break;
if( pStmt ){
...
}
z = zEnd;
}
My question is: is the if(pStmt) block there a case of too much error
handling, or can it really happen that prepare() returns OK but also leaves
pStmt as NULL (e.g. for an empty SQL statement)???
pStmt will be NULL if zSql contains only a comment or whitespace. There
is a comment about this in sqlite3_exec (legacy.c).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about the changes to e_select.test
Recently several of the expected results in the test e_select.test were
changed to results that look very odd. Basically the numbers that were
returned by the query were replaced with lots of #. For example, test
4.10 went from:
# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
# values are considered equal.
#
do_select_tests e_select-4.10 {
1 SELECT group_concat(y) FROM b2 GROUP BY x {0,1 3 2,4}
2 SELECT count(*) FROM b2 GROUP BY CASE WHEN y4 THEN NULL ELSE 0
END {4 1}
}
To:
# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
# values are considered equal.
#
do_select_tests e_select-4.10 {
1 SELECT group_concat(y) FROM b2 GROUP BY x {/#,# 3 #,#/}
2 SELECT count(*) FROM b2 GROUP BY CASE WHEN y4 THEN NULL ELSE 0
END {4 1}
}
The change was in a change set meant to fix test failures after changes
to the query optimizer, but I fail to see how changing the optimizer
would change the results of queries in such an odd way. Was this change
intentional, and if so, why are such odd results being printed out now?
You can view the specific change set at
http://www.hwaci.com/cgi-bin/sqlite/fdiff?v1=69013a64b4694588v2=07e8d81268ba1ffc
Thanks for your time.
Lauren
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about the changes to e_select.test
On Tue, May 14, 2013 at 3:39 PM, Lauren Foutz lauren.fo...@oracle.comwrote:
test 4.10 went from:
SELECT group_concat(y) FROM b2 GROUP BY x {0,1 3 2,4}
To:
SELECT group_concat(y) FROM b2 GROUP BY x {/#,# 3 #,#/}
The other change (that you didn't notice) is that the expected result is
now surrounded by /../. The /../ means that the expected result is in fact
a regular expression in which # is shorthand for [0-9]+.
The reason for this change is that the original test made assumptions about
the output that are not necessarily true. The group_concat() function
promises to concatenate strings together, but it makes no promises about
the order in which they are grouped together. If three input strings are
A, B, and C, then the output can legally by any of the following: A,B,C
A,C,B B,A,C B,C,A C,A,B C,B,A.
In the example above, the test was originally assuming that the output
would always be 0,1, when it could in fact be 1,0. A change to the query
optimizer caused the output to sometimes appear as 1,0, so we needed to
change the test to accommodate this legal and correct result.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about binding
Hi, ALL, On the page http://www.sqlite.org/c3ref/bind_blob.html it says: [quote] . that matches one of following templates: - ? - ?NNN - :VVV - @VVV - $VVV [/quote] What is the purpose of having NNN and VVV? Are those standard? How do I use those templates? Everywhere I see an examples which uses just ? and no other 4 templates. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about binding
http://www.sqlite.org/lang_expr.html#varparam They are what are called Named Parameters. You use the sqlite3_bind_parameter_index to look up the index associated with a name ... ?nnn simply means to use index nnn for that parameter. Subsequent bare ? parameter indexes are incremented by 1 from the largest parameter index thus far used. Ie where a= ? and b = ? and c = ?1 and d=? Has 3 parameters. A and C use the same bind index and thus the same parameter. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Igor Korot Sent: Sunday, 28 April, 2013 13:05 To: General Discussion of SQLite Database Subject: [sqlite] Question about binding Hi, ALL, On the page http://www.sqlite.org/c3ref/bind_blob.html it says: [quote] . that matches one of following templates: - ? - ?NNN - :VVV - @VVV - $VVV [/quote] What is the purpose of having NNN and VVV? Are those standard? How do I use those templates? Everywhere I see an examples which uses just ? and no other 4 templates. Thank you. ___ 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] Question about binding
Hi, Keith, On Sun, Apr 28, 2013 at 12:34 PM, Keith Medcalf kmedc...@dessus.com wrote: http://www.sqlite.org/lang_expr.html#varparam They are what are called Named Parameters. You use the sqlite3_bind_parameter_index to look up the index associated with a name ... ?nnn simply means to use index nnn for that parameter. Subsequent bare ? parameter indexes are incremented by 1 from the largest parameter index thus far used. Ie where a= ? and b = ? and c = ?1 and d=? So it means that: [pseudo-code] sqlite3_bind...( stmt, 1,...); sqlite3_bind...( stmt, 2,...); sqlite3_bind...( stmt, 3, ...); [/pseudo-code] and a and c will have 1, b - 2 and c - 3, right? Thank you. Has 3 parameters. A and C use the same bind index and thus the same parameter. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Igor Korot Sent: Sunday, 28 April, 2013 13:05 To: General Discussion of SQLite Database Subject: [sqlite] Question about binding Hi, ALL, On the page http://www.sqlite.org/c3ref/bind_blob.html it says: [quote] . that matches one of following templates: - ? - ?NNN - :VVV - @VVV - $VVV [/quote] What is the purpose of having NNN and VVV? Are those standard? How do I use those templates? Everywhere I see an examples which uses just ? and no other 4 templates. Thank you. ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about binding
Exactly ... Both a=? and c=?1 will use the same parameter. With named parameters you would do something like: Where a = :a and b = :b and c = :a and d = :d sqlite3_bind_parameter_index(stmt, :a) - 1 sqlite3_bind_parameter_index(stmt, :b) - 2 sqlite3_bind_parameter_index(stmt, :d) - 3 named parameters just create a mapping between unique names - index so you do not have to track which index number corresponds to which parameter name. If you have lots of parameters and/or a complicated query you can see where this greatly improves maintainability (at a small expense of speed and code size). Many of the traditional types of SQL embeding (for example the EXEC SQL macro in older environments) or the tcl interpreter (for example) will automatically bind named parameters to local variables of the same name. Many SQLite wrappers can map parameters the same way, to a dictionary or to object attributes. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Igor Korot Sent: Sunday, 28 April, 2013 14:53 To: General Discussion of SQLite Database Subject: Re: [sqlite] Question about binding Hi, Keith, On Sun, Apr 28, 2013 at 12:34 PM, Keith Medcalf kmedc...@dessus.com wrote: http://www.sqlite.org/lang_expr.html#varparam They are what are called Named Parameters. You use the sqlite3_bind_parameter_index to look up the index associated with a name ... ?nnn simply means to use index nnn for that parameter. Subsequent bare ? parameter indexes are incremented by 1 from the largest parameter index thus far used. Ie where a= ? and b = ? and c = ?1 and d=? So it means that: [pseudo-code] sqlite3_bind...( stmt, 1,...); sqlite3_bind...( stmt, 2,...); sqlite3_bind...( stmt, 3, ...); [/pseudo-code] and a and c will have 1, b - 2 and c - 3, right? Thank you. Has 3 parameters. A and C use the same bind index and thus the same parameter. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Igor Korot Sent: Sunday, 28 April, 2013 13:05 To: General Discussion of SQLite Database Subject: [sqlite] Question about binding Hi, ALL, On the page http://www.sqlite.org/c3ref/bind_blob.html it says: [quote] . that matches one of following templates: - ? - ?NNN - :VVV - @VVV - $VVV [/quote] What is the purpose of having NNN and VVV? Are those standard? How do I use those templates? Everywhere I see an examples which uses just ? and no other 4 templates. Thank you. ___ 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 ___ 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] Question about binding
Keith, On Sun, Apr 28, 2013 at 2:21 PM, Keith Medcalf kmedc...@dessus.com wrote: Exactly ... Both a=? and c=?1 will use the same parameter. With named parameters you would do something like: Where a = :a and b = :b and c = :a and d = :d sqlite3_bind_parameter_index(stmt, :a) - 1 sqlite3_bind_parameter_index(stmt, :b) - 2 sqlite3_bind_parameter_index(stmt, :d) - 3 named parameters just create a mapping between unique names - index so you do not have to track which index number corresponds to which parameter name. If you have lots of parameters and/or a complicated query you can see where this greatly improves maintainability (at a small expense of speed and code size). Many of the traditional types of SQL embeding (for example the EXEC SQL macro in older environments) or the tcl interpreter (for example) will automatically bind named parameters to local variables of the same name. Many SQLite wrappers can map parameters the same way, to a dictionary or to object attributes. Thank you for confirmation. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Igor Korot Sent: Sunday, 28 April, 2013 14:53 To: General Discussion of SQLite Database Subject: Re: [sqlite] Question about binding Hi, Keith, On Sun, Apr 28, 2013 at 12:34 PM, Keith Medcalf kmedc...@dessus.com wrote: http://www.sqlite.org/lang_expr.html#varparam They are what are called Named Parameters. You use the sqlite3_bind_parameter_index to look up the index associated with a name ... ?nnn simply means to use index nnn for that parameter. Subsequent bare ? parameter indexes are incremented by 1 from the largest parameter index thus far used. Ie where a= ? and b = ? and c = ?1 and d=? So it means that: [pseudo-code] sqlite3_bind...( stmt, 1,...); sqlite3_bind...( stmt, 2,...); sqlite3_bind...( stmt, 3, ...); [/pseudo-code] and a and c will have 1, b - 2 and c - 3, right? Thank you. Has 3 parameters. A and C use the same bind index and thus the same parameter. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Igor Korot Sent: Sunday, 28 April, 2013 13:05 To: General Discussion of SQLite Database Subject: [sqlite] Question about binding Hi, ALL, On the page http://www.sqlite.org/c3ref/bind_blob.html it says: [quote] . that matches one of following templates: - ? - ?NNN - :VVV - @VVV - $VVV [/quote] What is the purpose of having NNN and VVV? Are those standard? How do I use those templates? Everywhere I see an examples which uses just ? and no other 4 templates. Thank you. ___ 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 ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Question] SQLITE_FCNTL_CHUNK_SIZE
Hi.
Another question.
Below source code is a part of pager_write_pagelist().
In this code, dbSize of pPager or pgno of pList are compared with
dbHistSize of pPager.
However, szFile variable is only calculated from dbSize of pPager.
/* Before the first write, give the VFS a hint of what the final
** file size will be.
*/
assert( rc!=SQLITE_OK || isOpen(pPager-fd) );
if( rc==SQLITE_OK
(pList-pDirty ? pPager-dbSize : pList-pgno+1)pPager-dbHintSize
){
sqlite3_int64 szFile = pPager-pageSize *
(sqlite3_int64)pPager-dbSize;
sqlite3OsFileControlHint(pPager-fd, SQLITE_FCNTL_SIZE_HINT, szFile);
pPager-dbHintSize = pPager-dbSize;
}
Should this line be changed to check pList-pgno?
From: sqlite3_int64 szFile = pPager-pageSize *
(sqlite3_int64)pPager-dbSize;
To: sqlite3_int64 szFile = pPager-pageSize *
(sqlite3_int64)(pList-pDirty ? pPager-dbSize : pList-pgno+1);
When the original source is tested, fstat() in fcntlSizeHint() is called
frequently without any changes to file size.
Regards,
Yongil Jang.
2013/4/19 Yongil Jang yongilj...@gmail.com
Dear all,
I have a qustion about using chunk size on VFS.
AFAIK, chunk size means that increasing and reducing file size has
dependency on chunk size.
For example, with chunk size of 32KB, DB file size can be 32KB, 64KB, 96KB
and so on...
However, when I tested with my own test scripts that inserts single record
to a database file, file size increased with single page size. (ex: 4KB
page size)
In case of multiple insertion or update with transaction calls fallocate
with chunk size.
It would not be a big problem, but I will be happy if I can get some
description about this.
Best regards,
Yongil Jang.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [Question] SQLITE_FCNTL_CHUNK_SIZE
Dear all, I have a qustion about using chunk size on VFS. AFAIK, chunk size means that increasing and reducing file size has dependency on chunk size. For example, with chunk size of 32KB, DB file size can be 32KB, 64KB, 96KB and so on... However, when I tested with my own test scripts that inserts single record to a database file, file size increased with single page size. (ex: 4KB page size) In case of multiple insertion or update with transaction calls fallocate with chunk size. It would not be a big problem, but I will be happy if I can get some description about this. Best regards, Yongil Jang. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?
Hello, I have a question about user defined function. When I make user defined function, that function has argument count and values only. Is there any way that I can recognize there arguments are generated from dynamic binding (ex: ?) or static string? For following examples, there are two different cases. ex1) SELECT * FROM myTable WHERE myFunc(col1, test string); ex2) SELECT * FROM myTable WHERE myFunc(col1, ?); As you can see here, second parameter is changed. But, myFunc() gets same values for each case. For more information, I just made some functions that handling files path. But, if file name includes Special characters(ex: '') or Unicode and it is used for myFunc() then it makes Syntax error error code and execution is failed. What I want to do is to make an WARNING message if parameter is not dynamic binding value for developers who would use myFunc(). Best regards, Yongil Jang. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?
On Wed, Apr 3, 2013 at 8:58 AM, Yongil Jang yongilj...@gmail.com wrote: Is there any way that I can recognize there arguments are generated from dynamic binding (ex: ?) or static string? No. Applications-defined functions are call-by-value, as in C. If you have a C function, you cannot tell if the parameters came from variables, expressions, or literals. In the same way, SQLite application defined functions have no means of tracing the original of parameter values. -- 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] [Question] How can I recognize arguments are dynamic binding values in user defined function?
Thank you! I may need to make plan B. 2013. 4. 3. 오후 10:04에 Richard Hipp d...@sqlite.org님이 작성: On Wed, Apr 3, 2013 at 8:58 AM, Yongil Jang yongilj...@gmail.com wrote: Is there any way that I can recognize there arguments are generated from dynamic binding (ex: ?) or static string? No. Applications-defined functions are call-by-value, as in C. If you have a C function, you cannot tell if the parameters came from variables, expressions, or literals. In the same way, SQLite application defined functions have no means of tracing the original of parameter values. -- D. Richard Hipp d...@sqlite.org ___ 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] [Question] How can I recognize arguments are dynamic binding values in user defined function?
On 3 Apr 2013, at 2:08pm, Yongil Jang yongilj...@gmail.com wrote: Thank you! I may need to make plan B. Create a third parameter that tells your function what to do. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?
On 4/3/2013 8:58 AM, Yongil Jang wrote: For more information, I just made some functions that handling files path. But, if file name includes Special characters(ex: '') or Unicode and it is used for myFunc() then it makes Syntax error error code and execution is failed. What I want to do is to make an WARNING message if parameter is not dynamic binding value for developers who would use myFunc(). If the string literal is syntactically invalid, any syntax errors would be reported at the time the statement is prepared. Naturally, a statement with syntax errors cannot be executed, so your function wouldn't be called in the first place; of course, if it's not called, it can't issue any warnings. If your function is running, this means it's been given a valid string, whether as a string literal or a bound parameter. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?
Thank you, Simon and Igor. I will investigate about your opinion, as you mentioned. In general, if parameter string contains alphabets only, it doesn't make any problems. However, I couldn't check that my function is used correctly for every applications. Some developers don't know why does it fails when using special characters and applications can be packaged with hidden issues. For this reason, I was looking for some solutions that I can send warning messages to application developers if they use plain text without binding arguments. Best regards, Yongil jang. 2013. 4. 3. 오후 10:13에 Igor Tandetnik i...@tandetnik.org님이 작성: On 4/3/2013 8:58 AM, Yongil Jang wrote: For more information, I just made some functions that handling files path. But, if file name includes Special characters(ex: '') or Unicode and it is used for myFunc() then it makes Syntax error error code and execution is failed. What I want to do is to make an WARNING message if parameter is not dynamic binding value for developers who would use myFunc(). If the string literal is syntactically invalid, any syntax errors would be reported at the time the statement is prepared. Naturally, a statement with syntax errors cannot be executed, so your function wouldn't be called in the first place; of course, if it's not called, it can't issue any warnings. If your function is running, this means it's been given a valid string, whether as a string literal or a bound parameter. -- Igor Tandetnik __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] [Question] How can I recognize arguments are dynamic binding values in user defined function?
The people who are using your software need a lesson about SQL injection. No one should create SQL statements on the fly that include literal character strings built from data. Not only could there be issues if there are special characters in the data to be included as a literal string (including the possibility of a syntax error that prevents the statement from being executed) but evil people could do severe damage -- but this can easily be avoided by using parameters. For example, if the value being searched for came from user input (say on a web page) users of your software must not do things like this -- sql = select * from mytbl where mycol =' + input + ' because, if the input is something like x' ; drop table mytbl; -- the table will be dropped! This cannot happen if parameters are used to pass the string. You might be better off providing only a method where the user passes strings for the table name (mytbl in the example before), the columns to be returned (separated by commas, or * for all as above), the name of the column to compare (mycol in the example) and the value to search for. It would then be your code that builds and runs the SQL statement using parameters. J. Merrill -Original Message- Date: Wed, 3 Apr 2013 22:41:01 +0900 From: Yongil Jang yongilj...@gmail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function? Message-ID: cao_0w+hg70q00zzcxn6ojigo_otrng25bbn9n_p+t0xavyq...@mail.gmail.com Content-Type: text/plain; charset=EUC-KR Thank you, Simon and Igor. I will investigate about your opinion, as you mentioned. In general, if parameter string contains alphabets only, it doesn't make any problems. However, I couldn't check that my function is used correctly for every applications. Some developers don't know why does it fails when using special characters and applications can be packaged with hidden issues. For this reason, I was looking for some solutions that I can send warning messages to application developers if they use plain text without binding arguments. Best regards, Yongil jang. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?
http://xkcd.com/327/ --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of j.merr...@enlyton.com Sent: Wednesday, 03 April, 2013 17:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function? The people who are using your software need a lesson about SQL injection. No one should create SQL statements on the fly that include literal character strings built from data. Not only could there be issues if there are special characters in the data to be included as a literal string (including the possibility of a syntax error that prevents the statement from being executed) but evil people could do severe damage -- but this can easily be avoided by using parameters. For example, if the value being searched for came from user input (say on a web page) users of your software must not do things like this -- sql = select * from mytbl where mycol =' + input + ' because, if the input is something like x' ; drop table mytbl; -- the table will be dropped! This cannot happen if parameters are used to pass the string. You might be better off providing only a method where the user passes strings for the table name (mytbl in the example before), the columns to be returned (separated by commas, or * for all as above), the name of the column to compare (mycol in the example) and the value to search for. It would then be your code that builds and runs the SQL statement using parameters. J. Merrill -Original Message- Date: Wed, 3 Apr 2013 22:41:01 +0900 From: Yongil Jang yongilj...@gmail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function? Message-ID: cao_0w+hg70q00zzcxn6ojigo_otrng25bbn9n_p+t0xavyq...@mail.gmail.com Content-Type: text/plain; charset=EUC-KR Thank you, Simon and Igor. I will investigate about your opinion, as you mentioned. In general, if parameter string contains alphabets only, it doesn't make any problems. However, I couldn't check that my function is used correctly for every applications. Some developers don't know why does it fails when using special characters and applications can be packaged with hidden issues. For this reason, I was looking for some solutions that I can send warning messages to application developers if they use plain text without binding arguments. Best regards, Yongil jang. ___ 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] [Question] How can I recognize arguments are dynamic binding values in user defined function?
Thank you, J. and Keith! 2013/4/4 Keith Medcalf kmedc...@dessus.com http://xkcd.com/327/ --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of j.merr...@enlyton.com Sent: Wednesday, 03 April, 2013 17:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function? The people who are using your software need a lesson about SQL injection. No one should create SQL statements on the fly that include literal character strings built from data. Not only could there be issues if there are special characters in the data to be included as a literal string (including the possibility of a syntax error that prevents the statement from being executed) but evil people could do severe damage -- but this can easily be avoided by using parameters. For example, if the value being searched for came from user input (say on a web page) users of your software must not do things like this -- sql = select * from mytbl where mycol =' + input + ' because, if the input is something like x' ; drop table mytbl; -- the table will be dropped! This cannot happen if parameters are used to pass the string. You might be better off providing only a method where the user passes strings for the table name (mytbl in the example before), the columns to be returned (separated by commas, or * for all as above), the name of the column to compare (mycol in the example) and the value to search for. It would then be your code that builds and runs the SQL statement using parameters. J. Merrill -Original Message- Date: Wed, 3 Apr 2013 22:41:01 +0900 From: Yongil Jang yongilj...@gmail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function? Message-ID: cao_0w+hg70q00zzcxn6ojigo_otrng25bbn9n_p+t0xavyq...@mail.gmail.com Content-Type: text/plain; charset=EUC-KR Thank you, Simon and Igor. I will investigate about your opinion, as you mentioned. In general, if parameter string contains alphabets only, it doesn't make any problems. However, I couldn't check that my function is used correctly for every applications. Some developers don't know why does it fails when using special characters and applications can be packaged with hidden issues. For this reason, I was looking for some solutions that I can send warning messages to application developers if they use plain text without binding arguments. Best regards, Yongil jang. ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about behavior when journal_mode = off
If all connections (1 per thread) of all processes (multiple simultaneous) issue command PRAGMA journal_mode = off, is access to a common database file located on the local machine still synchronized correctly between the random accesses (reads and writes) occurring across all? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about behavior when journal_mode = off
On Wed, Mar 27, 2013 at 8:05 AM, Jeff Archer jsarc...@nanotronicsimaging.com wrote: If all connections (1 per thread) of all processes (multiple simultaneous) issue command PRAGMA journal_mode = off, is access to a common database file located on the local machine still synchronized correctly between the random accesses (reads and writes) occurring across all? Yes. The journal handles rollback and atomic commit (both of which you lose when you go to journal_mode=off) but isolation is handled with file locking on the original database file. -- 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] Question about aggregate returning empty row
Ok, thanks ! There are certainly good reasons for that, but I find it surprising at first sight. Pierre That's SQL standard -- query with aggregate functions always return at least one row. [tested under 3.6.12 and 3.7.15.2] I have a question regarding the use of aggregate functions. Let's imagine the following db : create table A (id1 INTEGER UNIQUE PRIMARY KEY, id2 INTEGER); It is empty. The following query : select id1,id2 from A; returns nothing, there is no row. However, the following query, using the aggregate min() : select min(id1),id2 from A; returns an empty line (displays '|' in the shell). Using avg(), max()... will do the same. With the C interface, SQLITE_ROW is returned, and I must test sqlite_column_type() against SQLITE_NULL to check that in fact, there is no result. Is this expected behaviour ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about aggregate returning empty row
Hi, you may add a dummy GROUP BY clause, to return no row if the table is empty: select min (x) from tbl group by null; By the way, the good reasons for the standard behaviour may be the use of other aggregate functions like COUNT (*). Op 23 feb 2013, om 11:51 heeft Pierre Chatelier het volgende geschreven: Ok, thanks ! There are certainly good reasons for that, but I find it surprising at first sight. Pierre That's SQL standard -- query with aggregate functions always return at least one row. [tested under 3.6.12 and 3.7.15.2] I have a question regarding the use of aggregate functions. Let's imagine the following db : create table A (id1 INTEGER UNIQUE PRIMARY KEY, id2 INTEGER); It is empty. The following query : select id1,id2 from A; returns nothing, there is no row. However, the following query, using the aggregate min() : select min(id1),id2 from A; returns an empty line (displays '|' in the shell). Using avg(), max()... will do the same. With the C interface, SQLITE_ROW is returned, and I must test sqlite_column_type() against SQLITE_NULL to check that in fact, there is no result. Is this expected behaviour ? ___ 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] Question about aggregate returning empty row
I would think so. You're asking for the minimum value of what is encountered. Follows along the line of count as well. The minimum return is going to be zero or null (I've not checked), but, you're asking for an absolute answer that will return some sort of value. On Fri, Feb 22, 2013 at 3:06 PM, Pierre Chatelier k...@club-internet.frwrote: Hello, [tested under 3.6.12 and 3.7.15.2] I have a question regarding the use of aggregate functions. Let's imagine the following db : create table A (id1 INTEGER UNIQUE PRIMARY KEY, id2 INTEGER); It is empty. The following query : select id1,id2 from A; returns nothing, there is no row. However, the following query, using the aggregate min() : select min(id1),id2 from A; returns an empty line (displays '|' in the shell). Using avg(), max()... will do the same. With the C interface, SQLITE_ROW is returned, and I must test sqlite_column_type() against SQLITE_NULL to check that in fact, there is no result. Is this expected behaviour ? Regards, Pierre Chatelier ___ 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] Question about aggregate returning empty row
That's SQL standard -- query with aggregate functions always return at least one row. Pavel On Fri, Feb 22, 2013 at 12:06 PM, Pierre Chatelier k...@club-internet.fr wrote: Hello, [tested under 3.6.12 and 3.7.15.2] I have a question regarding the use of aggregate functions. Let's imagine the following db : create table A (id1 INTEGER UNIQUE PRIMARY KEY, id2 INTEGER); It is empty. The following query : select id1,id2 from A; returns nothing, there is no row. However, the following query, using the aggregate min() : select min(id1),id2 from A; returns an empty line (displays '|' in the shell). Using avg(), max()... will do the same. With the C interface, SQLITE_ROW is returned, and I must test sqlite_column_type() against SQLITE_NULL to check that in fact, there is no result. Is this expected behaviour ? Regards, Pierre Chatelier ___ 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
[sqlite] question on request Linq is SQL
question on request Linq is SQL I hooked a provider edmx model and try to pull the essence. Two tables and typearmatura beton ying this request is no error var quer = gf.typearmatura.Where (c = c.idtypearmatura == Ls1.SelectedIndex + 1). FirstOrDefault (); this request var quer = gf.beton.Where (c = c.idBeton == Ls1.SelectedIndex + 1). FirstOrDefault (); bug InnerExeption make sure that the base type can be converted to final InnerExeption upon actuation of its value must be finite -- С Уважением Алексей Куликов Справочник строителя INCD www.rossecorp.ru ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question on VACCUUM, WAL, and Encryption Codecs
Hi All, Can anyone verify that VACCUUM and WAL uses encryption codecs if available? I think I found answers for other components such as rollback journals, but I'm not clear on the two items above. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on VACCUUM, WAL, and Encryption Codecs
On Mon, Feb 18, 2013 at 11:21 AM, Jeffrey Walton noloa...@gmail.com wrote: Hi All, Can anyone verify that VACCUUM and WAL uses encryption codecs if available? I think I found answers for other components such as rollback journals, but I'm not clear on the two items above. Yes. VACUUM and WAL work the same. With the SQLite Encryption Extension ( http://www.hwaci.com/sw/sqlite/see.html) everything works as with ordinary SQLite, include VACUUM, WAL, ROLLBACK, and ATTACH. The only difference is that your database file might be slightly larger (about 0.1% larger) due to space used to hold encryption nounces, and the database file will look like white noise to anybody without SEE and knowledge of the encryption key. Jeff ___ 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
[sqlite] Question: User defined function and auxiliary data for multiple instances on one query
Dear, I'm trying to make a mystrstr function to use it as a sub-function of like. mystrstr function is designed to use boyer-moore algorithm to improve string search performance. (Only for simple substing search, but not a complex search pattern) But, the problem is occurred when this mystrstr function is called two more times on one query. In boyer-moore algorithm, search pattern string should be preprocessed before it is used. I used sqlite3_set_auxdata() and sqlite3_get_auxdata() function to save preprocessed search pattern to call preprocessing process only once. It works very well if I use only one mystrstr function on one query. example) select * from mytable where mystrstr(col1, 'test1') and mystrstr(col2, 'test2'); In this case, mystrstr function is called twice and there are two different search patterns. I can use two aux data slots and can find preprocessed patterns by string compare with original pattern string for each call of mystrstr(). But, I think it is not a good idea, because of string compare can make not necessary processing cost. Could I get some more information of currently called function? For example, If I can get PC(program count of VDBE) from context, this value can be used to distinguish current position of my function on a query. Thank you. Yongil Jang. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question: User defined function and auxiliary data for multiple instances on one query
On 02/08/2013 12:08 PM, Yongil Jang wrote: Dear, I'm trying to make a mystrstr function to use it as a sub-function of like. mystrstr function is designed to use boyer-moore algorithm to improve string search performance. (Only for simple substing search, but not a complex search pattern) But, the problem is occurred when this mystrstr function is called two more times on one query. In boyer-moore algorithm, search pattern string should be preprocessed before it is used. I used sqlite3_set_auxdata() and sqlite3_get_auxdata() function to save preprocessed search pattern to call preprocessing process only once. It works very well if I use only one mystrstr function on one query. example) select * from mytable where mystrstr(col1, 'test1') and mystrstr(col2, 'test2'); In this case, mystrstr function is called twice and there are two different search patterns. I can use two aux data slots and can find preprocessed patterns by string compare with original pattern string for each call of mystrstr(). But, I think it is not a good idea, because of string compare can make not necessary processing cost. I don't think you should have to do anything special for this to work. SQLite will allocate separate aux-data slots to each invocation. The array of aux-data slots accessed by mystrstr(col1, 'test1') is different to the array accessed by mystrstr(col2, 'test2'). So if the implementation just stores the compiled version of the search pattern in aux-data slot 1 things should just work. Dan. Could I get some more information of currently called function? For example, If I can get PC(program count of VDBE) from context, this value can be used to distinguish current position of my function on a query. Thank you. Yongil Jang. ___ 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] Question: User defined function and auxiliary data for multiple instances on one query
Thank you, Dan! It really works well as you commented! Thank you again! 2013/2/8 Dan Kennedy danielk1...@gmail.com On 02/08/2013 12:08 PM, Yongil Jang wrote: Dear, I'm trying to make a mystrstr function to use it as a sub-function of like. mystrstr function is designed to use boyer-moore algorithm to improve string search performance. (Only for simple substing search, but not a complex search pattern) But, the problem is occurred when this mystrstr function is called two more times on one query. In boyer-moore algorithm, search pattern string should be preprocessed before it is used. I used sqlite3_set_auxdata() and sqlite3_get_auxdata() function to save preprocessed search pattern to call preprocessing process only once. It works very well if I use only one mystrstr function on one query. example) select * from mytable where mystrstr(col1, 'test1') and mystrstr(col2, 'test2'); In this case, mystrstr function is called twice and there are two different search patterns. I can use two aux data slots and can find preprocessed patterns by string compare with original pattern string for each call of mystrstr(). But, I think it is not a good idea, because of string compare can make not necessary processing cost. I don't think you should have to do anything special for this to work. SQLite will allocate separate aux-data slots to each invocation. The array of aux-data slots accessed by mystrstr(col1, 'test1') is different to the array accessed by mystrstr(col2, 'test2'). So if the implementation just stores the compiled version of the search pattern in aux-data slot 1 things should just work. Dan. Could I get some more information of currently called function? For example, If I can get PC(program count of VDBE) from context, this value can be used to distinguish current position of my function on a query. Thank you. Yongil Jang. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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-**usershttp://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
[sqlite] New to SQLite Question - References
Hello All, Can you give suggestions on where to look for information on connecting to a SQLite database from within a Java application? I'm new to both Java SQLite and am wondering where is the best place to start? I have been programming in C++ a long time using SQL Server so I'm not a novice programmer, just a novice Java guy SQLite newbie, trying to learn new stuff. Thanks in advance, Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New to SQLite Question - References
On 1 Nov 2012, at 2:53pm, Ryan Kool rk...@us.ibm.com wrote: Can you give suggestions on where to look for information on connecting to a SQLite database from within a Java application? They're usually called 'wrappers'. Google the two words Java SQLite and pick one. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about foreign key
On Sat, Oct 20, 2012 at 10:41 PM, Igor Korot ikoro...@gmail.com wrote: Hi, ALL, According to http://www.sqlite.org/foreignkeys.html the FOREIGN KEY support is disabled by default. In order to enable it I need to compile SQLITE with 2 defines undefined. Which also undefined by default. Any SQLITE_OMIT_* define is undefined by default. I downloaded the file sqlite-amalgamation-3071400.zip, unpacked it, added .c and .h file to my project and inspected them. SQLITE_OMIT_FOREIGN_KEY can not be found in both .h files and I don't see the #define of this constant anywhere in the .c file. Is foreign key documentation outdated? Nope, everything is right. Also one minor question: do I need both .h files or just one will suffice? Would be nice to know what names those both .h files have. But generally speaking sqlite3.h should be enough. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about foreign key
On Saturday, 20 October, 2012 at 23:42, Igor Korot ikoro...@gmail.com wrote: According to http://www.sqlite.org/foreignkeys.html the FOREIGN KEY support is disabled by default. Yes, foreign key enforcement is disabled by default. In order to enable it I need to compile SQLITE with 2 defines undefined. Not quite. The default setting for the enforcement of foreign key constraints is controlled by the define SQLITE_DEFAULT_FOREIGN_KEYS The default value is 0, which disables foreign key constraint enforcement by default. Recompiling with a value of 1 enables foreign key constraint enforcement by default. At runtime, you can use the SQL statements PRAGMA FOREIGN_KEYS=1; to enable foreign key constraint enforcement and PRAGMA FOREIGN_KEYS=0; to disable foreign key constraint enforcement. PRAGMA FOREIGN_KEYS; will return 0 or 1 indicating whether foreign key constraints are being enforced or not. I downloaded the file sqlite-amalgamation-3071400.zip, unpacked it, added .c and .h file to my project and inspected them. SQLITE_OMIT_FOREIGN_KEY can not be found in both .h files and I don't see the #define of this constant anywhere in the .c file. By default nothing is omitted. However, if you *do* choose to omit parts of the engine, the effect will be as described: If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to version 3.6.19 - foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced. The PRAGMA foreign_keys command is a no-op in this configuration. If OMIT_FOREIGN_KEY is defined, then foreign key definitions cannot even be parsed (attempting to specify a foreign key definition is a syntax error). Is foreign key documentation outdated? No, it is correct. See http://www.sqlite.org/compile.html for the options you can define at compile time, the defaults, and the effect. Also one minor question: do I need both .h files or just one will suffice? sqlite3ext.h is used when compiling extensions to the sqlite3 engine that are not part of the engine itself -- that is, loadable modules. sqlite3.h is the header file which must be included by c sources which are part of the engine, such as the sqlite3.c amalgamation itself, or extension modules that will be built-in. Thank you. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about foreign key
Hi, Keith, On Sat, Oct 20, 2012 at 11:10 PM, Keith Medcalf kmedc...@dessus.com wrote: On Saturday, 20 October, 2012 at 23:42, Igor Korot ikoro...@gmail.com wrote: According to http://www.sqlite.org/foreignkeys.html the FOREIGN KEY support is disabled by default. Yes, foreign key enforcement is disabled by default. OK. In order to enable it I need to compile SQLITE with 2 defines undefined. Not quite. The default setting for the enforcement of foreign key constraints is controlled by the define SQLITE_DEFAULT_FOREIGN_KEYS The default value is 0, which disables foreign key constraint enforcement by default. Recompiling with a value of 1 enables foreign key constraint enforcement by default. At runtime, you can use the SQL statements PRAGMA FOREIGN_KEYS=1; to enable foreign key constraint enforcement and PRAGMA FOREIGN_KEYS=0; to disable foreign key constraint enforcement. PRAGMA FOREIGN_KEYS; will return 0 or 1 indicating whether foreign key constraints are being enforced or not. All this contradicts the document I referenced above. See part 2 of it. [quote] In order to use foreign key constraints in SQLite, the library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined. [/quote] The constant you mentioned is not present in this part of online documentation. I downloaded the file sqlite-amalgamation-3071400.zip, unpacked it, added .c and .h file to my project and inspected them. SQLITE_OMIT_FOREIGN_KEY can not be found in both .h files and I don't see the #define of this constant anywhere in the .c file. By default nothing is omitted. However, if you *do* choose to omit parts of the engine, the effect will be as described: If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to version 3.6.19 - foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced. The PRAGMA foreign_keys command is a no-op in this configuration. If OMIT_FOREIGN_KEY is defined, then foreign key definitions cannot even be parsed (attempting to specify a foreign key definition is a syntax error). Well let me clarify a bit. I am trying to develop a software which will communicate with SQLite database. In order for that to happen properly I need the foreing key constraint to be included. And now I need to look for a third constant that was not in a picture. ;-) Is foreign key documentation outdated? No, it is correct. See http://www.sqlite.org/compile.html for the options you can define at compile time, the defaults, and the effect. Well it does not mention the constant you were talking about so at the very least it's incorrect/incomplete. Also one minor question: do I need both .h files or just one will suffice? sqlite3ext.h is used when compiling extensions to the sqlite3 engine that are not part of the engine itself -- that is, loadable modules. sqlite3.h is the header file which must be included by c sources which are part of the engine, such as the sqlite3.c amalgamation itself, or extension modules that will be built-in. OK, so sqlite3.h should be enough to work with basic query. That's what I thought but needed confirmation for. Thank you. Thank you. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ 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
