[sqlite] sqlite3_enable_shared_cache
I'd like to use sqlite3_enable_shared_cache in my APSW wrapper but I can't see a sensible safe way of doing it. The wrapper already enforces the conditions mentioned in the doc (all operations on a handle happening in the same thread). It looks like I'd have to do a lot of housekeeping calling sqlite3_enable_shared_cache just before the first sqlite3_open in a thread and just after the last sqlite3_close. Is SQLite already tracking these? Why can't I call enable on every sqlite3_open and have it just succeed if sharing is already setup? Will the last sqlite3_close not clean up the shared cache anyway? Roger
Re: [sqlite] last modified time or version of sqlite database
Thanks for your suggestions :) Chris.
Re: [sqlite] sqlite3 dll symbols
thanks you all for the explanations. It was a small project for a friend and
nothing critical really. I'm no windows guru, and i'm not so sure i want to
be (what with all the hoops and noops) ;)
Now back to Linux defender of... oops that's Voltron :)
On 4/1/06, John Stanton [EMAIL PROTECTED] wrote:
Dennis Jenkins wrote:
Essien Essien wrote:
hiya,
I have a code snippet that looks like:
typedef int (*SQLITE3_CLOSE)(sqlite3*);
typedef const char* (*SQLITE3_ERRMSG)(sqlite3*);
typedef int (*SQLITE3_OPEN)(const char*, sqlite3**);
typedef int (*SQLITE3_EXEC) (sqlite3*, const char*, sqlite3_callback,
void*,
char**);
HINSTANCE sqlite3_dll;
SQLITE3_CLOSE _sqlite3_close;
SQLITE3_ERRMSG _sqlite3_errmsg;
SQLITE3_OPEN _sqlite3_open;
SQLITE3_EXEC _sqlite3_exec;
int DB_Init()
{
sqlite3_dll = LoadLibrary(sqlite3.dll);
if (sqlite3_dll == NULL) {
printf(Cannot find sqlite3.dll. Make sure its in the same
directory as the program\n);
return 0;
}
_sqlite3_open = (SQLITE3_OPEN)GetProcAddress(sqlite3_dll,
sqlite3_open);
if (_sqlite3_open == NULL) {
printf(Cannot load function sqlite3_open);
return 0;
}
}
problem is, when ever i call DB_Init(), it always fails with 'Cannot
load
function sqlite3_open'. But it successfully passes the LoadLibrary
portion.
I'm not a win32 guru, so i'm willing to admit i've made a mistake
somewhere.
Any ideas on what i'm doing wrong?
I'm using Turbo C++ 4.5 IDE and related tools. (yeah... i know turbo
C++ 4.5is realy aged, but could this be the problem?)
Essien
Since you have the borland compiler product, use the TDUMP.EXE tool to
view the PE header of the sqlite3.dll file. Sometimes the functions
will be exported with a leading underscore. If your compiler is
producing 32 bit binaries, and the DLL is also 32 bit, then you might
try adding a leading underscore to the symbol name when you call
'GetProcAddress'.
You could also try statically linking Sqlite and bypassing the DLL.
AW: [sqlite] [ANN] SQLite ODBC driver version 0.67
Hi, I just would like to point out that: - a zip-archive for the drivers (no-install) is not provided - the documentation for sqliteodbc.c / sqliteodbc.h is missing Regards, Michael -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Christian Werner Gesendet: Montag, 3. April 2006 18:19 An: sqlite-users@sqlite.org Betreff: [sqlite] [ANN] SQLite ODBC driver version 0.67 Version 0.67 of the SQLite ODBC driver is ready for download from http://www.ch-werner.de/sqliteodbc It fixes some Unicode length issues with the SQLite 2.8.17 version of the driver and now fully supports binary data. The Win32 version is now made with a MinGW cross compiler and NSIS. Enjoy, Christian
Re: AW: [sqlite] [ANN] SQLite ODBC driver version 0.67
[EMAIL PROTECTED] wrote: Hi, I just would like to point out that: - a zip-archive for the drivers (no-install) is not provided That was my intention; is the ZIP version really necessary ? - the documentation for sqliteodbc.c / sqliteodbc.h is missing Ditto, since SQLite 2.* is legacy. The doxygen stuff had a lot of warnings when formatting both SQLite2 and SQLite3 driver sources, therefore I left the legacy one out. If there's really interest in browsable source code for the SQLite2 driver, I'll try to fix it. Regards, Christian
Re: [sqlite] sqlite3_enable_shared_cache
Roger Binns [EMAIL PROTECTED] wrote: I'd like to use sqlite3_enable_shared_cache in my APSW wrapper but I can't see a sensible safe way of doing it. The wrapper already enforces the conditions mentioned in the doc (all operations on a handle happening in the same thread). It looks like I'd have to do a lot of housekeeping calling sqlite3_enable_shared_cache just before the first sqlite3_open in a thread and just after the last sqlite3_close. Is SQLite already tracking these? Why can't I call enable on every sqlite3_open and have it just succeed if sharing is already setup? If you call sqlite3_enable_shared_cache while another connection is open, you will get an SQLITE_MISUSE error. But you can ignore that error if you want. Will the last sqlite3_close not clean up the shared cache anyway? It will. -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] Testing for table existence?
Hi, How can I test for the existence of a single table in a SQLite database? I tried sqlite3_get_table with select * from persons to test for the existence of the table 'persons' and I had hoped the return value would be SQLITE_NOTFOUND in the case of a non-existent table. However, I got SQLITE_ERROR, which is less helpfull. Any hints? Regards, Olaf
Re: [sqlite] Testing for table existence?
Hi, How can I test for the existence of a single table in a SQLite database? I tried sqlite3_get_table with select * from persons to test for the existence of the table 'persons' and I had hoped the return value would be SQLITE_NOTFOUND in the case of a non-existent table. However, I got SQLITE_ERROR, which is less helpfull. Any hints? Regards, Olaf Hello Olaf You can access the sqlite_master table. http://www.sqlite.org/cvstrac/wiki?p=InformationSchema enjoy! Philipp
[sqlite] Programmatic notification of BEGIN TRANSACTION?
Hi, I have some triggers that fire on INSERT, UPDATE, and DELETE to a view. Those triggers calls a user-defined library function. What I was wondering was, is there some way my library function can be notified of the beginning and end of a transaction? For instance, if the user issues an UPDATE on the view that affects four records, my UPDATE trigger fires four times. What I'd like to know is when that outer UPDATE transaction begins and ends, so that I can then group my user-defined function's actions in a way that continues to guarantee transactional atomicity. Is this possible short of mucking around in the VDBE? And if VDBE- mucking is required, does anybody want to offer any pointers? Thanks, -Mike Ashmore
Re: [sqlite] Testing for table existence?
Thanks, I implemented this and it works. I also stumbled upon the CREATE TABLE IF NOT EXIST syntax and I was wondering if this wouldn't be a better idea, at the very least it would be more convenient. Olaf - Original Message - From: Philipp Knüsel [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Tuesday, April 04, 2006 2:34 PM Subject: Re: [sqlite] Testing for table existence? Hi, How can I test for the existence of a single table in a SQLite database? I tried sqlite3_get_table with select * from persons to test for the existence of the table 'persons' and I had hoped the return value would be SQLITE_NOTFOUND in the case of a non-existent table. However, I got SQLITE_ERROR, which is less helpfull. Any hints? Regards, Olaf Hello Olaf You can access the sqlite_master table. http://www.sqlite.org/cvstrac/wiki?p=InformationSchema enjoy! Philipp
RE: [sqlite] Testing for table existence?
sqlite_master table tells you everything about every object in the database -Original Message- From: Olaf Beckman Lapré [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 9:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Testing for table existence? Thanks, I implemented this and it works. I also stumbled upon the CREATE TABLE IF NOT EXIST syntax and I was wondering if this wouldn't be a better idea, at the very least it would be more convenient. Olaf - Original Message - From: Philipp Knüsel [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Tuesday, April 04, 2006 2:34 PM Subject: Re: [sqlite] Testing for table existence? Hi, How can I test for the existence of a single table in a SQLite database? I tried sqlite3_get_table with select * from persons to test for the existence of the table 'persons' and I had hoped the return value would be SQLITE_NOTFOUND in the case of a non-existent table. However, I got SQLITE_ERROR, which is less helpfull. Any hints? Regards, Olaf Hello Olaf You can access the sqlite_master table. http://www.sqlite.org/cvstrac/wiki?p=InformationSchema enjoy! Philipp
Re: [sqlite] Re: Db copy
Iulian Popescu wrote: I looked at the .dump feature implementation in shell.c as well as how vacuum is implemented. Based on that an idea will be to iterate through the metainformation on the SQLITE_MASTER table in order to recreate the new Db schema and for each table run SQL statements that will transfer the content from the old db into the new one. However, I was wondering if there is an easier or computationally less expensive way to accomplish the same thing (do it without going through each table) something like cloning the B-Tree representing the database. Iulian, I looked through the vacuum code as well and that seems like the best approach to me. I think you might be looking for optimizations that aren't needed. The copy operation should be very quick with only two SQL statements executed for each table, one to copy the schema, and one to copy the contents. The data copy itself will be reading records from one memory database and writing that record to the other memory database, one record at a time, but without any conversion from the internal record format. While not as fast as a direct memory copy, it should be pretty quick. I don't think there is any way to clone the B-trees that make up the database. There is certainly no public API to access the B-trees, so even if something works today, there is no guarantee that it will work with the next version of SQLite. If you really need to copy the database faster than the SQL copy allows, you might want to talk to Richard Hipp about some paid support. He can surely let you know if it is possible, and may be able to do it for you for a reasonable fee. HTH Dennis Cote
Re: [sqlite] how to fix problem of lock
OK English is not my first language, i am from south America, from Paraguay so i speak Spanish... I am doing a project in C multiplataform.. right it project like google, for a Linux. the project uses SQ Lite for repository. The software is a server which listen connection UDP. The server could index (INSERT) and search within the database (SELECT). The problem is when i do so much connection at the same time for do index and search at the same time. Know is it clear? sorry for my english :D Thanxs On 4/3/06, John Stanton [EMAIL PROTECTED] wrote: Try to explain what you are doing. How do you access SQLITE? What language? What SQL are you using. How many concurrent users do you have? Are you getting a LOCK error because of multiple users? Cesar David Rodas Maldonado wrote: please i need some help On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: i insert numbers and select numbers, so what could be the solutions, couse i have to do that On 4/3/06, Pam Greene [EMAIL PROTECTED] wrote: An INSERT can change the results of your SELECT, so the database has to be locked during INSERT. Otherwise, the result of your SELECT would depend on whether the INSERT had finished yet. (The INSERT might even have only partly finished, which would mean the SELECT was looking at a database in an inconsistent state.) It's not good to have unpredictable results like that. For more explanation of why what you want isn't a good idea, see any discussion of an ACID database, for example http://en.wikipedia.org/wiki/ACID . - Pam On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: HElp me, couse i just need to do insert and select, i dont use delete or replate or update On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: thanx man but is posible open several DATABASE with a programm and do transaccion without locked the table??? On 4/3/06, Jay Sprenkle [EMAIL PROTECTED] wrote: On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: I have a database ok... i do a lot of insert and select, but there is sometime that i cant do the select couse the database is locked... i have to do a lot of insert every time, so how can i do for dont lock the database... understand guy? Try this: http://sqlite.org/lang_transaction.html
Re: [sqlite] sqlite3_enable_shared_cache
If you call sqlite3_enable_shared_cache while another connection is open, you will get an SQLITE_MISUSE error. But you can ignore that error if you want. The doc is far scarier: This routine must not be called when any database connections are active in the current thread. Enabling or disabling shared cache while there are active database connections will result in memory corruption. From what you said it sounds like changing the setting while connections are active will cause corruption, but calling the api and setting it to what it already is will give SQLITE_MISUSE. Is the shared cache known to be bug free? I need to default to it on or off. Roger
Re: [sqlite] how to fix problem of lock
Please people help me :D, the project will be open source... On 4/4/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: OK English is not my first language, i am from south America, from Paraguay so i speak Spanish... I am doing a project in C multiplataform.. right it project like google, for a Linux. the project uses SQ Lite for repository. The software is a server which listen connection UDP. The server could index (INSERT) and search within the database (SELECT). The problem is when i do so much connection at the same time for do index and search at the same time. Know is it clear? sorry for my english :D Thanxs On 4/3/06, John Stanton [EMAIL PROTECTED] wrote: Try to explain what you are doing. How do you access SQLITE? What language? What SQL are you using. How many concurrent users do you have? Are you getting a LOCK error because of multiple users? Cesar David Rodas Maldonado wrote: please i need some help On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: i insert numbers and select numbers, so what could be the solutions, couse i have to do that On 4/3/06, Pam Greene [EMAIL PROTECTED] wrote: An INSERT can change the results of your SELECT, so the database has to be locked during INSERT. Otherwise, the result of your SELECT would depend on whether the INSERT had finished yet. (The INSERT might even have only partly finished, which would mean the SELECT was looking at a database in an inconsistent state.) It's not good to have unpredictable results like that. For more explanation of why what you want isn't a good idea, see any discussion of an ACID database, for example http://en.wikipedia.org/wiki/ACID . - Pam On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: HElp me, couse i just need to do insert and select, i dont use delete or replate or update On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: thanx man but is posible open several DATABASE with a programm and do transaccion without locked the table??? On 4/3/06, Jay Sprenkle [EMAIL PROTECTED] wrote: On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: I have a database ok... i do a lot of insert and select, but there is sometime that i cant do the select couse the database is locked... i have to do a lot of insert every time, so how can i do for dont lock the database... understand guy? Try this: http://sqlite.org/lang_transaction.html
RE: [sqlite] how to fix problem of lock
I would suggest as a beginning: www.hwaci.com/sw/sqlite/whentouse.html www.hwaci.com/sw/sqlite/lockingv3.html www.hwaci.com/sw/sqlite/faq.html#q7 www.hwaci.com/sw/sqlite/faq.html#q8 www.sqlite.org/cvstrac/wiki?p=MultiThreading AND www.hwaci.com/sw/sqlite/ has wealth of very helpful information about all things SQLite. Once you have digested all that information, perhaps you will be in a better position to ask more detailed and to the point questions. General cries for help pretty much go unnoticed here. Most everyone here does not have the time and especially the patience required to teach an SQLite beginner how to write a Google replacement. Fred -Original Message- From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 1:23 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] how to fix problem of lock Please people help me :D, the project will be open source... On 4/4/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: OK English is not my first language, i am from south America, from Paraguay so i speak Spanish... I am doing a project in C multiplataform.. right it project like google, for a Linux. the project uses SQ Lite for repository. The software is a server which listen connection UDP. The server could index (INSERT) and search within the database (SELECT). The problem is when i do so much connection at the same time for do index and search at the same time. Know is it clear? sorry for my english :D Thanxs ...
Re: [sqlite] how to fix problem of lock
Thanx fred On 4/4/06, Fred Williams [EMAIL PROTECTED] wrote: I would suggest as a beginning: www.hwaci.com/sw/sqlite/whentouse.html www.hwaci.com/sw/sqlite/lockingv3.html www.hwaci.com/sw/sqlite/faq.html#q7 www.hwaci.com/sw/sqlite/faq.html#q8 www.sqlite.org/cvstrac/wiki?p=MultiThreading AND www.hwaci.com/sw/sqlite/ has wealth of very helpful information about all things SQLite. Once you have digested all that information, perhaps you will be in a better position to ask more detailed and to the point questions. General cries for help pretty much go unnoticed here. Most everyone here does not have the time and especially the patience required to teach an SQLite beginner how to write a Google replacement. Fred -Original Message- From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 1:23 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] how to fix problem of lock Please people help me :D, the project will be open source... On 4/4/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: OK English is not my first language, i am from south America, from Paraguay so i speak Spanish... I am doing a project in C multiplataform.. right it project like google, for a Linux. the project uses SQ Lite for repository. The software is a server which listen connection UDP. The server could index (INSERT) and search within the database (SELECT). The problem is when i do so much connection at the same time for do index and search at the same time. Know is it clear? sorry for my english :D Thanxs ...
RE: [sqlite] how to fix problem of lock
From http://www.hwaci.com/sw/sqlite/whentouse.html Situations Where Another RDBMS May Work Better * High Concurrency SQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database. For many situations, this is not a problem. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution. If you are really writing an app like Google, SQLite is quite possibly not the best solution because of its locking. -Original Message- From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 1:23 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] how to fix problem of lock Please people help me :D, the project will be open source... On 4/4/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: OK English is not my first language, i am from south America, from Paraguay so i speak Spanish... I am doing a project in C multiplataform.. right it project like google, for a Linux. the project uses SQ Lite for repository. The software is a server which listen connection UDP. The server could index (INSERT) and search within the database (SELECT). The problem is when i do so much connection at the same time for do index and search at the same time. Know is it clear? sorry for my english :D Thanxs On 4/3/06, John Stanton [EMAIL PROTECTED] wrote: Try to explain what you are doing. How do you access SQLITE? What language? What SQL are you using. How many concurrent users do you have? Are you getting a LOCK error because of multiple users? Cesar David Rodas Maldonado wrote: please i need some help On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: i insert numbers and select numbers, so what could be the solutions, couse i have to do that On 4/3/06, Pam Greene [EMAIL PROTECTED] wrote: An INSERT can change the results of your SELECT, so the database has to be locked during INSERT. Otherwise, the result of your SELECT would depend on whether the INSERT had finished yet. (The INSERT might even have only partly finished, which would mean the SELECT was looking at a database in an inconsistent state.) It's not good to have unpredictable results like that. For more explanation of why what you want isn't a good idea, see any discussion of an ACID database, for example http://en.wikipedia.org/wiki/ACID . - Pam On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: HElp me, couse i just need to do insert and select, i dont use delete or replate or update On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: thanx man but is posible open several DATABASE with a programm and do transaccion without locked the table??? On 4/3/06, Jay Sprenkle [EMAIL PROTECTED] wrote: On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: I have a database ok... i do a lot of insert and select, but there is sometime that i cant do the select couse the database is locked... i have to do a lot of insert every time, so how can i do for dont lock the database... understand guy? Try this: http://sqlite.org/lang_transaction.html To find out more about Reuters visit www.about.reuters.com Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Reuters Ltd.
Re: [sqlite] Programmatic notification of BEGIN TRANSACTION?
Mike Ashmore [EMAIL PROTECTED] wrote: Hi, I have some triggers that fire on INSERT, UPDATE, and DELETE to a view. Those triggers calls a user-defined library function. What I was wondering was, is there some way my library function can be notified of the beginning and end of a transaction? For instance, if the user issues an UPDATE on the view that affects four records, my UPDATE trigger fires four times. What I'd like to know is when that outer UPDATE transaction begins and ends, so that I can then group my user-defined function's actions in a way that continues to guarantee transactional atomicity. Is this possible short of mucking around in the VDBE? And if VDBE- mucking is required, does anybody want to offer any pointers? http://www.sqlite.org/capi3ref.html#sqlite3_commit_hook -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] sqlite3_enable_shared_cache
Roger Binns [EMAIL PROTECTED] wrote: If you call sqlite3_enable_shared_cache while another connection is open, you will get an SQLITE_MISUSE error. But you can ignore that error if you want. The doc is far scarier: This routine must not be called when any database connections are active in the current thread. Enabling or disabling shared cache while there are active database connections will result in memory corruption. From what you said it sounds like changing the setting while connections are active will cause corruption, but calling the api and setting it to what it already is will give SQLITE_MISUSE. Changing the setting while a session is active will lead to serious problems. That's why I added a check for that behavior that makes the routine return SQLITE_MISUSE instead. Is the shared cache known to be bug free? I am not aware of any bugs in the code. This doesn't mean there aren't any. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] how to fix problem of lock
The method I have found works well and efficiently is to surround your Sqlite accesses with a semaphore and have accessing processes WAIT to get access to Sqlite. For best performance you would only block when an INSERT was in progress and let multiple SELECTs run concurrently. This assumes that all your users go through the same interface so that they can co-operate. The alternative is to put busy logic into your Sqlite code, but that is tedious and cannot be as efficient as synchronising using the more efficient kernel based method of a semaphore. Since it is a single file and linked into the application Sqlite is essentially single user, requiring that only one process at a time modify the database. It has to use a file lock to enforce that constraint. If you work with it in your design you can transparently handle large numbers of users. As you will already have appreciated, a multi-user Sqlite system must have short transactions to effectively share access. You might also look to making users who only read the database be read-only. In an extreme case you could look to having database INSERTs be handled by a daemon which would single stream access. Cesar David Rodas Maldonado wrote: OK English is not my first language, i am from south America, from Paraguay so i speak Spanish... I am doing a project in C multiplataform.. right it project like google, for a Linux. the project uses SQ Lite for repository. The software is a server which listen connection UDP. The server could index (INSERT) and search within the database (SELECT). The problem is when i do so much connection at the same time for do index and search at the same time. Know is it clear? sorry for my english :D Thanxs On 4/3/06, John Stanton [EMAIL PROTECTED] wrote: Try to explain what you are doing. How do you access SQLITE? What language? What SQL are you using. How many concurrent users do you have? Are you getting a LOCK error because of multiple users? Cesar David Rodas Maldonado wrote: please i need some help On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: i insert numbers and select numbers, so what could be the solutions, couse i have to do that On 4/3/06, Pam Greene [EMAIL PROTECTED] wrote: An INSERT can change the results of your SELECT, so the database has to be locked during INSERT. Otherwise, the result of your SELECT would depend on whether the INSERT had finished yet. (The INSERT might even have only partly finished, which would mean the SELECT was looking at a database in an inconsistent state.) It's not good to have unpredictable results like that. For more explanation of why what you want isn't a good idea, see any discussion of an ACID database, for example http://en.wikipedia.org/wiki/ACID . - Pam On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: HElp me, couse i just need to do insert and select, i dont use delete or replate or update On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: thanx man but is posible open several DATABASE with a programm and do transaccion without locked the table??? On 4/3/06, Jay Sprenkle [EMAIL PROTECTED] wrote: On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: I have a database ok... i do a lot of insert and select, but there is sometime that i cant do the select couse the database is locked... i have to do a lot of insert every time, so how can i do for dont lock the database... understand guy? Try this: http://sqlite.org/lang_transaction.html
[sqlite] Question on how to enter blobs in sqlite3
Hello, I am using sqlite3 on am embedded platform. I am using c++ to enter data. I have BLOB field, and I do not know how to enter hex values from a data string. I am trying to use update, but how do I enter col_name=' data ' format? How does ' escaping work? I could not find help on the sqlite site, and I am trying this list. Thanks in advance, RK
Re: [sqlite] DBD Sqlite
At 15:03 -0700 4/4/06, Sripathi Raj wrote: Hi, I have a few questions regarding SQLite. I'm using it on Windows and connecting to it from Perl. And I will answer some of them. 1. How do I find out if the current version of DBD::SQLite uses SQLite 3.0or greater? DBD::SQLite versions = 1.0 embed SQLite versions = 3.x. DBD::SQLite versions 1.0 and all DBD::SQLite2 embed SQLite versions = 2.x. 2. How do I allow dirty reads? I understand that the whole file is locked for writing but I believe I can do reads using a deferred transaction. How do I do this from the Perl DBI? I'm not sure that what you want is possible. But why would you want to? 3. The performance for inserts is really bad. Around 40k entries takes a few hours. What might I be doing wrong? I do a commit after all the inserts. A few things to help with speed: 1. Use DBI's prepared statements; eg, 1 prepare() and many execute(). 2. Don't commit for each row inserted but batch them so, say, you commit once per 1000 rows. Thanks, Raj -- Darren Duncan
Re: [sqlite] DBD Sqlite
Darren Duncan wrote: At 15:03 -0700 4/4/06, Sripathi Raj wrote: Hi, I have a few questions regarding SQLite. I'm using it on Windows and connecting to it from Perl. And I will answer some of them. 1. How do I find out if the current version of DBD::SQLite uses SQLite 3.0or greater? DBD::SQLite versions = 1.0 embed SQLite versions = 3.x. DBD::SQLite versions 1.0 and all DBD::SQLite2 embed SQLite versions = 2.x. 2. How do I allow dirty reads? I understand that the whole file is locked for writing but I believe I can do reads using a deferred transaction. How do I do this from the Perl DBI? I'm not sure that what you want is possible. But why would you want to? The database is being shared between two processes. The other process just reads the database and never writes to it. Even if it doesn't get the updated information it's ok to give whatever information it has currently. Also it's not able to read even the table information. 3. The performance for inserts is really bad. Around 40k entries takes a few hours. What might I be doing wrong? I do a commit after all the inserts. A few things to help with speed: 1. Use DBI's prepared statements; eg, 1 prepare() and many execute(). Yes, this is what I do. 2. Don't commit for each row inserted but batch them so, say, you commit once per 1000 rows. Unfortunately, I cannot commit till I do all the inserts. Thanks, Raj -- Darren Duncan On 4/4/06, Darren Duncan [EMAIL PROTECTED] wrote: At 15:03 -0700 4/4/06, Sripathi Raj wrote: Hi, I have a few questions regarding SQLite. I'm using it on Windows and connecting to it from Perl. And I will answer some of them. 1. How do I find out if the current version of DBD::SQLite uses SQLite 3.0or greater? DBD::SQLite versions = 1.0 embed SQLite versions = 3.x. DBD::SQLite versions 1.0 and all DBD::SQLite2 embed SQLite versions = 2.x. 2. How do I allow dirty reads? I understand that the whole file is locked for writing but I believe I can do reads using a deferred transaction. How do I do this from the Perl DBI? I'm not sure that what you want is possible. But why would you want to? 3. The performance for inserts is really bad. Around 40k entries takes a few hours. What might I be doing wrong? I do a commit after all the inserts. A few things to help with speed: 1. Use DBI's prepared statements; eg, 1 prepare() and many execute(). 2. Don't commit for each row inserted but batch them so, say, you commit once per 1000 rows. Thanks, Raj -- Darren Duncan
Re: [sqlite] DBD Sqlite
3. The performance for inserts is really bad. Around 40k entries takes a few hours. What might I be doing wrong? I do a commit after all the inserts. A few things to help with speed: 1. Use DBI's prepared statements; eg, 1 prepare() and many execute(). Yes, this is what I do. 2. Don't commit for each row inserted but batch them so, say, you commit once per 1000 rows. Unfortunately, I cannot commit till I do all the inserts. That doesn't seem right for speed. In addition to using commit, are you beginning a transaction with begin? Are your inserts particularly complex or large? More details about what you are doing would probably be good here, since something odd is happening here. Maybe you could post a tiny test program along with the time it takes? --nate
Re: [sqlite] DBD Sqlite
On 4/4/06, Nathan Kurz [EMAIL PROTECTED] wrote: 3. The performance for inserts is really bad. Around 40k entries takes a few hours. What might I be doing wrong? I do a commit after all the inserts. A few things to help with speed: 1. Use DBI's prepared statements; eg, 1 prepare() and many execute(). Yes, this is what I do. 2. Don't commit for each row inserted but batch them so, say, you commit once per 1000 rows. Unfortunately, I cannot commit till I do all the inserts. That doesn't seem right for speed. In addition to using commit, are you beginning a transaction with begin? Are your inserts particularly complex or large? More details about what you are doing would probably be good here, since something odd is happening here. Maybe you could post a tiny test program along with the time it takes? --nate I don't begin the transaction with begin. My assumption was that the first insert operation would automatically begin a transaction. My inserts are fairly simple with two columsn being long strings of length 255. my @values = ($task_info_gid,$file_type_gid,$extracted_path,$media_path, $size,$ctime,$mtime,$job_id,$is_in_du); Raj
RE: [sqlite] how to fix problem of lock
How long does one INSERT take? Do you have long transactions with INSERTs? If you have one INSERT at a time and it doesn't take too long, and still you have reader starvation issues with the SELECTs, the only solution that I see is to queue requests and make sure that they are served on a first-come-first-served basis. -Original Message- From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 1:23 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] how to fix problem of lock Please people help me :D, the project will be open source... On 4/4/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: OK English is not my first language, i am from south America, from Paraguay so i speak Spanish... I am doing a project in C multiplataform.. right it project like google, for a Linux. the project uses SQ Lite for repository. The software is a server which listen connection UDP. The server could index (INSERT) and search within the database (SELECT). The problem is when i do so much connection at the same time for do index and search at the same time. Know is it clear? sorry for my english :D Thanxs On 4/3/06, John Stanton [EMAIL PROTECTED] wrote: Try to explain what you are doing. How do you access SQLITE? What language? What SQL are you using. How many concurrent users do you have? Are you getting a LOCK error because of multiple users? Cesar David Rodas Maldonado wrote: please i need some help On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: i insert numbers and select numbers, so what could be the solutions, couse i have to do that On 4/3/06, Pam Greene [EMAIL PROTECTED] wrote: An INSERT can change the results of your SELECT, so the database has to be locked during INSERT. Otherwise, the result of your SELECT would depend on whether the INSERT had finished yet. (The INSERT might even have only partly finished, which would mean the SELECT was looking at a database in an inconsistent state.) It's not good to have unpredictable results like that. For more explanation of why what you want isn't a good idea, see any discussion of an ACID database, for example http://en.wikipedia.org/wiki/ACID . - Pam On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: HElp me, couse i just need to do insert and select, i dont use delete or replate or update On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: thanx man but is posible open several DATABASE with a programm and do transaccion without locked the table??? On 4/3/06, Jay Sprenkle [EMAIL PROTECTED] wrote: On 4/3/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: I have a database ok... i do a lot of insert and select, but there is sometime that i cant do the select couse the database is locked... i have to do a lot of insert every time, so how can i do for dont lock the database... understand guy? Try this: http://sqlite.org/lang_transaction.html
Re: [sqlite] DBD Sqlite
On Tue, Apr 04, 2006 at 04:18:35PM -0700, Sripathi Raj wrote: On 4/4/06, Nathan Kurz [EMAIL PROTECTED] wrote: 3. The performance for inserts is really bad. Around 40k entries takes a few hours. What might I be doing wrong? I do a commit after all the inserts. That doesn't seem right for speed. In addition to using commit, are you beginning a transaction with begin? Are your inserts particularly complex or large? More details about what you are doing would probably be good here, since something odd is happening here. Maybe you could post a tiny test program along with the time it takes? I don't begin the transaction with begin. My assumption was that the first insert operation would automatically begin a transaction. True, it does an implicit BEGIN, but it also does an implicit COMMIT when each command finishes. Try adding an explicit BEGIN; before you do the inserts: http://www.sqlite.org/lang_transaction.html --nate
Re: [sqlite] DBD Sqlite
On 4/4/06, Nathan Kurz [EMAIL PROTECTED] wrote: On Tue, Apr 04, 2006 at 04:18:35PM -0700, Sripathi Raj wrote: On 4/4/06, Nathan Kurz [EMAIL PROTECTED] wrote: 3. The performance for inserts is really bad. Around 40k entries takes a few hours. What might I be doing wrong? I do a commit after all the inserts. That doesn't seem right for speed. In addition to using commit, are you beginning a transaction with begin? Are your inserts particularly complex or large? More details about what you are doing would probably be good here, since something odd is happening here. Maybe you could post a tiny test program along with the time it takes? I don't begin the transaction with begin. My assumption was that the first insert operation would automatically begin a transaction. True, it does an implicit BEGIN, but it also does an implicit COMMIT when each command finishes. Try adding an explicit BEGIN; before you do the inserts: http://www.sqlite.org/lang_transaction.html --nate But if you set Autocommit to 0, doesn't it wait till commit is called explicitly?
Re: [sqlite] DBD Sqlite
Sripathi Raj wrote: I don't begin the transaction with begin. My assumption was that the first insert operation would automatically begin a transaction. It does. But, the transaction it starts ends with that insert, and a new transaction begins with the next insert. You need an explicit begin
[sqlite] Windows problem when updating
After a machine restart the first time I try to change a field in a record, on Windows, it takes an inordinately long time for the sqlite code to return, the amount of time is dependent on the size of the database but even for a 20 Meg one with about 4000 records, the time is in the order of a minute. Once the change is made I can quite the program and relaunch it, open the database, and make another change exactly like the first to another record and the sqlite code returns almost immediately. On the Mac OS X, I don't see this delay. I see this with SQLite 2.8.16 as well as 3.3.4. Here's the schema for the database: CREATE TABLE MyTable (RID Integer Primary Key, Filename Text, PathType Integer, Path Text, Category Text null, Favorite Boolean null, Missing Boolean null, Thumbnail Blob null); CREATE UNIQUE INDEX MyTable_RID on MyTable (RID); CREATE INDEX MyTable_Filename on MyTable (Filename); a record is added with INSERT into MyTable (RID, Filename, PathType, Path, Category, Thumbnail) (1, 'afile.jpg', 1, 'path', 'data'); where path = dos path (windows path - whatever you want to call it - it starts with c:\) data = hex encoded data of a raw bit map - (this originally used 2.8.16 and I could see any other way to get raw binary data into the database) the update is simply this: UPDATE MyTable set Favorite = 1 where RID = 1; In fact I find that I get a delay anytime I change the database following a reboot of the machine, i.e. deleting a record, inserting a record, updating a record. The routine that seems to get take the time is sqlite3VdbeExec. Anybody have any idea what might be going on here or how to fix it? Why does it only happen after a reboot of the machine? Similarly, and this I see on both the Mac and Windows if on launch I do the following: select rid from MyTable order by lower(filename); on a database with approximatley 4000 records it will take about 14 seconds, after a reboot, to complete. Quitting the program and relaunching on the Mac the select completes in 0.04 seconds. Milt
Re: [sqlite] DBD Sqlite
You can get the underlying SQLite version from DBD-SQLite as
$dbh-{sqlite_version};
Make sure you set $dbh-{AutoCommit=0}. This will ensure you're always in a
transaction. Without it, you're probably committing every row. From what I
can tell, you can twiddle AutoCommit at any point in the program to turn it on
(1), or off (0).
Using $dbh-{AutoCommit=0}, and a DBI prepared statement, I see inserts read
from a file running somewhere around 5K/second on a 733MHz P3 (Linux). I have
a daily process that imports ~9,900 records in 2.077 seconds
I think the docs for DBD-SQLite indicate a writer always locks the whole file,
so you probably can't do dirty reads with DBD-SQLite. Once you start writing,
the reader won't have access to the DB until the writer commits or rolls-back.
-Clark
- Original Message
From: Sripathi Raj [EMAIL PROTECTED]
To: sqlite-users@sqlite.org
Sent: Tuesday, April 4, 2006 4:18:35 PM
Subject: Re: [sqlite] DBD Sqlite
On 4/4/06, Nathan Kurz [EMAIL PROTECTED] wrote:
3. The performance for inserts is really bad. Around 40k entries
takes a
few hours. What might I be doing wrong? I do a commit after
all the inserts.
A few things to help with speed:
1. Use DBI's prepared statements; eg, 1 prepare() and many execute().
Yes, this is what I do.
2. Don't commit for each row inserted but batch them so, say, you
commit once per 1000 rows.
Unfortunately, I cannot commit till I do all the inserts.
That doesn't seem right for speed. In addition to using commit, are
you beginning a transaction with begin? Are your inserts
particularly complex or large? More details about what you are doing
would probably be good here, since something odd is happening here.
Maybe you could post a tiny test program along with the time it takes?
--nate
I don't begin the transaction with begin. My assumption was that the first
insert operation would automatically begin a transaction.
My inserts are fairly simple with two columsn being long strings of length
255.
my @values = ($task_info_gid,$file_type_gid,$extracted_path,$media_path,
$size,$ctime,$mtime,$job_id,$is_in_du);
Raj
Re: [sqlite] Question on how to enter blobs in sqlite3
On Tue, 4 Apr 2006, Roman wrote: [...] I am using sqlite3 on am embedded platform. I am using c++ to enter data. I have BLOB field, and I do not know how to enter hex values from a data string. [...] Hello, if you need to put BLOB data verbatim into an SQL statement, SQLite3 supports syntax like INSERT INTO blobs VALUES(X'deadbeef'); You just denote the BLOB as a string of an even number of hexadecimal characters preceded by an 'X' or 'x'. In many cases it may be a better idea, though, to put placeholders into your SQL statement and to use sqlite3_bind_blob to load the BLOB data for statement execution. cu, Thomas
Re: [sqlite] Question on how to enter blobs in sqlite3
Hi Thomas, I have tried sqlite select * from loc_configuration; 0|0|18|52|86|4|24|22|51|116|39|15|0|0|0|0|0|0|0|0|0|0|0|0 The blob is the one before the last one, and the length is the very last one The serials are second, third , fourth and fifth columns. sqlite UPDATE loc_configuration SET loc_authorization_code_length = 18, loc_authorization_code = X'000102030405060708' WHERE loc_serial_0=0 AND loc_serial_1=18 AND loc_serial_2=52 AND loc_serial_3=86; sqlite select * from loc_configuration; 0|0|18|52|86|4|24|22|51|116|39|15|0|0|0|0|0|0|0|0|0|0||0 I get no response or error from sqlite3. Roman On Tuesday 04 April 2006 05:21 pm, Thomas Chust wrote: On Tue, 4 Apr 2006, Roman wrote: [...] I am using sqlite3 on am embedded platform. I am using c++ to enter data. I have BLOB field, and I do not know how to enter hex values from a data string. [...] Hello, if you need to put BLOB data verbatim into an SQL statement, SQLite3 supports syntax like INSERT INTO blobs VALUES(X'deadbeef'); You just denote the BLOB as a string of an even number of hexadecimal characters preceded by an 'X' or 'x'. In many cases it may be a better idea, though, to put placeholders into your SQL statement and to use sqlite3_bind_blob to load the BLOB data for statement execution. cu, Thomas
Re: RE: [sqlite] How to unsubscribe from this maillist ?
Bill Giannotti！ As i have lost my programming job for old age (38 years old), so i wanted to unsubscribe many maillists. However i feel sqlite can still help me. so i determine not to unsubscribe now ! regards! 2006-04-03 07:25:00 您在来信中写道： I had an issue with my computer where I started getting files filling my temporary directory without reason that began with sqlite_ and wanted to ask the forum if anyone new why this was. In order to do so I had to join the forum, knowing no other avenue to find out. I got my answer - turns out that AOL and McAfee use the sqlite database and are working on a bug in their delivery. I don't use sqlite, never did and don't know much about programming. All the other messages in the forum don't make any sense to me and so I figured I'd unsubscribe. thank you -Original Message- From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED] Sent: Sunday, April 02, 2006 10:06 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to unsubscribe from this maillist ? why do you want to unsuscribe? On 4/1/06, z-axis [EMAIL PROTECTED] wrote: thanks = = = = = = = = = = = = = = = = = = = = = = 致 礼！ z-axis [EMAIL PROTECTED] 2006-04-05
Re: [sqlite] Probably, bug in SQLite (Or, what is wrong with this query?)
Re-run on Oracle 8.1.7 with the corrected SQL: select a, (select c from (select b + 1 as c from dual)) as d from (select 1 as a, 2 as b from dual) ORA-00904: invalid column name It did not like the 'select b' either. --- Alexander Kozlovsky [EMAIL PROTECTED] wrote: Cory Nelson wrote: Can anybody tell me, what is wrong with this simple query: select a, (select c from (select b + 1 as c) as Table2) as d from (select 1 as a, 2 as b) as Table1 I don't claim to be a master of SQL, but isn't select b + 1 as c its own query, without access to the 2 as b in the outer query? But this query is work well: select a, (select b + 1 as c) as d from (select 1 as a, 2 as b) as Table1 The problem is only with subqueries nested in another subquery. -- Best regards, Alexander mailto:[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Performance Difference on Linux Kernel 2.4 and Kernel 2.6
[EMAIL PROTECTED] wrote: Your script requires 1002 separate ACID transactions. Each such transaction should require at least 2 complete revolutions of your disk drive platter - meaning that the minimum time to complete your script should be 16 seconds. The 2.4 kernel finished faster than this, which tells me that the 2.4 kernel probably does not implement the fsync() system call correctly. I have heard reports of this but could not verify it. What this means is that if you loose power unexpectedly, the 2.4 kernel might corrupt your database. Probably since the ReiserFS does a good job of journalling itself, you won't corrupt the database, but your transactions will certainly not be Durable. The 2.6 kernel is much slower because reiserFS is inefficient in its implementation of fsync(). Thanks for the thorough explanation. I understand it better now. You script does not measure how fast SQLite processes inserts. It measure how fast it does transacxtions. If you put a BEGIN and a COMMIT at the beginning and end of your script, I think you will find that the whole thing will run in a fraction of a second under either kernel. Yeah, I have tried that, but my benchmarking is on individual transactions instead of everything in one transaction. Thanks for the great help! -- D. Richard Hipp [EMAIL PROTECTED] Cheers, Phuah Yee Keat
[sqlite] Strange Behaviour on Solaris 8 on Sparc
Hi, I am currently running some tests to decide whether to use sqlite, and bump into some strange behavior. I compiled sqlite 3.3.4 from source and installed it on a solaris 8 on sparc without any updates. I run the same scripts (which insert 1000 entries without BEGIN/END block), on the same machine, but in different directories, getting totally different results: ### # In the db1 directory: ### [EMAIL PROTECTED]:~/db1$ time cat /export/home/ykphuah/test.sql | sqlite3 test.db real3m53.708s user0m0.710s sys 0m2.140s [EMAIL PROTECTED]:~/db1$ time cat /export/home/ykphuah/test.sql | sqlite3 test.db real3m48.267s user0m0.590s sys 0m2.120s [EMAIL PROTECTED]:~/db1$ time cat /export/home/ykphuah/test.sql | sqlite3 test.db real3m48.435s user0m0.630s sys 0m2.100s [EMAIL PROTECTED]:~/db1$ ### # In the db2 directory: ### [EMAIL PROTECTED]:~/db2$ time cat /export/home/ykphuah/test.sql | sqlite3 test.db real0m12.523s user0m0.650s sys 0m1.960s [EMAIL PROTECTED]:~/db2$ time cat /export/home/ykphuah/test.sql | sqlite3 test.db real0m6.296s user0m0.490s sys 0m1.720s [EMAIL PROTECTED]:~/db2$ time cat /export/home/ykphuah/test.sql | sqlite3 test.db real0m6.264s user0m0.470s sys 0m1.750s [EMAIL PROTECTED]:~/db2$ I am really puzzled as to why there's so much difference in the time to execute the same script on the same box just in different directories, thinking that it might be placement of the directories in the physical disc itself causing the fsync calls to differ so much? Is there any way where I can provide more information to help you guys help me? Thanks in advance. Cheers, Phuah Yee Keat
