Re: [sqlite] sqlite3_enable_shared_cache

[Roger Binns]

Changing the setting while a session is active will lead
to serious problems.  That's why I added a check for that
behavior that makes the routine return SQLITE_MISUSE instead.


Ok, then the documentation is wrong as it says calling the
routine when connections exist will cause memory corruption.  
You have implmented it in such a way that no memory corruption

is possible.  You just get SQLITE_MISUSE if called while there
are connections.

Similarly the doc for this and sqlite3_thread_cleanup doesn't
make it clear that if you do close all your connections then
you don't need to call any of them.

Roger

[sqlite] Strange Behaviour on Solaris 8 on Sparc

[Phuah Yee Keat]

Hi,

I am currently running some tests to decide whether to use sqlite, and 
bump into some strange behavior. I compiled sqlite 3.3.4 from source and 
installed it on a solaris 8 on sparc without any updates. I run the same 
scripts (which insert 1000 entries without BEGIN/END block), on the same 
machine, but in different directories, getting totally different results:


###
# In the "db1" directory:
###
[EMAIL PROTECTED]:~/db1$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real3m53.708s
user0m0.710s
sys 0m2.140s
[EMAIL PROTECTED]:~/db1$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real3m48.267s
user0m0.590s
sys 0m2.120s
[EMAIL PROTECTED]:~/db1$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real3m48.435s
user0m0.630s
sys 0m2.100s
[EMAIL PROTECTED]:~/db1$

###
# In the "db2" directory:
###
[EMAIL PROTECTED]:~/db2$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real0m12.523s
user0m0.650s
sys 0m1.960s
[EMAIL PROTECTED]:~/db2$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real0m6.296s
user0m0.490s
sys 0m1.720s
[EMAIL PROTECTED]:~/db2$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real0m6.264s
user0m0.470s
sys 0m1.750s
[EMAIL PROTECTED]:~/db2$


I am really puzzled as to why there's so much difference in the time to 
execute the same script on the same box just in different directories, 
thinking that it might be placement of the directories in the physical 
disc itself causing the fsync calls to differ so much?


Is there any way where I can provide more information to help you guys 
help me?


Thanks in advance.

Cheers,
Phuah Yee Keat

Re: [sqlite] Performance Difference on Linux Kernel 2.4 and Kernel 2.6

[Phuah Yee Keat]

[EMAIL PROTECTED] wrote:

Your script requires 1002 separate ACID transactions.  Each such
transaction should require at least 2 complete revolutions of your
disk drive platter - meaning that the minimum time to complete your
script should be 16 seconds.

The 2.4 kernel finished faster than this, which tells me that the
2.4 kernel probably does not implement the fsync() system call
correctly.  I have heard reports of this but could not verify
it.  What this means is that if you loose power unexpectedly,
the 2.4 kernel might corrupt your database.  Probably since the
ReiserFS does a good job of journalling itself, you won't corrupt
the database, but your transactions will certainly not be Durable.

The 2.6 kernel is much slower because reiserFS is inefficient in
its implementation of fsync().


Thanks for the thorough explanation. I understand it better now.


You script does not measure how fast SQLite processes inserts.  It
measure how fast it does transacxtions.  If you put a BEGIN and
a COMMIT at the beginning and end of your script, I think you will
find that the whole thing will run in a fraction of a second under
either kernel.


Yeah, I have tried that, but my benchmarking is on individual 
transactions instead of everything in one transaction.


Thanks for the great help!



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



Cheers,
Phuah Yee Keat

Re: [sqlite] Probably, bug in SQLite (Or, what is wrong with this query?)

[Joe Wilson]

Re-run on Oracle 8.1.7 with the corrected SQL:

select a, (select c from (select b + 1 as c from dual)) as d
from (select 1 as a, 2 as b from dual) 

ORA-00904: invalid column name

It did not like the 'select b' either.

--- Alexander Kozlovsky <[EMAIL PROTECTED]> wrote:

> Cory Nelson wrote:
> 
> > > Can anybody tell me, what is wrong with this simple query:
> > >
> > >select a, (select c
> > >   from (select b + 1 as c) as Table2) as d
> > >from (select 1 as a, 2 as b) as Table1
> >
> > I don't claim to be a master of SQL, but isn't "select b + 1 as c" its
> > own query, without access to the "2 as b" in the outer query?
> 
> But this query is work well:
> 
> select a, (select b + 1 as c) as d
> from (select 1 as a, 2 as b) as Table1
> 
> The problem is only with subqueries nested in another subquery.
> 
> 
> -- 
> Best regards,
>  Alexander  mailto:[EMAIL PROTECTED]
> 
> 


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: RE: [sqlite] How to unsubscribe from this maillist ?

[z-axis]

Bill Giannotti！

As i have lost my programming job for old age (38 years old),  so i wanted to 
unsubscribe many maillists.
However i feel sqlite can still help me. so i determine not to unsubscribe now !

regards!
　　

 2006-04-03 07:25:00 您在来信中写道： 

I had an issue with my computer where I started getting files filling my
temporary directory without reason that began with "sqlite_" and wanted
to ask the forum if anyone new why this was.  In order to do so I had to
join the forum, knowing no other avenue to find out.  I got my answer -
turns out that AOL and McAfee use the sqlite database and are working on a
bug in their delivery.  I don't use sqlite, never did and don't know much
about programming.  All the other messages in the forum don't make any sense
to me and so I figured I'd unsubscribe.

thank you

-Original Message-
From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED]
Sent: Sunday, April 02, 2006 10:06 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to unsubscribe from this maillist ?


why do you want to unsuscribe?

On 4/1/06, z-axis < [EMAIL PROTECTED]>  wrote:
>>
>>  thanks
>>
>>

= = = = = = = = = = = = = = = = = = = = = =
致
礼！

　　z-axis
[EMAIL PROTECTED]
　2006-04-05

Re: [sqlite] Question on how to enter blobs in sqlite3

[Roman]

Hi Thomas,

I have tried 

sqlite> select * from loc_configuration; 
0|0|18|52|86|4|24|22|51|116|39|15|0|0|0|0|0|0|0|0|0|0|0|0

The blob is the one before the last one, and the length is the very last one
The serials are second, third , fourth and fifth columns.

sqlite> UPDATE loc_configuration SET loc_authorization_code_length = 18, 
loc_authorization_code = X'000102030405060708' WHERE loc_serial_0=0 AND 
loc_serial_1=18 AND loc_serial_2=52 AND loc_serial_3=86;
sqlite> select * from loc_configuration;

0|0|18|52|86|4|24|22|51|116|39|15|0|0|0|0|0|0|0|0|0|0||0

I get no response or error from sqlite3. 

Roman



On Tuesday 04 April 2006 05:21 pm, Thomas Chust wrote:
> On Tue, 4 Apr 2006, Roman wrote:
> > [...] I am using sqlite3 on am embedded platform. I am using c++ to
> > enter data. I have BLOB field, and I do not know how to enter hex values
> > from a data string. [...]
>
> Hello,
>
> if you need to put BLOB data verbatim into an SQL statement, SQLite3
> supports syntax like
> INSERT INTO blobs VALUES(X'deadbeef');
> You just denote the BLOB as a string of an even number of hexadecimal
> characters preceded by an 'X' or 'x'.
>
> In many cases it may be a better idea, though, to put placeholders into
> your SQL statement and to use sqlite3_bind_blob to load the BLOB data for
> statement execution.
>
> cu,
> Thomas

