[sqlite] Avoiding Out Of Office Auto Reply To Group

[Odekirk, Shawn]

I will be out of the office beginning next week and was planning to turn
on the out of office auto reply in my email client, but I would like to
avoid spamming the group. I noticed a couple auto replies got through to
the group earlier in the week.
Is there anything I should do on my end to minimize the chances the
group will receive auto replies?

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

Re: [sqlite] Avoiding Database Is Locked Error

[Odekirk, Shawn]

>> I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a
>> problem in my case, since now there really is just one program
accessing
>> the database.
>
>Did you forget the message this thread was started from? The sqlite3
>command line utility is a second program, so this could be a problem.
>
>Pavel

Thank you for taking the time to respond.
No, I did not forget the message this thread was started from. I am the
one who started the thread.
In my last post I mentioned that I updated my programs so that I can
execute an arbitrary query or update, thus eliminating the need to use
the sqlite command line utility. There really is only one program
accessing the database now.
Please let me know if you still think that BEGIN IMMEDIATE is something
I should be using.
I am a newbie to sqlite and welcome any advice or suggestions.

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

Re: [sqlite] Avoiding Database Is Locked Error

[Odekirk, Shawn]

Thank you all for your responses. This discussion has grown a lot more
than I thought it would.
Like I said in my original question, my system is made up of several
programs that communicate by sending messages to each other. I have a
utility program that can send messages to the programs for testing and
debugging purposes. I have updated my utility program and the program
that accesses the database so that I can send arbitrary SQL queries or
update statements to the database program to execute. Now I don't need
to use the command line SQLite tool and the locked database problem is
no longer an issue.
The discussion of transactions and interleaving queries with updates was
really good information.
I am (mis)using transactions not so much to group a unit of work that
must succeed together, but more to improve the I/O performance by
causing any updates to be written to disk at the end of the transaction.
Writing each change individually to disk was taking too much time and
wrapping things in a transaction reduced that time considerably.
I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a
problem in my case, since now there really is just one program accessing
the database.
I am probably interleaving queries and updates in at least one part of
my program, but it doesn't seem to be causing any problems. I'll let you
know if I get the 1:00 am hotline support call.
Swapnil, thanks for the link. I'm running on SCO OpenServer so I'm not
sure your product would work for me but I'll take a look. I was really
looking for code examples that demonstrate best practices.

Thanks again for all the comments, suggestions and good information.

Shawn


> Hi Shawn,
> 
> > demonstrate SQLite best
> > practices, including how to correctly handle error conditions, such
as
> > the database is locked condition?
> > If you pre-select and then modify, you have to be aware enough to
> > realize you MUST wrap the whole process in a manual transaction,
> > and you still need to know how to deal with all the locking and
> > busy issues that come with that.
> 
> One way to assure use of best-practices in dealing with SQLite
> nitty-gritties of transactions, locking and busy errors would be to
> use a tool like StepSqlite PL/SQL compiler
> 
> (https://www.metatranz.com/stepsqlite
 ).
> 
> It automates SQLite best-practices (as recommended by Jay, Pavel and
> Simon in this thread) by wrapping things in transactions, handling
> BUSY errors and retrying after delay and others like preparing all SQL
> statements only once etc.
> SQLite combined with a 4GL like StepSqlite PL/SQL lets you focus on
> writing your code instead of having to learn to deal with SQLite
> idiosyncrasies right at the beginning.
> 
> Full disclosure: I am the creator of StepSqlite.
> 
> -Swapnil Kashikar
> support @ metatranz . com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Avoiding Database Is Locked Error

[Odekirk, Shawn]

