Re: [sqlite] Newbie Question

[JohnD]

Bob,

I was able to compile on Linux after removing tclsqlite.c from the 
build.  It appears as though that was causing my errors.  Once I removed 
that, by changing


HAVE_TCL = 1
to
HAVE_TCL =

It compiled without errors.  I found this in another thread which 
indicated, to me at least, that it  wouldn't be needed unless I wanted 
to use the TCL shell, which I don't.


Thanks,
John

Robert L Cochran wrote:

Post the error messages and someone will help you. I'll also check for 
messages after work tonight. There are others on this forum who are 
far more knowledgable than I.

Re: [sqlite] drop statement returns SQLITE_ERROR

[Preston]

You right... i had even been following a similar thread and didn't realize 
that it was my problem too.. thanks a ton!

--preston

- Original Message - 
From: "Robert Simpson" <[EMAIL PROTECTED]>

To: 
Sent: Friday, October 14, 2005 2:55 PM
Subject: Re: [sqlite] drop statement returns SQLITE_ERROR


- Original Message - 
From: "Preston" <[EMAIL PROTECTED]>

To: 
Sent: Friday, October 14, 2005 1:21 PM
Subject: Re: [sqlite] drop statement returns SQLITE_ERROR


Here is the full source to a stripped down version of the app i am truing 
to

