Re: [sqlite] Insert statement

[Keith Medcalf]

Or, if you are binding the values you can always do something like (a single 
statement):

insert or ignore into table (val) values (:val); 
select id from table where val = :val;

Where you bind you long val string value to the parameter named "val".  This 
has the advantage that you only bind (pass in) the long string once, and you 
will always get back the id to use whether the value had to be inserted or not.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of James K. Lowden
> Sent: Friday, 13 September, 2013 20:20
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Insert statement
> 
> On Thu, 12 Sep 2013 18:15:29 +
> "Joseph L. Casale" <jcas...@activenetwerx.com> wrote:
> 
> > > If you make val unique -- and I see no reason not to -- then you
> > > can select the id for every val you insert with "where val =
> >
> > I omitted the fact that val in table_a is unique.
> 
> Ah, that will be very helpful.
> 
> > Sending one large statement in this case would bypass the overhead,
> > but using val as the reference would make the string very long. That
> > text data might be several thousand chars long.
> 
> So, the integer is a proxy for a giant unique string.  OK, I might
> have done the same thing.
> 
> In principle, because the text is unique, you can find the id with
> 
>   select id where val = 'giant string'
> 
> and that might be fine.  If it's not fine --if it's too slow or
> unwieldy -- you might consider computing, say, an MD5 has of the giant
> string and adding that as a unique column instead of the integer
> primary key.
> 
> --jkl
> ___
> 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] Insert statement

[James K. Lowden]

On Thu, 12 Sep 2013 18:15:29 +
"Joseph L. Casale"  wrote:

> > If you make val unique -- and I see no reason not to -- then you
> > can select the id for every val you insert with "where val =
>
> I omitted the fact that val in table_a is unique. 

Ah, that will be very helpful.  

> Sending one large statement in this case would bypass the overhead,
> but using val as the reference would make the string very long. That
> text data might be several thousand chars long. 

So, the integer is a proxy for a giant unique string.  OK, I might
have done the same thing.  

In principle, because the text is unique, you can find the id with 

select id where val = 'giant string'

and that might be fine.  If it's not fine --if it's too slow or
unwieldy -- you might consider computing, say, an MD5 has of the giant
string and adding that as a unique column instead of the integer
primary key.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[David King]

> What is the most efficient way to insert several records into a table which
> has a fk ref to the auto incrementing pk of another insert I need to do in the
> same statement.

Without knowing too much about your application, I'd say that it's usually fine 
to just:

1. Do the INSERT
2. Get the last_insert_rowid()
3. Do your dependent INSERT with that ID.

Usually the reason people want to combine steps #1 and #2 is that there is 
network latency in between or lock contention some other cost to separating 
them. But sqlite doesn't have that, your requests don't go over a network, it's 
all just in your process space.

Is there another reason that you want to combine these steps?



signature.asc
Description: Message signed with OpenPGP using GPGMail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[Joseph L. Casale]

> Yes, that's what I suspected.  Because your table_a has no natural key, you 
> have
> no good way to select the auto-generated id value.  You can find out what the 
> last
> auto-generated value was, which lets you work a row at a time,  but you're 
> really
> suffering from a poor design choice.  
>
> If you make val unique -- and I see no reason not to -- then you can select 
> the id for
> every val you insert with "where val = 'value' ". 

Hi James,
Thanks for the follow up. I am certainly open to critique and although this is 
working I
would rather have it right. I realize I omitted the fact that val in table_a is 
unique. Given
the unanimous opinion within the thread I bit the bullet and just refactored 
but I am still
keen to leverage one large self-contained sql script.

The reason is, accessing pure dbapi c code in python is fast but the module I 
am now
using still mixes in plenty python in there and it's not nearly as fast as the 
proper
programmatic approach to inserting and using code to deduce the rowid, followed 
up
with the related inserts while using mostly python dbapi.

Sending one large statement in this case would bypass the overhead, but using 
val as the
reference would make the string very long. That text data might be several 
thousand chars
long. As soon as I have a moment to revisit this, I will try Simon's suggestion.

Thanks,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[James K. Lowden]

On Mon, 9 Sep 2013 02:17:00 +
"Joseph L. Casale"  wrote:

> > If I understand the question, and there is no key other than the
> > auto-incrementing integer, there might not be a good way.  It
> > sounds like the database's design may have painted you into a
> > corner.  
> 
> Well, after inserting one row into table A which looks like (without
> specifying the id and letting it auto generate):
> 
> CREATE TABLE table_a ( 
> valVARCHAR COLLATE "nocase" NOT NULL,
> id INTEGER NOT NULL,
> PRIMARY KEY ( id ) 
> );
> 
> I have for example 20 rows in table B to insert referencing the above:

