[sqlite] is there a pragma to disable triggers?
Hello, I am working on a syncing project and I do have a lot of triggers to read/write foreign keys. And while I do the sync of one table the triggers are causing a strange effect (as the other table is not yet synced). Is there a pragma to disable triggers on the sqlite database? Regards, Marton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] quickly locking/unlocking database with password
Hello, I use C# to connect SQLite. connStr = @"Data Source=" + databaseFilePath + ";New=True;Version=3;"; conn = new SQLiteConnection(connStr); I know I can add a Password=something; string to the connection string in order to get a locked/encrypted database. I am wondering if there is a quick and simple way to transform a not encrypted database to an encrypted one? Regards, Marton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "unable to open database file" on DROP
Hello people, why is it that I can look at the ".schema" of a db with the sqlite3 command line tool, but can't drop a table or view? After all, if the db file weren't open, I couldn't even see the schema. My problem is that I've written an app that uses views to access data. After usage, I don't want those views no more. Unfortunately I can't DROP them, so I create more and more views with different names and keep littering my db with an increasing number of usesless views that I can't delete. CREATE TEMPORARY VIEW produces an "unable to open database file" error as well. I'm up a bit of a stump here because re-creating the database (which contains millions of lines but is only about 140M total file size) takes about a week. Here's a screenshot that should tell the whole story. I happened to try to drop a table, but it really doesn't matter what I drop. $ sqlite3.exe clhist.sqlite SQLite version 3.5.1 Enter ".help" for instructions sqlite> .schema CREATE TABLE Batches ( Id TEXT PRIMARY KEY, Moves_V1 INTEGER, Moves_V2 INTEGER, Moves_V3 INTEGER, Moves_V4 INTEGER, Moves_V5 INTEGER, Moves_R1 INTEGER, Moves_R2 INTEGER, Moves_R3 INTEGER, Moves_R4 INTEGER, Moves_R5 INTEGER ); CREATE TABLE Logs ( Shortname TEXT PRIMARY KEY, Status INTEGER ); CREATE TABLE Modules ( Name TEXT ); CREATE TABLE Moves ( Batch TEXT, SlotNo INTEGER, Tool TEXT, Module TEXT, TimeStart INTEGER, Duration INTEGER, ZipDate TEXT ); CREATE TABLE Tools ( Name TEXT ); CREATE VIEW "B470330" AS SELECT * FROM Moves WHERE Batch="470330"; CREATE VIEW BatchMoves AS SELECT * FROM Moves WHERE Batch="P"; CREATE VIEW "R1" AS SELECT * FROM "B470330" WHERE Tool="R1"; CREATE INDEX MovesBatch ON Moves(Batch); sqlite> DROP TABLE "Batches" ; SQL error: unable to open database file sqlite> .quit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
On 8/11/08, Robert Latest <[EMAIL PROTECTED]> wrote: > Hello people, > > why is it that I can look at the ".schema" of a db with the sqlite3 > command line tool, but can't drop a table or view? After all, if the > db file weren't open, I couldn't even see the schema. I have no idea what you are talking about... of course, you can drop a view or a table. Here you go... [04:23 PM] ~$sqlite3 SQLite version 3.5.9 Enter ".help" for instructions sqlite> CREATE TABLE foo (a, b); sqlite> CREATE VIEW bar AS SELECT * FROM foo; sqlite> .s CREATE TABLE foo (a, b); CREATE VIEW bar AS SELECT * FROM foo; sqlite> DROP VIEW bar; sqlite> .s CREATE TABLE foo (a, b); sqlite> DROP TABLE foo; sqlite> .s sqlite> Maybe I didn't understand your question. > > My problem is that I've written an app that uses views to access data. > After usage, I don't want those views no more. Unfortunately I can't > DROP them, so I create more and more views with different names and > keep littering my db with an increasing number of usesless views that > I can't delete. > > CREATE TEMPORARY VIEW produces an "unable to open database file" error as > well. > > I'm up a bit of a stump here because re-creating the database (which > contains millions of lines but is only about 140M total file size) > takes about a week. > > Here's a screenshot that should tell the whole story. I happened to > try to drop a table, but it really doesn't matter what I drop. > > $ sqlite3.exe clhist.sqlite > SQLite version 3.5.1 > Enter ".help" for instructions > sqlite> .schema > CREATE TABLE Batches ( > Id TEXT PRIMARY KEY, > Moves_V1 INTEGER, > Moves_V2 INTEGER, > Moves_V3 INTEGER, > Moves_V4 INTEGER, > Moves_V5 INTEGER, > Moves_R1 INTEGER, > Moves_R2 INTEGER, > Moves_R3 INTEGER, > Moves_R4 INTEGER, > Moves_R5 INTEGER > ); > CREATE TABLE Logs ( > Shortname TEXT PRIMARY KEY, > Status INTEGER > ); > CREATE TABLE Modules ( > Name TEXT > ); > CREATE TABLE Moves ( > Batch TEXT, > SlotNo INTEGER, > Tool TEXT, > Module TEXT, > TimeStart INTEGER, > Duration INTEGER, > ZipDate TEXT > ); > CREATE TABLE Tools ( > Name TEXT > ); > CREATE VIEW "B470330" AS SELECT * FROM Moves WHERE Batch="470330"; > CREATE VIEW BatchMoves AS SELECT * FROM Moves WHERE Batch="P"; > CREATE VIEW "R1" AS SELECT * FROM "B470330" WHERE Tool="R1"; > CREATE INDEX MovesBatch ON Moves(Batch); > sqlite> DROP TABLE "Batches" ; > SQL error: unable to open database file > sqlite> .quit > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quickly locking/unlocking database with password
Kodok Márton wrote: Hello, I use C# to connect SQLite. connStr = @"Data Source=" + databaseFilePath + ";New=True;Version=3;"; conn = new SQLiteConnection(connStr); I know I can add a Password=something; string to the connection string in order to get a locked/encrypted database. I am wondering if there is a quick and simple way to transform a not encrypted database to an encrypted one? Regards, Marton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users That functionality is implemented by the .NET wrapper you are using - I'm afraid you will have to ask the question on the mailing list operated by that project. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
Robert Latest wrote: Hello people, why is it that I can look at the ".schema" of a db with the sqlite3 command line tool, but can't drop a table or view? After all, if the db file weren't open, I couldn't even see the schema. My problem is that I've written an app that uses views to access data. After usage, I don't want those views no more. Unfortunately I can't DROP them, so I create more and more views with different names and keep littering my db with an increasing number of usesless views that I can't delete. CREATE TEMPORARY VIEW produces an "unable to open database file" error as well. I'm up a bit of a stump here because re-creating the database (which contains millions of lines but is only about 140M total file size) takes about a week. Here's a screenshot that should tell the whole story. I happened to try to drop a table, but it really doesn't matter what I drop. $ sqlite3.exe clhist.sqlite SQLite version 3.5.1 Enter ".help" for instructions sqlite> .schema CREATE TABLE Batches ( Id TEXT PRIMARY KEY, Moves_V1 INTEGER, Moves_V2 INTEGER, Moves_V3 INTEGER, Moves_V4 INTEGER, Moves_V5 INTEGER, Moves_R1 INTEGER, Moves_R2 INTEGER, Moves_R3 INTEGER, Moves_R4 INTEGER, Moves_R5 INTEGER ); CREATE TABLE Logs ( Shortname TEXT PRIMARY KEY, Status INTEGER ); CREATE TABLE Modules ( Name TEXT ); CREATE TABLE Moves ( Batch TEXT, SlotNo INTEGER, Tool TEXT, Module TEXT, TimeStart INTEGER, Duration INTEGER, ZipDate TEXT ); CREATE TABLE Tools ( Name TEXT ); CREATE VIEW "B470330" AS SELECT * FROM Moves WHERE Batch="470330"; CREATE VIEW BatchMoves AS SELECT * FROM Moves WHERE Batch="P"; CREATE VIEW "R1" AS SELECT * FROM "B470330" WHERE Tool="R1"; CREATE INDEX MovesBatch ON Moves(Batch); sqlite> DROP TABLE "Batches" ; SQL error: unable to open database file sqlite> .quit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Is it possible that the database file is at the same time in use from a different process? If yes, try shutting down the other database consumers and retry then. -- Multumesc, Mihai Limbasan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ODBC driver for C error "only one SQL statement allowed"
Hi, sqlite-users, am evaluating Sqlite ODBC driver for C (current sqliteodbc.exe by Ch. Werner, http://www.ch-werner.de/sqliteodbc/). After defining successfully two tables "mytable" and "mysequence", I tried to define a trigger by means of "CREATE TRIGGER trg BEFORE INSERT ON mytable FOR EACH ROW BEGIN INSERT INTO mysequence VALUES(0); END" This worked fine with sqlite3.c, when passed to sqlite3_exec(), but gave rise to an error message with sqlite3odbc.c , when passed to SQLExecDirect(). The message was : "only one SQL statement allowed". Is there a special SQL syntax required for sqlite3odbc? Are there any (further) restrictions for triggers? Or is it a bug? Best regards Martin Abshagen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ODBC driver for C error "only one SQL statement allowed"
Abshagen, Martin RD-AS2 wrote: Hi, sqlite-users, am evaluating Sqlite ODBC driver for C (current sqliteodbc.exe by Ch. Werner, http://www.ch-werner.de/sqliteodbc/). After defining successfully two tables "mytable" and "mysequence", I tried to define a trigger by means of "CREATE TRIGGER trg BEFORE INSERT ON mytable FOR EACH ROW BEGIN INSERT INTO mysequence VALUES(0); END" This worked fine with sqlite3.c, when passed to sqlite3_exec(), but gave rise to an error message with sqlite3odbc.c , when passed to SQLExecDirect(). The message was : "only one SQL statement allowed". Is there a special SQL syntax required for sqlite3odbc? Are there any (further) restrictions for triggers? Or is it a bug? Best regards Martin Abshagen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users It's clearly a bug / limitation in the ODBC wrapper - it obviously parses the SQL you're passing it and doesn't understand trigger syntax, thus bailing out. You might want to contact the developer and ask whether there are plans to update the wrapper. -- Multumesc, Mihai Limbasan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
On 8/11/08, Mihai Limbasan <[EMAIL PROTECTED]> wrote: > Robert Latest wrote: > > > Hello people, > > > > why is it that I can look at the ".schema" of a db with the sqlite3 > > command line tool, but can't drop a table or view? After all, if the > > db file weren't open, I couldn't even see the schema. > > > > My problem is that I've written an app that uses views to access data. > > After usage, I don't want those views no more. Unfortunately I can't > > DROP them, so I create more and more views with different names and > > keep littering my db with an increasing number of usesless views that > > I can't delete. > > > > CREATE TEMPORARY VIEW produces an "unable to open database file" error as > well. > > > > I'm up a bit of a stump here because re-creating the database (which > > contains millions of lines but is only about 140M total file size) > > takes about a week. > > > > Here's a screenshot that should tell the whole story. I happened to > > try to drop a table, but it really doesn't matter what I drop. > > > > $ sqlite3.exe clhist.sqlite > > SQLite version 3.5.1 > > Enter ".help" for instructions > > sqlite> .schema > > CREATE TABLE Batches ( > >Id TEXT PRIMARY KEY, > >Moves_V1 INTEGER, > >Moves_V2 INTEGER, > >Moves_V3 INTEGER, > >Moves_V4 INTEGER, > >Moves_V5 INTEGER, > >Moves_R1 INTEGER, > >Moves_R2 INTEGER, > >Moves_R3 INTEGER, > >Moves_R4 INTEGER, > >Moves_R5 INTEGER > > ); > > CREATE TABLE Logs ( > >Shortname TEXT PRIMARY KEY, > >Status INTEGER > > ); > > CREATE TABLE Modules ( > >Name TEXT > > ); > > CREATE TABLE Moves ( > >Batch TEXT, > >SlotNo INTEGER, > >Tool TEXT, > >Module TEXT, > >TimeStart INTEGER, > >Duration INTEGER, > >ZipDate TEXT > > ); > > CREATE TABLE Tools ( > >Name TEXT > > ); > > CREATE VIEW "B470330" AS SELECT * FROM Moves WHERE Batch="470330"; > > CREATE VIEW BatchMoves AS SELECT * FROM Moves WHERE Batch="P"; > > CREATE VIEW "R1" AS SELECT * FROM "B470330" WHERE Tool="R1"; > > CREATE INDEX MovesBatch ON Moves(Batch); > > sqlite> DROP TABLE "Batches" ; > > SQL error: unable to open database file > > sqlite> .quit > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > Is it possible that the database file is at the same time in use from a > different process? If yes, try shutting down the other database consumers > and retry then. ahhh... I did misread the question. Yes, the above explanation seems logical. Your app is probably tying up the db, so you can't drop the table from the command line. > > -- > Multumesc, > Mihai Limbasan > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
On Mon, Aug 11, 2008 at 1:42 PM, P Kishor <[EMAIL PROTECTED]> wrote: > ahhh... I did misread the question. Yes, the above explanation seems > logical. Your app is probably tying up the db, so you can't drop the > table from the command line. No it ain't. That's of course the first thing I checked. I fact I re-booted the machine just to make sure that no rogue process was holding a lock ono the db. Check this out: $ sqlite3 /cygdrive/d/cl_hist_dat/clhist.sqlite SQLite version 3.5.1 Enter ".help" for instructions sqlite> create view y as select * from tools; sqlite> create temporary view z as select * from modules; SQL error: unable to open database file sqlite> drop view y; sqlite> .quit $ So I may create a view, y. Then I'm not allowed to create a temporary view, z (I never am when mucking around with this particular db). After that I want to drop view y (which I just created) and can't, either. This is really puzzling. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
"Robert Latest" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > $ sqlite3 /cygdrive/d/cl_hist_dat/clhist.sqlite > SQLite version 3.5.1 > Enter ".help" for instructions > sqlite> create view y as select * from tools; > sqlite> create temporary view z as select * from modules; > SQL error: unable to open database file SQLite can't write to your temp directory. That's why you can create a regular view, but not a temporary view (which requires opening a temp database). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multiple inserts
Hi, Does SQLite accepts multiple insert? insert into table (col1,col2) values (val1,val2), (val3,val4), (val5,val6) If not, how can I speed up large inserts? eg: 1000 rows Regards, Marton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple inserts
On 8/11/08, Kodok Márton <[EMAIL PROTECTED]> wrote: > Hi, > > Does SQLite accepts multiple insert? > insert into table (col1,col2) values (val1,val2), (val3,val4), (val5,val6) > > If not, how can I speed up large inserts? > eg: 1000 rows one word... transactions > > Regards, > Marton > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL error: disk I/O error, for attached DBs on power PC
Hi! All, We recently got an error "Disk I/O error" while using the select command on attached DBs. These DBs have same table format We only execute select, insert, and attach commands The environment is as follows: SQLite: V3.5.6 OS: Linux 2.6.24.2 Platform Power PC (MPC8543) Memory: 256M DB Location: SATA hard drive, within a 512-maga-byte partition The error is happened when the size of one DB file is larger than 10M, Ex: -rw-r--r--1 root root 10600448 Aug 1 14:38 system_log-07.db -rw-r--r--1 root root27648 Aug 7 22:18 system_log-08.db ./sqlite3 system_log-08.db sqlite> attach ''system_log-07.db' as db1; sqlite> select * from main.disk_log union all select * from db1.disk_log order by timestamp desc; SQL error: disk I/O error Available partition size: 467.2M Sometimes I executed the command "select count(*) ..." successfully, but sometimes failed. The total number of the result is about 15. Sometimes I executed the command "select * ... limit 0, 3" successfully, but sometimes failed. As observing top when executing sqlite, the available memory is enough (about 120-140M). Would you provide some comments or some solutions? Thank you all very much. -- Best Regards, France Hsu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple inserts
On Mon, 11 Aug 2008, P Kishor wrote: On 8/11/08, Kodok M�rton <[EMAIL PROTECTED]> wrote: Hi, Does SQLite accepts multiple insert? insert into table (col1,col2) values (val1,val2), (val3,val4), (val5,val6) If not, how can I speed up large inserts? eg: 1000 rows one word... transactions Can you give an example of how to use transactions to accomplish this specific case? Chris Christopher F. Martin School of Medicine Center for Digestive Diseases & Nutrition CB# 7555, 4104 Bioinformatics Bldg. University of North Carolina at Chapel Hill Chapel Hill, North Carolina 27599-7555 Phone: 919.966.9340 Fax: 919.966.7592 ~~~ Regards, Marton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple inserts
begin;
insert into table (col1,col2) values (val1,val2);
insert into table (col1,col2) values (val3,val4);
insert into table (col1,col2) values (val5,val6);
end;
you might use FOR or WHILE to construct the query
another example
sqlite_exec($db,"BEGIN;");
for($x = 1; $x<=50; $x++) {
sqlite_exec($db,"INSERT INTO Hits VALUES ('reports.php5', 'jim')");
}
sqlite_exec($db,"END;");
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database"
Sent: Monday, August 11, 2008 4:13 PM
Subject: Re: [sqlite] multiple inserts
>
> On Mon, 11 Aug 2008, P Kishor wrote:
>
>> On 8/11/08, Kodok Mrton <[EMAIL PROTECTED]> wrote:
>>> Hi,
>>>
>>> Does SQLite accepts multiple insert?
>>> insert into table (col1,col2) values (val1,val2), (val3,val4),
>>> (val5,val6)
>>>
>>> If not, how can I speed up large inserts?
>>> eg: 1000 rows
>>
>> one word... transactions
>
> Can you give an example of how to use transactions to accomplish this
> specific case?
>
> Chris
>
>
> Christopher F. Martin
> School of Medicine
> Center for Digestive Diseases & Nutrition
> CB# 7555, 4104 Bioinformatics Bldg.
> University of North Carolina at Chapel Hill
> Chapel Hill, North Carolina 27599-7555
> Phone: 919.966.9340 Fax: 919.966.7592
> ~~~
>
>
>
>
>>
>>
>>>
>>> Regards,
>>> Marton
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> Puneet Kishor http://punkish.eidesis.org/
>> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
>> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple inserts
On 8/11/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > On Mon, 11 Aug 2008, P Kishor wrote: > > > > On 8/11/08, Kodok Márton <[EMAIL PROTECTED]> wrote: > > > > > Hi, > > > > > > Does SQLite accepts multiple insert? > > > insert into table (col1,col2) values (val1,val2), (val3,val4), > (val5,val6) > > > > > > If not, how can I speed up large inserts? > > > eg: 1000 rows > > > > > > > one word... transactions > > > > Can you give an example of how to use transactions to accomplish this > specific case? my suggestion is for speeding up INSERTs, not for doing the INSERT in one statement (although, discussion of that INSERT syntax has taken place on this mailing list... search the archives). Wrt transactions... nothing special BEGIN TRANSACTION; INSERT...; INSERT...; 1000 times COMMIT; > > Chris > > > Christopher F. Martin > School of Medicine > Center for Digestive Diseases & Nutrition > CB# 7555, 4104 Bioinformatics Bldg. > University of North Carolina at Chapel Hill > Chapel Hill, North Carolina 27599-7555 > Phone: 919.966.9340 Fax: 919.966.7592 > ~~~ > > > > > > > > > > > > > > > > > > Regards, > > > Marton > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > -- > > Puneet Kishor http://punkish.eidesis.org/ > > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple inserts
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Kodok Márton > Sent: Monday, August 11, 2008 2:21 PM > To: General Discussion of SQLite Database > Subject: [sqlite] multiple inserts > > Hi, > > Does SQLite accepts multiple insert? > insert into table (col1,col2) values (val1,val2), > (val3,val4), (val5,val6) No. > If not, how can I speed up large inserts? > eg: 1000 rows > Here is a quick shot: Just use a transaction surrounding your INSERTs, and use prepared statements and parameter bindings to prevent multiple parsing. - char* errmsg = NULL; sqlite3* db = NULL; db = open( ) // begin transaction sqlite3_exec( db, "BEGIN TRANSACTION", NULL, NULL, ); // prepare for multiple inserts sqlite3_stmt* stmt = sqlite3_prepare_v2( "INSERT INTO T1 (C1, C2, C3) VALUES (?, ?, ?)") -for-each-row // read doc for sqlite3_bind_* carefully!! sqlite3_bind_int( stmt, 1, someIntVariable); sqlite3_bind_text( stmt, 2, -1, "hello", SQLITE_STATIC); sqlite3_bind_text( stmt, 3, -1, pzSomeString, SQLITE_TRANSIENT); sqlite3_step(stmt); // executes the INSERT sqlite3_reset(stmt); // important! Clears the old values, makes the statement accept new parameters. -end-for-each sqlite3_finalize(stmt); // clean up prepared statement // begin transaction sqlite3_exec( db, "COMMIT", NULL, NULL, ); // COMMIT all dirty pages at once - Regards, Stefan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using TOTAL and AS in the SQL of a Flex app
I really don't know where to post this, as it involves both SQLite and Flex 3, but, I'll try here first. Here's what I have in FlexBuilder: sqlText = "SELECT Up18RODetail.ProductID, TOTAL(Up18RODetail.Qty) AS :qtySum " + "FROM Up18RODetail LEFT JOIN Up18ROHeader ON Up18RODetail.ROID = Up18ROHeader.ROID " + "WHERE Up18ROHeader.DateUploaded Between :startDate AND :endDate " + "GROUP BY Up18RODetail.ProductID " + "HAVING Up18RODetail.ProductID = :prodProdID"; transactionStatement = new SQLStatement(); conn = new SQLConnection(); transactionStatement.sqlConnection = conn; conn.open(dbFile); transactionStatement.addEventListener(SQLEvent.RESULT, calcUsePercentResult); transactionStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler); transactionStatement.text = sqlText; transactionStatement.parameters[":startDate"] = startDate; transactionStatement.parameters[":endDate"] = endDate; transactionStatement.parameters[":qtySum"] = qtySum; //String variable transactionStatement.parameters[":prodProdID"] = prodDataArray[i].ProductID transactionStatement.execute(); Now, the SQL actually works in the SQLite database browser, however it throws a SQL syntax error when I try to run it in my flex app and I'm not sure why. The error reads SQLError: 'Error #3115: SQL Error.', details:'near ":qtySum": syntax error', operation:'execute'. I could sure use a hand here, thanks in advance. Brian Ross Edwards Tech-Connect LLC ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
Thanks, that was it. I don't know exactly how the errors were interrelated, but once I told sqlite (via the PRAGMA thing) to use memory for tmp stuff, all was fine. I'm working under a cygwin environment which seems to be a bit shaky when it comes to system-specific stuff like permissions. robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database repairing
Alexey Pechnikov wrote: > > Can I get full log of sql statements for to sent it other network or store to > outher device? > Alexey, You may want to read http://www.sqlite.org/cvstrac/wiki?p=UndoRedo for an example of using triggers to generate SQL to modify a database. This example is used for undo/redo, but the principals would be the same if you want to generate an SQL log of changes that have been made to a database. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] setting a foreign key in a trigger
I'm having difficulty writing a trigger in SQLite to do what I need to do. I have very little background in SQL, so I might well be missing something obvious. Any help would be greatly appreciated. I have a table foo with a column that is a foreign key to table bar. Every time a row in foo is created, I want to automatically create a row in bar and include the reference to it in foo. I don't know how to write the trigger step such that it can insert the row in bar, get the value of the key column from the new row, and then update foo to point to it. The part that I don't know is how to refer to the newly-created row in the trigger step. Here is what I have: -- CREATE TABLE bar (id INTEGER PRIMARY KEY, ...); CREATE TABLE foo (..., bar_ref INTEGER REFERENCES foo(id)); CREATE TRIGGER new_bar INSERT ON foo FOR EACH ROW BEGIN INSERT INTO bar (...) VALUES(...); UPDATE foo SET new.bar_ref = ???; END -- Is there any way to refer to the id column of the new bar row? I think the usual approach for this problem would be for callers to create the bar row and then set the foo row themselves. That doesn't work in my case because I am adding bar to an existing system. Is there something different I could do? Maybe on table creation? Thanks! --Bruce ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using TOTAL and AS in the SQL of a Flex app
Ross Edwards <[EMAIL PROTECTED]> wrote: > I really don't know where to post this, as it involves both SQLite > and Flex 3, but, I'll try here first. Here's what I have in > FlexBuilder: > > > > sqlText = "SELECT Up18RODetail.ProductID, TOTAL(Up18RODetail.Qty) AS > :qtySum " + You cannot parameterize a column alias. A parameter can only appear where a literal is allowed. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] setting a foreign key in a trigger
How about: drop trigger new_bar ; drop table foo; drop table bar; CREATE TABLE bar (id INTEGER PRIMARY KEY AUTOINCREMENT, text varchar2(255)); CREATE TABLE foo ( id integer primary key, bar_ref integer REFERENCES bar(id), text ); CREATE TRIGGER new_bar AFTER INSERT ON foo FOR EACH ROW BEGIN INSERT INTO bar (text) VALUES(new.text); UPDATE foo SET bar_ref = (select max(id) from bar ) where id = new.id ; END; begin; insert into foo values (1, NULL, 'hello1'); insert into foo values (3, NULL, 'hello3'); insert into foo values (5, NULL, 'hello5'); commit; select * from foo; select * from bar; --- On Mon, 8/11/08, Bruce Clift <[EMAIL PROTECTED]> wrote: From: Bruce Clift <[EMAIL PROTECTED]> Subject: [sqlite] setting a foreign key in a trigger To: sqlite-users@sqlite.org Date: Monday, August 11, 2008, 12:25 PM I'm having difficulty writing a trigger in SQLite to do what I need to do. I have very little background in SQL, so I might well be missing something obvious. Any help would be greatly appreciated. I have a table foo with a column that is a foreign key to table bar. Every time a row in foo is created, I want to automatically create a row in bar and include the reference to it in foo. I don't know how to write the trigger step such that it can insert the row in bar, get the value of the key column from the new row, and then update foo to point to it. The part that I don't know is how to refer to the newly-created row in the trigger step. Here is what I have: -- CREATE TABLE bar (id INTEGER PRIMARY KEY, ...); CREATE TABLE foo (..., bar_ref INTEGER REFERENCES foo(id)); CREATE TRIGGER new_bar INSERT ON foo FOR EACH ROW BEGIN INSERT INTO bar (...) VALUES(...); UPDATE foo SET new.bar_ref = ???; END -- Is there any way to refer to the id column of the new bar row? I think the usual approach for this problem would be for callers to create the bar row and then set the foo row themselves. That doesn't work in my case because I am adding bar to an existing system. Is there something different I could do? Maybe on table creation? Thanks! --Bruce ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANN: SQLite ADO.NET Provider 1.0.56.0
Coinciding with Visual Studio 2008 SP1 going RTM today, I've put out a new version of the SQLite ADO.NET provider. The LINQ bits are fairly solid, but still officially in beta. Nonetheless, it gains the distinction of being the very first ADO.NET provider publically released (other than Sql Server) that supports LINQ on the RTM Entity Framework. This version also introduces full Mono support via a 100% managed provider that back-ends into the official SQLite distribution. Requires 3.6.1 or higher of the shared library (Linux) or the Windows sqlite3.dll. Although the Mono.Data.Sqlite code was based on my provider, it hadn't been updated in 17 months and didn't work all that well. http://sqlite.phxsoftware.com Robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Resources for newbies.
David Nelson wrote:
>
> Getting "logic error or bad data" when using largw data strings:
>
>
> I create the database as follows:
> create table Event( Key TEXT[unique], DateTime DATE, Event TEXT, PRIMARY
> KEY (Key) )
>
This is incorrect and not doing what you want. The unique constraint
must be a separate word, what you have is a "type" called
"TEXT[unique]". Furthermore being a primary key implies that Key must be
unique so that constraint is redundant.
Also, sqlite does not support a DATE type as you have used in your
DateTime column. As you have just seen, sqlite allows almost anything to
be used as a type name, but only a few special values have real meaning.
See http://www.sqlite.org/datatype3.html for the details. The details of
your table definition can be see using the table_info() pragma.
sqlite> create table Event( Key TEXT[unique], DateTime DATE, Event TEXT,
PRIMARY
KEY (Key) );
sqlite> pragma table_info(Event);
cid nametype notnull dflt_value pk
-- -- -- -- --
0 Key TEXT[unique] 0 1
1 DateTimeDATE 0 0
2 Event TEXT 0 0
The actual type of the data in the DateTime column can be seen using the
typeof() function (after you put some data in the table).
sqlite> insert or replace into Event values( 'strKey', '07/25/2008
08:00:00','da
ta string' );
sqlite> select typeof(DateTime) from Event limit 1;
typeof(DateTime)
text
Try this table definition instead:
create table Event(
Key Text primary key,
DateTimeText,
Event Text
);
>
> I add records to the database as follows:
> insert or replace into Event values( 'strKey', '07/25/2008 08:00:00',
> 'data string' )
>
> And I query the database as follows:
> SELECT * FROM [Event] WHERE([Event] LIKE "*foo*" AND [Event] LIKE
> "*bar*") AND[Date/Time] BETWEEN DateAdd("n",-5,Now()) AND Now()
>
>
>
> The query works fine when my inserted data strings are small, like < 256
> bytes.
>
This query never worked in sqlite. The column name [Date/Time] is wrong,
and the functions Now() and DateAdd() don't exist (unless you have
created you own custom functions), and the LIKE operator uses %, not *,
for a wildcard character. Also, literal strings should be enclosed in
single quotes not double quotes.
Try something like this instead:
SELECT * FROM Event
WHERE Event LIKE '%foo%'
AND Event LIKE '%bar%'
AND DateTime BETWEEN datetime('now', '-5 minutes')
AND datetime('now');
> However, I get 'logic error' on the query if my data strings are largere,
> like 2048 bytes.
>
It works fine for me with 3000 bytes of lorem ipsum text.
sqlite> insert or replace into Event values( 'strKey2', '07/25/2008
08:00:01','L
orem ipsum dolor sit amet, consectetuer adipiscing elit. Praesent porta
tortor a
t leo. Vestibulum fringilla tempor nisi. Quisque in tellus. Quisque sit
amet pur
us. Nulla euismod commodo lacus. Sed ut mi a urna pretium consectetuer.
Cras fer
mentum dignissim massa. Pellentesque ante. Donec commodo scelerisque
tortor. Mor
bi nisi lorem, ultrices quis, varius id, accumsan non, nulla. Vivamus
mauris neq
ue, pellentesque ac, pharetra posuere, accumsan non, est. Nulla eu enim.
Integer
aliquam libero tempor turpis. Donec ut libero ut pede mattis
tristique. Vivamus
est.
...>
...> Aenean vitae purus. Aenean et velit. Donec felis nunc, pretium
imperdiet
, lacinia ac, auctor at, massa. Ut vitae metus. In tempus viverra neque.
Etiam f
acilisis, pede eu posuere euismod, felis neque tristique metus, a
rhoncus est ar
cu a tellus. Vestibulum lacinia fringilla ante. Vestibulum ante ipsum
primis in
faucibus orci luctus et ultrices posuere cubilia Curae; Curabitur
pellentesque t
ortor a neque. Mauris in quam.
...>
...> Phasellus rutrum. Pellentesque eget neque nec elit faucibus
gravida. Aen
ean eu augue. Integer interdum consequat arcu. Mauris interdum, nisl eu
convalli
s pulvinar, mi lectus cursus tellus, quis congue nisi risus eu felis.
Morbi adip
iscing, est ut adipiscing ultrices, libero orci condimentum nunc, quis
luctus ni
bh eros nec enim. Cras eros arcu, dignissim eget, ornare vitae, volutpat
non, au
gue. Pellentesque aliquam fringilla ipsum. Nullam vulputate consectetuer
massa.
Proin tincidunt pede a ante. Praesent luctus, nunc sed pellentesque
suscipit, li
bero felis ultrices lectus, et vehicula ante mi quis augue.
...>
...> Nunc sit amet eros ut velit faucibus varius. Nulla facilisi.
Cras non pe
de sed massa accumsan consequat. Proin in augue. Suspendisse potenti.
Aenean tri
stique consequat lorem. Donec suscipit mi eu nibh. Duis feugiat tellus.
Vivamus
tristique. Maecenas nunc lectus, egestas non, tempus eu, accumsan nec,
sem. Sed
