Re: [sqlite] insert statement using temp variable

[venkat easwar]

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, );
rc = sqlite3_exec(db, a, 0, 0, );

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, );
rc = sqlite3_exec(db, "insert into emp values(i);", 0, 0, );

---

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

[RAKESH HEMRAJANI]

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, );
rc = sqlite3_exec(db, "insert into emp values(i);", 0, 0, );

---

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

[Pavel Ivanov]

> 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

[Ricky Huang]

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

[Simon Slavin]

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

[Gert Van Assche]

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

[Pavel Ivanov]

> 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++) {
>                        

Re: [sqlite] Build instructions for Winodws with unicode support

[tiwaris]

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();

Re: [sqlite] IF-THEN-ELSE sqlite

[Nico Williams]

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

[Simon Slavin]

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

[Nico Williams]

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

[Pavel Ivanov]

> 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

[Simon Slavin]

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

[tiwaris]

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

[Marcelo Serrano Zanetti]

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

[Robert Poor]

@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()

[Alexey Pechnikov]

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()

[Nico Williams]

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()

[Enrico Thierbach]

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()

[Enrico Thierbach]

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()

[Simon Slavin]

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?

[Colin Cuthbert]

> 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()

[Enrico Thierbach]

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?

[Colin Cuthbert]

> 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()

[Nico Williams]

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?

[Mr. Puneet Kishor]

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?

[Colin Cuthbert]

> 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

[Stephan-Alexander Meyer]

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?

[Colin Cuthbert]

> 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()

[Enrico Thierbach]

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()

[Simon Slavin]

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

[Simon Slavin]

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()

[Alexey Pechnikov]

$ 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

[thilo]

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

[Simon Slavin]

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

[Marcelo Serrano Zanetti]

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

Re: [sqlite] Import data in SQLite from excel using C# code

[Deepti Marathe]

>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. 


Mohd Radzi Ibrahim wrote:
> 
> Hi,
> Try use www.libxl.com that has C/C++/C#/Delphi interface to read excel
> files. The rest are just normal sqlite commands.
> 
> 
> On 28-Mar-2011, at 9:17 AM, Deepti Marathe wrote:
> 
>> 
>> Hi, 
>> 
>> I am new to SQLite and am using it for the first time. I need to create
>> an
>> application using C# that will export the data from EXCEL to SQLite. 
>> Please
>> can anybody guide me.
>> Thanks in advance!
>> 
>> -- 
>> View this message in context:
>> http://old.nabble.com/Import-data-in-SQLite-from-excel-using-C--code-tp31253267p31253267.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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Import-data-in-SQLite-from-excel-using-C--code-tp31253267p31311644.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