Yes, that's what I suspected.  Because your table_a has no natural key,
you have no good way to select the auto-generated id value.  You can
find out what the last auto-generated value was, which lets you work a
row at a time,  but you're really suffering from a poor design choice.  

If you make val unique -- and I see no reason not to -- then you can
select the id for every val you insert with "where val = 'value' ". 

--jkl

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[Kees Nuyt]

On Sun, 8 Sep 2013 22:56:20 +, "Joseph L. Casale"
 wrote:

>Hi,
>What is the most efficient way to insert several records into a table which
>has a fk ref to the auto incrementing pk of another insert I need to do in the
>same statement.

What is efficient? Apparently you are not looking for performance, but
for short SQL code. In that case I think a combination of updateble view
(instead of triggers are very powerful) and emulated variables might be
what your are looking for. 
Have a look at this example: http://ideone.com/C36YV

Rewrite for your use case...

I agree with Keith Medcalf that the "val" in your case would have to be
declared unique, and inserts into a should be insert or ignore.
This is partially demonzstrated in http://ideone.com/bTOre .
In the latter, the "a" tables are presumed preloaded in a separate pass,
you could try to combine the triggers in both examples to take care of
that.

>I am migrating some code away from using the SQLAlchemy orm to using the
>Core. The way the data is returned to me is a string (requiring an insert into 
>table A)
>accompanied by several more strings (requiring inserts into table B with a ref 
>to a pk
>in table A's row).
>
>So instead of doing this the typical way, if I can prepare all the sql as one 
>large
>statement for several sets of related inserts (The initial insert into table A 
>with all
>the related inserts into table B) I will get the performance I am after.
>
>Does this seem reasonable? Sqlite doesn't support variable declaration but I am
>sure there is a more efficient means to this using something along the lines of
>INSERT INTO SELECT, just not sure how to craft this with "n" inserts based on 
>one
>select from the PK generating initial insert.
>
>Thanks,
>jlc

HTH

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[Simon Slavin]

On 9 Sep 2013, at 3:36am, Joseph L. Casale  wrote:

> That is the procedure I utilize normally, the requirement for this specific 
> case is
> that the entire set of inserts into table_a be bundled with their associated 
> inserts
> into table_b in one statement where I won't have the luxury of an iterative 
> approach.

Don't let non-programmers set programming requirements.

> So all of these lines of sql will be sent as one statement.
> 
> Normally I would just use variables, but we know this is not an option so I 
> was hoping
> to find a way to accomplish this otherwise.

See the second option mentioned in my post, which solves your problem.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[David Bicking]

You might be able to store your "variable" in a table:

CREATE TABLE table_lastid  (id INTEGER);
INSERT INTO table_lastid (id) VALUES(0); 


Then in your sequence:

INSERT INTO table_a (val) VALUES ('xx');

UPDATE table_lastid SET id = last_insert_rowid();

INSERT INTO table_b (id, key, val)
       SELECT id, 'yyy', 'zzz' from table_lastid;

David




 From: Joseph L. Casale <jcas...@activenetwerx.com>
To: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org> 
Sent: Sunday, September 8, 2013 10:17 PM
Subject: Re: [sqlite] Insert statement
 

> If I understand the question, and there is no key other than the 
> auto-incrementing
> integer, there might not be a good way.  It sounds like the database's design 
> may
> have painted you into a corner.  

Hi James,
Well, after inserting one row into table A which looks like (without specifying 
the id
and letting it auto generate):

CREATE TABLE table_a ( 
    val    VARCHAR COLLATE "nocase" NOT NULL,
    id INTEGER NOT NULL,
    PRIMARY KEY ( id ) 
);

(forgive that odd looking format, its SQLAlchemy output...)

I have for example 20 rows in table B to insert referencing the above:

CREATE TABLE table_b ( 
    val VARCHAR COLLATE "nocase",
    key VARCHAR COLLATE "nocase" NOT NULL,
    id   INTEGER,
    seqno   INTEGER NOT NULL,
    PRIMARY KEY ( seqno ),
    FOREIGN KEY ( id ) REFERENCES table_a ( id ) 
);

So selecting last_insert_rowid() always gives me the 'id' of the previous row 
from table_a
after an insert. So I would insert into table_a, get that rowid, and build the 
remaining 20
inserts. For the sake of keeping the entire sql statement manageable, I was 
hoping not to
build the next 20 statements based on SELECT id FROM table_a WHERE val='xxx' as 
that string
will be very long.

So this works for one insert:

INSERT INTO table_a (val) VALUES ('xx');
INSERT INTO table_b (id, key, val)
       SELECT last_insert_rowid(), 'yyy', 'zzz';

Just not sure how to perform 20 or 30 of those inserts into table_b after the 
one into table_a
yields the id value I need.

Thanks!
jlc
___
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] Insert statement

