Re: [sqlite] Multiple Updates
Hi Trevor, Thanks, that makes a big difference to the way I thought it worked. Regards, Chris Trevor Talbot wrote: On 9/29/06, Chris Gurtler <[EMAIL PROTECTED]> wrote: A transaction has the select statements in one single string starting with a begin and ending with an end. I have a column which is a BLOB, and this may contain control characters like NULL. Think of BEGIN and COMMIT more like state-changing commands. Once you execute a BEGIN, all future work on that connection will be inside of a transaction, until you COMMIT. They don't need to be within a single string, and your loops and sqlite3_bind_* can be used exactly the same way they are now (with the corrections noted by Igor of course). Just prepare/step a BEGIN before the loop, and a COMMIT afterward. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple Updates
Hi Trevor, A transaction has the select statements in one single string starting with a begin and ending with an end. I have a column which is a BLOB, and this may contain control characters like NULL. Regards, Chris Trevor Talbot wrote: On 9/28/06, Chris Gurtler <[EMAIL PROTECTED]> wrote: I wish I could use transactions, but because I'm updating a blob I need to do it that way. Ill check out the PRAGMA option to see if that helps. Why does what you showed above mean you can't use transactions? Also, that PRAGMA will mean a corrupted database if there is a crash or power failure while you're performing operations on it, so make sure that's acceptable. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple Updates
Thanks,
I wish I could use transactions, but because I'm updating a blob I need
to do it that way. Ill check out the PRAGMA option to see if that helps.
Regards,
Chris
He Shiming wrote:
Hi All,
Is it possible to do multiple updates of blobs using the bind
variables, I was doing them 1 at a time but it was a little slow.
For example :-
rc = sqlite3_prepare(objects_db, "UPDATE table SET proprietary_data =
? WHERE device_id = ? and instance = ?", -1, , 0);
for (i= 0; i <10;i++)
{
sqlite3_bind_blob(pStmt, 1, proprietary_data,
proprietary_data_len, SQLITE_STATIC);
sqlite3_bind_int(pStmt, 2, object->device_id);
sqlite3_bind_int(pStmt, 3, object->objectIdentifier.instance);
rc = sqlite3_step(pStmt);
}
if (sqlite3_finalize(pStmt))
Regards,
Chris
There is a pretty simple answer to all these kinds of questions. Use
transactions. You can see the details about transactions and
performance here:
http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations . In my
experience, performance do improve a lot when transaction is used for
such an update or insert iteration.
If you don't know it yet, another idea is to use PRAGMA synchronous =
OFF; . This way, sqlite will rely on operating system disk cache,
which is an even greater improvement in performance.
Best regards,
He Shiming
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Most appropriate Web based database? (Newbie)
Hi Lenster, For a Web application you are probably better off with MySQL, it runs as a seperate process and handles multiple threads really well. I'm probably going to offend the sqlite people out there, but sqlite is fantastic for an embedded database, and a web app is not what I would call embedded. Regards, Chris Lenster wrote: I am investigating which would be the most appropriate RDMS to use for a new Intranet based application. I have rounded down my choice to two candidates - SQLite and MySQL. The application needs to be available to about twenty users on a daily basis, with most of those users making no more than five 'write' transactions a day, and around twenty 'read' transactions a day. Depending upon its success the application may well develop to incorporate other datasets and users - so the RDMS needs to be able to develop without difficulty. IMHO SQLite looks as though it would be the fastest to develop and with the least resources required but after reading some SQLite documentation: "A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem." I am concerned that even though I expect light usage it is likely that multiple users will access the same database concurrently. Would I be correct in assuming that MySQL is a safer (longterm) bet? Thanks for any and all responses Len -- View this message in context: http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3780531 Sent from the SQLite forum at Nabble.com.
[sqlite] Using the Precompiled Binaries on Linux
Hi All, This is hopefully a really basic question for someone. I have downloaded the precompiled binary for Linux and I've got a file called sqlite-3.2.8.so.gz So far so good, but I'm not sure what steps to use to install this properly. Should I extract it and copy it into \usr\lib and then use ln to set up the linker name? Most of the files here start with lib, and I was expecting to get a file called libsqlite3.so.x.x Am I missing something? Hopefully someone can point me in the right direction. Thanks, Chris
[sqlite] SQLite is Awesome
Hi, Ok, so Mr. Hipp's spelling may need a little work, but SQLite has got to be the best bit of software that I have come accross for quite a while. Thank you !! Regards, Chris
Re: [sqlite] SQL Queries
Thanks Eric, That's exactly what the issue was, and it all works like a treat now. After about 4 hours I have managed to remove my Access Database and now have a far better solution, I'm impressed! Regards, Chris - Original Message - From: "Eric Bohlman" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Tuesday, September 27, 2005 4:37 AM Subject: Re: [sqlite] SQL Queries Chris Gurtler wrote: I'm pretty new to SQLite, and am just looking for a few pointers on SQL queries, this is an example of a query from a crapy MS Access database that I want to convert to SQLite, but it fails. it says c.group_id does not exist. I'm wondering if anyone has got some tips on multiple joins, I suspect SQLite doesn't like these joins very much and I will need to rewrite some of the queries. It's not a problem with joins _per se_, it involves the propagation of column names from subqueries. It looks like this is the same problem reported in ticket (http://www.sqlite.org/cvstrac/tktview?tn=,33) and the workaround suggested there might help.
[sqlite] SQL Queries
Hi, I'm pretty new to SQLite, and am just looking for a few pointers on SQL queries, this is an example of a query from a crapy MS Access database that I want to convert to SQLite, but it fails. it says c.group_id does not exist. I'm wondering if anyone has got some tips on multiple joins, I suspect SQLite doesn't like these joins very much and I will need to rewrite some of the queries. Regards, Chris. Query is :- Select * from ( select b.group_id from users a INNER JOIN ( select group_id from group_users Where user_id = 'pass' ) as b on (a.user_id = b.user_id) Where a.password = 'pass' ) c inner join groups as d on (c.group_id = d.group_id)