Re: [sqlite] Question on how to enter blobs in sqlite3

[Thomas Chust]

On Tue, 4 Apr 2006, Roman wrote:

[...] I am using sqlite3 on am embedded platform. I am using c++ to 
enter data. I have BLOB field, and I do not know how to enter hex values 
from a data string. [...]


Hello,

if you need to put BLOB data verbatim into an SQL statement, SQLite3 
supports syntax like

   INSERT INTO blobs VALUES(X'deadbeef');
You just denote the BLOB as a string of an even number of hexadecimal 
characters preceded by an 'X' or 'x'.


In many cases it may be a better idea, though, to put placeholders into 
your SQL statement and to use sqlite3_bind_blob to load the BLOB data for 
statement execution.


cu,
Thomas

Re: [sqlite] DBD Sqlite

[Clark Christensen]

You can get the underlying SQLite version from DBD-SQLite as 
$dbh->{sqlite_version};
 
 Make sure you set $dbh->{AutoCommit=>0}.  This will ensure you're always in a 
transaction.  Without it, you're probably committing every row.  From what I 
can tell, you can twiddle AutoCommit at any point in the program to turn it on 
(1), or off (0).

Using $dbh->{AutoCommit=>0}, and a DBI prepared statement, I see inserts read 
from a file running somewhere around 5K/second on a 733MHz P3 (Linux).  I have 
a daily process that imports ~9,900 records in 2.077 seconds

I think the docs for DBD-SQLite indicate a writer always locks the whole file, 
so you probably can't do dirty reads with DBD-SQLite.  Once you start writing, 
the reader won't have access to the DB until the writer commits or rolls-back.

 -Clark

- Original Message 
From: Sripathi Raj <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, April 4, 2006 4:18:35 PM
Subject: Re: [sqlite] DBD Sqlite

On 4/4/06, Nathan Kurz <[EMAIL PROTECTED]> wrote:
>
> > >> 3. The performance for inserts is really bad. Around 40k entries
> takes a
> > >>few hours. What might I be doing wrong? I do a commit after
> > >>all the inserts.
> > >
> > > A few things to help with speed:
> > >
> > > 1. Use DBI's prepared statements; eg, 1 prepare() and many execute().
> >
> >  Yes, this is what I do.
> > >
> > > 2. Don't commit for each row inserted but batch them so, say, you
> > >commit once per 1000 rows.
> > >
> >  Unfortunately, I cannot commit till I do all the inserts.
>
> That doesn't seem right for speed.  In addition to using "commit", are
> you beginning a transaction with "begin"?  Are your inserts
> particularly complex or large?  More details about what you are doing
> would probably be good here, since something odd is happening here.
> Maybe you could post a tiny test program along with the time it takes?
>
> --nate
>
> I don't begin the transaction with begin. My assumption was that the first
insert operation would automatically begin a transaction.
My inserts are fairly simple with two columsn being long strings of length
255.


my @values = ($task_info_gid,$file_type_gid,$extracted_path,$media_path,
$size,$ctime,$mtime,$job_id,$is_in_du);

Raj

[sqlite] Windows problem when updating

[Milton Sagen]

After a machine restart the first time I try to change a field in a  
record, on Windows, it takes an inordinately long time for the sqlite  
code to return, the amount of time is dependent on the size of the  
database but even for a 20 Meg one with about 4000 records, the time  
is in the order of a minute. Once the change is made I can quite the  
program and relaunch it, open the database, and make another change  
exactly like the first to another record and the sqlite code returns  
almost immediately. On the Mac OS X, I don't see this delay.


I see this with SQLite 2.8.16 as well as 3.3.4.

Here's the schema for the database:

CREATE TABLE MyTable (RID Integer Primary Key, Filename Text,  
PathType Integer, Path Text, Category Text null, Favorite Boolean  
null, Missing Boolean null, Thumbnail Blob null);

CREATE UNIQUE INDEX MyTable_RID on MyTable (RID);
CREATE INDEX MyTable_Filename on MyTable (Filename);

a record is added with

INSERT into MyTable (RID, Filename, PathType, Path, Category,  
Thumbnail) (1, 'afile.jpg', 1, 'path', 'data');


where
path = dos path (windows path - whatever you want to call it - it  
starts with c:\)
data = hex encoded data of a raw bit map - (this originally used  
2.8.16 and I could see any other way to get raw binary data into the  
database)


the update is simply this:

UPDATE MyTable set Favorite = 1 where RID = 1;

In fact I find that I get a delay anytime I change the database  
following a reboot of the machine, i.e. deleting a record, inserting  
a record, updating a record.


The routine that seems to get take the time is sqlite3VdbeExec.

Anybody have any idea what might be going on here or how to fix it?  
Why does it only happen after a reboot of the machine? Similarly, and  
this I see on both the Mac and Windows if on launch I do the following:


select rid from MyTable order by lower(filename);

on a database with approximatley 4000 records it will take about 14  
seconds, after a reboot, to complete. Quitting the program and  
relaunching on the Mac the select completes in 0.04 seconds.


Milt

Re: [sqlite] DBD Sqlite

[Gerry Snyder]

Sripathi Raj wrote:

 I don't begin the transaction with begin. My assumption was that the first


insert operation would automatically begin a transaction.
 

It does. But, the transaction it starts ends with that insert, and a new 
transaction begins with the next insert.


You need an explicit   begin

Re: [sqlite] DBD Sqlite

[Sripathi Raj]

On 4/4/06, Nathan Kurz <[EMAIL PROTECTED]> wrote:
>
> On Tue, Apr 04, 2006 at 04:18:35PM -0700, Sripathi Raj wrote:
> > On 4/4/06, Nathan Kurz <[EMAIL PROTECTED]> wrote:
> > >
> > > > >> 3. The performance for inserts is really bad. Around 40k entries
> > > takes a
> > > > >>few hours. What might I be doing wrong? I do a commit after
> > > > >>all the inserts.
> > >
> > > That doesn't seem right for speed.  In addition to using "commit", are
> > > you beginning a transaction with "begin"?  Are your inserts
> > > particularly complex or large?  More details about what you are doing
> > > would probably be good here, since something odd is happening here.
> > > Maybe you could post a tiny test program along with the time it takes?
> >
> > I don't begin the transaction with begin. My assumption was that the
> first
> > insert operation would automatically begin a transaction.
>
> True, it does an implicit "BEGIN", but it also does an implicit
> "COMMIT" when each command finishes.  Try adding an explicit "BEGIN;"
> before you do the inserts: 
>
> --nate



But if you set Autocommit to 0, doesn't it wait till commit is called
explicitly?

Re: [sqlite] DBD Sqlite

[Nathan Kurz]

