Re: [sqlite] commas in columns and temporary tables
Hi Ulrik, Now, that's all fine and everything for interactive SQL, but when I'm using this in a program (written in Delphi), the comma messes up the returned values (since they are comma seperated) You can do escaping that replaces the comma with something else when writing to the table, then converts it back after you have gotten the info from the comma-separated format. For example, URLs regularly use %XX to escape characters such as space, where XX is the hexadecimal ASCII value. So "space" (ASCII 32) will be "%20". Just remember to escape not only the comma, but also the percentage sign or whatever signals your escape sequences. Ahh! That's a good idea! What characters would I need to escape? Comma, obviously, percentage, what else would freak SQLite out? I guess that when stuff is returned from the database or inserted into it, I should escape it or unescape the text? Thanks for the tip! -- Taj
Re: [sqlite] commas in columns and temporary tables
Dennis Cote wrote: Taj Morton wrote: Now, I've got two questions. The first is that I have a column (company) in a customers table. The problem is that this column has a comma in it for some rows. That's no good :(. Does anyone have any suggestions on what to do about this problem? I'm using SQLite 2.8.15. If the Delphi interface handles quoted strings in the CSV data then you can quote the columns that may contain commas. Use something like this: SELECT lastname, '"' || bussiness || '"' FROM customers ... The || operator is the concatenation operator. This adds double quotes around all the bussiness name strings. Right. Unfortunately, the SQLite header uses regular expressions to split the results. However, I can probably muck around in the header file and make that work... The obvious problem with that I have to update all my SQL queries... Temporary tables are destroyed when the database connection is closed or when the user explicitly drops them. "The database connection is closed"...means...? If I'm using the SQLite DLL, when is a connection closed? Is it started when I call Open, and closed when I call Close (that would mean that a connection only lasts for one query)... Can you please explain? I hope this helps. Thanks for the suggestions! -- Taj
Re: [sqlite] Recursive Triggers
On Dec 3, 2004, at 4:04 PM, D. Richard Hipp wrote: SQLite does not currently support recursive triggers. On of the main reasons for not supporting recursive triggers is that disallowing recursive triggers was seen as the easiest way to avoid infinite loops like this: CREATE TRIGGER loop AFTER UPDATE OF table1 BEGIN UPDATE table1 SET cnt=(cnt+1)%100 WHERE rowid=old.rowid; END; UPDATE table1 SET cnt=1 WHERE rowid=1; -- Infinite loop By disallowing recursive triggers, SQLite avoids the infinite loop above. But there are useful things one could do with recursive triggers that do not involve infinite loops. I would like to relax the constraint in SQLite and allow some support for recursive triggers as long as the recursive triggers do not cause an infinite loop. But I'm not sure how to do about it? Question: What do other RDBMSes do with triggers that form infinite loops? Does anybody know? Dynamic languages typically have a hard coded 'stack limit'. I think this is what Oracle does, see: http://www.oreilly.com/catalog/ordevworkbook/chapter/ch16s.html#8 Question: Can anybody suggest a way of providing support for recursive triggers which also guarantees that every SQL statement will eventually complete? Use a 'stack limit' ;) Report an error when the stack limit is reached. Unfortunately I don't know if how to implement or if this is even a feasible solution for SQLite. Best, Charlie
[sqlite] Recursive Triggers
SQLite does not currently support recursive triggers. On of the main reasons for not supporting recursive triggers is that disallowing recursive triggers was seen as the easiest way to avoid infinite loops like this: CREATE TRIGGER loop AFTER UPDATE OF table1 BEGIN UPDATE table1 SET cnt=(cnt+1)%100 WHERE rowid=old.rowid; END; UPDATE table1 SET cnt=1 WHERE rowid=1; -- Infinite loop By disallowing recursive triggers, SQLite avoids the infinite loop above. But there are useful things one could do with recursive triggers that do not involve infinite loops. I would like to relax the constraint in SQLite and allow some support for recursive triggers as long as the recursive triggers do not cause an infinite loop. But I'm not sure how to do about it? Question: What do other RDBMSes do with triggers that form infinite loops? Does anybody know? Question: Can anybody suggest a way of providing support for recursive triggers which also guarantees that every SQL statement will eventually complete? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] FW: SQL error: no such column: State
> Executing > sqlite> .schema BillingNumbers shows the column to be there > > CREATE TABLE BillingNumbers(id int default 0 not null, clecID int default 0 > not null, ilecID int default 0 not null, BillingNumber > varchar(20) default " not null, State varchar(20) default " not null, > Resale int default 0 not null, UNEP int default 0 not null, > PctDiscount decimal(8,2) default 0 not null); I don't know anything about your development system, so I don't know exactly what's going on; but do you not see anything wrong with the quotes in the schema above. Regards
Re: [sqlite] Storing precompiled queries/vm?
Keith Herold wrote: 3.0 supports precompiled queries natively; is it possible to store the vm in a resource dll or something, load it up when the application starts, and then attach an open sqlite db to the stored vm? No. The compiler in SQLite is very efficient. It is likely that it would take more time to read a prepared query from the disk than it would to just recompile it. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Storing precompiled queries/vm?
3.0 supports precompiled queries natively; is it possible to store the vm in a resource dll or something, load it up when the application starts, and then attach an open sqlite db to the stored vm? My impression was no, based on the API and the lack of information on wiki and the mailing list about it. --Keith ** - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
Re: [sqlite] FW: SQL error: no such column: State
At 12:51 03/12/2004 -0600, you wrote: When The Table is created in code with the following statements cmd.CommandText = "CREATE TABLE BillingNumbers(id int default 0 not null, " & _ "clecID int default 0 not null, " & _ "ilecID int default 0 not null, " & _ "BillingNumber varchar(20) default "" not null, " & _ "State varchar(20) default "" not null, " & _ "Resale int default 0 not null, " & _ "UNEP int default 0 not null, " & _ "PctDiscount decimal(8,2) default 0 not null)" ... This Insert statement returns the error "SQL error: no such column: State" INSERT INTO BillingNumbers (id, clecID, ilecID, BillingNumber, State, Resale, UNEP, PctDiscount) VALUES (15, 2, 0, '318Q802095', 'LA', 1, 0, 0) I'd suggest using " a few more times; in VB, when you put "" inside a string, it only leaves one ". So the BillingNumber and State columns should be defined thus: ... "BillingNumber varchar(20) default not null, " & _ "State varchar(20) default not null, " & _ ... In your code, the State column is not defined; the default string for BillingNumber, OTOH, is defined as "not null, State varchar(20) default" Guy
Re: [sqlite] FW: SQL error: no such column: State
Mike Willhite said:
> cmd.CommandText = "CREATE TABLE BillingNumbers(id int default not null,"
> "clecID int default not null, " & _
> "ilecID int default not null, " & _
> "BillingNumber varchar(20) default "" not null, " & _
> "State varchar(20) default "" not null, " & _
> "Resale int default not null, " & _
> "UNEP int default not null, " & _
> "PctDiscount decimal(8,2) default not null)"
Try this instead and you'll see better results:
cmd.CommandText = "CREATE TABLE BillingNumbers (" & vbCrLf _
& "clecID int default 0 not null," & vbCrLf _
& "ilecID int default 0 not null," & vbCrLf _
& "BillingNumber varchar(20) default '' not null," & vbCrLf _
& "State varchar(20) default '' not null," & vbCrLf _
& "Resale int default 0 not null," & vbCrLf _
& "UNEP int default 0 not null," & vbCrLf _
& "PctDiscount decimal(8,2) default 0.00 not null)"
The problem you were experiencing and not seeing is that in Visual Basic,
inside of quotes, "" is not an empty string but a single " character.
Thus you don't have a State fields but a BillingNumber field with a
default value of ' not null State varchar(20) default '. That's probably
not what you expected.
Clay Dowling
--
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/
Articles, Reviews and Commentary on web development
[sqlite] FW: SQL error: no such column: State
_ From: Mike Willhite [mailto:[EMAIL PROTECTED] Sent: Friday, December 03, 2004 12:13 PM To: '[EMAIL PROTECTED]' Subject: SQL error: no such column: State System Configuration: Microsoft Windows Server 2003 Ver. 5.2.3790 CoreLab MySQLDirect.NET Ver. 2.70.0 05.11.04 Microsoft Development Environment 2003 Ver 7.1.3088 Microsoft .Net 1.1 Ver 1.1.4322 SP1 When The Table is created in code with the following statements cmd.CommandText = "CREATE TABLE BillingNumbers(id int default 0 not null, " & _ "clecID int default 0 not null, " & _ "ilecID int default 0 not null, " & _ "BillingNumber varchar(20) default "" not null, " & _ "State varchar(20) default "" not null, " & _ "Resale int default 0 not null, " & _ "UNEP int default 0 not null, " & _ "PctDiscount decimal(8,2) default 0 not null)" cmd.ExecuteNonQuery() This Insert statement returns the error "SQL error: no such column: State" INSERT INTO BillingNumbers (id, clecID, ilecID, BillingNumber, State, Resale, UNEP, PctDiscount) VALUES (15, 2, 0, '318Q802095', 'LA', 1, 0, 0) Executing sqlite> .schema BillingNumbers shows the column to be there CREATE TABLE BillingNumbers(id int default 0 not null, clecID int default 0 not null, ilecID int default 0 not null, BillingNumber varchar(20) default " not null, State varchar(20) default " not null, Resale int default 0 not null, UNEP int default 0 not null, PctDiscount decimal(8,2) default 0 not null); sqlite> Executing sqlite> select State from BillingNumbers; SQL error: no such column: State I can use SQLite3.exe to drop the table and then create it again using the SQLite3.exe Command Line and the INSERT and Select statements then work. sqlite> drop table BillingNumbers; sqlite> CREATE TABLE BillingNumbers(id int default 0 not null, ...> clecID int default 0 not null, ...> ilecID int default 0 not null, ...> BillingNumber varchar(20) default "" not null, ...> State varchar(20) default "" not null, ...> Resale int default 0 not null, ...> UNEP int default 0 not null, ...> PctDiscount decimal(8,2) default 0 not null) ...> ; sqlite> select State from BillingNumbers; sqlite> sqlite> INSERT INTO BillingNumbers (id, clecID, ilecID, BillingNumber, State, Resale, UNEP, PctDiscount) VALUES (15, 2, 0, '318Q80 2095', 'LA', 1, 0, 0); sqlite> Thanks Mike
[sqlite] busy handler?
Hi everybody!
i'm running my multi-threads program under linux, so in that program i'm
tring to do an INSERT, a number x of times. sqlite libraries are
compiled with threadsafe, and i have a sqlite3_busy_handler in each
connection to database (there is a different connection for each
thread), sometimes get an error like "database is locked", and my busy
handler function never is called, but sometimes that works, but only in
a few threads. sqlite3_busy_timeout doesn't work either ¿why?!!!
This is my little program:
int handlerBusy(void * ptr, int prior)
{
printf("enter to busy handler\n");
fflush(stdout);
sleep(1);
return(1);
}
void * functionThread(void * param)
{
sqlite3 * db=NULL;
int rc;
char * zErrMsg;
int i;
i=*((int*)param);
pthread_mutex_unlock();
printf("start %d\n", i);
fflush(stdout);
rc = sqlite3_open("/root/testSpeed.db3", );
if(rc)
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return(NULL);
}
sqlite3_busy_timeout(db, 6000);
sqlite3_busy_handler(db, , NULL);
sqlite3_exec(db, "PRAGMA default_temp_store = MEMORY;PRAGMA
temp_store = MEMORY;", NULL, NULL, );
rc=sqlite3_exec(db, "INSERT INTO t2 VALUES(NULL, 234, 'DSFDSFDS')",
NULL, NULL, );
if( rc!=SQLITE_OK )
{
fprintf(stdout, "%d: SQL error: %s\n", i, sqlite3_errmsg(db));
fflush(stdout);
}
sqlite3_close(db);
printf("end %d\n", i);
fflush(stdout);
pthread_exit(NULL);
return(NULL);
}
int main()
{
int i;
int i2;
for(i=0;i
Re: [sqlite] PySQLcrypt
On Fri, Dec 03, 2004 at 09:56:42PM +0800, Ng Pheng Siong wrote: > PySQLcrypt is an enhanced version of PySQLite that uses SQLcrypt, which > itself enhances SQLite version 3 with transparent AES encryption. I'm apologise for following up to myself, but I forgot to mention that I've posted a blog entry with more details: http://sandbox.rulemaker.net/ngps/169 Thanks. Cheers. -- Ng Pheng Siong <[EMAIL PROTECTED]> http://sandbox.rulemaker.net/ngps -+- M2Crypto, ZServerSSL for Zope, Blog http://www.sqlcrypt.com -+- Database Engine with Transparent AES Encryption
Re: [sqlite] TRANSACTION-question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mime-Version: 1.0 Content-Type: multipart/mixed; x-avg-checked=avg-ok-786B32B5; boundary="===176C28DF===" - --===176C28DF=== Content-Type: text/plain; x-avg-checked=avg-ok-786B32B5; charset=us-ascii; format=flowed Content-Transfer-Encoding: 8bit I work via sqlite3_prepare and submit this transaction at once... What I now saw is that prepare only parses 1 action ... Is that correct? At 12:51 3/12/2004, you wrote: >Steven Van Ingelgem wrote: >>Hi, >>I do the following (SQLite 308 from C++ source in windows): >>BEGIN DEFERRED TRANSACTION;DELETE FROM Inbox;INSERT...;COMMIT >>TRANSACTION; END TRANSACTION; >>Now, nothing is done? Nothing inserted, nothing deleted from Inbox... > >Probably you are encountering an error which is causing the >transaction to roll back. Check your return codes. > >COMMIT ends the transaction. The END is redundant. > > >-- >D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > > - --===176C28DF===-- -BEGIN PGP SIGNATURE- Version: PGP 8.0.3 iQA/AwUBQbBpE8pHmxag5XckEQJjogCZAcJxdn/xqZ2BGkIdrkjGLyDmrO4AoPT9 Yom25cqVcvtGZ3aUDvX2WP0H =Qdrd -END PGP SIGNATURE-
Re: [sqlite] Clearing of memory
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mime-Version: 1.0 Content-Type: multipart/mixed; x-avg-checked=avg-ok-786B32B5; boundary="===733C1D2===" - --===733C1D2=== Content-Type: multipart/alternative; x-avg-checked=avg-ok-786B32B5; boundary="=_30639234==.ALT" - --=_30639234==.ALT Content-Type: text/plain; x-avg-checked=avg-ok-786B32B5; charset=us-ascii; format=flowed Content-Transfer-Encoding: 8bit You are right... I changed the code to "return true" always ;) Thanks for the tip :) At 13:43 3/12/2004, you wrote: >You are making a bad assumption. sqlite3_finalize() clears >the memory regardless of what return code you get. KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ #: 35217584 - --=_30639234==.ALT Content-Type: text/html; x-avg-checked=avg-ok-786B32B5; charset=us-ascii Content-Transfer-Encoding: 8bit You are right... I changed the code to return true always ;) Thanks for the tip :) At 13:43 3/12/2004, you wrote: You are making a bad assumption. sqlite3_finalize() clears the memory regardless of what return code you get. KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/; eudora="autourl">http://www.karels0ft.be/ ICQ # : 35217584 - --=_30639234==.ALT-- - --===733C1D2===-- -BEGIN PGP SIGNATURE- Version: PGP 8.0.3 iQA/AwUBQbBjO8pHmxag5XckEQKC7QCg8Kt1ou0AdSHuV1CUt8e0sUOrFdkAoLWM 6cJso/ex0wwnv0AVIOJLlKBC =cId6 -END PGP SIGNATURE-
Re: [sqlite] Clearing of memory
That's what I thought...
int r = sqlite3_finalize(m_Stmt);
if ( r != SQLITE_OK )
{
m_szErrorMsg = wxString(sqlite3_errmsg(m_DB),
wxConvUTF8);
return false;
}
The error I got is: "cannot start a transaction within a
transaction"
(cfr previous mail).
Thus the memory ain't cleared...
Probably I'm doing something wrong with the query, but that doesn't mean
I cannot clear the memory right?
Cheers,
Steven
At 13:01 3/12/2004, you wrote:
Steven Van Ingelgem wrote:
Hi,
Another question that arises is ... How do I clear my
memory...
Call sqlite3_finalize() on every pointer you got from
sqlite3_prepare(). Then call sqlite3_close().
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
KaReL (aka Steven)
Main Webpage :
http://www.karels0ft.be/
ICQ # :
35217584
pgp2sycU3bCmW.pgp
Description: PGP signature
Re: [sqlite] Clearing of memory
Steven Van Ingelgem wrote: Hi, Another question that arises is ... How do I clear my memory... Call sqlite3_finalize() on every pointer you got from sqlite3_prepare(). Then call sqlite3_close(). -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] TRANSACTION-question
Steven Van Ingelgem wrote: Hi, I do the following (SQLite 308 from C++ source in windows): BEGIN DEFERRED TRANSACTION;DELETE FROM Inbox;INSERT...;COMMIT TRANSACTION; END TRANSACTION; Now, nothing is done? Nothing inserted, nothing deleted from Inbox... Probably you are encountering an error which is causing the transaction to roll back. Check your return codes. COMMIT ends the transaction. The END is redundant. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] TRANSACTION-question
Hmmm, it doesn't work... It still doesn't write to the database. When I do everything without a transaction, it is written correctly, but I don't want it because I first empty the whole table. Now if the pc crashes, everything is lost. And that's not what I want... So only when the complete transaction is finished I want it to continue. At 10:26 3/12/2004, you wrote: Yes, you do it. Bye - Original Message - From: Steven Van Ingelgem To: [EMAIL PROTECTED] Sent: Friday, December 03, 2004 10:02 AM Subject: Re: [sqlite] TRANSACTION-question Do you automatically end a transaction when committing? At 09:11 3/12/2004, you wrote: Hi Steven, I used transactions in C++ code (windows and linux) with sqlite 2.8; try the sequence " BEGIN; DELETE ...; INSERT ...; COMMIT; ", it works in 2.8. Regards Paolo - Original Message - From: Steven Van Ingelgem To: [EMAIL PROTECTED] Sent: Friday, December 03, 2004 8:51 AM Subject: [sqlite] TRANSACTION-question Hi, I do the following (SQLite 308 from C++ source in windows): BEGIN DEFERRED TRANSACTION;DELETE FROM Inbox;INSERT...;COMMIT TRANSACTION; END TRANSACTION; Now, nothing is done? Nothing inserted, nothing deleted from Inbox... Then I tried to execute the same afterwards, but then I got that I am already in a transaction? Is that possible or should I look further? Does a commit end the transaction, or should I end it anyhow? Thx, KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ # : 35217584 KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ # : 35217584 KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ # : 35217584 pgpZbry6ZHQ7y.pgp Description: PGP signature
Re: [sqlite] TRANSACTION-question
Yes, you do it. Bye - Original Message - From: Steven Van Ingelgem To: [EMAIL PROTECTED] Sent: Friday, December 03, 2004 10:02 AM Subject: Re: [sqlite] TRANSACTION-question Do you automatically end a transaction when committing? At 09:11 3/12/2004, you wrote: Hi Steven, I used transactions in C++ code (windows and linux) with sqlite 2.8; try the sequence " BEGIN; DELETE ...; INSERT ...; COMMIT; ", it works in 2.8. Regards Paolo - Original Message - From: Steven Van Ingelgem To: [EMAIL PROTECTED] Sent: Friday, December 03, 2004 8:51 AM Subject: [sqlite] TRANSACTION-question Hi, I do the following (SQLite 308 from C++ source in windows): BEGIN DEFERRED TRANSACTION;DELETE FROM Inbox;INSERT...;COMMIT TRANSACTION; END TRANSACTION; Now, nothing is done? Nothing inserted, nothing deleted from Inbox... Then I tried to execute the same afterwards, but then I got that I am already in a transaction? Is that possible or should I look further? Does a commit end the transaction, or should I end it anyhow? Thx, KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ #: 35217584 KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ #: 35217584
Re: [sqlite] TRANSACTION-question
Do you automatically end a transaction when committing? At 09:11 3/12/2004, you wrote: Hi Steven, I used transactions in C++ code (windows and linux) with sqlite 2.8; try the sequence " BEGIN; DELETE ...; INSERT ...; COMMIT; ", it works in 2.8. Regards Paolo - Original Message - From: Steven Van Ingelgem To: [EMAIL PROTECTED] Sent: Friday, December 03, 2004 8:51 AM Subject: [sqlite] TRANSACTION-question Hi, I do the following (SQLite 308 from C++ source in windows): BEGIN DEFERRED TRANSACTION;DELETE FROM Inbox;INSERT...;COMMIT TRANSACTION; END TRANSACTION; Now, nothing is done? Nothing inserted, nothing deleted from Inbox... Then I tried to execute the same afterwards, but then I got that I am already in a transaction? Is that possible or should I look further? Does a commit end the transaction, or should I end it anyhow? Thx, KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ # : 35217584 KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ # : 35217584 pgp28wWEMi9Uy.pgp Description: PGP signature
[sqlite] Clearing of memory
Hi, Another question that arises is ... How do I clear my memory... I try to close a database with a query (transaction: cfr previous mail) prepared & database opened: Is it enough to do "sqlite3_finalize" (which is not when for example you are in a transaction -> memory ain't cleared). Is it enough to do "sqlite3_close" (which is not because you still have open statements) is it ok to use sqlite3_free ( which probably isn't because it works on 'char*' ) When I try to use sqlite3_interrupt on my database I got "library_misuse" Any other options? ;) KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ # : 35217584 pgpa6SEkKCUbs.pgp Description: PGP signature
Re: [sqlite] TRANSACTION-question
Hi Steven, I used transactions in C++ code (windows and linux) with sqlite 2.8; try the sequence " BEGIN; DELETE ...; INSERT ...; COMMIT; ", it works in 2.8. Regards Paolo - Original Message - From: Steven Van Ingelgem To: [EMAIL PROTECTED] Sent: Friday, December 03, 2004 8:51 AM Subject: [sqlite] TRANSACTION-question Hi, I do the following (SQLite 308 from C++ source in windows): BEGIN DEFERRED TRANSACTION;DELETE FROM Inbox;INSERT...;COMMIT TRANSACTION; END TRANSACTION; Now, nothing is done? Nothing inserted, nothing deleted from Inbox... Then I tried to execute the same afterwards, but then I got that I am already in a transaction? Is that possible or should I look further? Does a commit end the transaction, or should I end it anyhow? Thx, KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ #: 35217584