I have recently upgraded an old system that managed its data in a
dynamically allocated linked list to use SQLite instead. I have been
extremely pleased with SQLite. Most operations have been greatly
simplified and I am much more confident in the stability of the system
and the integrity of the data.
The system is made up of several independent programs which communicate
by sending messages to the other programs. Only one program opens the
SQLite database and the other programs request data by sending a message
to this program.
During normal operation I have no problems. However, while the program
is running there are times I would like to use the command line SQLite
tool to query or update the database. Since I planned to have only one
program access the database I did not handle the database is locked
condition in my program. During testing my program encountered the
database is locked condition when I was using the SQLite command line
tool while the program was running. I have avoided using the command
line tool on the live system.
Is there any way to ensure that the SQLite command line tool does not
interfere with the operation of my program? I don't care how long it
takes the command line tool to perform its queries as long as it doesn't
interrupt the operation of my program.
Also, are there any code examples that demonstrate SQLite best
practices, including how to correctly handle error conditions, such as
the database is locked condition? I have been unable to find complete
code examples.
If anyone is interested, my program is written in C running on SCO Open
Server 5.0.7.
 
Thanks,
Shawn
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mailing List Question

[Odekirk, Shawn]

I'm sorry if this is not the correct forum for this question, but does
anyone know if the digest mailing list is working?  I have tried to
subscribe several times but it doesn't seem to work.

I assume that the digest will be one email each day containing all of
the posts to the regular mailing list.  Is this correct?

I would really like to continue to read the posts to the mailing list,
but I am finding that I am spending way too much time reading each
individual post as it is posted, and not enough time getting my other
work done.

 

Thanks,

Shawn

 

RE: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Odekirk, Shawn]

Adding "primary key" to column "a" results in the behavior I think you
were first expecting.

sqlite> create table tmp (a integer primary key, b integer);
sqlite> create unique index tmpIndex on tmp (a, b);
sqlite> insert into tmp values (1, 1);
sqlite> insert into tmp values (2, 2);
sqlite> select last_insert_rowid();
2
sqlite> insert or replace into tmp values (1, 1);
sqlite> select last_insert_rowid();
1
sqlite> select * from tmp;
1|1
2|2

I wonder if Michael could use OR REPLACE instead of OR IGNORE to solve
his problem.

Shawn

-Original Message-
From: Simon Davies [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 29, 2007 11:02 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

Thanks for the explanation!

On 29/10/2007, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Simon Davies wrote:
> > Following this thread, I was experimenting with last_insert_rowid(),
> > and found the following, which does not look right:
> >
> > SQLite version 3.4.2
> > Enter ".help" for instructions
> > sqlite>
> > sqlite> create table tmp( a integer, b integer );
> > sqlite> create unique index tmpIndex on tmp( a, b );
> > sqlite> insert into tmp values( 1, 1 );
> > sqlite> insert into tmp values( 2, 2 );
> > sqlite> select last_insert_rowid();
> > 2
> > sqlite>
> > sqlite> insert or replace into tmp values( 1, 1 );
> > sqlite> select last_insert_rowid();
> > 3
> > <-- !!!???!!!
> > sqlite> select * from tmp;
> > 2|2
> > 1|1
> > sqlite>
> >
> >
> >
> >
> Simon,
>
> If you change your query to;
>
>select rowid, * from tmp;
>
> it will display the rowid which is different than either of the fields
> in the table.
>
> When doing a replace sqlite deletes the existing row and adds a new
row.
>
> HTH
> Dennis Cote
>
>

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

-
>
>


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

RE: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Odekirk, Shawn]

This link gives a little more information:
http://www.sqlite.org/autoinc.html

Shawn

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 29, 2007 10:57 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

Simon Davies wrote:
> Following this thread, I was experimenting with last_insert_rowid(),
> and found the following, which does not look right:
>
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite> create table tmp( a integer, b integer );
> sqlite> create unique index tmpIndex on tmp( a, b );
> sqlite> insert into tmp values( 1, 1 );
> sqlite> insert into tmp values( 2, 2 );
> sqlite> select last_insert_rowid();
> 2
> sqlite>
> sqlite> insert or replace into tmp values( 1, 1 );
> sqlite> select last_insert_rowid();
> 3
> <-- !!!???!!!
> sqlite> select * from tmp;
> 2|2
> 1|1
> sqlite>
>
>
>
>   
Simon,

If you change your query to;

select rowid, * from tmp;

it will display the rowid which is different than either of the fields 
in the table.

When doing a replace sqlite deletes the existing row and adds a new row.

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

RE: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Odekirk, Shawn]