On Tue, Apr 04, 2006 at 04:18:35PM -0700, Sripathi Raj wrote:
> On 4/4/06, Nathan Kurz <[EMAIL PROTECTED]> wrote:
> >
> > > >> 3. The performance for inserts is really bad. Around 40k entries
> > takes a
> > > >>few hours. What might I be doing wrong? I do a commit after
> > > >>all the inserts.
> >
> > That doesn't seem right for speed.  In addition to using "commit", are
> > you beginning a transaction with "begin"?  Are your inserts
> > particularly complex or large?  More details about what you are doing
> > would probably be good here, since something odd is happening here.
> > Maybe you could post a tiny test program along with the time it takes?
>
> I don't begin the transaction with begin. My assumption was that the first
> insert operation would automatically begin a transaction.

True, it does an implicit "BEGIN", but it also does an implicit
"COMMIT" when each command finishes.  Try adding an explicit "BEGIN;"
before you do the inserts: 

--nate

RE: [sqlite] how to fix problem of lock

[Marian Olteanu]

How long does one INSERT take? Do you have long transactions with INSERTs?
If you have one INSERT at a time and it doesn't take too long, and still you
have reader starvation issues with the SELECTs, the only solution that I see
is to queue requests and make sure that they are served on a
first-come-first-served basis. 

-Original Message-
From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 04, 2006 1:23 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] how to fix problem of lock

Please people help me :D, the project will be open source...

On 4/4/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
>
> OK English is not my first language, i am from south America, from
> Paraguay so i speak Spanish...
>
> I am doing a project in C multiplataform.. right it project like google,
> for a Linux.
>
> the project uses SQ Lite for repository.
>
> The software is a server which listen connection UDP. The server could
> index (INSERT) and search within the database (SELECT).
>
> The problem is when i do so much connection at the same time for do index
> and search at the same time.
>
> Know is it clear? sorry for my english :D
>
> Thanxs
>
>
>
>
> On 4/3/06, John Stanton <[EMAIL PROTECTED]> wrote:
> >
> > Try to explain what you are doing.  How do you access SQLITE?  What
> > language?  What SQL are you using.  How many concurrent users do you
> > have?  Are you getting a LOCK error because of multiple users?
> >
> > Cesar David Rodas Maldonado wrote:
> > > please i need some help
> > > On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> > >
> > >>i insert numbers and  select numbers, so what could be the solutions,
> > >>couse i have to do that
> > >>
> > >>
> > >>On 4/3/06, Pam Greene < [EMAIL PROTECTED] > wrote:
> > >>
> > >>>An INSERT can change the results of your SELECT, so the database has
> > to
> > >>>be
> > >>>locked during INSERT.  Otherwise, the result of your SELECT would
> > depend
> > >>>on
> > >>>whether the INSERT had finished yet.  (The INSERT might even have
> > only
> > >>>partly finished, which would mean the SELECT was looking at a
> > database
> > >>>in an
> > >>>inconsistent state.)  It's not good to have unpredictable results
> > like
> > >>>that.
> > >>>
> > >>>For more explanation of why what you want isn't a good idea, see any
> > >>>discussion of an "ACID" database, for example
> > >>>http://en.wikipedia.org/wiki/ACID .
> > >>>
> > >>>- Pam
> > >>>
> > >>>On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> > >>>
> > HElp me, couse i just need to do insert and select, i dont use
> > delete
> > >>>
> > >>>or
> > >>>
> > replate or update
> > 
> > On 4/3/06, Cesar David Rodas Maldonado < [EMAIL PROTECTED] > wrote:
> > 
> > >thanx man
> > >
> > >
> > >but is posible open several DATABASE with a programm and do
> > >>>
> > >>>transaccion
> > >>>
> > >without locked the table???
> > >
> > >On 4/3/06, Jay Sprenkle < [EMAIL PROTECTED]> wrote:
> > >
> > >
> > >>On 4/3/06, Cesar David Rodas Maldonado < [EMAIL PROTECTED] > wrote:
> > >>
> > >>>I have a database ok... i do a lot of insert and select, but
> > >>>
> > there
> > 
> > >>is
> > >>
> > >>>sometime that i cant do the select couse the database is
> > >>>
> > >>>locked...
> > >>>
> > >>>i have to do a lot of insert every time, so how can i do for
> > >>>
> > >>>dont
> > >>>
> > lock
> > 
> > >>the
> > >>
> > >>>database...
> > >>>
> > >>>understand guy?
> > >>
> > >>Try this:
> > >>http://sqlite.org/lang_transaction.html
> > >>
> > >
> > >
> > 
> > >>>
> > >
> >
> >
>

Re: [sqlite] DBD Sqlite

[Sripathi Raj]

On 4/4/06, Nathan Kurz <[EMAIL PROTECTED]> wrote:
>
> > >> 3. The performance for inserts is really bad. Around 40k entries
> takes a
> > >>few hours. What might I be doing wrong? I do a commit after
> > >>all the inserts.
> > >
> > > A few things to help with speed:
> > >
> > > 1. Use DBI's prepared statements; eg, 1 prepare() and many execute().
> >
> >  Yes, this is what I do.
> > >
> > > 2. Don't commit for each row inserted but batch them so, say, you
> > >commit once per 1000 rows.
> > >
> >  Unfortunately, I cannot commit till I do all the inserts.
>
> That doesn't seem right for speed.  In addition to using "commit", are
> you beginning a transaction with "begin"?  Are your inserts
> particularly complex or large?  More details about what you are doing
> would probably be good here, since something odd is happening here.
> Maybe you could post a tiny test program along with the time it takes?
>
> --nate
>
> I don't begin the transaction with begin. My assumption was that the first
insert operation would automatically begin a transaction.
My inserts are fairly simple with two columsn being long strings of length
255.


my @values = ($task_info_gid,$file_type_gid,$extracted_path,$media_path,
$size,$ctime,$mtime,$job_id,$is_in_du);

Raj

Re: [sqlite] DBD Sqlite

[Nathan Kurz]

> >> 3. The performance for inserts is really bad. Around 40k entries takes a
> >>few hours. What might I be doing wrong? I do a commit after
> >>all the inserts.
> >
> > A few things to help with speed:
> >
> > 1. Use DBI's prepared statements; eg, 1 prepare() and many execute().
> 
>  Yes, this is what I do.
> >
> > 2. Don't commit for each row inserted but batch them so, say, you
> >commit once per 1000 rows.
> >
>  Unfortunately, I cannot commit till I do all the inserts.

That doesn't seem right for speed.  In addition to using "commit", are
you beginning a transaction with "begin"?  Are your inserts
particularly complex or large?  More details about what you are doing
would probably be good here, since something odd is happening here.
Maybe you could post a tiny test program along with the time it takes?

--nate

Re: [sqlite] DBD Sqlite

[Sripathi Raj]

