RE: [sqlite] Failing Transaction Help.
I think that I just found my problem. For some stupid reason, I coded it so that if a statement failed, then it stopped executing the rest of the statements. Thus leaving the transaction open and causing my problems. I have since fixed it. -Original Message- From: Rob Lohman [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 01, 2006 5:09 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Failing Transaction Help. If I'm not mistaken you still need to close the transaction. Are you doing an "end transaction" even if a statement fails (ie, a rollback is done)? - Original Message - From: "nbiggs" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Wednesday, March 01, 2006 10:24 PM Subject: [sqlite] Failing Transaction Help. > In my application, I am using a transaction to insert about 10 records > at a time. The problem is that if one of the statements in the > transaction fail, commit is not being executed. When I try creating > another transaction, I get a constant error message "can not create a > transaction within a transaction". How do I get around this issue? > > Nathan Biggs > Computerway Food Systems > (336) 841-7289 > >
[sqlite] Failing Transaction Help.
In my application, I am using a transaction to insert about 10 records at a time. The problem is that if one of the statements in the transaction fail, commit is not being executed. When I try creating another transaction, I get a constant error message "can not create a transaction within a transaction". How do I get around this issue? Nathan Biggs Computerway Food Systems (336) 841-7289
RE: [sqlite] How to read column names
select name from sqlite_master where type='table' That will get you the names of tables in the database. -Original Message- From: Eugen Stoianovici [mailto:[EMAIL PROTECTED] Sent: Thursday, February 09, 2006 3:57 PM To: sqlite-users@sqlite.org Subject: [sqlite] How to read column names Is there a way of reading the names of the columns in a table? how about the table names in a data base?
RE: [sqlite] SQLite to MySQL
Are you using SQLite that is built into PHP5? That SQLite version is 2.8.17, or at least it is on my pc. Anyway, you might want to consider switching to a newer version of SQLite and using PHP's PDO functions which will allow you to connect to a SQLite3 database. In my application, I experienced a dramatic increase in speed by switching to version 3.2.8. -Original Message- From: Laurent Goussard [mailto:[EMAIL PROTECTED] Sent: Monday, February 06, 2006 12:05 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite to MySQL I don't know, I suppose my queries are not as optimized as I thought (even if this optimization was my leitmotiv for all the development part), or perhaps it's an apache2+php5 issue on my windows computer... But the fact is since the database has grown (like my traffic : 6000 visitors/day and 22Mb db file), I've got more and more "maximum execution time" errors at the peak hours. I've monitored them, and it seems a lot of simultaneous queries are freezing the server and finally generates this error. The interresting point is the same queries sent a testing mysql db while the sqlite part is not responding anymore are working very well, So that's the reason why I consider to switch on a mysql solution for this website. Do you got clues concerning conversion ? 2006/2/6, Jay Sprenkle <[EMAIL PROTECTED]>: > > Hi there, > > > > I use SQLite on my website for 2 years now. I do like SQLite a lot and > > will use it for a lot of new web projects but, because I got more and > > more traffic, I consider to move this one to MySQL in order to reduce > > the over load of my computer (I host it @ home). > > How is this going to reduce load? > > sqlite = mysql - server code > > You're adding server code. More code = More load. >
[sqlite] Help with IF NOT EXiSTS
I get the following error if I try to use the IF NOT EXISTS function. If I take out the "IF NOT EXISTS", the statement creates the index. The same happens when I try using IF NOT EXISTS in a CREATE table statement. CREATE UNIQUE INDEX IF NOT EXISTS idx_table ON table (field1, field2); SQL error: near "NOT": syntax error Any ideas? Thanks for your help. Nathan Biggs
RE: Re[2]: [sqlite] Save my harddrive!
Thanks for everyone's input, that's what I wanted to hear. -Original Message- From: Teg [mailto:[EMAIL PROTECTED] Sent: Friday, January 27, 2006 2:06 PM To: nbiggs Subject: Re[2]: [sqlite] Save my harddrive! Hello nbiggs, My users typically download between 3 to 40 gigs of data a day to commodity IDE hard drives. This means downloading files in pieces and when there are enough pieces to create the file, assemble the files on the hard disk at maximum speed. The files range from 60K to 50 Megs each. During download they sustain fairly constant writes to disk of between 1.5-10 Mbps. Some run 24x7 (and some have been tossed out by their ISP's). I've asked them whether they've been seeing increased failure rates on their hard drives, I use SCSI only so, they're designed for this kind of usage. The results were inconclusive. Some have lost hard drives but, for the most part their hard disks just crunch away for years at a time. I think it unlikely that your usage is more than a blip of data to the hard drive. C Friday, January 27, 2006, 12:26:15 PM, you wrote: n> This is what I am inserting per record. n> Insert into table values(1, 1, 172, 97, 1, 4, 1, 2.29, 'A', n> '2006012410052941', 12345, 0, 0, 0, 1, 1, 0) n> Other then that, I do some updates on the last field by setting the n> value to 1 or 2. n> -Original Message- n> From: Robert Simpson [mailto:[EMAIL PROTECTED] n> Sent: Friday, January 27, 2006 12:06 PM n> To: sqlite-users@sqlite.org n> Subject: Re: [sqlite] Save my harddrive! n> - Original Message - n> From: "nbiggs" <[EMAIL PROTECTED]> >> >> My application generates about 12 records a second. I have no n> problems >> storing the records into the database, but started thinking that if I >> commit every 12 records, will my hard drive eventually die to extreme >> usage? During a 24 hour period up to 1 million records will be >> generated and inserted. At the end of the day, all the records will n> be >> deleted and the inserts will start again for another 24 hours. >> >> Can I store the records into memory, or just not commit as often, n> maybe >> once every 5 minutes while still protecting my data in case of a PC >> crash or unexpected shutdown due to user ignorance? >> >> Does anyone have any ideas for this type of situation? n> How large are these rows? 12 inserts a second is chump change if n> they're n> small ... If you're inserting 100k blobs then you may want to rethink n> things. n> At 12 rows per second (given a relatively small row), 24hrs of usage n> will n> still be less than the amount of harddrive churning involved in a single n> reboot of your machine. Consider that a fast app can insert about 1 n> million n> rows into a SQLite table in about 15 seconds. n> Robert -- Best regards, Tegmailto:[EMAIL PROTECTED]
RE: [sqlite] Save my harddrive!
This is what I am inserting per record. Insert into table values(1, 1, 172, 97, 1, 4, 1, 2.29, 'A', '2006012410052941', 12345, 0, 0, 0, 1, 1, 0) Other then that, I do some updates on the last field by setting the value to 1 or 2. -Original Message- From: Robert Simpson [mailto:[EMAIL PROTECTED] Sent: Friday, January 27, 2006 12:06 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Save my harddrive! - Original Message - From: "nbiggs" <[EMAIL PROTECTED]> > > My application generates about 12 records a second. I have no problems > storing the records into the database, but started thinking that if I > commit every 12 records, will my hard drive eventually die to extreme > usage? During a 24 hour period up to 1 million records will be > generated and inserted. At the end of the day, all the records will be > deleted and the inserts will start again for another 24 hours. > > Can I store the records into memory, or just not commit as often, maybe > once every 5 minutes while still protecting my data in case of a PC > crash or unexpected shutdown due to user ignorance? > > Does anyone have any ideas for this type of situation? How large are these rows? 12 inserts a second is chump change if they're small ... If you're inserting 100k blobs then you may want to rethink things. At 12 rows per second (given a relatively small row), 24hrs of usage will still be less than the amount of harddrive churning involved in a single reboot of your machine. Consider that a fast app can insert about 1 million rows into a SQLite table in about 15 seconds. Robert
[sqlite] Save my harddrive!
My application generates about 12 records a second. I have no problems storing the records into the database, but started thinking that if I commit every 12 records, will my hard drive eventually die to extreme usage? During a 24 hour period up to 1 million records will be generated and inserted. At the end of the day, all the records will be deleted and the inserts will start again for another 24 hours. Can I store the records into memory, or just not commit as often, maybe once every 5 minutes while still protecting my data in case of a PC crash or unexpected shutdown due to user ignorance? Does anyone have any ideas for this type of situation? Nathan Biggs
RE: [sqlite] Re: Difference between finalize and reset.
What would be a good example of reusing the statement? Why not just execute the SQL again? -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Thursday, January 26, 2006 12:21 PM To: SQLite Subject: [sqlite] Re: Difference between finalize and reset. nbiggs <[EMAIL PROTECTED]> wrote: > Can somebody please explain the difference between the > sqlite3_finalize and sqlite3_reset functions. sqlite3_finalize destroys the statement handle and all internal structures associated with it. The handle is unusable after that. sqlite3_reset clears the information related to the query now in progress, but does not destroy the statement. You can now run another query using the same statement, perhaps after binding different parameters. You must call sqlite3_reset between two queries that use the same statement. > Do I just call finalize after calling prepare and step, or do I need > to call reset also? No need to call reset before finalize, if you are done with the statement. You only need to call sqlite3_reset if you want to reuse the statement for further queries. Igor Tandetnik
RE: [sqlite] Re: Difference between finalize and reset.
Thanks -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Thursday, January 26, 2006 12:21 PM To: SQLite Subject: [sqlite] Re: Difference between finalize and reset. nbiggs <[EMAIL PROTECTED]> wrote: > Can somebody please explain the difference between the > sqlite3_finalize and sqlite3_reset functions. sqlite3_finalize destroys the statement handle and all internal structures associated with it. The handle is unusable after that. sqlite3_reset clears the information related to the query now in progress, but does not destroy the statement. You can now run another query using the same statement, perhaps after binding different parameters. You must call sqlite3_reset between two queries that use the same statement. > Do I just call finalize after calling prepare and step, or do I need > to call reset also? No need to call reset before finalize, if you are done with the statement. You only need to call sqlite3_reset if you want to reuse the statement for further queries. Igor Tandetnik
[sqlite] Difference between finalize and reset.
Can somebody please explain the difference between the sqlite3_finalize and sqlite3_reset functions. Do I just call finalize after calling prepare and step, or do I need to call reset also? Looking at the source code, it looks like the exec function just uses prepare, step and finalize. Nathan Biggs
[sqlite] Insert triggers
Can an insert trigger cause an update trigger to be fired also? My triggers are listed below. I want tgr_on_insert to fire tgr_on_update. -- CREATE TRIGGER tgr_on_insert AFTER INSERT ON table BEGIN UPDATE table SET flag = 1 where column1 = NEW.column1 AND column2 = NEW.column2; END; -- CREATE TRIGGER tgr_on_update AFTER UPDATE OF flag ON table BEGIN UPDATE table SET flag = 1 where time < NEW.time and flag = 0; END; -- Please note that the update trigger is set for updates on the flag field, but I want to run the update code if there are records with a time < the time of the updated record and the flag = 0. This will only happen if the original insert trigger does not update the flag field. Nathan Biggs Computerway Food Systems (336) 841-7289
RE: [sqlite] Decimal conversion
Thanks Dennis, I will give that a shot. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Monday, January 23, 2006 11:20 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Decimal conversion nbiggs wrote: >I was using version 3.2.1. I downloaded 3.3.1 and your suggestion >worked, but it is very slow compared to 3.2.1. I use the original query >to test the speed. 3.2.1 returned the data in a little over 10 seconds >while 3.3.1 took a minute. Why is that? > > > I don't know. There were some performance issues reported with version 3.3.1. I believe that version 3.3.2, which should be released shortly, will address these issues, but I don't think any of them were causing this much of a slowdown. I though they were on the order of 10% slower, not 500%. I wouldn't have expected the conversion calculation to take very long. On the other hand, the round function does the same type of calculation internally, but it is implemented in C rather than as SQLite VDBE opcodes. And, by looking at the explain output, I can see that SQLIte is actually executing the calculation (both the round, or the cast) twice (once for each input row to build a temp table to sort, and once as each temp table row is scanned to do he group by). That means that the performance improvement of the round function is doubled. Your best bet may be to create a user defined function to truncate a number. This would perform the same calculation as the cast expression, but be implemented in C. It should execute even faster than the round function (Since it wouldn't need to do the conversion to a string that round does. The output does need to be converted to a string once to be displayed, but that is only done once for each output row in the histogram, not twice for each row in the table). HTH Dennis Cote
RE: [sqlite] Decimal conversion
I was using version 3.2.1. I downloaded 3.3.1 and your suggestion worked, but it is very slow compared to 3.2.1. I use the original query to test the speed. 3.2.1 returned the data in a little over 10 seconds while 3.3.1 took a minute. Why is that? I used .dump to create .sql files and then loaded 3.3.1 from the .sql files. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, January 19, 2006 2:31 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Decimal conversion nbiggs wrote: >When I try executing the command I get 'SQL error: near "as": syntax >error'. > >Let me explain what I am trying to do, there might be a better way to do >it. I have a table of weights as one of the columns. I am trying to >return data so that I can create a histogram of the data. My query is >as follows: > >Select round(field, 1), count(*) >from table >group by round(field, 1); > > > > Nathan, What version of SQLite are you using? The cast syntax was added fairly recently. I'm using version 3.2.7 to test. With a current version of SQLite this should work. select cast (field * 10 as integer) / 10.0 as bin, count(*) from table group by bin; Note, you wont get a result row for any bin values that would have had a count of zero. I.e. if there are no rows with a value of 49.8?? then there will not be a result row in the histogram for that value with a count of zero. The histogram output only has rows where the count was 1 or more. This may or may not be what you want. HTH Dennis Cote
RE: [sqlite] Decimal conversion
When I try executing the command I get 'SQL error: near "as": syntax error'. Let me explain what I am trying to do, there might be a better way to do it. I have a table of weights as one of the columns. I am trying to return data so that I can create a histogram of the data. My query is as follows: Select round(field, 1), count(*) from table group by round(field, 1); -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, January 19, 2006 1:31 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Decimal conversion nbiggs wrote: >How do I convert the number 49.991 to just 49.9 in a select statement? >Using the round(weight, 1) returns 50.0. > >Is there a truncate function? > > Nathan, You can use: select cast ((field * 10) as integer) / 10.0 If this is something you do a lot of it might make sense to define a custom function. HTH Dennis Cote
[sqlite] Decimal conversion
How do I convert the number 49.991 to just 49.9 in a select statement? Using the round(weight, 1) returns 50.0. Is there a truncate function? Nathan Biggs
[sqlite] SQLITE Wrappers
Hello, I have created some wrappers to call the sqlite3.dll. I just wanted to make sure that I have all the wrappers I need to use sqlite. It is working, I just wanted to make sure that I am making all the calls in the correct order. To Open: sqlite3_open To Execute: sqlite3_prepare sqlite3_column_count sqlite3_column_name (if set to return column names) sqlite3_step sqlite3_column_text sqlite3_reset sqlite3_finalize To Close: sqlite3_close For Errors: sqlite3_errmsg Nathan Biggs Computerway Food Systems (336) 841-7289