[Keith Medcalf]

val would have to be declared unique (have a unique index) in order for that to 
work as intended, otherwise it will insert as many rows as there are duplicate 
val values ...

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Sunday, 8 September, 2013 20:27
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Insert statement
> 
> 
> On 9 Sep 2013, at 3:17am, Joseph L. Casale <jcas...@activenetwerx.com>
> wrote:
> 
> > INSERT INTO table_a (val) VALUES ('xx');
> > INSERT INTO table_b (id, key, val)
> >   SELECT last_insert_rowid(), 'yyy', 'zzz';
> >
> > Just not sure how to perform 20 or 30 of those inserts into table_b
> after the one into table_a
> > yields the id value I need.
> 
> Look up the last_insert_rowid() you want and store it in your
> programming language.  That's what programming languages are for.  But
> if you want to do it less efficiently ...
> 
> Look it up each time you insert into table_b:
> 
> INSERT INTO table_b (id, key, val)
>   SELECT id, 'yyy', 'zzz' FROM table_a WHERE val='xx';
> 
> Simon.
> ___
> 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] Insert statement

[Keith Medcalf]

If you are using a programming language, simply retrieve the id after the first 
insert, then bind that host variable when executing subsequent statements.

You can also try something like:

BEGIN IMMEDIATE;
Insert into table1 values ('...');
Insert into table2 (id, key, val)
Select id, key, value 
  from (select 'key1' key, 'val1' val
 Union
Select 'key2', 'val2'
  ... and as many union and selects as you want) as A, 
(select (select last_insert_id()) id) as B;
COMMIT;

if your programming environment does not support function calls and bound 
values.

Of course, table_a.val should probably be unique, in which case you can simply 
select the id corresponding to that val in subsequent inserts.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Joseph L. Casale
> Sent: Sunday, 8 September, 2013 20:17
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Insert statement
> 
> > If I understand the question, and there is no key other than the auto-
> incrementing
> > integer, there might not be a good way.  It sounds like the database's
> design may
> > have painted you into a corner.
> 
> Hi James,
> Well, after inserting one row into table A which looks like (without
> specifying the id
> and letting it auto generate):
> 
> CREATE TABLE table_a (
> valVARCHAR COLLATE "nocase" NOT NULL,
> id INTEGER NOT NULL,
> PRIMARY KEY ( id )
> );
> 
> (forgive that odd looking format, its SQLAlchemy output...)
> 
> I have for example 20 rows in table B to insert referencing the above:
> 
> CREATE TABLE table_b (
> val VARCHAR COLLATE "nocase",
> key VARCHAR COLLATE "nocase" NOT NULL,
> id   INTEGER,
> seqno   INTEGER NOT NULL,
> PRIMARY KEY ( seqno ),
> FOREIGN KEY ( id ) REFERENCES table_a ( id )
> );
> 
> So selecting last_insert_rowid() always gives me the 'id' of the
> previous row from table_a
> after an insert. So I would insert into table_a, get that rowid, and
> build the remaining 20
> inserts. For the sake of keeping the entire sql statement manageable, I
> was hoping not to
> build the next 20 statements based on SELECT id FROM table_a WHERE
> val='xxx' as that string
> will be very long.
> 
> So this works for one insert:
> 
> INSERT INTO table_a (val) VALUES ('xx');
> INSERT INTO table_b (id, key, val)
>SELECT last_insert_rowid(), 'yyy', 'zzz';
> 
> Just not sure how to perform 20 or 30 of those inserts into table_b
> after the one into table_a
> yields the id value I need.
> 
> Thanks!
> jlc
> ___
> 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] Insert statement

[Joseph L. Casale]

> Look up the last_insert_rowid() you want and store it in your programming
> language.  That's what programming languages are for.  But if you want to do
> it less efficiently ...

Hey Simon,
That is the procedure I utilize normally, the requirement for this specific 
case is
that the entire set of inserts into table_a be bundled with their associated 
inserts
into table_b in one statement where I won't have the luxury of an iterative 
approach.

So all of these lines of sql will be sent as one statement.