Darren Duncan wrote:
> At 15:03 -0700 4/4/06, Sripathi Raj wrote:
>> Hi,
>> I have a few questions regarding SQLite. I'm using it on Windows and
>> connecting to it from Perl.
>
> And I will answer some of them.
>
>> 1. How do I find out if the current version of DBD::SQLite uses SQLite
>> 3.0or greater?
>
> DBD::SQLite versions >= 1.0 embed SQLite versions >= 3.x.
>
> DBD::SQLite versions < 1.0 and all DBD::SQLite2 embed SQLite versions <=
2.x.
>
 2. How do I allow dirty reads? I understand that the whole file is locked
 for writing but I believe I can do reads using a deferred transaction. How
 do I do this from the Perl DBI?
>
> I'm not sure that what you want is possible. But why would you want to?

The database is being shared between two processes. The other process just
reads the database and never writes to it. Even if it doesn't get the
updated information it's ok to give whatever information it has currently.
Also it's not able to read even the table information.
>
>> 3. The performance for inserts is really bad. Around 40k entries takes a
few
>> hours. What might I be doing wrong? I do a commit after all the inserts.
>
> A few things to help with speed:
>
> 1. Use DBI's prepared statements; eg, 1 prepare() and many execute().

 Yes, this is what I do.
>
> 2. Don't commit for each row inserted but batch them so, say, you commit
once per 1000 rows.
>
 Unfortunately, I cannot commit till I do all the inserts.

Thanks,

 Raj
>
> -- Darren Duncan
>




On 4/4/06, Darren Duncan <[EMAIL PROTECTED]> wrote:
>
> At 15:03 -0700 4/4/06, Sripathi Raj wrote:
> >Hi,
> >  I have a few questions regarding SQLite. I'm using it on Windows and
> >connecting to it from Perl.
>
> And I will answer some of them.
>
> >1. How do I find out if the current version of DBD::SQLite uses SQLite
> >3.0or greater?
>
> DBD::SQLite versions >= 1.0 embed SQLite versions >= 3.x.
>
> DBD::SQLite versions < 1.0 and all DBD::SQLite2 embed SQLite versions <=
> 2.x.
>
> >2. How do I allow dirty reads? I understand that the whole file is locked
> >for writing but I believe I can do reads using a deferred transaction.
> How
> >do I do this from the Perl DBI?
>
> I'm not sure that what you want is possible.  But why would you want to?
>
> >3. The performance for inserts is really bad. Around 40k entries takes a
> few
> >hours. What might I be doing wrong? I do a commit after all the inserts.
>
> A few things to help with speed:
>
> 1. Use DBI's prepared statements; eg, 1 prepare() and many execute().
>
> 2. Don't commit for each row inserted but batch them so, say, you
> commit once per 1000 rows.
>
> >Thanks,
> >
> >Raj
>
> -- Darren Duncan
>

Re: [sqlite] DBD Sqlite

[Darren Duncan]

At 15:03 -0700 4/4/06, Sripathi Raj wrote:

Hi,
 I have a few questions regarding SQLite. I'm using it on Windows and
connecting to it from Perl.


And I will answer some of them.


1. How do I find out if the current version of DBD::SQLite uses SQLite
3.0or greater?


DBD::SQLite versions >= 1.0 embed SQLite versions >= 3.x.

DBD::SQLite versions < 1.0 and all DBD::SQLite2 embed SQLite versions <= 2.x.


2. How do I allow dirty reads? I understand that the whole file is locked
for writing but I believe I can do reads using a deferred transaction. How
do I do this from the Perl DBI?


I'm not sure that what you want is possible.  But why would you want to?


3. The performance for inserts is really bad. Around 40k entries takes a few
hours. What might I be doing wrong? I do a commit after all the inserts.


A few things to help with speed:

1. Use DBI's prepared statements; eg, 1 prepare() and many execute().

2. Don't commit for each row inserted but batch them so, say, you 
commit once per 1000 rows.



Thanks,

Raj


-- Darren Duncan

[sqlite] Question on how to enter blobs in sqlite3

[Roman]

Hello,
I am using sqlite3 on am embedded platform. I am using c++ to enter data. I 
have BLOB field, and I do not know how to enter hex values from a data 
string. I am trying to use update, but how do I enter col_name=' data ' 
format?

How does ' escaping work? I could not find help on the sqlite site, and I am 
trying this list.

Thanks in advance,
RK

Re: [sqlite] how to fix problem of lock

[John Stanton]

The method I have found works well and efficiently is to surround your 
Sqlite accesses with a semaphore and have accessing processes WAIT to 
get access to Sqlite.  For best performance you would only block when an 
INSERT was in progress and let multiple SELECTs run concurrently.


This assumes that all your users go through the same interface so that 
they can co-operate.


The alternative is to put busy logic into your Sqlite code, but that is 
tedious and cannot be as efficient as synchronising using the more 
efficient kernel based method of a semaphore.


Since it is a single file and linked into the application Sqlite is 
essentially single user, requiring that only one process at a time 
modify the database.  It has to use a file lock to enforce that 
constraint.  If you work with it in your design you can transparently 
handle large numbers of users.


As you will already have appreciated, a multi-user Sqlite system must 
have short transactions to effectively share access.  You might also 
look to making users who only read the database be read-only.


In an extreme case you could look to having database INSERTs be handled 
by a daemon which would single stream access.


Cesar David Rodas Maldonado wrote:

OK English is not my first language, i am from south America, from Paraguay
so i speak Spanish...

I am doing a project in C multiplataform.. right it project like google, for
a Linux.

the project uses SQ Lite for repository.

The software is a server which listen connection UDP. The server could index
(INSERT) and search within the database (SELECT).

The problem is when i do so much connection at the same time for do index
and search at the same time.

Know is it clear? sorry for my english :D

Thanxs



On 4/3/06, John Stanton <[EMAIL PROTECTED]> wrote:


Try to explain what you are doing.  How do you access SQLITE?  What
language?  What SQL are you using.  How many concurrent users do you
have?  Are you getting a LOCK error because of multiple users?

Cesar David Rodas Maldonado wrote:


please i need some help
On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:



i insert numbers and  select numbers, so what could be the solutions,
couse i have to do that


On 4/3/06, Pam Greene < [EMAIL PROTECTED]> wrote:



An INSERT can change the results of your SELECT, so the database has to



be
locked during INSERT.  Otherwise, the result of your SELECT would


depend


on
whether the INSERT had finished yet.  (The INSERT might even have only
partly finished, which would mean the SELECT was looking at a database
in an
inconsistent state.)  It's not good to have unpredictable results like
that.

For more explanation of why what you want isn't a good idea, see any
discussion of an "ACID" database, for example
http://en.wikipedia.org/wiki/ACID .

- Pam

On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:



HElp me, couse i just need to do insert and select, i dont use delete


or



replate or update

On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED] > wrote:



thanx man


but is posible open several DATABASE with a programm and do


transaccion



without locked the table???

On 4/3/06, Jay Sprenkle < [EMAIL PROTECTED]> wrote:




On 4/3/06, Cesar David Rodas Maldonado < [EMAIL PROTECTED]> wrote:



I have a database ok... i do a lot of insert and select, but



there



is



sometime that i cant do the select couse the database is


locked...



i have to do a lot of insert every time, so how can i do for


dont



lock



the



database...