debug. It exhibits the problem. to recreat this problem simply create a
target.bin and add a table t1, then create a database update.bin with the
table
adhoc_sql(adhocsqlid int, sqltext text) and insert the values (1, 'drop
table t1;')

I'm sure it is something in my code... i'm just not sure what. I really 
do

appreciate the time that anybody puts into this.. i know it isn't a quick
question.
Thanks again
--Preston

-
#include "sqlite/sqlite3.h"
#include 
#include 

bool RunAdHoc(sqlite3** ppDb, char* SqlStatementList)
{
int rc;
sqlite3_stmt *pListStmt;
sqlite3_stmt *pUpdateStmt;
sqlite3* pDb = *ppDb;
bool ok = true;

sqlite3_prepare(pDb, SqlStatementList, -1, , 0);
rc = sqlite3_step(pListStmt);
while( rc == SQLITE_ROW && ok)
{
 sqlite3_prepare(pDb, (const char*)sqlite3_column_text(pListStmt, 0), -1,
, 0);
 rc = sqlite3_step(pUpdateStmt);
 ok = rc == SQLITE_OK || rc == SQLITE_ROW || rc == SQLITE_DONE;
 sqlite3_finalize(pUpdateStmt);


You have a prepared statement stepping in a loop (locking the database for 
read-only access), and inside that loop you execute a statement that 
changes the database schema, thereby invalidating the outer prepared 
statement.  You need to read in all the statements first into an array, 
finalize, then loop through the array and execute all the statements. 
Either that, or you need to open a new connection to execute the inner 
statements on.


Robert

Re: [sqlite] drop statement returns SQLITE_ERROR

[Robert Simpson]

- Original Message - 
From: "Preston" <[EMAIL PROTECTED]>

To: 
Sent: Friday, October 14, 2005 1:21 PM
Subject: Re: [sqlite] drop statement returns SQLITE_ERROR


Here is the full source to a stripped down version of the app i am truing 
to

debug. It exhibits the problem. to recreat this problem simply create a
target.bin and add a table t1, then create a database update.bin with the
table
adhoc_sql(adhocsqlid int, sqltext text) and insert the values (1, 'drop
table t1;')

I'm sure it is something in my code... i'm just not sure what. I really do
appreciate the time that anybody puts into this.. i know it isn't a quick
question.
Thanks again
--Preston

-
#include "sqlite/sqlite3.h"
#include 
#include 

bool RunAdHoc(sqlite3** ppDb, char* SqlStatementList)
{
int rc;
sqlite3_stmt *pListStmt;
sqlite3_stmt *pUpdateStmt;
sqlite3* pDb = *ppDb;
bool ok = true;

sqlite3_prepare(pDb, SqlStatementList, -1, , 0);
rc = sqlite3_step(pListStmt);
while( rc == SQLITE_ROW && ok)
{
 sqlite3_prepare(pDb, (const char*)sqlite3_column_text(pListStmt, 0), -1,
, 0);
 rc = sqlite3_step(pUpdateStmt);
 ok = rc == SQLITE_OK || rc == SQLITE_ROW || rc == SQLITE_DONE;
 sqlite3_finalize(pUpdateStmt);


You have a prepared statement stepping in a loop (locking the database for 
read-only access), and inside that loop you execute a statement that changes 
the database schema, thereby invalidating the outer prepared statement.  You 
need to read in all the statements first into an array, finalize, then loop 
through the array and execute all the statements.  Either that, or you need 
to open a new connection to execute the inner statements on.


Robert

[sqlite] Question: Insert trigger to make an insert conditional

[Jay Godse]

Hi.

I would like to put an insert trigger on a table A before the
insert where the insert only happens if the values in the new
row meet certain conditions. 

I can achieve the same result by having the trigger run after
the insert on Table A and delete the newly inserted row if the
row does not match the conditions. 

Also, if I call this "insert" statement from C, and the delete
trigger runs, do I get a message from the API that the row was
not inserted?


Curiously,   Jay Godse

Re: [sqlite] Infinite loop on sqlite3_close()

[Preston Zaugg]

From: Jens Miltner <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Infinite loop on sqlite3_close()
Date: Fri, 14 Oct 2005 17:38:04 +0200


Am 10.10.2005 um 16:53 schrieb Preston Zaugg:

While running some performance tests i ran into a condition where  
sqlite3_close got caught in an infinite loop.

The loop it gets caught in is on line main.c 194:
   while( pPrev && pPrev->pNext!=db ){
 pPrev = pPrev->pNext;
   }

This did not happen until i upgraded to 3.2.7, not the only change,  but 
the most significant. This is repeatable, but does not happen  in the same 
spot each time.


Is anybody else running into this?


I've once seen a similar problem and it was caused by missing thread  
synchronization. Are you using multiple threads? If so, did you  configure 
sqlite as thread safe?





Yes.. that was my problem. Thank you very much!

Re: [sqlite] locking problem (on insert inside callback)

[Rachel Willmer]

> Solution 1 is to use a TEMP table:
>
>   CREATE TEMP TABLE diffs AS
>  SELECT * FROM table1 LEFT JOIN table2 ;
>   SELECT * FROM diffs; -- Insert into table1 in the callback;
>   DROP TABLE diffs;

that sounds like the answer for me
thanks
Rachel

Re: [sqlite] locking problem (on insert inside callback)

[Rachel Willmer]

> I'm not sure I understand your logic. Your left join
> indicates that there are records missing from table2,
> so I would expect that you want to insert the missing
> records into table2. Assuming that's what you meant,
>
>   insert into table2
>   select * from table1
>   where table1.field not in (select field from table2)
>
> or
>
>   insert into table2
>   select * from table1
>   where not exists
> (select field from table2 where table2.field=table1.field)
>
> The subquery in the first form is static (executed only
> once). The subquery in the second form is correlated
> (executed many times), but the second form can be
> faster in some circumstances.

I missed out a bit of the logic. I want to be able to ask the user if
they want to approve the addition of the new record, so I need to be
able to do this as two separate operations, rather than combine them
into a single sql statement.

Rachel

Re: [sqlite] Please, please do _not_ remove this feature from SQLite...

[Jens Miltner]

Am 12.10.2005 um 12:12 schrieb pilot pirx:

P.S. On somewhat related note: since sqlite is written in C - why  
it does not expose some basic functions from the standard C library  
(log, exp, sqrt, sin), at least optionally? Understandably, the  
idea is to keep it 'lite'. But, may be, an approach similar to ant  
and other packages could be applied to SQLite - that is there is a  
set of standard (but still simple) extensions, including things  
which may add some bulk, but do not require any large  
implementation effort.




Guess that's what user functions are for: 

Re: [sqlite] Infinite loop on sqlite3_close()

[Jens Miltner]

Am 10.10.2005 um 16:53 schrieb Preston Zaugg:

While running some performance tests i ran into a condition where  
sqlite3_close got caught in an infinite loop.

The loop it gets caught in is on line main.c 194:
   while( pPrev && pPrev->pNext!=db ){
 pPrev = pPrev->pNext;
   }

This did not happen until i upgraded to 3.2.7, not the only change,  
but the most significant. This is repeatable, but does not happen  
in the same spot each time.


Is anybody else running into this?


I've once seen a similar problem and it was caused by missing thread  
synchronization. Are you using multiple threads? If so, did you  
configure sqlite as thread safe?

Re: [sqlite] locking problem (on insert inside callback)

[Kurt Welgehausen]

> SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field
> WHERE table2.field is NULL
>
> So far, so good, I get the records I want. Then in the callback, I try
>
> INSERT INTO table1 etc...

I'm not sure I understand your logic. Your left join
indicates that there are records missing from table2,
so I would expect that you want to insert the missing
records into table2. Assuming that's what you meant,

  insert into table2
  select * from table1
  where table1.field not in (select field from table2)

or

  insert into table2
  select * from table1
  where not exists
(select field from table2 where table2.field=table1.field)

The subquery in the first form is static (executed only
once). The subquery in the second form is correlated
(executed many times), but the second form can be
faster in some circumstances.

Regards

[sqlite] glibc version issue

[Craig Jacobs]

I am trying to use sqlite in an embedded linux application. Unfortunately,
the OS we are running on the system is a bit old and there is no great way
to update existing units. When I tried to load the sqlite library, it
complains about GLIBC_VERSION not matching. I haven't really gotten into
cross-compiling (I was kinda trying to stay out of it).

Does anyone have any suggestions or maybe even a precompiled x86 binary that
wants GLIBC 2.2?

I appreciate it!

-Craig

P.S.: I realize this may have been the wrong list on which to ask a question
about cross compiling. I apologize.

Re: [sqlite] locking problem (on insert inside callback)

[Jay Sprenkle]

Why do it in the call back? Why not just do it in sql:

CREATE TEMP TABLE diffs AS
SELECT * FROM table1 LEFT JOIN table2 ;

insert into table1
SELECT * FROM diffs; -- Insert into table1

DROP TABLE diffs;



On 10/14/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> Rachel Willmer <[EMAIL PROTECTED]> wrote:
> > Hi, apologies if this is a trivial question, but I'm a newbie to
> > sqlite3. (very impressed so far)
> >
> > I want to search two tables which should contain the same records and
> > add any that are missing from the second into the first.
> >
> > So I do
> >
> > SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field
> > WHERE table2.field is NULL
> >
> > So far, so good, I get the records I want. Then in the callback, I try
> >
> > INSERT INTO table1 etc...
> >
> > This fails with a "database table is locked" error.
> >
> > I'm assuming that this is because I'm still in the middle of doing the
> > SELECT statement.
> >
> > So my question is this, do I have to use the callback to copy the
> > records into a temp table, and then only add them after the
> > sqlite3_exec() which calls the SELECT has returned? or is there a more
> > elegant/obvious solution?
> >
>
> Solution 1 is to use a TEMP table:
>
> CREATE TEMP TABLE diffs AS
> SELECT * FROM table1 LEFT JOIN table2 ;
> SELECT * FROM diffs; -- Insert into table1 in the callback;
> DROP TABLE diffs;
>
> Solution 2 is a dirty trick. It works now and in all historical versions
> of SQLite and there are no plans to change it, but there are also no
> promises not to change it. In solution 2, add
>
> ORDER BY +table1.rowid
>
> to the end of your SELECT statement. The "+" sign in front of the
> "table1.rowid" is *essential* if this is trick is to work.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>


--
---
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264

Re: [sqlite] detecting database corruption?

[drh]

Wilson Yeung <[EMAIL PROTECTED]> wrote:
> 
> Does SQLite detect database corruption?  If so, what kind of
> corruption is detected and how does it do so?
> 

SQLite does attempt to detect corruption in database files
and returns SQLITE_CORRUPT when it finds it.  The regression
test suite for SQLite runs thousands of tests in which
random binary data overwrites random small sections of a
database file. These tests confirm that the corruption is
detected and reported properly.

However

(1) Corruption is only detected when SQLite tries to use
the corrupted section of the database.  If the corruption
occurs in table ABC and you are only accessing table PQR,
then you will never see the SQLITE_CORRUPT error.

(2) If the corruption occurs in the middle of (say) a large
BLOB, the BLOB will be changed of course, but as SQLite
does not store checksums or other error detecting codes
on data, there is no way for SQLite to know this.

(3) Corrupt detection has historically been a buggy area of
SQLite.  In spite of the many thousands of test cases that
are run against the library, users do from time to time
find creative ways to corrupt databases that lead to
to segfaults.  There are no outstanding issues in this
area that I am aware of, but it has historically been
a weakness.

Re: [sqlite] drop statement returns SQLITE_ERROR

[drh]

"Preston Zaugg" <[EMAIL PROTECTED]> wrote:
> >
> >This is probably an SQLITE_SCHEMA error.  Rerun sqlite3_prepare() and
> >sqlite3_step() and it should work the second time.
> >
> >This particular SQLITE_SCHEMA error seems unnecessary though.  Looks
> >like sqlite3_step() could probably do a better job of preventing it.
> >I'll have a look and see what I can find
> >--
> >D. Richard Hipp <[EMAIL PROTECTED]>
> >
> 
> It actually seems to be returning the SQLITE_ERROR code (1) with the message 
> "SQL logic error or missing database". I re-ordered my statements for a 
> quick test and had the drop statement run first and i still get the error. I 
> tried to create a dummy table with just one dummy row to see if it was 
> something about this table, but that drop statement would run either.
> 
> Could it have something to do with the fact that i have a second database 
> attached?

Can you provide a specific test case that fails using the
command-line client?
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] locking problem (on insert inside callback)

[drh]

Rachel Willmer <[EMAIL PROTECTED]> wrote:
> Hi, apologies if this is a trivial question, but I'm a newbie to
> sqlite3. (very impressed so far)
> 
> I want to search two tables which should contain the same records and
> add any that are missing from the second into the first.
> 
> So I do
> 
> SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field
> WHERE table2.field is NULL
> 
> So far, so good, I get the records I want. Then in the callback, I try
> 
> INSERT INTO table1 etc...
> 
> This fails with a "database table is locked" error.
> 
> I'm assuming that this is because I'm still in the middle of doing the
> SELECT statement.
> 
> So my question is this, do I have to use the callback to copy the
> records into a temp table, and then only add them after the
> sqlite3_exec() which calls the SELECT has returned? or is there a more
> elegant/obvious solution?
> 

Solution 1 is to use a TEMP table:

  CREATE TEMP TABLE diffs AS
 SELECT * FROM table1 LEFT JOIN table2 ;
  SELECT * FROM diffs; -- Insert into table1 in the callback;
  DROP TABLE diffs;

Solution 2 is a dirty trick.  It works now and in all historical versions
of SQLite and there are no plans to change it, but there are also no
promises not to change it.  In solution 2, add

  ORDER BY +table1.rowid

to the end of your SELECT statement.  The "+" sign in front of the 
"table1.rowid" is *essential* if this is trick is to work. 

  --
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Static library for sqlite for Windows

[Murugan, Muthulakshmi]

Thanks for your response. It helps.

Thanks,
Lakshmi.

> > I have downloaded the sqlite 3.2.7. binary version for 
> Windows. But I
> > found no libsqlite3.lib.
> > Can anyone help in this regard?
> 
> See http://www.sqlite.org/cvstrac/wiki?p=HowToCompile
> 
> This question comes up so often that it should really be 
> included in the 
> FAQ.
> 

[sqlite] locking problem (on insert inside callback)

[Rachel Willmer]

Hi, apologies if this is a trivial question, but I'm a newbie to
sqlite3. (very impressed so far)

I want to search two tables which should contain the same records and
add any that are missing from the second into the first.

So I do

SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field
WHERE table2.field is NULL

So far, so good, I get the records I want. Then in the callback, I try

INSERT INTO table1 etc...

This fails with a "database table is locked" error.

I'm assuming that this is because I'm still in the middle of doing the
SELECT statement.

So my question is this, do I have to use the callback to copy the
records into a temp table, and then only add them after the
sqlite3_exec() which calls the SELECT has returned? or is there a more
elegant/obvious solution?

Any pointers most welcome
Rachel

Re: [sqlite] Static library for sqlite for Windows

[Eric Bohlman]

Murugan, Muthulakshmi wrote:

I have downloaded the sqlite 3.2.7. binary version for Windows. But I
found no libsqlite3.lib.
Can anyone help in this regard?


See http://www.sqlite.org/cvstrac/wiki?p=HowToCompile

This question comes up so often that it should really be included in the 
FAQ.

Re: [sqlite] Newbie Question

[Robert L Cochran]

Post the error messages and someone will help you. I'll also check for 
messages after work tonight. There are others on this forum who are far 
more knowledgable than I.



Bob

JohnD wrote:


Bob,

Thanks for the information.  Are the required dependencies for Sqlite 
listed anywhere?  I'm unable to compile on Linux and it appears as 
though I'm missing a dependency or two.  Any idea where I can check 
those?


Thanks,
John

Robert L Cochran wrote:

I think sqlite3.h is generated for you automatically as part of the 
build process from source code and it will be later installed for you 
in the include directory. You must actually compile sqlite from the 
source code.


For Linux, why not just download sqlite-3.2.7.tar.gz (the linux 
source code), untar it, and build from that?


wget http://www.sqlite.org/sqlite-3.2.7.tar.gz
tar -xvzf sqlite-3.2.7.tar.gz
cd sqlite-3.2.7
mkdir bld
cd !$
../configure --enable-utf8 --includedir=/usr/include
[fix any errors...you might need to install the readline development 
package, etc...if you get an error, fix it, then rerun ./configure...]

make
[fix any errors if make doesn't finish successfully. Warnings about 
signedness differences under gcc4 are okay.]

su
make install
exit
su -
ldconfig
exit

At this point you should be ready to build your own C/C++ programs 
that include sqlite3. You'll see the sqlite files in 
/usr/local/include if you compiled with the default installation path 
options.


Bob Cochran
Greenbelt, Maryland, USA


JohnD wrote:


Hello,

I'm new to Sqlite and am trying to create a simple C/C++ program.  
I've downloaded both the binary and the CVS source but can't seem to 
locate the sqlite3.h header file.


Is there any document that details what I need to do in order to set 
up an environment to be able to write and compile my own C/C++ 
programs on either Windows or Linux?

Re[2]: [sqlite] 3.2.7 and russian characters

[Sergey Startsev]

Hello,

Thursday, October 13, 2005, 7:15:46 PM, you wrote:

dhc> The sqlite3_open() routine is suppose to accept the filename
dhc> as UTF-8.  There was a bug in earlier versions of SQLite that
dhc> allowed ISO-8859-? names to be used.  That bug was fixed.  Probably
dhc> your program is depending on the old (broken) behavior.

   Yes. I called sqlite3_open() with non UTF8 string. Thank you.

-- 
Best regards

 Sergey Startsev
 SQLite Analyzer - SQLite database management tool with GUI.
 http://www.kraslabs.com/sqlite_analyzer.html

Re: [sqlite] Newbie Question

[JohnD]

Bob,

Thanks for the information.  Are the required dependencies for Sqlite 
listed anywhere?  I'm unable to compile on Linux and it appears as 
though I'm missing a dependency or two.  Any idea where I can check those?


Thanks,
John

Robert L Cochran wrote:

I think sqlite3.h is generated for you automatically as part of the 
build process from source code and it will be later installed for you 
in the include directory. You must actually compile sqlite from the 
source code.


For Linux, why not just download sqlite-3.2.7.tar.gz (the linux source 
code), untar it, and build from that?


wget http://www.sqlite.org/sqlite-3.2.7.tar.gz
tar -xvzf sqlite-3.2.7.tar.gz
cd sqlite-3.2.7
mkdir bld
cd !$
../configure --enable-utf8 --includedir=/usr/include
[fix any errors...you might need to install the readline development 
package, etc...if you get an error, fix it, then rerun ./configure...]

make
[fix any errors if make doesn't finish successfully. Warnings about 
signedness differences under gcc4 are okay.]

su
make install
exit
su -
ldconfig
exit

At this point you should be ready to build your own C/C++ programs 
that include sqlite3. You'll see the sqlite files in 
/usr/local/include if you compiled with the default installation path 
options.


Bob Cochran
Greenbelt, Maryland, USA


JohnD wrote:


Hello,

I'm new to Sqlite and am trying to create a simple C/C++ program.  
I've downloaded both the binary and the CVS source but can't seem to 
locate the sqlite3.h header file.


Is there any document that details what I need to do in order to set 
up an environment to be able to write and compile my own C/C++ 
programs on either Windows or Linux?

[sqlite] Static library for sqlite for Windows

[Murugan, Muthulakshmi]

Hi,
I have downloaded the sqlite 3.2.7. binary version for Windows. But I
found no libsqlite3.lib.
Can anyone help in this regard?

Thanks,
Lakshmi.