Normally I would just use variables, but we know this is not an option so I was 
hoping
to find a way to accomplish this otherwise.

Thanks,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[Simon Slavin]

On 9 Sep 2013, at 3:17am, Joseph L. Casale  wrote:

> INSERT INTO table_a (val) VALUES ('xx');
> INSERT INTO table_b (id, key, val)
>   SELECT last_insert_rowid(), 'yyy', 'zzz';
> 
> Just not sure how to perform 20 or 30 of those inserts into table_b after the 
> one into table_a
> yields the id value I need.

Look up the last_insert_rowid() you want and store it in your programming 
language.  That's what programming languages are for.  But if you want to do it 
less efficiently ...

Look it up each time you insert into table_b:

INSERT INTO table_b (id, key, val)
  SELECT id, 'yyy', 'zzz' FROM table_a WHERE val='xx';

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[Joseph L. Casale]

> If I understand the question, and there is no key other than the 
> auto-incrementing
> integer, there might not be a good way.  It sounds like the database's design 
> may
> have painted you into a corner.  

Hi James,
Well, after inserting one row into table A which looks like (without specifying 
the id
and letting it auto generate):

CREATE TABLE table_a ( 
valVARCHAR COLLATE "nocase" NOT NULL,
id INTEGER NOT NULL,
PRIMARY KEY ( id ) 
);

(forgive that odd looking format, its SQLAlchemy output...)

I have for example 20 rows in table B to insert referencing the above:

CREATE TABLE table_b ( 
val VARCHAR COLLATE "nocase",
key VARCHAR COLLATE "nocase" NOT NULL,
id   INTEGER,
seqno   INTEGER NOT NULL,
PRIMARY KEY ( seqno ),
FOREIGN KEY ( id ) REFERENCES table_a ( id ) 
);

So selecting last_insert_rowid() always gives me the 'id' of the previous row 
from table_a
after an insert. So I would insert into table_a, get that rowid, and build the 
remaining 20
inserts. For the sake of keeping the entire sql statement manageable, I was 
hoping not to
build the next 20 statements based on SELECT id FROM table_a WHERE val='xxx' as 
that string
will be very long.

So this works for one insert:

INSERT INTO table_a (val) VALUES ('xx');
INSERT INTO table_b (id, key, val)
   SELECT last_insert_rowid(), 'yyy', 'zzz';

Just not sure how to perform 20 or 30 of those inserts into table_b after the 
one into table_a
yields the id value I need.

Thanks!
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[James K. Lowden]

On Sun, 8 Sep 2013 22:56:20 +
"Joseph L. Casale"  wrote:

> What is the most efficient way to insert several records into a table
> which has a fk ref to the auto incrementing pk of another insert I
> need to do in the same statement.

If I understand the question, and there is no key other than the
auto-incrementing integer, there might not be a good way.  It sounds
like the database's design may have painted you into a corner.  

I think you want 

insert into B
insert into A with FK pointing to new rows in B

The way to do that is

insert into B
insert into A
select ... from B 
where natural key = key of new rows

The insert-select statement gives you the magic integer based on
something you do know: the natural key of the data.  

HTH.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert statement using temp variable

[Igor Tandetnik]

RAKESH HEMRAJANI  wrote:
> int i=0;
> rc = sqlite3_exec(db, "insert into emp values(i);", 0, 0, );

Use sqlite3_prepare_v2, sqlite3_step, sqlite3_bind_* et al to run a 
parameterized query. Something like this:

sqlite3_stmt* stmt;
sqlite3_prepare_v2(db, "insert into emp values(?);", -1, , NULL);
for (int i = 0; i < 10; ++i) {
  sqlite3_bind_int(stmt, 1, i);
  sqlite3_step(stmt);
  sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);

This example also shows how you can run the same statement multiple times with 
different values for parameters.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?

[Dennis Cote]