understand guy?


Try this:
http://sqlite.org/lang_transaction.html

[sqlite] DBD Sqlite

[Sripathi Raj]

Hi,

 I have a few questions regarding SQLite. I'm using it on Windows and
connecting to it from Perl.

1. How do I find out if the current version of DBD::SQLite uses SQLite
3.0or greater?
2. How do I allow dirty reads? I understand that the whole file is locked
for writing but I believe I can do reads using a deferred transaction. How
do I do this from the Perl DBI?
3. The performance for inserts is really bad. Around 40k entries takes a few
hours. What might I be doing wrong? I do a commit after all the inserts.

Thanks,

Raj

Re: [sqlite] sqlite3_enable_shared_cache

[drh]

"Roger Binns" <[EMAIL PROTECTED]> wrote:
> > If you call sqlite3_enable_shared_cache while
> > another connection is open, you will get an
> > SQLITE_MISUSE error.  But you can ignore that
> > error if you want.
> 
> The doc is far scarier:
> 
>   This routine must not be called when any database 
>   connections are active in the current thread. Enabling 
>   or disabling shared cache while there are active database 
>   connections will result in memory corruption.
> 
> >From what you said it sounds like changing the setting
> while connections are active will cause corruption,
> but calling the api and setting it to what it already
> is will give SQLITE_MISUSE.

Changing the setting while a session is active will lead
to serious problems.  That's why I added a check for that
behavior that makes the routine return SQLITE_MISUSE instead.

> 
> Is the shared cache known to be bug free? 
> 

I am not aware of any bugs in the code.  This doesn't
mean there aren't any.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Programmatic notification of BEGIN TRANSACTION?

[drh]

Mike Ashmore <[EMAIL PROTECTED]> wrote:
> Hi,
> I have some triggers that fire on INSERT, UPDATE, and DELETE to a  
> view. Those triggers calls a user-defined library function. What I  
> was wondering was, is there some way my library function can be  
> notified of the beginning and end of a transaction?
> 
> For instance, if the user issues an UPDATE on the view that affects  
> four records, my UPDATE trigger fires four times. What I'd like to  
> know is when that outer UPDATE transaction begins and ends, so that I  
> can then group my user-defined function's actions in a way that  
> continues to guarantee transactional atomicity.
> 
> Is this possible short of mucking around in the VDBE? And if VDBE- 
> mucking is required, does anybody want to offer any pointers?
> 

http://www.sqlite.org/capi3ref.html#sqlite3_commit_hook
--
D. Richard Hipp   <[EMAIL PROTECTED]>

RE: [sqlite] how to fix problem of lock

[Doug Nebeker]

>From http://www.hwaci.com/sw/sqlite/whentouse.html
"Situations Where Another RDBMS May Work Better"
* High Concurrency

SQLite uses reader/writer locks on the entire database file. That means
if any process is reading from any part of the database, all other
processes are prevented from writing any other part of the database.
Similarly, if any one process is writing to the database, all other
processes are prevented from reading any other part of the database. For
many situations, this is not a problem. Each application does its
database work quickly and moves on, and no lock lasts for more than a
few dozen milliseconds. But there are some applications that require
more concurrency, and those applications may need to seek a different
solution. 

If you are really writing an app like Google, SQLite is quite possibly
not the best solution because of its locking.  


-Original Message-
From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 04, 2006 1:23 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] how to fix problem of lock

Please people help me :D, the project will be open source...

On 4/4/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
>
> OK English is not my first language, i am from south America, from 
> Paraguay so i speak Spanish...
>
> I am doing a project in C multiplataform.. right it project like 
> google, for a Linux.
>
> the project uses SQ Lite for repository.
>
> The software is a server which listen connection UDP. The server could

> index (INSERT) and search within the database (SELECT).
>
> The problem is when i do so much connection at the same time for do 
> index and search at the same time.
>
> Know is it clear? sorry for my english :D
>
> Thanxs
>
>
>
>
> On 4/3/06, John Stanton <[EMAIL PROTECTED]> wrote:
> >
> > Try to explain what you are doing.  How do you access SQLITE?  What 
> > language?  What SQL are you using.  How many concurrent users do you

> > have?  Are you getting a LOCK error because of multiple users?
> >
> > Cesar David Rodas Maldonado wrote:
> > > please i need some help
> > > On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> > >
> > >>i insert numbers and  select numbers, so what could be the 
> > >>solutions, couse i have to do that
> > >>
> > >>
> > >>On 4/3/06, Pam Greene < [EMAIL PROTECTED] > wrote:
> > >>
> > >>>An INSERT can change the results of your SELECT, so the database 
> > >>>has
> > to
> > >>>be
> > >>>locked during INSERT.  Otherwise, the result of your SELECT would
> > depend
> > >>>on
> > >>>whether the INSERT had finished yet.  (The INSERT might even have
> > only
> > >>>partly finished, which would mean the SELECT was looking at a
> > database
> > >>>in an
> > >>>inconsistent state.)  It's not good to have unpredictable results
> > like
> > >>>that.
> > >>>
> > >>>For more explanation of why what you want isn't a good idea, see 
> > >>>any discussion of an "ACID" database, for example 
> > >>>http://en.wikipedia.org/wiki/ACID .
> > >>>
> > >>>- Pam
> > >>>
> > >>>On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> > >>>
> > HElp me, couse i just need to do insert and select, i dont use
> > delete
> > >>>
> > >>>or
> > >>>
> > replate or update
> > 
> > On 4/3/06, Cesar David Rodas Maldonado < [EMAIL PROTECTED] >
wrote:
> > 
> > >thanx man
> > >
> > >
> > >but is posible open several DATABASE with a programm and do
> > >>>
> > >>>transaccion
> > >>>
> > >without locked the table???
> > >
> > >On 4/3/06, Jay Sprenkle < [EMAIL PROTECTED]> wrote:
> > >
> > >
> > >>On 4/3/06, Cesar David Rodas Maldonado < [EMAIL PROTECTED] >
wrote:
> > >>
> > >>>I have a database ok... i do a lot of insert and select, 
> > >>>but
> > >>>
> > there
> > 
> > >>is
> > >>
> > >>>sometime that i cant do the select couse the database is
> > >>>
> > >>>locked...
> > >>>
> > >>>i have to do a lot of insert every time, so how can i do for
> > >>>
> > >>>dont
> > >>>
> > lock
> > 
> > >>the
> > >>
> > >>>database...
> > >>>
> > >>>understand guy?
> > >>
> > >>Try this:
> > >>http://sqlite.org/lang_transaction.html
> > >>
> > >
> > >
> > 
> > >>>
> > >
> >
> >
>


To find out more about Reuters visit www.about.reuters.com

Any views expressed in this message are those of the individual sender, except 
where the sender specifically states them to be the views of Reuters Ltd.

Re: [sqlite] how to fix problem of lock

[Cesar David Rodas Maldonado]

Thanx fred