The sqlite3_last_insert_rowid function is completely, 100% reliable in your 
scenario.  The problem is that in your scenario you shouldn't be calling that 
function.
The function is called sqlite3_last_insert_rowid, not 
sqlite3_last_insert_or_ignore_rowid, and not sqlite3_last_insert_or_fail_rowid. 
 It makes perfect sense that it returns the row id of the last row inserted 
successfully.  This function should only be called after a successful insert.  
In your scenario you have not performed a successful insert.  There is no 
reason to think that the function will return a meaningful row id after a 
failed insert attempt.
I hope my response was not too harsh.  You seem so adamant that there is a 
problem with the function or documentation, and I completely disagree.

Shawn

-Original Message-
From: Michael Ruck [mailto:[EMAIL PROTECTED] 
Sent: Sunday, October 28, 2007 12:55 PM
To: sqlite-users@sqlite.org
Subject: AW: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

I'd suggest putting this into the documentation of
sqlite3_last_insert_rowid(), that
the call is not reliable in scenarios such as this one. 

-Ursprüngliche Nachricht-
Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Gesendet: Sonntag, 28. Oktober 2007 17:48
An: sqlite-users@sqlite.org
Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and
sqlite3_last_insert_rowid()


On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote:

> Yes, I am well aware of this possibility as I've written in my  
> initial mail.
> It just doesn't fit with the
> description of sqlite3_last_insert_rowid() in my understanding. I  
> think this
> is a bug - either in the documentation
> or in the implementation. sqlite3_last_insert_rowid() should return  
> the
> correct id, no matter what and it doesn't.
>

Consider this scenario:

 CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE);
 INSERT INTO ex1 VALUES(1,1,1);
 INSERT INTO ex1 VALUES(2,2,2);
 INSERT INTO ex1 VALUES(3,3,3);

Now you do your INSERT OR IGNORE:

 INSERT OR IGNORE INTO ex1 VALUES(1,2,3);

Three different constraints fail, one for each of three different
rows.  So if sqlite3_last_insert_rowid() were to operate as you
suggest and return the rowid of the failed insert, when rowid
would it return?  1, 2, or 3?


D. Richard Hipp
[EMAIL PROTECTED]





-
To unsubscribe, send email to [EMAIL PROTECTED]

-



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




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

RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question

[Odekirk, Shawn]

