Re: [sqlite] insert statement using temp variable
Hi, Very simple, What will be the output of printf("i"); it won't be 0 right? use snprintf or sprintf and formulate the string then execute the query. int i=0; char * a[100]; snprintf(a,100,"insert into emp values(%d);",i); /or /*sprintf(a,"insert into emp values(%d);",i);*/ rc = sqlite3_exec(db, "create table emp (empid num);", callback, 0, &zErrMsg); rc = sqlite3_exec(db, a, 0, 0, &zErrMsg); This should work. VENKAT Bug the Bugs From: RAKESH HEMRAJANI To: sqlite-users@sqlite.org Sent: Tue, April 5, 2011 10:51:09 AM Subject: [sqlite] insert statement using temp variable hi, need help with very basic question.. More of C than SQLite. have a very simple C program using sqlite DB. .. int i=0; rc = sqlite3_exec(db, "create table emp (empid num);", callback, 0, &zErrMsg); rc = sqlite3_exec(db, "insert into emp values(i);", 0, 0, &zErrMsg); --- the insert query fails with the message stating no such column i. the aim is very simple to insert the value of i into empid column but not sure how to achieve it. pls note that value of i is dynamic and wont be hardcoded. ___ 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] insert statement using temp variable
hi, need help with very basic question.. More of C than SQLite. have a very simple C program using sqlite DB. .. int i=0; rc = sqlite3_exec(db, "create table emp (empid num);", callback, 0, &zErrMsg); rc = sqlite3_exec(db, "insert into emp values(i);", 0, 0, &zErrMsg); --- the insert query fails with the message stating no such column i. the aim is very simple to insert the value of i into empid column but not sure how to achieve it. pls note that value of i is dynamic and wont be hardcoded. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request for an example code use async IO
> Can someone be so kind as to provide a short example of initializing > asynchronous module, opening DB for read/write, create a table, and write > some data to it? There's nothing special in opening db, creating table or writing data into it while using async module. You should use the same API in the same way you use it without async module. All you need to do is to initialize async module and call sqlite3async_run in the dedicated thread. I believe everything is well explained in here http://www.sqlite.org/asyncvfs.html (pay special attention to section 2.0) and in sqlite3async.h header. Do you find something specifically hard to understand? Pavel On Mon, Apr 4, 2011 at 9:47 PM, Ricky Huang wrote: > Hi, > > I am planning on incorporating the sqlite3async module into my project to > improve code responsiveness during disk writes. I've Googled all over for > example code on using the module but can't find any. > > Can someone be so kind as to provide a short example of initializing > asynchronous module, opening DB for read/write, create a table, and write > some data to it? > > > Thanks in advance. > ___ > 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] Request for an example code use async IO
Hi, I am planning on incorporating the sqlite3async module into my project to improve code responsiveness during disk writes. I've Googled all over for example code on using the module but can't find any. Can someone be so kind as to provide a short example of initializing asynchronous module, opening DB for read/write, create a table, and write some data to it? Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] import thousands of documents in SQLite
On 4 Apr 2011, at 9:04pm, Gert Van Assche wrote: > We need to import thousands of documents in an SQLite db for use with FTS. > The FTS part I understand (more or less) but I don't know how to import so > many docs in the DB. > Does anyone know a tool to do this? > I won't be the one doing the import, but users that can't work on command > line (or we don't want them to do this). Employ a programmer to write you a program that will do it. That's what they're for. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] import thousands of documents in SQLite
All, We need to import thousands of documents in an SQLite db for use with FTS. The FTS part I understand (more or less) but I don't know how to import so many docs in the DB. Does anyone know a tool to do this? I won't be the one doing the import, but users that can't work on command line (or we don't want them to do this). Your advise is highly appreciated, Thank you all. gert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Build instructions for Winodws with unicode support
> Basically, the column with name "model" has data type BLOB, and null is > being written to that column. I think your problem is with jdbc driver (I guess its setBytes implemented via the text data type, not blob) and with the fact that writeBuffer[0] is equal to 0. Changing writeBuffer[0] to something other than 0 could prove that. Pavel On Mon, Apr 4, 2011 at 1:58 PM, tiwaris wrote: > > Hello, > > I created the following sample code to test the BLOB data type. > > I am using sqlite-jdbc driver provided at > (http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC). > > The following is the source code (SSCE). > > package org.sqlite; > > import java.sql.Connection; > import java.sql.DriverManager; > import java.sql.PreparedStatement; > import java.sql.ResultSet; > import java.sql.SQLException; > import java.sql.Statement; > > public class MainDriver { > > > public static void main(String[] args) { > new MainDriver(); > } > > > public MainDriver() { > > //Buffers to read and write > byte[] writeBuffer = new byte[10]; > byte[] readBuffer = null; > for (int i = 1; i < 10; i++) { > writeBuffer[i] = (byte)i; > } > > //Database objects > Connection conn = null; > Statement stat = null; > PreparedStatement prep = null; > > //Load the database driver > try { > System.loadLibrary("sqlite"); > Class.forName("org.sqlite.JDBC"); > } catch (Exception e) { > System.err.println("Could not load sqlite library or > instantiate the > database driver."); > System.err.println(e); > e.printStackTrace(); > return; > } > > //Open a connection to the database > try { > conn = DriverManager.getConnection("jdbc:sqlite:" + > "file.db"); > } catch (SQLException e) { > System.err.println("Could not open a connection to the > database with name > file.db"); > System.err.println(e); > e.printStackTrace(); > return; > } > > //Create a table > try { > stat = conn.createStatement(); > stat.execute("CREATE TABLE TEST (model BLOB NOT > NULL)"); > stat.close(); > } catch (SQLException e) { > System.err.println("The table could not be created."); > System.err.println(e); > e.printStackTrace(); > return; > } > > //Write buffer into the database > try { > conn.setAutoCommit(false); > prep = conn.prepareStatement("INSERT INTO TEST (model) > VALUES(?)"); > prep.setBytes(1, writeBuffer); > prep.addBatch(); > prep.executeBatch(); > conn.setAutoCommit(true); > prep.close(); > } catch (SQLException e) { > System.err.println("The buffer could not be written to > the database."); > System.err.println(e); > e.printStackTrace(); > return; > } > > //Read buffer from the database > try { > stat = conn.createStatement(); > ResultSet rs = stat.executeQuery("SELECT * FROM TEST"); > readBuffer = rs.getBytes(1); > rs.close(); > stat.close(); > } catch (SQLException e) { > System.err.println("The buffer could not be read"); > System.err.println(e); > e.printStackTrace(); > } > > //Close the database > try { > conn.close(); > } catch (SQLException e) { > System.err.println("Database could not be closed"); > System.err.println(e); > e.printStackTrace(); > } > > //Print the buffers > System.out.print("Write buffer = "); > for (int i = 0; i < writeBuffer.length; i++) { > System.out.print(writeBuffer[i]); > } > System.out.println(); > System.out.print("Read buffer = "); > for (int i = 0; i < readBuffer.length; i++) { > System.out.print(readBuf
Re: [sqlite] Build instructions for Winodws with unicode support
Hello, I created the following sample code to test the BLOB data type. I am using sqlite-jdbc driver provided at (http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC). The following is the source code (SSCE). package org.sqlite; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class MainDriver { public static void main(String[] args) { new MainDriver(); } public MainDriver() { //Buffers to read and write byte[] writeBuffer = new byte[10]; byte[] readBuffer = null; for (int i = 1; i < 10; i++) { writeBuffer[i] = (byte)i; } //Database objects Connection conn = null; Statement stat = null; PreparedStatement prep = null; //Load the database driver try { System.loadLibrary("sqlite"); Class.forName("org.sqlite.JDBC"); } catch (Exception e) { System.err.println("Could not load sqlite library or instantiate the database driver."); System.err.println(e); e.printStackTrace(); return; } //Open a connection to the database try { conn = DriverManager.getConnection("jdbc:sqlite:" + "file.db"); } catch (SQLException e) { System.err.println("Could not open a connection to the database with name file.db"); System.err.println(e); e.printStackTrace(); return; } //Create a table try { stat = conn.createStatement(); stat.execute("CREATE TABLE TEST (model BLOB NOT NULL)"); stat.close(); } catch (SQLException e) { System.err.println("The table could not be created."); System.err.println(e); e.printStackTrace(); return; } //Write buffer into the database try { conn.setAutoCommit(false); prep = conn.prepareStatement("INSERT INTO TEST (model) VALUES(?)"); prep.setBytes(1, writeBuffer); prep.addBatch(); prep.executeBatch(); conn.setAutoCommit(true); prep.close(); } catch (SQLException e) { System.err.println("The buffer could not be written to the database."); System.err.println(e); e.printStackTrace(); return; } //Read buffer from the database try { stat = conn.createStatement(); ResultSet rs = stat.executeQuery("SELECT * FROM TEST"); readBuffer = rs.getBytes(1); rs.close(); stat.close(); } catch (SQLException e) { System.err.println("The buffer could not be read"); System.err.println(e); e.printStackTrace(); } //Close the database try { conn.close(); } catch (SQLException e) { System.err.println("Database could not be closed"); System.err.println(e); e.printStackTrace(); } //Print the buffers System.out.print("Write buffer = "); for (int i = 0; i < writeBuffer.length; i++) { System.out.print(writeBuffer[i]); } System.out.println(); System.out.print("Read buffer = "); for (int i = 0; i < readBuffer.length; i++) { System.out.print(readBuffer[i]); } System.out.println(); //Check the md5sum try { java.security.MessageDigest digest = java.security.MessageDigest.getInstance("MD5"); byte[] md5sum = null; java.math.BigInteger bigInt = null; //Write buffer digest.reset(); digest.update(writeBuffer); md5sum = digest.digest(); bi
Re: [sqlite] IF-THEN-ELSE sqlite
On Mon, Apr 4, 2011 at 12:26 PM, Simon Slavin wrote: > But he's combining two INSERTs into one. What I think he needs is first an > INSERT OR FAIL to possibly add a new person, and then an INSERT ... SELECT > which looks up that person's ID. I didn't see that in the original post. Did I miss it? But even so: INSERT ...; INSERT ... SELECT ... WHERE ... (SELECT EXISTS ); :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On 4 Apr 2011, at 6:01pm, Nico Williams wrote: > On Mon, Apr 4, 2011 at 11:25 AM, Simon Slavin wrote: >> Probably not. Don't try to turn SQL into a procedural language. Do the >> SELECT that tells you whether the record exists and gives you the >> information you need if it does, then do whatever INSERTs you need to do. > > I agree with the first part. I don't agree with the second. SQL _is_ > a programming language. If you resort to using a procedural language > to do the things you don't know how to do in SQL then you're not > really meeting the spirit of your first recommendation. This is why I > like the INSERT ... SELECT ... WHERE > idiom: it's declarative, > and it uses SQL as a programming language with less glue needed from > the host language. But he's combining two INSERTs into one. What I think he needs is first an INSERT OR FAIL to possibly add a new person, and then an INSERT ... SELECT which looks up that person's ID. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On Mon, Apr 4, 2011 at 11:25 AM, Simon Slavin wrote: > Probably not. Don't try to turn SQL into a procedural language. Do the > SELECT that tells you whether the record exists and gives you the information > you need if it does, then do whatever INSERTs you need to do. I agree with the first part. I don't agree with the second. SQL _is_ a programming language. If you resort to using a procedural language to do the things you don't know how to do in SQL then you're not really meeting the spirit of your first recommendation. This is why I like the INSERT ... SELECT ... WHERE idiom: it's declarative, and it uses SQL as a programming language with less glue needed from the host language. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Build instructions for Winodws with unicode support
> I can create the dll, but it does not work with BLOB data type. It works > with other data types. The dll that I downloaded from the sqlite.org website > works with BLOB data type. > > Any help would be appreciated. Any pointers on what doesn't work for you and how it works instead of intended behavior would be appreciated too. Pavel On Mon, Apr 4, 2011 at 11:32 AM, tiwaris wrote: > > Hello, > > I need build instructions for Windows with unicode support. > > I am using sqlite3.h and sqlite3.c from the amalgamation source downloaded > from http://sqlite.org/sqlite-amalgamation-3070500.zip. > > I am using the following CFLAGS when compiling sqlite > > SQLITE_ENABLE_COLUMN_METADATA > SQLITE_ENABLE_FTS3 > SQLITE_THREADSAFE=1 > SQLITE_ENABLE_ICU > > I am linking sqlite with icuuc.lib and icuin.lib unicode libraries to create > a sqlite dll. > > I can create the dll, but it does not work with BLOB data type. It works > with other data types. The dll that I downloaded from the sqlite.org website > works with BLOB data type. > > Any help would be appreciated. > > Thanks. > > > > > > > -- > View this message in context: > http://old.nabble.com/Build-instructions-for-Winodws-with-unicode-support-tp31315626p31315626.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > 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] IF-THEN-ELSE sqlite
On 4 Apr 2011, at 4:26pm, Marcelo Serrano Zanetti wrote: > About the "new" item, yes I do compare some specific fields and not the > primary key. For example name and surname of a person ... I look in the > database for such a person ... if yes I return her id ... if not I > include her and then return her id ... so I thought it would be more > efficient to construct a single query that does all the job avoiding > multiple queries. Probably not. Don't try to turn SQL into a procedural language. Do the SELECT that tells you whether the record exists and gives you the information you need if it does, then do whatever INSERTs you need to do. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Build instructions for Winodws with unicode support
Hello, I need build instructions for Windows with unicode support. I am using sqlite3.h and sqlite3.c from the amalgamation source downloaded from http://sqlite.org/sqlite-amalgamation-3070500.zip. I am using the following CFLAGS when compiling sqlite SQLITE_ENABLE_COLUMN_METADATA SQLITE_ENABLE_FTS3 SQLITE_THREADSAFE=1 SQLITE_ENABLE_ICU I am linking sqlite with icuuc.lib and icuin.lib unicode libraries to create a sqlite dll. I can create the dll, but it does not work with BLOB data type. It works with other data types. The dll that I downloaded from the sqlite.org website works with BLOB data type. Any help would be appreciated. Thanks. -- View this message in context: http://old.nabble.com/Build-instructions-for-Winodws-with-unicode-support-tp31315626p31315626.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On 04/04/2011 05:16 PM, Robert Poor wrote: > @Marcelo: > > Going back to your original question: do you really only want to > insert one item at a time? If so, I think your question has been > answered reasonably well. > > BUT: if you have a large number of items, and you want to insert items > that aren't yet in the table, then you can do it efficiently in a > single query. Check the documentation for "INSERT OR IGNORE", i.e. > > http://sqlite.org/lang_insert.html > > Also, you say a new item is to be "inserted only if this item is not > yet in that table", but you haven't described how you discriminate a > new item from an existing item. Obviously you are not comparing > primary keys (since the new item won't have a primary key) -- do you > mean to compare all of the other fields? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Thx for answering Robert, In principle I want to include one at a time ... because I am parsing web sites. So after each parsing I insert the results in the database. About the "new" item, yes I do compare some specific fields and not the primary key. For example name and surname of a person ... I look in the database for such a person ... if yes I return her id ... if not I include her and then return her id ... so I thought it would be more efficient to construct a single query that does all the job avoiding multiple queries. I need that id to process other tables that is why I am doing one at a time. best -- Marcelo S Zanetti ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
@Marcelo: Going back to your original question: do you really only want to insert one item at a time? If so, I think your question has been answered reasonably well. BUT: if you have a large number of items, and you want to insert items that aren't yet in the table, then you can do it efficiently in a single query. Check the documentation for "INSERT OR IGNORE", i.e. http://sqlite.org/lang_insert.html Also, you say a new item is to be "inserted only if this item is not yet in that table", but you haven't described how you discriminate a new item from an existing item. Obviously you are not comparing primary keys (since the new item won't have a primary key) -- do you mean to compare all of the other fields? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
2011/4/4 Nico Williams : > You're missing something: FTS4 is a virtual table In triggers code we can't know about virtual tables! So in triggers we may get wrong results?.. As example (this _does not_ work now): CREATE TRIGGER view_job_update instead of update on view_job begin insert into job_record (record_version,user_id,id,users,routes,forms,ts_from,ts_to,name,state) values (OLD.record_version+1, NEW.user_id, OLD.id, NEW.users, NEW.routes, NEW.forms, NEW.ts_from, NEW.ts_to, NEW.name, NEW.state); insert into job (id,current_id,ts,ts_from,ts_to,name) select id,rowid,ts,ts_from,ts_to,name from job_record where rowid=last_insert_rowid(); delete from job_fts where rowid=last_insert_rowid(); insert into job_fts (rowid,задание,дата,автор,состояние) select id,name,date('now','localtime'),user,state from view_job where id=last_insert_rowid(); end; -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
On Mon, Apr 4, 2011 at 9:10 AM, Enrico Thierbach wrote: > I might have an exceptionally dumb day, but this sequence (from this post > http://www.mail-archive.com/sqlite-users@sqlite.org/msg34082.html ) looks > totally fine: You're missing something: FTS4 is a virtual table and it re-enters SQLite3 in the same connection as it was invoked in, which means that if FTS4 does any additional INSERTs on behalf of an INSERT into an FTS4 table THEN SQLite3 returns the right last insert rowid for the wrong INSERT. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
On 04.04.2011, at 15:59, Simon Slavin wrote: > > On 4 Apr 2011, at 2:48pm, Enrico Thierbach wrote: > >> isn't last_insert_rowid defined as the ID of the last row inserted > > Yes it is. But the FTS system does what it does by maintaining extra tables, > and doing extra operations to them besides the ones that the programmer has > asked for. Naturally this means that several things get done after each > INSERT operation the programmer asked for, so last_insert_rowid() may as well > be a random number. > I might have an exceptionally dumb day, but this sequence (from this post http://www.mail-archive.com/sqlite-users@sqlite.org/msg34082.html ) looks totally fine: > insert into one (value) values ("hello1"); > select last_insert_rowid(); -- returns 1 > insert into one (value) values ("hello2"); > select last_insert_rowid(); -- returns 2 > update one set value="hello3" where id=1; > select last_insert_rowid(); -- returns 3, but should return 2 until the update (assuming that the IDs reported are right, of course). The last one would not be a problem in my understanding, as last_insert_rowid() gets undefined by the UPDATE anyways. Or do I miss a point? On the other hand, http://www.sqlite.org/c3ref/last_insert_rowid.html defines the last insert rowid as "This routine returns the rowid of the most recent successful INSERT into the database from the database connection in the first argument. If no successful INSERTs have ever occurred on that database connection, zero is returned." As this doesn't mention non-INSERT changes I tend to see my understanding as wrong :) /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
On 04.04.2011, at 15:59, Simon Slavin wrote: > > On 4 Apr 2011, at 2:48pm, Enrico Thierbach wrote: > >> isn't last_insert_rowid defined as the ID of the last row inserted > > Yes it is. But the FTS system does what it does by maintaining extra tables, > and doing extra operations to them besides the ones that the programmer has > asked for. Naturally this means that several things get done after each > INSERT operation the programmer asked for, so last_insert_rowid() may as well > be a random number. > I might have an exceptionally dumb day, but this sequence (from this post http://www.mail-archive.com/sqlite-users@sqlite.org/msg34082.html ) looks totally fine: > insert into one (value) values ("hello1"); > select last_insert_rowid(); -- returns 1 > insert into one (value) values ("hello2"); > select last_insert_rowid(); -- returns 2 > update one set value="hello3" where id=1; > select last_insert_rowid(); -- returns 3, but should return 2 until the update (assuming that the IDs reported are right, of course). The last one would be a problem, as last_insert_rowid() gets undefined in my understanding by the UPDATE anyways. Or do I miss a point? /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
On 4 Apr 2011, at 2:48pm, Enrico Thierbach wrote: > isn't last_insert_rowid defined as the ID of the last row inserted Yes it is. But the FTS system does what it does by maintaining extra tables, and doing extra operations to them besides the ones that the programmer has asked for. Naturally this means that several things get done after each INSERT operation the programmer asked for, so last_insert_rowid() may as well be a random number. Just get used to the idea that last_insert_rowid() doesn't work for FTS tables and won't do unless someone rewrites FTS with that specifically in mind. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?
> From: contactcolincuthb...@hotmail.com > To: sqlite-users@sqlite.org > Date: Mon, 4 Apr 2011 13:12:02 + > Subject: Re: [sqlite] Question:how to insert row with multiple values from > same field of different rows of another table? > > > > > > From: punk.k...@gmail.com > > Date: Mon, 4 Apr 2011 08:06:31 -0500 > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] Question:how to insert row with multiple values from > > same field of different rows of another table? > > > > > > On Apr 4, 2011, at 7:59 AM, Colin Cuthbert wrote: > > > > > > > > > > > > > >> From: punk.k...@gmail.com > > >> Date: Sun, 3 Apr 2011 07:52:42 -0500 > > >> To: sqlite-users@sqlite.org > > >> Subject: Re: [sqlite] Question:how to insert row with multiple values > > >> from same field of different rows of another table? > > >> > > >> > > >> On Apr 3, 2011, at 7:50 AM, Luuk wrote: > > >> > > >>> On 03-04-2011 14:43, Colin Cuthbert wrote: > > First time I've used this (or any!) mailing list, so sorry if I've > > done something wrong. > > > > Pretty sure my question (in the subect) is phrased badly but it's the > > best I could do! > > > > create table People(id integer primary key, name text); > > insert into People (name) values ('bob'); > > insert into People (name) values ('fred'); > > > > create table Cars(id integer primary key, name text); > > insert into Cars (name) values ('ford'); > > insert into Cars (name) values ('volvo'); > > > > create table CarOwners(id integer primary key, carId integer > > references Cars(id), ownerId integer references People(id)); > > insert into CarOwners (carId, ownerId) select Cars.id, People.id from > > Cars, People where Cars.name='ford' and People.name='bob'; > > > > create table Couples(id integer primary key, personId1 integer > > references People(id), personId2 integer references People(id)); > > > > The last 'insert' statement seems to work for inserting a row into the > > 'CarOwners' table, but I'm not sure that's the right/best way to do it. > > > > But how can I do a similar insert into the 'Couples' table? ie, how > > can I insert a row (specifying 'personId1' and 'personId2' via queries > > based on 'People.name') into the 'Couples' table? > > >>> > > >>> You forgot to define 'Couples'. > > Does it start something like this? > > > > insert into Couples (personId1, personId2) select id, id from People > > where... > > >>> > > >>> select id, id from People will return the same id (from the same record) > > >>> twice > > >>> > > >>> somehting like: > > >>> select a.id, b.id from People a join People b on a.id<>b.id > > >>> will give other results, but what youactually want to be returned > > >>> depends on the definition of a 'Couple'... > > >>> > > >> > > >> You also want to do all of the above in a TRANSACTION, preferably with a > > >> TRIGGER, to ensure the correct relationships are preserved. > > > > > > Ok I looked into transactions (I'm new to sql!). Isn't a transaction > > > automatically created with the insert statement? Or are you saying I > > > need to explicitly begin/end one as part of the solution to my problem? > > > > > > Yes, you need explicit BEGIN/END to perform a transaction. > > > > > > > > And regarding a trigger to ensure the correct relationships are > > > preserved... yeah you're right, but that's another issue isn't it? Or is > > > it related to this issue in a way that I'm not seeing? > > > > Another, but related issue. > > > > Regarding your original problem, the following works > > > > INSERT INTO Couples (personId1, personId2) VALUES ((SELECT id FROM People > > WHERE name = 'bob'), (SELECT id FROM People WHERE name = 'fred')); > > sqlite> SELECT * FROM Couples; > > id personId1 personId2 > > -- -- -- > > 1 1 2 > > > > Ha! You're brilliant! Thank you :) > > I think I tried that but without the parentheses around the individual select > statements... would that have made a difference? Or maybe I didn't try that > at all :) Either way, thanks again! > Discovered that this works too: insert into Couples (personId1, personId2) select p1.id, p2.id from People p1, People p2 where p1.name='fred' and p2.name='bob'; ...and I think I prefer that but stil, thanks for the help! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
Hi Nico, this >> Is this really a bug? I at least wouldn't expect last_insert_rowid to be >> constant if the database gets modified. > was more a question for sake of my understanding. Both in the post Simon referred to > insert into one (value) values ("hello1"); > select last_insert_rowid(); -- returns 1 > insert into one (value) values ("hello2"); > select last_insert_rowid(); -- returns 2 > update one set value="hello3" where id=1; > select last_insert_rowid(); -- returns 3, but should return 2 and in the OP's post > > sqlite> CREATE VIRTUAL TABLE fts USING fts4(a,TOKENIZE icu russian); > sqlite> select last_insert_rowid(); > 0 > sqlite> insert into fts(a) values ('test'); > sqlite> insert into fts(a) values ('test'); > sqlite> select last_insert_rowid(); > 2 > sqlite> delete from fts where rowid=2; > sqlite> select last_insert_rowid(); > 3 there is some some change to the database after the last insert, and it is this change that apparently changes the last_insert_rowid. And as I said: neither would I expect it to change nor would I expect it not to change; I just wouldn't expect anything here. But there might be something in the SQL specs or somewhere else that states otherwise. > > INSTEAD OF triggers that don't actually insert anything? Then there's > the re-entrance issue we have in this case. I tend to thing that > last_insert_rowid() is best avoided because it's an optimization (no > need to run a query to find what your last statement did) that is not > needed if you manage your primary keys directly (i.e., don't rely on > the RDBMS to do autoincrement or any other form of primary key > allocation). I don't see the re-entrance issue: isn't last_insert_rowid defined as the ID of the last row inserted *within the current connection*? (And, as my understanding, only valid directly after the INSERT). For your avoidance of last_insert_rowid at all: do you always generate the primary key client-side? How do you guarantee uniqueness then? BTW: last_insert_rowid is not necessarily a query, as it is already reported to the client by the C-interface. /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?
> From: punk.k...@gmail.com > Date: Mon, 4 Apr 2011 08:06:31 -0500 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Question:how to insert row with multiple values from > same field of different rows of another table? > > > On Apr 4, 2011, at 7:59 AM, Colin Cuthbert wrote: > > > > > > > > >> From: punk.k...@gmail.com > >> Date: Sun, 3 Apr 2011 07:52:42 -0500 > >> To: sqlite-users@sqlite.org > >> Subject: Re: [sqlite] Question:how to insert row with multiple values from > >> same field of different rows of another table? > >> > >> > >> On Apr 3, 2011, at 7:50 AM, Luuk wrote: > >> > >>> On 03-04-2011 14:43, Colin Cuthbert wrote: > First time I've used this (or any!) mailing list, so sorry if I've done > something wrong. > > Pretty sure my question (in the subect) is phrased badly but it's the > best I could do! > > create table People(id integer primary key, name text); > insert into People (name) values ('bob'); > insert into People (name) values ('fred'); > > create table Cars(id integer primary key, name text); > insert into Cars (name) values ('ford'); > insert into Cars (name) values ('volvo'); > > create table CarOwners(id integer primary key, carId integer references > Cars(id), ownerId integer references People(id)); > insert into CarOwners (carId, ownerId) select Cars.id, People.id from > Cars, People where Cars.name='ford' and People.name='bob'; > > create table Couples(id integer primary key, personId1 integer > references People(id), personId2 integer references People(id)); > > The last 'insert' statement seems to work for inserting a row into the > 'CarOwners' table, but I'm not sure that's the right/best way to do it. > > But how can I do a similar insert into the 'Couples' table? ie, how can > I insert a row (specifying 'personId1' and 'personId2' via queries based > on 'People.name') into the 'Couples' table? > >>> > >>> You forgot to define 'Couples'. > Does it start something like this? > > insert into Couples (personId1, personId2) select id, id from People > where... > >>> > >>> select id, id from People will return the same id (from the same record) > >>> twice > >>> > >>> somehting like: > >>> select a.id, b.id from People a join People b on a.id<>b.id > >>> will give other results, but what youactually want to be returned > >>> depends on the definition of a 'Couple'... > >>> > >> > >> You also want to do all of the above in a TRANSACTION, preferably with a > >> TRIGGER, to ensure the correct relationships are preserved. > > > > Ok I looked into transactions (I'm new to sql!). Isn't a transaction > > automatically created with the insert statement? Or are you saying I need > > to explicitly begin/end one as part of the solution to my problem? > > > Yes, you need explicit BEGIN/END to perform a transaction. > > > > > And regarding a trigger to ensure the correct relationships are > > preserved... yeah you're right, but that's another issue isn't it? Or is > > it related to this issue in a way that I'm not seeing? > > Another, but related issue. > > Regarding your original problem, the following works > > INSERT INTO Couples (personId1, personId2) VALUES ((SELECT id FROM People > WHERE name = 'bob'), (SELECT id FROM People WHERE name = 'fred')); > sqlite> SELECT * FROM Couples; > id personId1 personId2 > -- -- -- > 1 1 2 > Ha! You're brilliant! Thank you :) I think I tried that but without the parentheses around the individual select statements... would that have made a difference? Or maybe I didn't try that at all :) Either way, thanks again! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
On Mon, Apr 4, 2011 at 6:43 AM, Enrico Thierbach wrote: > Is this really a bug? I at least wouldn't expect last_insert_rowid to be > constant if the database gets modified. If you read the post that Simon referenced you'll see that the caller typically wants to know the row ID of the last row explicitly inserted by the caller. OK, that was implied in that post. However, there are oddities w.r.t. this function. What of INSERTs into VIEWs with INSTEAD OF triggers that don't actually insert anything? Then there's the re-entrance issue we have in this case. I tend to thing that last_insert_rowid() is best avoided because it's an optimization (no need to run a query to find what your last statement did) that is not needed if you manage your primary keys directly (i.e., don't rely on the RDBMS to do autoincrement or any other form of primary key allocation). In any case, last_insert_rowid() seems particularly difficult to get right, though D. R. Hipp seems to be getting at a reasonable implementation and semantics: last_insert_rowid() returns the row ID of the first row inserted by the last statement other than the currently executing one at the same level of re-entrance. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?
On Apr 4, 2011, at 7:59 AM, Colin Cuthbert wrote: > > > >> From: punk.k...@gmail.com >> Date: Sun, 3 Apr 2011 07:52:42 -0500 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Question:how to insert row with multiple values from >> same field of different rows of another table? >> >> >> On Apr 3, 2011, at 7:50 AM, Luuk wrote: >> >>> On 03-04-2011 14:43, Colin Cuthbert wrote: First time I've used this (or any!) mailing list, so sorry if I've done something wrong. Pretty sure my question (in the subect) is phrased badly but it's the best I could do! create table People(id integer primary key, name text); insert into People (name) values ('bob'); insert into People (name) values ('fred'); create table Cars(id integer primary key, name text); insert into Cars (name) values ('ford'); insert into Cars (name) values ('volvo'); create table CarOwners(id integer primary key, carId integer references Cars(id), ownerId integer references People(id)); insert into CarOwners (carId, ownerId) select Cars.id, People.id from Cars, People where Cars.name='ford' and People.name='bob'; create table Couples(id integer primary key, personId1 integer references People(id), personId2 integer references People(id)); The last 'insert' statement seems to work for inserting a row into the 'CarOwners' table, but I'm not sure that's the right/best way to do it. But how can I do a similar insert into the 'Couples' table? ie, how can I insert a row (specifying 'personId1' and 'personId2' via queries based on 'People.name') into the 'Couples' table? >>> >>> You forgot to define 'Couples'. Does it start something like this? insert into Couples (personId1, personId2) select id, id from People where... >>> >>> select id, id from People will return the same id (from the same record) >>> twice >>> >>> somehting like: >>> select a.id, b.id from People a join People b on a.id<>b.id >>> will give other results, but what youactually want to be returned >>> depends on the definition of a 'Couple'... >>> >> >> You also want to do all of the above in a TRANSACTION, preferably with a >> TRIGGER, to ensure the correct relationships are preserved. > > Ok I looked into transactions (I'm new to sql!). Isn't a transaction > automatically created with the insert statement? Or are you saying I need to > explicitly begin/end one as part of the solution to my problem? Yes, you need explicit BEGIN/END to perform a transaction. > > And regarding a trigger to ensure the correct relationships are preserved... > yeah you're right, but that's another issue isn't it? Or is it related to > this issue in a way that I'm not seeing? Another, but related issue. Regarding your original problem, the following works INSERT INTO Couples (personId1, personId2) VALUES ((SELECT id FROM People WHERE name = 'bob'), (SELECT id FROM People WHERE name = 'fred')); sqlite> SELECT * FROM Couples; id personId1 personId2 -- -- -- 1 1 2 > > Thanks. > > > ___ > 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] Question:how to insert row with multiple values from same field of different rows of another table?
> From: punk.k...@gmail.com > Date: Sun, 3 Apr 2011 07:52:42 -0500 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Question:how to insert row with multiple values from > same field of different rows of another table? > > > On Apr 3, 2011, at 7:50 AM, Luuk wrote: > > > On 03-04-2011 14:43, Colin Cuthbert wrote: > >> First time I've used this (or any!) mailing list, so sorry if I've done > >> something wrong. > >> > >> Pretty sure my question (in the subect) is phrased badly but it's the best > >> I could do! > >> > >> create table People(id integer primary key, name text); > >> insert into People (name) values ('bob'); > >> insert into People (name) values ('fred'); > >> > >> create table Cars(id integer primary key, name text); > >> insert into Cars (name) values ('ford'); > >> insert into Cars (name) values ('volvo'); > >> > >> create table CarOwners(id integer primary key, carId integer references > >> Cars(id), ownerId integer references People(id)); > >> insert into CarOwners (carId, ownerId) select Cars.id, People.id from > >> Cars, People where Cars.name='ford' and People.name='bob'; > >> > >> create table Couples(id integer primary key, personId1 integer references > >> People(id), personId2 integer references People(id)); > >> > >> The last 'insert' statement seems to work for inserting a row into the > >> 'CarOwners' table, but I'm not sure that's the right/best way to do it. > >> > >> But how can I do a similar insert into the 'Couples' table? ie, how can I > >> insert a row (specifying 'personId1' and 'personId2' via queries based on > >> 'People.name') into the 'Couples' table? > > > > You forgot to define 'Couples'. > >> Does it start something like this? > >> > >> insert into Couples (personId1, personId2) select id, id from People > >> where... > > > > select id, id from People will return the same id (from the same record) > > twice > > > > somehting like: > > select a.id, b.id from People a join People b on a.id<>b.id > > will give other results, but what youactually want to be returned > > depends on the definition of a 'Couple'... > > > > You also want to do all of the above in a TRANSACTION, preferably with a > TRIGGER, to ensure the correct relationships are preserved. Ok I looked into transactions (I'm new to sql!). Isn't a transaction automatically created with the insert statement? Or are you saying I need to explicitly begin/end one as part of the solution to my problem? And regarding a trigger to ensure the correct relationships are preserved... yeah you're right, but that's another issue isn't it? Or is it related to this issue in a way that I'm not seeing? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] enable spatiaLite extension
Hi. I want to use spatiaLite extension in a php-Project. But i am not able to loaad it. I hope somebody could help me. I am using SQLite in this project as database. I have added: sqlite3.extension_dir = "/usr/local/spatiaLite"" ...to the php.ini and restarted apache. Now I can find exactly this entry in php_info(). I am using openSuse with apache2. SQLite 3.6.4 is installed. I have not found an update for SQLite in yast. Of course, I have also copied "libspatialite.so" to: "/usr/local/spatiaLite/libspatialite.so". I thought everything is correct, but when I try: --- $db->loadExtension('libspatialite.so') --- ...apache tells me: "PHP Fatal error: Call to undefined method SQLite3::loadExtension() in". I don't know why, because, this is exactly, what is told here: http://php.net/manual/de/sqlite3.loadextension.php I have read something about: http://www.sqlite.org/c3ref/enable_load_extension.html ...but don't know how to enable it. Maybe this is the reason, why loadExtension() is not found? I know, maybe it is an php problem, but I don't know how to solve it and did not find any help in any php doc / Forum. I hope somebody of the SQLite community could help me. Thanks a lot, Stephan RapidEye AG Molkenmarkt 30 14776 Brandenburg an der Havel Germany Follow us on Twitter! www.twitter.com/rapideye_ag Head Office/Sitz der Gesellschaft: Brandenburg an der Havel Management Board/Vorstand: Wolfgang G. Biedermann, Frederik Jung-Rothenhaeusler Chairman of Supervisory Board/Vorsitzender des Aufsichtsrates: Juergen Breitkopf Commercial Register/Handelsregister Potsdam HRB 17 796 Tax Number/Steuernummer: 048/100/00053 VAT-Ident-Number/Ust.-ID: DE 199331235 DIN EN ISO 9001 certified * Diese E-Mail enthaelt vertrauliche und/oder rechtlich geschuetzte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet. The information in this e-mail is intended for the named recipients only. It may contain privileged and confidential information. If you have received this communication in error, any use, copying or dissemination of its contents is strictly prohibited. Please erase all copies of the message along with any included attachments and notify RapidEye AG or the sender immediately by telephone at the number indicated on this page. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?
> Date: Sun, 3 Apr 2011 14:50:30 +0200 > From: luu...@gmail.com > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Question:how to insert row with multiple values from > same field of different rows of another table? > > On 03-04-2011 14:43, Colin Cuthbert wrote: > > First time I've used this (or any!) mailing list, so sorry if I've done > > something wrong. > > > > Pretty sure my question (in the subect) is phrased badly but it's the best > > I could do! > > > > create table People(id integer primary key, name text); > > insert into People (name) values ('bob'); > > insert into People (name) values ('fred'); > > > > create table Cars(id integer primary key, name text); > > insert into Cars (name) values ('ford'); > > insert into Cars (name) values ('volvo'); > > > > create table CarOwners(id integer primary key, carId integer references > > Cars(id), ownerId integer references People(id)); > > insert into CarOwners (carId, ownerId) select Cars.id, People.id from Cars, > > People where Cars.name='ford' and People.name='bob'; > > > > create table Couples(id integer primary key, personId1 integer references > > People(id), personId2 integer references People(id)); > > > > The last 'insert' statement seems to work for inserting a row into the > > 'CarOwners' table, but I'm not sure that's the right/best way to do it. > > > > But how can I do a similar insert into the 'Couples' table? ie, how can I > > insert a row (specifying 'personId1' and 'personId2' via queries based on > > 'People.name') into the 'Couples' table? > > You forgot to define 'Couples'. > > Does it start something like this? > > > > insert into Couples (personId1, personId2) select id, id from People > > where... > > select id, id from People will return the same id (from the same record) > twice > > somehting like: > select a.id, b.id from People a join People b on a.id<>b.id > will give other results, but what youactually want to be returned > depends on the definition of a 'Couple'... > > ;) Sorry for not being clear. The row I want to insert into the Couples table would contain, for example: personId1 = bob's id (ie the 'id' member of the 'People'-table-row whose 'name' member is 'bob') personId2 = fred's id (ie the 'id' member of the 'People'-table-row whose 'name' member is 'fred') So what I want to do is search the People table to find the id's of bob and fred and add a row to the Couples table containing those id's all in one insert statement. Is that clearer? Or possible? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
Is this really a bug? I at least wouldn't expect last_insert_rowid to be constant if the database gets modified. /eno On 04.04.2011, at 13:28, Alexey Pechnikov wrote: > $ sqlite3 > SQLite version 3.7.6 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> CREATE VIRTUAL TABLE fts USING fts4(a,TOKENIZE icu russian); > sqlite> select last_insert_rowid(); > 0 > sqlite> insert into fts(a) values ('test'); > sqlite> insert into fts(a) values ('test'); > sqlite> select last_insert_rowid(); > 2 > sqlite> delete from fts where rowid=2; > sqlite> select last_insert_rowid(); > 3 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
On 4 Apr 2011, at 12:28pm, Alexey Pechnikov wrote: > sqlite> delete from fts where rowid=2; > sqlite> select last_insert_rowid(); > 3 See http://www.mail-archive.com/sqlite-users@sqlite.org/msg34082.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with changing the code
On 4 Apr 2011, at 11:53am, thilo wrote: > On 4/4/2011 2:05 AM, Guilherme Bamepe wrote: >> Hi! >> >> I'm new in SQLite, and I'm studying it to do a work in my college, >> and would be helpful if I get the SQLite to print, while executing >> the sql, after each table scan or join, the name of the table and >> number of rows that are going to the next operator... for example.. >> the following sql: select * from student join college on student.id >> = college.id where student.age = 20; would print: After table >> student scan - rows = 2324; After 1st join - rows = 200; > Have you tried "explain query plan" > http://www.sqlite.org/lang_explain.html > or the trace api: http://www.sqlite.org/c3ref/profile.html ? Are you trying to dissect the inner workings of SQLite in a way that's specific to SQLite, or do you simply want to know information about your data ? How would you expect to get the details when a SELECT has multiple JOINs, and what would you do with the information if you could get it ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS4 bug in last_insert_rowid()
$ sqlite3 SQLite version 3.7.6 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE VIRTUAL TABLE fts USING fts4(a,TOKENIZE icu russian); sqlite> select last_insert_rowid(); 0 sqlite> insert into fts(a) values ('test'); sqlite> insert into fts(a) values ('test'); sqlite> select last_insert_rowid(); 2 sqlite> delete from fts where rowid=2; sqlite> select last_insert_rowid(); 3 -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with changing the code
On 4/4/2011 2:05 AM, Guilherme Bamepe wrote: > Hi! > > I'm new in SQLite, and I'm studying it to do a work in my college, > and would be helpful if I get the SQLite to print, while executing > the sql, after each table scan or join, the name of the table and > number of rows that are going to the next operator... for example.. > the following sql: select * from student join college on student.id > = college.id where student.age = 20; would print: After table > student scan - rows = 2324; After 1st join - rows = 200; Have you tried "explain query plan" http://www.sqlite.org/lang_explain.html or the trace api: http://www.sqlite.org/c3ref/profile.html ? thilo > > Is there anything like this in sqlite that I could use? If not, is > it too difficult change the code? Could anyone give me some simple > code examples? > > > Thanks!! > > > > ___ sqlite-users > mailing list sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Dipl. Ing. Thilo Jeremias Zur Rabenwiese 14 27239 Twistringen T: +49 15782492240 T: +49 4243941633 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Import data in SQLite from excel using C# code
On 4 Apr 2011, at 7:12am, Deepti Marathe wrote: > From what I see in the examples in libxml, I can read an excel file in my > program, but I still cannot figure out how to upload that file(the data in > that file) to a table in SQlite. Use the data from that table to create the appropriate 'INSERT' commands. http://www.sqlite.org/lang_insert.html PS: When posting, please add your own new text after the text you're replying to. That way we know what you're talking about. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On 04/03/2011 09:05 PM, Petite Abeille wrote: > On Apr 3, 2011, at 3:18 PM, Marcelo Serrano Zanetti wrote: > >> It does not work in this way ... could somebody tell me please what is >> the correct sintax or whether this is possible at all. > As mentioned, SQL is not a procedural language, so, no. > > That said, you can achieve the same effect with two SQL statements called in > succession: > > (1) insert or ignore into table( item ) values( new ) [1] > (2) select itemID from table where item = new > > In other words, always try to create the new item, then select it. > > [1] http://www.sqlite.org/lang_insert.html > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users thx -- Marcelo S Zanetti __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users