On 4/4/06, Fred Williams <[EMAIL PROTECTED]> wrote:
>
> I would suggest as a beginning:
>
> www.hwaci.com/sw/sqlite/whentouse.html
>
> www.hwaci.com/sw/sqlite/lockingv3.html
>
> www.hwaci.com/sw/sqlite/faq.html#q7
>
> www.hwaci.com/sw/sqlite/faq.html#q8
>
> www.sqlite.org/cvstrac/wiki?p=MultiThreading
>
> AND
>
> www.hwaci.com/sw/sqlite/
>
> has wealth of very helpful information about all things SQLite.
>
> Once you have digested all that information, perhaps you will be in a
> better position to ask more detailed and to the point questions.
> General cries for help pretty much go unnoticed here.
>
> Most everyone here does not have the time and especially the patience
> required to teach an SQLite beginner how to write a Google replacement.
>
> Fred
>
> > -Original Message-
> > From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, April 04, 2006 1:23 PM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] how to fix problem of lock
> >
> >
> > Please people help me :D, the project will be open source...
> >
> > On 4/4/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> > >
> > > OK English is not my first language, i am from south America, from
> > > Paraguay so i speak Spanish...
> > >
> > > I am doing a project in C multiplataform.. right it project
> > like google,
> > > for a Linux.
> > >
> > > the project uses SQ Lite for repository.
> > >
> > > The software is a server which listen connection UDP. The
> > server could
> > > index (INSERT) and search within the database (SELECT).
> > >
> > > The problem is when i do so much connection at the same
> > time for do index
> > > and search at the same time.
> > >
> > > Know is it clear? sorry for my english :D
> > >
> > > Thanxs
> > >
> > >
> ...
>
>

RE: [sqlite] how to fix problem of lock

[Fred Williams]

I would suggest as a beginning:

www.hwaci.com/sw/sqlite/whentouse.html

www.hwaci.com/sw/sqlite/lockingv3.html

www.hwaci.com/sw/sqlite/faq.html#q7

www.hwaci.com/sw/sqlite/faq.html#q8

www.sqlite.org/cvstrac/wiki?p=MultiThreading

AND

www.hwaci.com/sw/sqlite/

has wealth of very helpful information about all things SQLite.

Once you have digested all that information, perhaps you will be in a
better position to ask more detailed and to the point questions.
General cries for help pretty much go unnoticed here.

Most everyone here does not have the time and especially the patience
required to teach an SQLite beginner how to write a Google replacement.

Fred

> -Original Message-
> From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 04, 2006 1:23 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] how to fix problem of lock
>
>
> Please people help me :D, the project will be open source...
>
> On 4/4/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> >
> > OK English is not my first language, i am from south America, from
> > Paraguay so i speak Spanish...
> >
> > I am doing a project in C multiplataform.. right it project
> like google,
> > for a Linux.
> >
> > the project uses SQ Lite for repository.
> >
> > The software is a server which listen connection UDP. The
> server could
> > index (INSERT) and search within the database (SELECT).
> >
> > The problem is when i do so much connection at the same
> time for do index
> > and search at the same time.
> >
> > Know is it clear? sorry for my english :D
> >
> > Thanxs
> >
> >
...

Re: [sqlite] how to fix problem of lock

[Cesar David Rodas Maldonado]

Please people help me :D, the project will be open source...

On 4/4/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
>
> OK English is not my first language, i am from south America, from
> Paraguay so i speak Spanish...
>
> I am doing a project in C multiplataform.. right it project like google,
> for a Linux.
>
> the project uses SQ Lite for repository.
>
> The software is a server which listen connection UDP. The server could
> index (INSERT) and search within the database (SELECT).
>
> The problem is when i do so much connection at the same time for do index
> and search at the same time.
>
> Know is it clear? sorry for my english :D
>
> Thanxs
>
>
>
>
> On 4/3/06, John Stanton <[EMAIL PROTECTED]> wrote:
> >
> > Try to explain what you are doing.  How do you access SQLITE?  What
> > language?  What SQL are you using.  How many concurrent users do you
> > have?  Are you getting a LOCK error because of multiple users?
> >
> > Cesar David Rodas Maldonado wrote:
> > > please i need some help
> > > On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> > >
> > >>i insert numbers and  select numbers, so what could be the solutions,
> > >>couse i have to do that
> > >>
> > >>
> > >>On 4/3/06, Pam Greene < [EMAIL PROTECTED] > wrote:
> > >>
> > >>>An INSERT can change the results of your SELECT, so the database has
> > to
> > >>>be
> > >>>locked during INSERT.  Otherwise, the result of your SELECT would
> > depend
> > >>>on
> > >>>whether the INSERT had finished yet.  (The INSERT might even have
> > only
> > >>>partly finished, which would mean the SELECT was looking at a
> > database
> > >>>in an
> > >>>inconsistent state.)  It's not good to have unpredictable results
> > like
> > >>>that.
> > >>>
> > >>>For more explanation of why what you want isn't a good idea, see any
> > >>>discussion of an "ACID" database, for example
> > >>>http://en.wikipedia.org/wiki/ACID .
> > >>>
> > >>>- Pam
> > >>>
> > >>>On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> > >>>
> > HElp me, couse i just need to do insert and select, i dont use
> > delete
> > >>>
> > >>>or
> > >>>
> > replate or update
> > 
> > On 4/3/06, Cesar David Rodas Maldonado < [EMAIL PROTECTED] > wrote:
> > 
> > >thanx man
> > >
> > >
> > >but is posible open several DATABASE with a programm and do
> > >>>
> > >>>transaccion
> > >>>
> > >without locked the table???
> > >
> > >On 4/3/06, Jay Sprenkle < [EMAIL PROTECTED]> wrote:
> > >
> > >
> > >>On 4/3/06, Cesar David Rodas Maldonado < [EMAIL PROTECTED] > wrote:
> > >>
> > >>>I have a database ok... i do a lot of insert and select, but
> > >>>
> > there
> > 
> > >>is
> > >>
> > >>>sometime that i cant do the select couse the database is
> > >>>
> > >>>locked...
> > >>>
> > >>>i have to do a lot of insert every time, so how can i do for
> > >>>
> > >>>dont
> > >>>
> > lock
> > 
> > >>the
> > >>
> > >>>database...
> > >>>
> > >>>understand guy?
> > >>
> > >>Try this:
> > >>http://sqlite.org/lang_transaction.html
> > >>
> > >
> > >
> > 
> > >>>
> > >
> >
> >
>

Re: [sqlite] sqlite3_enable_shared_cache

[Roger Binns]

If you call sqlite3_enable_shared_cache while
another connection is open, you will get an
SQLITE_MISUSE error.  But you can ignore that
error if you want.


The doc is far scarier:

 This routine must not be called when any database 
 connections are active in the current thread. Enabling 
 or disabling shared cache while there are active database 
 connections will result in memory corruption.



From what you said it sounds like changing the setting

while connections are active will cause corruption,
but calling the api and setting it to what it already
is will give SQLITE_MISUSE.

Is the shared cache known to be bug free?  I need to default
to it on or off.

Roger

Re: [sqlite] how to fix problem of lock

[Cesar David Rodas Maldonado]

OK English is not my first language, i am from south America, from Paraguay
so i speak Spanish...