>> Brickl Roland [mailto:[EMAIL PROTECTED] wrote:
>> Integer PrimaryKeys are always autoincrementing. When you don't
>> specify it it uses after (2^63)-1 a random free positiv value.

>> "Odekirk, Shawn" <[EMAIL PROTECTED]> wrote:
>> I will compile this for Windows and see what my results are using a
>> compiler that supports 64 bit integers.

>  "Odekirk, Shawn" <[EMAIL PROTECTED]> wrote:
> Indeed, compiled using Microsoft Visual Studio 2005 it works as
> described.
> So, now to dive into the source and figure out how to make it work
> using my old SCO tools.

Well, that was easy!
A little poking around and I found the SQLITE_32BIT_ROWID preprocessor
symbol.  Simply defining that symbol and recompiling fixed my problem.
I have to say that I am very impressed with how easy it has been to
compile SQLite on different platforms and with how well it seems to
work.  I'm looking around trying to find other projects where I can use
it.

Shawn


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

RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question

[Odekirk, Shawn]

Brickl Roland [mailto:[EMAIL PROTECTED] wrote:
> Integer PrimaryKeys are always autoincrementing. When you don't
> specify it it uses after (2^63)-1 a random free positiv value.

"Odekirk, Shawn" <[EMAIL PROTECTED]> wrote:
> I will compile this for Windows and see what my results are using a
> compiler that supports 64 bit integers.

Indeed, compiled using Microsoft Visual Studio 2005 it works as
described.
So, now to dive into the source and figure out how to make it work using
my old SCO tools.

Thanks,
Shawn


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

RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question

[Odekirk, Shawn]

Thank you for your reply.
I tried adding NOT NULL to my primary key column, but the results are
the same.
My compiler is old and I don't think it supports a 64 bit data type.
Maybe this is the root cause of my problem.

If I create a table like this:
CREATE TABLE rollover (id INTEGER PRIMARY KEY, name TEXT)
or:
CREATE TABLE rollover (id INTEGER PRIMARY KEY NOT NULL, name TEXT)
and I insert a row like this:
INSERT INTO rollover VALUES (2147483647, 'One');
the row has the values:
2147483647, 'One'

If I then insert a row like this:
INSERT INTO rollover VALUES (NULL, 'Two');
the newly inserted row has the following values:
-2147483648, 'Two'

If I then try to insert another row like this:
INSERT INTO rollover VALUES (NULL, 'Three');
I get: "SQL error: PRIMARY KEY must be unique"

I would like the primary key to rollover to 1, instead of to
-2147483648.  Does anyone have any ideas where I should look?

I will compile this for Windows and see what my results are using a
compiler that supports 64 bit integers.

Thanks,
Shawn

-Original Message-
From: Brickl Roland [mailto:[EMAIL PROTECTED] 
Sent: Saturday, October 13, 2007 5:29 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover
Question

Hallo Odekirk Shawn,

SQLite use up to an 64Bit signed Integer for Primary Keys, even on non
64Bit-Systems!
Integer PrimaryKeys are always autoincrementing. When you don't specify
it it uses after (2^63)-1
a random free positiv value. When you write autoincrement for your
create table it never reuse
deleted positiv values and you get an SQLITE_FULL error, thats all.
And please don't forget the not null for your primary key. Without this
you get a little bit
different behavior.

Greats,
Brickl
--- "Odekirk, Shawn" <[EMAIL PROTECTED]> schrieb:

> I am evaluating SQLite for a project I am working on.
> 
> I have a question about the behavior of the INTEGER PRIMARY KEY auto
> increment feature.
> 
> My platform uses 32 bit integers, so the valid values for an unsigned
> integer are 0 - 4294967296 and the valid values for a signed integer
are
> -2147483648 - 2147483647.
> 
> Since the INTEGER PRIMARY KEY data type is a signed integer, the
maximum
> positive value is 2147483648.  If my table already has a row with the
> maximum positive value in the primary key field, and I insert a row
> using NULL as the value of the primary key field, the row is inserted
> and the primary key is assigned the value of -2147483648.  That makes
> sense to me and I have no problem with that.  The problem is that the
> next row I insert generates the error "SQL error: PRIMARY KEY must be
> unique".  I suspect that this is because SQLite tries to use the next
> largest positive value when it increments the primary key field.
> 
> Is there an easy way to cause the INTEGER PRIMARY KEY column to use an
> unsigned integer instead, or to roll over to 0 instead of the most
> negative value for the data type?
> 
> I suspect that in practice I will not run into this issue.  However, I
> would feel better knowing that there is no chance that I will
encounter
> this problem.
> 
>  
> 
> Thanks,
> 
> Shawn
> 
>  
> 
> 



  __  
Yahoo! Clever: Sie haben Fragen? Yahoo! Nutzer antworten Ihnen.
www.yahoo.de/clever



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

[sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question

[Odekirk, Shawn]

I am evaluating SQLite for a project I am working on.

I have a question about the behavior of the INTEGER PRIMARY KEY auto
increment feature.

My platform uses 32 bit integers, so the valid values for an unsigned
integer are 0 - 4294967296 and the valid values for a signed integer are
-2147483648 - 2147483647.

Since the INTEGER PRIMARY KEY data type is a signed integer, the maximum
positive value is 2147483648.  If my table already has a row with the
maximum positive value in the primary key field, and I insert a row
using NULL as the value of the primary key field, the row is inserted
and the primary key is assigned the value of -2147483648.  That makes
sense to me and I have no problem with that.  The problem is that the
next row I insert generates the error "SQL error: PRIMARY KEY must be
unique".  I suspect that this is because SQLite tries to use the next
largest positive value when it increments the primary key field.

Is there an easy way to cause the INTEGER PRIMARY KEY column to use an
unsigned integer instead, or to roll over to 0 instead of the most
negative value for the data type?

I suspect that in practice I will not run into this issue.  However, I
would feel better knowing that there is no chance that I will encounter
this problem.

 

Thanks,

Shawn