[sqlite] encrypted file
Hi List, Whether the data base which we create through the program will be in encrypted form? If yes, how can I see the result in sqlite? (How can use select statements on this database?) My application creates a database and to analyze the results in the database, it will be easy for me to use the interface provided by the sqlite. How can I open the database in SQLite ? Thanks, Lloyd. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regarding sqlite3_exec
Hi The optimal way is that you prepare the statement, fetch and count the results with sqlite3_step. How would I fetch and count the results via sqlite3_step? Do you mean fetch all the records first? What if my result set is huge, and I would only like to show the first few records but still know how many there are? For exmaple, Lets say I run a SQL statement (its a very heavy statement consiting of joins and subqueries). It returns 5000 rows. For speed I dont want to retrieve 5000 rows, I want to setup a list which shows that there are 5000 rows on the scroll bar, but only retrieves the first say 20 for display. Is this possible? I know I can do a select count(*) from (SQL) but its a heavy query and then I would be running it twice? Any solution to this? S On 10/6/06, He Shiming [EMAIL PROTECTED] wrote: Hi List, If I use sqlite3_exec to query a database, How can I know that the results in the data base got over. For example If I am expecting a 10 results in some for loop and actually there are only five results , How can I get a notification or return value that the results completed or Is there any way I can get SQLITE_DONE through sqlite3_Exec. What return value I will get If I query an empty table. Thanks and Regards, Vivek R SQLite didn't provide a get number of rows function for the result set. It is mentioned in the document that sqlite3_exec is actually a wrapper for sqlite3_prepare and sqlite3_step. It is in my opinion that sqlite3_exec should only be used when the result of the query isn't that important. For instance, a pragma query. For the record, sqlite3_exec did provide a callback function in which you can count and get the number of rows in a resultset. The optimal way is that you prepare the statement, fetch and count the results with sqlite3_step. Another thing I noticed from your question is that you might not want to expect 10 results. It's not very wise to design a hard loop such as for(i=0;i10;i++) when comes to a database query resultset. A better way would be to use an array to store the result set they way you could understand, and process them later. Then you'll have for(i=0;iarray.size()10?array.size():10;i++). Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] insertion time
Lloyd [EMAIL PROTECTED] wrote: Hi, When this 3M inserts are performed, it takes 130 more seconds compared to the actual application running time. The actual application run time is 20 seconds but when this 3M inserts are performed it takes 150 seconds. Is there any way to reduce the time ? 1. Use prepared statements 2. Reduce the number of indices on the table, or add the indices after all data has been inserted. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] encrypted file
Lloyd [EMAIL PROTECTED] wrote: Hi List, Whether the data base which we create through the program will be in encrypted form? The public-domain version of SQLite does not encrypt the data. But commerical extensions that encrypt the data are available. See, for example, http://www.hwaci.com/sw/sqlite/prosupport.html#crypto If yes, how can I see the result in sqlite? (How can use select statements on this database?) My application creates a database and to analyze the results in the database, it will be easy for me to use the interface provided by the sqlite. How can I open the database in SQLite ? When a database is encrypted you must supply the encryption key before reading or modifying the database. You can do this in several ways. There is a pragma: PRAGMA key=? If the database is being loaded using an ATTACH statement then you specify the key in the USING clause of the ATTACH: ATTACH DATABASE 'encrypted.db' AS e2 USING :key Or you can use the C/C++ API: sqlite3_key(db, zKey); Once the key is established, the database content is automatically decrypted as it is read from the disk and reencrypted as it is written back to the disk. If you use the command-line client, there is a special option to specify the key: sqlite3 -key 'hello' database.db After that the command-line client works just like it normally does. None of the above works in the public domain version of SQLite. You have to purchase a license for a version of SQLite that supports encryption in order to use these features. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regarding sqlite3_exec
Da Martian [EMAIL PROTECTED] wrote: Hi The optimal way is that you prepare the statement, fetch and count the results with sqlite3_step. How would I fetch and count the results via sqlite3_step? Do you mean fetch all the records first? What if my result set is huge, and I would only like to show the first few records but still know how many there are? For exmaple, Lets say I run a SQL statement (its a very heavy statement consiting of joins and subqueries). It returns 5000 rows. For speed I dont want to retrieve 5000 rows, I want to setup a list which shows that there are 5000 rows on the scroll bar, but only retrieves the first say 20 for display. Is this possible? No, it is not possible. In the general case where there are user-defined functions in the query, returning the number of rows in the result set is equivalent to the halting problem. See http://en.wikipedia.com/wiki/Halting_problem Even in the absence of the artifical constructs that make the problem theoretically undecidable, the problem is still very hard. I am not aware of a general solution other than to run the query to completion and count the rows. I suspect that I can write a proof that no solution exists that is faster than running the query to completion, though I have never taken the time to actually write that proof out. You might be able to find special cases where you can predict the size of the result set without actually computing the result set. But such techniques would only work for very narrowly defined queries over tables with very narrowly defined data constraints. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Potential corruption bug in 2.8.17. Patch attached.
[EMAIL PROTECTED] wrote:
This was likely a typo. In its current state, it's accessing uninitialized
memory. It looks like it's conceivable that an incorrect nextRowid could be
later used if the uninitialized value happens to be a small integer (smaller
than pC-nextRowid) and the valid flag therefore doesn't get set to false.
--- vdbe.c~ 2005-12-19 12:42:25.0 -0500
+++ vdbe.c2006-10-22 16:32:45.0 -0400
@@ -2937,7 +2937,7 @@
if( pOp-p2 OPFLAG_NCHANGE ) db-nChange++;
if( pOp-p2 OPFLAG_LASTROWID ) db-lastRowid = pNos-i;
if( pOp-p2 OPFLAG_CSCHANGE ) db-csChange++;
- if( pC-nextRowidValid pTos-i=pC-nextRowid ){
+ if( pC-nextRowidValid pNos-i=pC-nextRowid ){
pC-nextRowidValid = 0;
}
}
As it happens, pC-nextRowidValid is always false in this
context, as far as I can tell, so the pTos-i variable is
never accessed. The fix checked in was to remove the test
altogether and unconditionally set pC-nextRowidValid to 0.
Because the code is unreachable, this fix does not require
a new release of SQLite 2.8.
--
D. Richard Hipp [EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] memroy footprint for mobile phone
weiyang wang [EMAIL PROTECTED] wrote: hi, i am working on the sqlite evaluation for mobile phone. and now i am facing the issues of memory footprint. according to the documents, three parameters are used to control the memory footprint: default_cache_size, cache_size and page_size. i set them like this: default_cache_size =1: cache_size =1: page_size=1024 so the footprint for caching tables should be 1K. is it correct? The minimum cache size is 10. If you set the cache size to something smaller than 10, it defaults to 10. Note also that the cache size can increase above the limit you set if a complex query requires simultaneous access to more pages that the cache is defined to hold. So the cache size is not a hard limit. under this condition (default_cache_size =1, page_size=1024), i measured the memory handling of sqlite and the result showed that the total memory footprint is about 20K. is it the smallest footprint of Sqlite? if not, how to shrink it to a couple of Ks? 20K is already pretty small for an SQL RDBMS, don't you think? How much more of a miracle are you looking for here? -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Potential corruption bug in 2.8.17. Patch attached.
[EMAIL PROTECTED] writes:
[EMAIL PROTECTED] wrote:
This was likely a typo. In its current state, it's accessing uninitialized
memory. It looks like it's conceivable that an incorrect nextRowid could be
later used if the uninitialized value happens to be a small integer (smaller
than pC-nextRowid) and the valid flag therefore doesn't get set to false.
--- vdbe.c~ 2005-12-19 12:42:25.0 -0500
+++ vdbe.c 2006-10-22 16:32:45.0 -0400
@@ -2937,7 +2937,7 @@
if( pOp-p2 OPFLAG_NCHANGE ) db-nChange++;
if( pOp-p2 OPFLAG_LASTROWID ) db-lastRowid = pNos-i;
if( pOp-p2 OPFLAG_CSCHANGE ) db-csChange++;
- if( pC-nextRowidValid pTos-i=pC-nextRowid ){
+ if( pC-nextRowidValid pNos-i=pC-nextRowid ){
pC-nextRowidValid = 0;
}
}
As it happens, pC-nextRowidValid is always false in this
context, as far as I can tell, so the pTos-i variable is
never accessed.
That explains why in many years of use, I've never seen an actual corruption
caused by this. Thanks.
The fix checked in was to remove the test altogether and unconditionally set
pC-nextRowidValid to 0.
If, as you say, pC-nextRowidValid is always false anyway, wouldn't the
correct fix be to not even unconditionally set pC-nextRowidValid to 0; just
delete those two original lines entirely? It sounds like you're now
unnecessarily setting a variable that's already false to false. (Or did I
misunderstand your statement?)
Derrell
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] [OT] Java wrapper binaries for Windows with larget MAX_ARGS
On 10/20/06, David Crawshaw [EMAIL PROTECTED] wrote: Adriano Ferreira [EMAIL PROTECTED] wrote: Does anyone know where I can get binary files for the Christian Werner's Java wrapper (sqlite.jar and sqlite_jni.dll) which allows for a large number of placeholders (something like 256 or so)? If you are just after a JDBC driver with no arbitrary limit on the number of statement parameters, I provide a Windows binary: Thanks, David. I am giving a try with zentus driver. But I still don't know if it will fit my programming style (based on the notes on README file). For example, I will miss ResultSet.getObject() which I rely upon heavily. Adriano. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Potential corruption bug in 2.8.17. Patch attached.
[EMAIL PROTECTED] wrote:
--- vdbe.c~2005-12-19 12:42:25.0 -0500
+++ vdbe.c 2006-10-22 16:32:45.0 -0400
@@ -2937,7 +2937,7 @@
if( pOp-p2 OPFLAG_NCHANGE ) db-nChange++;
if( pOp-p2 OPFLAG_LASTROWID ) db-lastRowid = pNos-i;
if( pOp-p2 OPFLAG_CSCHANGE ) db-csChange++;
- if( pC-nextRowidValid pTos-i=pC-nextRowid ){
+ if( pC-nextRowidValid pNos-i=pC-nextRowid ){
pC-nextRowidValid = 0;
}
}
The fix checked in was to remove the test altogether and unconditionally set
pC-nextRowidValid to 0.
If, as you say, pC-nextRowidValid is always false anyway, wouldn't the
correct fix be to not even unconditionally set pC-nextRowidValid to 0; just
delete those two original lines entirely? It sounds like you're now
unnecessarily setting a variable that's already false to false. (Or did I
misunderstand your statement?)
I could not find a case where pC-nextRowidValid was true. But
neither did I prove it was impossible. Lets just say it was
very unlikely.
Setting pC-nextRowidValid merely invalidates a cache. Invalidating
the cache might make the code a little slower, but it will still get
the correct answer. So it is always safe to set pC-newRowidValid to
0. But we cannot leave pC-nextRowidValid in the true state because
that might leave an invalid value in the cache, resulting in a wrong
answer. So for safety, we always clear the cache here, even though
we have never seen an example where it is necessary. Better safe than
sorry.
--
D. Richard Hipp [EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Regarding sqlite3_exec
Hi I understand the problem in thoery and you are saying that sqlite is using an iterative algorithm which returns data as it finds it, is this correct? It appears that DBs like oracle etc.. get the whole resultset into temporary memory/space and then return the query (at least for unions this appears true), although this is just based off assumptions based on observations. It seems to me that the DB (ie sqlite) can determine the number of results far more effiently than a client application. The reason is, the client application has to call step (n) times and a column extract (n x no columns). While the db could just move through the results set and count them up without every doing any processing on the data being counted. Perhaps this could done as a seperate api, like preparewithcount() which returns the count as well. With carefull design most of the work needed to prepare the statement etc.. could avoid being repeated as would happen with a count(*) query. This is just an idea, and I am speaking simply as a developer, but one who has not looked at sqlite implentation at all. Thanks for your response, it was very informative, helpfull and poinient. S On 10/24/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Da Martian [EMAIL PROTECTED] wrote: Hi The optimal way is that you prepare the statement, fetch and count the results with sqlite3_step. How would I fetch and count the results via sqlite3_step? Do you mean fetch all the records first? What if my result set is huge, and I would only like to show the first few records but still know how many there are? For exmaple, Lets say I run a SQL statement (its a very heavy statement consiting of joins and subqueries). It returns 5000 rows. For speed I dont want to retrieve 5000 rows, I want to setup a list which shows that there are 5000 rows on the scroll bar, but only retrieves the first say 20 for display. Is this possible? No, it is not possible. In the general case where there are user-defined functions in the query, returning the number of rows in the result set is equivalent to the halting problem. See http://en.wikipedia.com/wiki/Halting_problem Even in the absence of the artifical constructs that make the problem theoretically undecidable, the problem is still very hard. I am not aware of a general solution other than to run the query to completion and count the rows. I suspect that I can write a proof that no solution exists that is faster than running the query to completion, though I have never taken the time to actually write that proof out. You might be able to find special cases where you can predict the size of the result set without actually computing the result set. But such techniques would only work for very narrowly defined queries over tables with very narrowly defined data constraints. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Sqlite Matlab
Hi All There seems to be a possible connection between Sqlite and Matlab available at http://sourceforge.net/project/showfiles.php?group_id=139986 However it is not refered to on the sqlite website. Does that mean it does not really work? Thank you in advance for your answer Victor - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regarding sqlite3_exec
Da Martian wrote:
Hi
I understand the problem in thoery and you are saying that sqlite is
using
an iterative algorithm which returns data as it finds it, is this
correct?
It appears that DBs like oracle etc.. get the whole resultset into
temporary
memory/space and then return the query (at least for unions this appears
true), although this is just based off assumptions based on observations.
It seems to me that the DB (ie sqlite) can determine the number of
results
far more effiently than a client application. The reason is, the client
application has to call step (n) times and a column extract (n x no
columns). While the db could just move through the results set and count
them up without every doing any processing on the data being counted.
Perhaps this could done as a seperate api, like preparewithcount() which
returns the count as well. With carefull design most of the work
needed to
prepare the statement etc.. could avoid being repeated as would happen
with
a count(*) query.
This is just an idea, and I am speaking simply as a developer, but one
who
has not looked at sqlite implentation at all.
Yes, sqlite iterates and returns each result row as it is located.
SQLite also has a legacy sqlite3_get_table API that will return the
entire result set in a table in ram. It can fail if there is not enough
memory to hold the result set though.
Your idea can (almost) be implemented in your application like this.
int prepare_and_count(sqlite3* db, const char* sql, int len,
sqlite3_stmt** s, const char** tail, int* count)
{
int rc = sqlite3_prepare(db, sql, len, s, tail);
*count = 0;
if (rc == SQLITE_OK) {
while (sqlite3_step(*s) == SQLITE_ROW)
++(*count);
sqlite3_reset(*s);
}
return rc;
}
This will avoid the need to prepare two queries by using the same query
twice, once to count the result rows and a second time to collect the
results. It does require N extra calls to sqlite3_step (which are very
low overhead compared to the execution of a step).
The extra calls to step are eliminated if you use a select count(*)
query instead. With a count query SQLite will scan through the
table as quickly as possible and count the results without stopping and
returning to the caller after each row. But this requires a second query
to be prepared.
When you look at the big picture though, optimizing the count query
isn't likely worth the effort. The count is usually only needed to
implement GUI controls like scrollbars. The time is takes to collect the
results and present them in the GUI will dominate the time it takes to
prepare and execute a second count query unless the result set is very
large. With large results the execution time of the count query
dominates, and the overhead time to prepare the query becomes
insignificant. It really doesn't take that long to prepare a count query.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Sqlite Matlab
[EMAIL PROTECTED] wrote: There seems to be a possible connection between Sqlite and Matlab available at http://sourceforge.net/project/showfiles.php?group_id=139986 However it is not refered to on the sqlite website. Does that mean it does not really work? Victor, I took a quick look at the code there. It hasn't been updated for a year or so, but it seems like it should be functional as is. It should work OK for small result sets, since it loads the entire result set into memory. Actually it ends up with two copies in memory while it is converting the data from sqlite to matlab formats. It also opens and closes the database file for each query which can be expensive. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Regarding sqlite3_exec
* Da Martian [EMAIL PROTECTED] [2006-10-24 16:15]: It appears that DBs like oracle etc.. get the whole resultset into temporary memory/space and then return the query (at least for unions this appears true), although this is just based off assumptions based on observations. What prevents you from doing the same yourself? Just collect the data in a memory buffer as you get it, before processing it. Perhaps this could done as a seperate api, like preparewithcount() which returns the count as well. That doesn’t seem to make sense – after all, the count can only be returned *after* all the rows have been collected. By then you know the count yourself anyway. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [OT] Java wrapper binaries for Windows with larget MAX_ARGS
Adriano Rodrigues [EMAIL PROTECTED] wrote: For example, I will miss ResultSet.getObject() which I rely upon heavily. Sorry, the README is out of date there. The latest version v026 supports ResultSet.getObject() by instancing the object that matches the storage type of the column. So you will get either an Integer, Double, byte[] or String. Other parts of the JDBC API are designed for ResultSets other than FORWARD_ONLY. Functions such as previou() or first() will never be supported, as they are not by most simple queries on most databases. d - To unsubscribe, send email to [EMAIL PROTECTED] -