I am doing a project in C multiplataform.. right it project like google, for
a Linux.

the project uses SQ Lite for repository.

The software is a server which listen connection UDP. The server could index
(INSERT) and search within the database (SELECT).

The problem is when i do so much connection at the same time for do index
and search at the same time.

Know is it clear? sorry for my english :D

Thanxs



On 4/3/06, John Stanton <[EMAIL PROTECTED]> wrote:
>
> Try to explain what you are doing.  How do you access SQLITE?  What
> language?  What SQL are you using.  How many concurrent users do you
> have?  Are you getting a LOCK error because of multiple users?
>
> Cesar David Rodas Maldonado wrote:
> > please i need some help
> > On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> >
> >>i insert numbers and  select numbers, so what could be the solutions,
> >>couse i have to do that
> >>
> >>
> >>On 4/3/06, Pam Greene < [EMAIL PROTECTED]> wrote:
> >>
> >>>An INSERT can change the results of your SELECT, so the database has to
>
> >>>be
> >>>locked during INSERT.  Otherwise, the result of your SELECT would
> depend
> >>>on
> >>>whether the INSERT had finished yet.  (The INSERT might even have only
> >>>partly finished, which would mean the SELECT was looking at a database
> >>>in an
> >>>inconsistent state.)  It's not good to have unpredictable results like
> >>>that.
> >>>
> >>>For more explanation of why what you want isn't a good idea, see any
> >>>discussion of an "ACID" database, for example
> >>>http://en.wikipedia.org/wiki/ACID .
> >>>
> >>>- Pam
> >>>
> >>>On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> >>>
> HElp me, couse i just need to do insert and select, i dont use delete
> >>>
> >>>or
> >>>
> replate or update
> 
> On 4/3/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED] > wrote:
> 
> >thanx man
> >
> >
> >but is posible open several DATABASE with a programm and do
> >>>
> >>>transaccion
> >>>
> >without locked the table???
> >
> >On 4/3/06, Jay Sprenkle < [EMAIL PROTECTED]> wrote:
> >
> >
> >>On 4/3/06, Cesar David Rodas Maldonado < [EMAIL PROTECTED]> wrote:
> >>
> >>>I have a database ok... i do a lot of insert and select, but
> >>>
> there
> 
> >>is
> >>
> >>>sometime that i cant do the select couse the database is
> >>>
> >>>locked...
> >>>
> >>>i have to do a lot of insert every time, so how can i do for
> >>>
> >>>dont
> >>>
> lock
> 
> >>the
> >>
> >>>database...
> >>>
> >>>understand guy?
> >>
> >>Try this:
> >>http://sqlite.org/lang_transaction.html
> >>
> >
> >
> 
> >>>
> >
>
>

Re: [sqlite] Re: Db copy

[Dennis Cote]

Iulian Popescu wrote:


I looked at the .dump feature implementation in shell.c as well as how
vacuum is implemented. Based on that an idea will be to iterate through the
metainformation on the SQLITE_MASTER table in order to recreate the new Db
schema and for each table run SQL statements that will transfer the content
from the old db into the new one. However, I was wondering if there is an
easier or computationally less expensive way to accomplish the same thing
(do it without going through each table) something like "cloning" the B-Tree
representing the database.

 


Iulian,

I looked through the vacuum code as well and that seems like the best 
approach to me. I think you might be looking for optimizations that 
aren't needed. The copy operation should be very quick with only two SQL 
statements executed for each table, one to copy the schema, and one to 
copy the contents. The data copy itself will be reading records from one 
memory database and writing that record to the other memory database, 
one record at a time, but without any conversion from the internal 
record format. While not as fast as a direct memory copy, it should be 
pretty quick.


I don't think there is any way to clone the B-trees that make up the 
database. There is certainly no public API to access the B-trees, so 
even if something works today, there is no guarantee that it will work 
with the next version of SQLite.


If you really need to copy the database faster than the SQL copy allows, 
you might want to talk to Richard Hipp about some paid support. He can 
surely let you know if it is possible, and may be able to do it for you 
for a reasonable fee.


HTH
Dennis Cote

RE: [sqlite] Testing for table existence?

[Marian Olteanu]

sqlite_master table tells you everything about every object in the database