Lothar Behrens wrote:
> Am 10.09.2008 um 17:37 schrieb Dennis Cote:
> 
>> Lothar Behrens wrote:
>>> What is the function to rollback a transaction or commit ?
>>> I want also to break into these functions. If there is no way I try  
>>> to implement the rollback and commit callbacks.
>>> Also the closing of the database would be interesting, or analysing  
>>> the data in the jornal.
>> The journal file is closed when a transaction ends. This is done by  
>> the function pager_end_transaction() at line 28880 of the  
>> amalgamation. Note this function is called for both a rollback or a  
>> commit.
>>
> 
> Hi,
> 
> I now have seen that many of my simple select statements automatically  
> does a rollback on behalv of OP_Halt.
> 
> Also I have seen that an insert, update or delete statement does  
> automatically a commit in some circumstances as:
> 
> * One VDBE is running only
> * the statement hits an ON FAIL and have to commit in that case
> * other circumstances I do not understand yet
> 
> If I do understand all this correctly I have one case I may stuck into:
> 
> A select statement (not readonly) is still open (having sqlite3_step()  
> returning SQLITE_ROW) and then
> I have created an insert statement that is committed but the outer  
> transaction as of the select statement
> does a rollback if closed later.
> 
> Thus, this results in readable (just inserted) data but loses these  
> data because the outer rollback occurs.
> 
> Right ?
> 
> If so, then I have to redesign something as of this may be the case in  
> my usage of the database API :-)
> 
> My database form opens a statement to select some data and navigates  
> to one (the first, the next or any other) and
> leaves the statement open in a transaction I think (form A, database  
> A) as of a call to sqlite3_step() returning SQLITE_ROW.
> 
> Then I open another database form (form B, database A) and try to add  
> some data. There is no Commit (sqlite3BTreeCommitPhaseOne) or
> rollback (sqlite3BTreeRollback), so I assume the running transaction  
> from form A is causing this.
> 
> Then when I close my application the transaction (form A, database A)  
> is rolled back and this loses my data changes.
> 
> Right ?

That is correct. You are in autocommit mode, so each SQL statement 
executes in its own transaction. The outer select starts a transaction. 
The insert does not start a transaction, since a transaction is already 
open (sqlite only has a single transaction open at any time). The 
application can see all the changes to the database (i.e. it can see the 
uncommitted data). If you now close the database before you reset or 
finalize the select query (which will commit the transaction it 
started), then the open transaction will be rolled back and the changes 
will be lost.

> 
> So my solution would be this:
> 
> Don't leave sqlite3_step() operations in SQLITE_ROW state. Better try  
> to finish until SQLITE_DONE to close the transaction.
> 
> I can do this because:
> 
> * I mostly read only the primary keys of a table (there it is done  
> automatically) to prepare for lazy load (pattern).
> * I read the full data row for a specific primary key as of any cursor  
> activity. (That way I have simulated full cursor support)
> 
> I hope with that I get solved this problem.
> 
> Please comment, If there is something still wrong in my understanding.
> 

That should work fine.

You don't have to let the select run to completion if you don't want to. 
If you reset or finalize the select after it returns the last desired 
row, it will also close the transaction.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?

[Lothar Behrens]

Am 10.09.2008 um 17:37 schrieb Dennis Cote:

> Lothar Behrens wrote:
>> What is the function to rollback a transaction or commit ?
>> I want also to break into these functions. If there is no way I try  
>> to implement the rollback and commit callbacks.
>> Also the closing of the database would be interesting, or analysing  
>> the data in the jornal.
>
> The journal file is closed when a transaction ends. This is done by  
> the function pager_end_transaction() at line 28880 of the  
> amalgamation. Note this function is called for both a rollback or a  
> commit.
>

Hi,

I now have seen that many of my simple select statements automatically  
does a rollback on behalv of OP_Halt.

Also I have seen that an insert, update or delete statement does  
automatically a commit in some circumstances as:

*   One VDBE is running only
*   the statement hits an ON FAIL and have to commit in that case
*   other circumstances I do not understand yet

If I do understand all this correctly I have one case I may stuck into:

A select statement (not readonly) is still open (having sqlite3_step()  
returning SQLITE_ROW) and then
I have created an insert statement that is committed but the outer  
transaction as of the select statement
does a rollback if closed later.

Thus, this results in readable (just inserted) data but loses these  
data because the outer rollback occurs.

Right ?

If so, then I have to redesign something as of this may be the case in  
my usage of the database API :-)

My database form opens a statement to select some data and navigates  
to one (the first, the next or any other) and
leaves the statement open in a transaction I think (form A, database  
A) as of a call to sqlite3_step() returning SQLITE_ROW.

Then I open another database form (form B, database A) and try to add  
some data. There is no Commit (sqlite3BTreeCommitPhaseOne) or
rollback (sqlite3BTreeRollback), so I assume the running transaction  
from form A is causing this.

Then when I close my application the transaction (form A, database A)  
is rolled back and this loses my data changes.

Right ?

So my solution would be this:

Don't leave sqlite3_step() operations in SQLITE_ROW state. Better try  
to finish until SQLITE_DONE to close the transaction.

I can do this because:

*   I mostly read only the primary keys of a table (there it is done  
automatically) to prepare for lazy load (pattern).
*   I read the full data row for a specific primary key as of any cursor  
activity. (That way I have simulated full cursor support)

I hope with that I get solved this problem.

Please comment, If there is something still wrong in my understanding.

Thanks

Lothar


> HTH
> Dennis Cote
>

-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen








___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?

[Lothar Behrens]

Am 09.09.2008 um 22:49 schrieb Dennis Cote:

> Lothar Behrens wrote:
>>
>> But when you say, that, if jornal files are open, transactions are
>> opened, I would set a
>> breakpoint at the line of code the transaction opens these jornal  
>> file
>> and I could look
>> arount there from who the transaction comes.
>>
>> Is that an option ?
>>
>> What function in the sqlite library does this ?
>>
>
> Yes, that is an option if you are using a source code version of  
> SQLite,
> either the individual source files or the amalgamation file,  
> sqlite3.c.
>
> The journal file is opened by the function pager_open_journal() at  
> line
> 30868 in the amalgamation source for version 3.6.2.
>

Yes, It passes the opening of the jornal file as an Op_Transation  
block (I think so inside of VDBE) of code (Insert). And if I start my  
application,
the first transaction is started as of an select statement to give me  
back my localized messages (jornal not opened in select statements)

I have also checked the cleanup of the prepared statements. They would  
be finalized as assumed.

What is the function to rollback a transaction or commit ?

I want also to break into these functions. If there is no way I try to  
implement the rollback and commit callbacks.
Also the closing of the database would be interesting, or analysing  
the data in the jornal.

Thanks

Lothar

> HTH
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen








___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?

[Dennis Cote]

Lothar Behrens wrote:
> 
> But when you say, that, if jornal files are open, transactions are  
> opened, I would set a
> breakpoint at the line of code the transaction opens these jornal file  
> and I could look
> arount there from who the transaction comes.
> 
> Is that an option ?
> 
> What function in the sqlite library does this ?
> 

Yes, that is an option if you are using a source code version of SQLite, 
either the individual source files or the amalgamation file, sqlite3.c.

The journal file is opened by the function pager_open_journal() at line 
30868 in the amalgamation source for version 3.6.2.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?

[Lothar Behrens]

Am 09.09.2008 um 20:46 schrieb Dennis Cote:

> Lothar Behrens wrote:
>>
>> I have added this function right after sqlite3_step, that does the
>> prepared insert statement.
>>
>> int nReturn = sqlite3_step((sqlite3_stmt*)(*start));
>>
>>int autocommit = sqlite3_get_autocommit(m_pDatabase);
>>  
>>if (autocommit == 0) {
>>  wxLogError(_("Warning: Database is not in autocommit mode.\n"));
>>}
>>
>> autocommit is always 1. Also I have thested the following:
>>
>> Open the application and opening the form to display first row -> no
>> jornal file is opened, because no write is yet done.
>>
>> Adding some rows and navigating forward and backbackward -> jornal
>> file is opened and I can see my data in the application.
>>
>
> The fact that a journal file exists at this point implies that you are
> still in a transaction. If you close the database without committing
> this transaction, the changes that you can see in your application  
> will
> be rolled back and lost (see H12019 at
> http://www.sqlite.org/c3ref/close.html).
>
> Can you add a function to check the auto commit status in your main  
> line
> code (i.e. where you are navigating and viewing the data)?
>

Hmm,

I can add such a function beside the others to update my status line  
for sample.

But I don't believe, that I do start any transaction. This is because  
if I simply open
only this database form that makes these problems all additions will  
be stored and if
I restart my application the data is still there as inserted.

I only start a transaction, when I create tables, because I rewrite  
some statements.
But this only happens, when the database is freshly created. Also the  
code is tested
and the transaction is committed, otherwise the shema wouldn't exist  
after a restart.

But when you say, that, if jornal files are open, transactions are  
opened, I would set a
breakpoint at the line of code the transaction opens these jornal file  
and I could look
arount there from who the transaction comes.

Is that an option ?

What function in the sqlite library does this ?

Lothar

> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen








___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?

[Dennis Cote]

Lothar Behrens wrote:
> 
> I have added this function right after sqlite3_step, that does the  
> prepared insert statement.
> 
>  int nReturn = sqlite3_step((sqlite3_stmt*)(*start));
> 
> int autocommit = sqlite3_get_autocommit(m_pDatabase);
>   
> if (autocommit == 0) {
>   wxLogError(_("Warning: Database is not in autocommit mode.\n"));
> }
> 
> autocommit is always 1. Also I have thested the following:
> 
> Open the application and opening the form to display first row -> no  
> jornal file is opened, because no write is yet done.
> 
> Adding some rows and navigating forward and backbackward -> jornal  
> file is opened and I can see my data in the application.
> 

The fact that a journal file exists at this point implies that you are 
still in a transaction. If you close the database without committing 
this transaction, the changes that you can see in your application will 
be rolled back and lost (see H12019 at 
http://www.sqlite.org/c3ref/close.html).

Can you add a function to check the auto commit status in your main line 
code (i.e. where you are navigating and viewing the data)?

Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?

[Lothar Behrens]

Am 09.09.2008 um 17:20 schrieb Dennis Cote:

> Jay A. Kreibich wrote:
>>
>>  Everything you describe sounds exactly as if a transaction has been
>>  started, but is not committed.  When you close the database, the
>>  transaction is automatically (and correctly) rolled back.  This will
>>  also delete the journal file.
>>
>>  I know you said you weren't trying to start a transaction, but you
>>  might double check that.  Set a breakpoint right after the INSERT is
>>  finished and check to see if you have a journal file or not.  You
>>  could also try issuing a "BEGIN" right after the INSERT.  If you get
>>  an error, you're already inside a transaction.
>>
>
> An easier and more accurate way to check may be to add a call to
> sqlite3_get_autocommit() after your insert is complete. It will return
> zero if there is an active transaction, and 1 if there is not (i.e. it
> it in autocommit mode).
>

Ok,

I have added this function right after sqlite3_step, that does the  
prepared insert statement.

 int nReturn = sqlite3_step((sqlite3_stmt*)(*start));

  int autocommit = sqlite3_get_autocommit(m_pDatabase);

  if (autocommit == 0) {
wxLogError(_("Warning: Database is not in autocommit mode.\n"));
  }

autocommit is always 1. Also I have thested the following:

Open the application and opening the form to display first row -> no  
jornal file is opened, because no write is yet done.

Adding some rows and navigating forward and backbackward -> jornal  
file is opened and I can see my data in the application.

Now I will try to use pragma database_list; but I need to restart my  
box.

So I will mail the result later :-(

Lothar

-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen








___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?

[Dennis Cote]

Jay A. Kreibich wrote:
> 
>   Everything you describe sounds exactly as if a transaction has been
>   started, but is not committed.  When you close the database, the
>   transaction is automatically (and correctly) rolled back.  This will
>   also delete the journal file.
> 
>   I know you said you weren't trying to start a transaction, but you
>   might double check that.  Set a breakpoint right after the INSERT is
>   finished and check to see if you have a journal file or not.  You
>   could also try issuing a "BEGIN" right after the INSERT.  If you get
>   an error, you're already inside a transaction.
> 

An easier and more accurate way to check may be to add a call to 
sqlite3_get_autocommit() after your insert is complete. It will return 
zero if there is an active transaction, and 1 if there is not (i.e. it 
it in autocommit mode).

See http://www.sqlite.org/c3ref/get_autocommit.html for details.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?

[Jay A. Kreibich]

On Tue, Sep 09, 2008 at 04:55:01PM +0200, Lothar Behrens scratched on the wall:


> I prepare an insert statement and bind the parameters that afterwards  
> get executed with sqlite_step(). This function then returns SQLITE_DONE
> and I prepare a new select statement wich let me display the just  
> inserted data in my database forms (database file not closed while  
> that).

> Does someone have any more ideas how to narrow the problem ?
> (After the insert statement until to closing of that file)

  Everything you describe sounds exactly as if a transaction has been
  started, but is not committed.  When you close the database, the
  transaction is automatically (and correctly) rolled back.  This will
  also delete the journal file.

  I know you said you weren't trying to start a transaction, but you
  might double check that.  Set a breakpoint right after the INSERT is
  finished and check to see if you have a journal file or not.  You
  could also try issuing a "BEGIN" right after the INSERT.  If you get
  an error, you're already inside a transaction.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?

[Dennis Cote]

Lothar Behrens wrote:
> 
> Does someone have any more ideas how to narrow the problem ?
> (After the insert statement until to closing of that file)
> 

Try executing "pragma database_list;" after the insert. Double check the 
file name and path shown for the main database and ensure that is the 
same file you are looking at with your database browser.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement taking too long

[spaminos-sqlite]

- Original Message 
From: Unit 5 <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 9, 2006 8:02:51 AM
Subject: RE: [sqlite] Insert statement taking too long


> --- Robert Simpson <[EMAIL PROTECTED]> wrote:
> > You need to create an index on the columns you're
> > joining.  Otherwise I
> > believe 100,000,000 rows (10k x 10k) in table 2 will
> > be scanned while SQLite
> > looks for matches against the 10,000 rows in table
> > 1.

> While that makes sense, I suspect there is something
> else going on.  
> 
> I did a few more tests.  For example, if I remove the
> INSERT but keep the exact same SELECT statement with
> the joins, it is fast again.  So, it seems that it is
> quite slow when doing the insert's.  I was thinking
> that perhaps the statement was not in a transaction,
> but I tried that too.

Could it just be that your data set is just too big and doesn't fit in memory? 
Your statement most likely results in random inserts in the target table.
 
Talking about this, is there a way to tell sqlite to put "holes" in the file so 
that when doing random inserts (even in a transaction), only portions of the 
file need to be moved around?
 
It would waste some disk space, but for improved performance (it's a 
trade-off), I would be willing to give away large amount of disk. I know this 
is quite the opposite of what (auto) vacuum does but when data doesn't fit in 
memory and most access is random there is not much performance benefit in 
having the data not sparse in the DB file. The "holes" could be recreated from 
time to time to ensure the sparseness of the db file (hence giving a guaranty 
on insert times).
 
Nicolas

RE: [sqlite] Insert statement taking too long

[Christian Smith]

Unit 5 uttered:


--- Robert Simpson <[EMAIL PROTECTED]> wrote:

You need to create an index on the columns you're
joining.  Otherwise I
believe 100,000,000 rows (10k x 10k) in table 2 will
be scanned while SQLite
looks for matches against the 10,000 rows in table
1.


While that makes sense, I suspect there is something
else going on.

I did a few more tests.  For example, if I remove the
INSERT but keep the exact same SELECT statement with
the joins, it is fast again.  So, it seems that it is
quite slow when doing the insert's.  I was thinking
that perhaps the statement was not in a transaction,
but I tried that too.



Try both the INSERT and the plain SELECT using EXPLAIN QUERY PLAN, which 
will give an indication of which indexes are being used. I'd hazard a 
guess that the INSERT case is not using the same query plan as the plain 
select case.


sqlite> EXPLAIN QUERY PLAN INSERT ... SELECT ... FROM table-1 JOIN table-2 ...
sqlite> EXPLAIN QUERY PLAN SELECT ... FROM table-1 JOIN table-2 ...


Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Insert statement taking too long

[Unit 5]

--- Robert Simpson <[EMAIL PROTECTED]> wrote:
> You need to create an index on the columns you're
> joining.  Otherwise I
> believe 100,000,000 rows (10k x 10k) in table 2 will
> be scanned while SQLite
> looks for matches against the 10,000 rows in table
> 1.

While that makes sense, I suspect there is something
else going on.  

I did a few more tests.  For example, if I remove the
INSERT but keep the exact same SELECT statement with
the joins, it is fast again.  So, it seems that it is
quite slow when doing the insert's.  I was thinking
that perhaps the statement was not in a transaction,
but I tried that too.




 

Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Insert statement taking too long

[Robert Simpson]

You need to create an index on the columns you're joining.  Otherwise I
believe 100,000,000 rows (10k x 10k) in table 2 will be scanned while SQLite
looks for matches against the 10,000 rows in table 1.

> -Original Message-
> From: Unit 5 [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, November 08, 2006 10:47 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Insert statement taking too long
> 
> Hello,
> 
> I am seeing an interesting performance issue with
> INSERT statements.  I am using Sqlite 3.3.5 on a
> Windows XP box. Here is a brief summary of the
> situation:
> 
> Insert statements that require no join are quite fast:
> a) I create an empty table (drop it first if it
> exists).
> b) INSERT ... SELECT ... FROM another-table WHERE ...
> 
> 
> But, if the Insert statement requires a join, the
> performance degrades drastically:
> 
> a) I create an empty table (drop it first if it
> exists).
> b) INSERT ... SELECT ... FROM table-1 JOIN table-2
> WHERE ...
> 
> Even when the two joined tables have 10,000 records
> each and those records match one to one, the query
> goes from taking a second or so in the first case to
> over 30-40 minutes in the second case.  The processing
> is CPU intensive and pretty much locks down the PC
> during this process.
> 
> Is this a common experience or do I need to play
> around with the configuration options? 
> 
>  
> 
> 
> 
>  
> __
> __
> Want to start your own business?
> Learn how on Yahoo! Small Business.
> http://smallbusiness.yahoo.com/r-index
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-