-Original Message-
From: Olaf Beckman Lapré [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 04, 2006 9:30 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Testing for table existence?

Thanks, I implemented this and it works.

I also stumbled upon the CREATE TABLE IF NOT EXIST syntax and I was
wondering if this wouldn't be a better idea, at the very least it would be
more convenient.

Olaf


- Original Message - 
From: "Philipp Knüsel" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, April 04, 2006 2:34 PM
Subject: Re: [sqlite] Testing for table existence?


> > Hi,
> >
> > How can I test for the existence of a single table in a SQLite database?
> >
> > I tried sqlite3_get_table with "select * from persons" to test for the
existence of the table 'persons' and I had hoped the return value would be
SQLITE_NOTFOUND in the case of a non-existent table. However, I got
SQLITE_ERROR, which is less helpfull.
> >
> > Any hints?
> >
> > Regards,
> >
> > Olaf
>
> Hello Olaf
>
> You can access the sqlite_master table.
>
> http://www.sqlite.org/cvstrac/wiki?p=InformationSchema
>
> enjoy!
>
> Philipp
>

Re: [sqlite] Testing for table existence?

[Olaf Beckman Lapré]

Thanks, I implemented this and it works.

I also stumbled upon the CREATE TABLE IF NOT EXIST syntax and I was
wondering if this wouldn't be a better idea, at the very least it would be
more convenient.

Olaf


- Original Message - 
From: "Philipp Knüsel" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, April 04, 2006 2:34 PM
Subject: Re: [sqlite] Testing for table existence?


> > Hi,
> >
> > How can I test for the existence of a single table in a SQLite database?
> >
> > I tried sqlite3_get_table with "select * from persons" to test for the
existence of the table 'persons' and I had hoped the return value would be
SQLITE_NOTFOUND in the case of a non-existent table. However, I got
SQLITE_ERROR, which is less helpfull.
> >
> > Any hints?
> >
> > Regards,
> >
> > Olaf
>
> Hello Olaf
>
> You can access the sqlite_master table.
>
> http://www.sqlite.org/cvstrac/wiki?p=InformationSchema
>
> enjoy!
>
> Philipp
>

[sqlite] Programmatic notification of BEGIN TRANSACTION?

[Mike Ashmore]

Hi,
I have some triggers that fire on INSERT, UPDATE, and DELETE to a  
view. Those triggers calls a user-defined library function. What I  
was wondering was, is there some way my library function can be  
notified of the beginning and end of a transaction?


For instance, if the user issues an UPDATE on the view that affects  
four records, my UPDATE trigger fires four times. What I'd like to  
know is when that outer UPDATE transaction begins and ends, so that I  
can then group my user-defined function's actions in a way that  
continues to guarantee transactional atomicity.


Is this possible short of mucking around in the VDBE? And if VDBE- 
mucking is required, does anybody want to offer any pointers?


Thanks,
-Mike Ashmore

Re: [sqlite] Testing for table existence?

[Philipp Knüsel]

> Hi,
> 
> How can I test for the existence of a single table in a SQLite database?
> 
> I tried sqlite3_get_table with "select * from persons" to test for the 
> existence of the table 'persons' and I had hoped the return value would be 
> SQLITE_NOTFOUND in the case of a non-existent table. However, I got 
> SQLITE_ERROR, which is less helpfull.
> 
> Any hints?
> 
> Regards, 
> 
> Olaf

Hello Olaf

You can access the sqlite_master table.

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

enjoy!

Philipp

[sqlite] Testing for table existence?

[Olaf Beckman Lapré]

Hi,

How can I test for the existence of a single table in a SQLite database?

I tried sqlite3_get_table with "select * from persons" to test for the 
existence of the table 'persons' and I had hoped the return value would be 
SQLITE_NOTFOUND in the case of a non-existent table. However, I got 
SQLITE_ERROR, which is less helpfull.

Any hints?

Regards, 

Olaf

Re: [sqlite] sqlite3_enable_shared_cache

[drh]

"Roger Binns" <[EMAIL PROTECTED]> wrote:
> I'd like to use sqlite3_enable_shared_cache in
> my APSW wrapper but I can't see a sensible
> safe way of doing it. The wrapper already enforces 
> the conditions mentioned in the doc (all operations 
> on a handle happening in the same thread).
> 
> It looks like I'd have to do a lot of housekeeping
> calling sqlite3_enable_shared_cache just before the
> first sqlite3_open in a thread and just after the
> last sqlite3_close.
> 
> Is SQLite already tracking these?  Why can't I
> call enable on every sqlite3_open and have it
> just succeed if sharing is already setup?

If you call sqlite3_enable_shared_cache while
another connection is open, you will get an
SQLITE_MISUSE error.  But you can ignore that
error if you want.

> 
> Will the last sqlite3_close not clean up the
> shared cache anyway?
> 

It will.

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

Re: AW: [sqlite] [ANN] SQLite ODBC driver version 0.67

[Christian Werner]

[EMAIL PROTECTED] wrote:
> 
> Hi,
> 
> I just would like to point out that:
> 
>   - a zip-archive for the drivers ("no-install") is not provided

That was my intention; is the ZIP version really necessary ?

>   - the documentation for sqliteodbc.c / sqliteodbc.h is missing

Ditto, since SQLite 2.* is legacy. The doxygen stuff had a lot
of warnings when formatting both SQLite2 and SQLite3 driver sources,
therefore I left the legacy one out.

If there's really interest in browsable source code for the SQLite2
driver, I'll try to fix it.

Regards,
Christian

AW: [sqlite] [ANN] SQLite ODBC driver version 0.67

[michael.hanssen]

Hi,

I just would like to point out that:

  - a zip-archive for the drivers ("no-install") is not provided

  - the documentation for sqliteodbc.c / sqliteodbc.h is missing


Regards,
Michael


-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Christian Werner
Gesendet: Montag, 3. April 2006 18:19
An: sqlite-users@sqlite.org
Betreff: [sqlite] [ANN] SQLite ODBC driver version 0.67

Version 0.67 of the SQLite ODBC driver is ready for download
from http://www.ch-werner.de/sqliteodbc
It fixes some Unicode length issues with the SQLite 2.8.17
version of the driver and now fully supports binary data.
The Win32 version is now made with a MinGW cross compiler
and NSIS.

Enjoy,
Christian

Re: [sqlite] sqlite3 dll symbols

[Essien Essien]

thanks you all for the explanations. It was a small project for a friend and
nothing critical really. I'm no windows guru, and i'm not so sure i want to
be (what with all the hoops and noops) ;)

Now back to Linux defender of... oops that's Voltron :)

On 4/1/06, John Stanton < [EMAIL PROTECTED]> wrote:
>
> Dennis Jenkins wrote:
> > Essien Essien wrote:
> >
> >>hiya,
> >>
> >>I have a code snippet that looks like:
> >>
> >>typedef int (*SQLITE3_CLOSE)(sqlite3*);
> >>typedef const char* (*SQLITE3_ERRMSG)(sqlite3*);
> >>typedef int (*SQLITE3_OPEN)(const char*, sqlite3**);
> >>typedef int (*SQLITE3_EXEC) (sqlite3*, const char*, sqlite3_callback,
> void*,
> >>char**);
> >>
> >>HINSTANCE sqlite3_dll;
> >>
> >>SQLITE3_CLOSE _sqlite3_close;
> >>SQLITE3_ERRMSG _sqlite3_errmsg;
> >>SQLITE3_OPEN _sqlite3_open;
> >>SQLITE3_EXEC _sqlite3_exec;
> >>
> >>int DB_Init()
> >>{
> >>sqlite3_dll = LoadLibrary("sqlite3.dll");
> >>if (sqlite3_dll == NULL) {
> >> printf("Cannot find sqlite3.dll. Make sure its in the same
> >>directory as the program\n");
> >> return 0;
> >>}
> >>
> >>_sqlite3_open = (SQLITE3_OPEN)GetProcAddress(sqlite3_dll,
> >>"sqlite3_open");
> >>if (_sqlite3_open == NULL) {
> >>printf("Cannot load function sqlite3_open");
> >>return 0;
> >>}
> >>}
> >>
> >>problem is, when ever i call DB_Init(), it always fails with 'Cannot
> load
> >>function sqlite3_open'. But it successfully passes the LoadLibrary
> portion.
> >>I'm not a win32 guru, so i'm willing to admit i've made a mistake
> somewhere.
> >>
> >>Any ideas on what i'm doing wrong?
> >>
> >>I'm using Turbo C++ 4.5 IDE and related tools. (yeah... i know turbo
> >>C++ 4.5is realy aged, but could this be the problem?)
> >>
> >>Essien
> >>
> >>
> >
> >
> > Since you have the borland compiler product, use the "TDUMP.EXE" tool to
> > view the PE header of the sqlite3.dll file.  Sometimes the functions
> > will be exported with a leading underscore.  If your compiler is
> > producing 32 bit binaries, and the DLL is also 32 bit, then you might
> > try adding a leading underscore to the symbol name when you call
> > 'GetProcAddress'.
> >
> You could also try statically linking Sqlite and bypassing the DLL.
>

Re: [sqlite] last modified time or version of sqlite database

[Chris Fletcher]

Thanks for your suggestions :)

Chris.

[sqlite] sqlite3_enable_shared_cache

[Roger Binns]

I'd like to use sqlite3_enable_shared_cache in
my APSW wrapper but I can't see a sensible
safe way of doing it. The wrapper already enforces 
the conditions mentioned in the doc (all operations 
on a handle happening in the same thread).


It looks like I'd have to do a lot of housekeeping
calling sqlite3_enable_shared_cache just before the
first sqlite3_open in a thread and just after the
last sqlite3_close.

Is SQLite already tracking these?  Why can't I
call enable on every sqlite3_open and have it
just succeed if sharing is already setup?

Will the last sqlite3_close not clean up the
shared cache anyway?

Roger