Re: [sqlite] Intermittent SQLITE_CANTOPEN on Windows

2008-10-14 Thread Filip Navara
http://www.mail-archive.com/sqlite-users@sqlite.org/msg34453.html

On Mon, Oct 13, 2008 at 6:52 PM, Doug <[EMAIL PROTECTED]> wrote:
> I'm using SQLite 3.5.6 on Windows and intermittently get SQLITE_CANTOPEN
> when doing an insert.  When that fails, I can use the debugger to go back up
> and step through the same lines again (using the same database handle -
> nothing opened or closed in between) and it will work.
>
> I am using sqlite3_bind_blob with the following: INSERT OR REPLACE INTO
> BlobTable (BlobKey, BlobVal) Values ('exampleKey', ?)
> in case that makes any difference (the SQLITE_CANTOPEN code is returned from
> sqlite3_step).
>
> I doubt this has anything to do with SQLite as it's been working perfectly
> for years, but I also can't figure out what has changed on my system such
> that this would be happening now.
>
> Thanks in advance for any ideas.
>
> Doug
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Efficient updating of arbitrary columns.

2008-10-14 Thread D. Richard Hipp

On Oct 14, 2008, at 4:26 PM, Peter van Hardenberg wrote:

> Hi all,
>
> We're using SQLite to back the media collection in Songbird, and our  
> database includes a table which often has a varying set of columns  
> updated. At the moment, I'm using a set of prepared statements that  
> look like this:
>
> UPDATE table SET columnN = ? WHERE table_id = ?;
>
> I talked with Habbie in IRC (who recommended I post to the list) and  
> he advised trying a query like this:
>
> UPDATE table SET
> column1 = CASE 1=? THEN ? ELSE column1 END,
> column2 = CASE 1=? THEN ? ELSE column2 END,
> column3 = CASE 1=? THEN ? ELSE column3 END
> WHERE table_id = ?;
>
> This would theoretically allow me to prepare a single query once and  
> to simply bind a "1" and a value to each property I want to change  
> with the rest staying the same. Unfortunately, the query executes  
> very slowly, about an order of magnitude slower than simply piecing  
> together the query as a string each time and compiling it from  
> scratch.


I tried the experiment on Linux.  I used:

 CREATE TABLE t1(a,b,c,d);
 -- insert over 1000 rows of data.

UPDATE t1 SET
 a  = CASE WHEN 1=$ax THEN $av END,
 b  = CASE WHEN 1=$bx THEN $bv END,
 c  = CASE WHEN 1=$cx THEN $cv END,
 d  = CASE WHEN 1=$dx THEN $dv END
WHERE
 rowid=$rid

where the bx, bv, cx, cv, dx, and dv values were all unbound and thus  
understood as NULL.  I prepared the statement once and reused it to do  
1000 inserts.  The average time was 26.058 milliseconds.  Then I did:

 UPDATE t1 SET a=$av WHERE rowid=$rid

I prepared the statement separately 1000 times.  The average time was  
55.458 milliseconds.

Everything was done inside a single transaction.



D. Richard Hipp
[EMAIL PROTECTED]



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


[sqlite] Efficient updating of arbitrary columns.

2008-10-14 Thread Peter van Hardenberg
Hi all,

We're using SQLite to back the media collection in Songbird, and our database 
includes a table which often has a varying set of columns updated. At the 
moment, I'm using a set of prepared statements that look like this:

UPDATE table SET columnN = ? WHERE table_id = ?;

I talked with Habbie in IRC (who recommended I post to the list) and he advised 
trying a query like this:

UPDATE table SET 
 column1 = CASE 1=? THEN ? ELSE column1 END,
 column2 = CASE 1=? THEN ? ELSE column2 END,
 column3 = CASE 1=? THEN ? ELSE column3 END
WHERE table_id = ?;

This would theoretically allow me to prepare a single query once and to simply 
bind a "1" and a value to each property I want to change with the rest staying 
the same. Unfortunately, the query executes very slowly, about an order of 
magnitude slower than simply piecing together the query as a string each time 
and compiling it from scratch.

On IRC, Habbie expressed some surprise that it was so slow. While I'm no expert 
with analyzing the VM output, I suspect SQLite is making some suboptimal 
decisions about how, or how often, to pull the data required to execute the 
query. Any suggestions on how to improve the performance of this query, or 
another one like it?

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


[sqlite] Problem : SQLite Database error

2008-10-14 Thread list
Heya guys,

iv got a database which was made my a program called BluePhoneElite,  
its a Mac OS X piece of software which pairs with a mobile phone over  
bluetooth to allow messages and calls to be managed from the  
computer... awesome piece of software untill it breaks.

Somehow the database has become corrupted, but my experience with  
programming and SQL is kinda limited, so turn to you guys...

There are two databases, one for messages (text's) and the other for  
calls ! both are .sqlite extensions (e.g messages.sqlite & calls.sqlite)

Iv managed to open them in SQLite Expert Personal 1.7.31 and look  
within the data, this is where it gets annoying, everything seems to  
look okay to me the data is perfectly readable (aka, no random symbols  
or things which look out of place)...

But, when i ask SQLite Personal to check the integreity of the  
databases i get this;

Messages.sqlite

*** in database main ***
On tree page 7686 cell 67: invalid page number 7692
On tree page 7686 cell 67: Child page depth differs
On tree page 7686 cell 68: invalid page number 7693
On tree page 7686 cell 69: invalid page number 7694
On tree page 7686 cell 70: invalid page number 7701
On tree page 7686 cell 71: invalid page number 7702
On tree page 7686 cell 72: invalid page number 7695
On tree page 7686 cell 73: invalid page number 7697
On tree page 7686 cell 74: invalid page number 7700
On tree page 7686 cell 75: invalid page number 7696
On tree page 7686 cell 76: invalid page number 7698
On page 7686 at right child: invalid page number 7699

Calls.sqlite

*** in database main ***
On page 273 at right child: invalid page number 361

Suffice to say these messages dont mean much to me, anyone else have  
any ideas ?

Also, when i scroll through the data, i keep getting alert messages  
popping up saying "The database disk image is malformed" and the  
details of this error just says
Exception message: The database disk image is malformed
Exception class: ESQLiteException
Date/Time: 2008-10-11 23:02:35.734

Im kinda desperate to get these databases back into a working state,  
can anyone help ?

Regards

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


Re: [sqlite] transaction question

2008-10-14 Thread Dan

On Oct 14, 2008, at 9:06 PM, Gene Allen wrote:

> I have a large sqlite database and I'm inserting a bunch of records  
> into it.
> My question is this..shouldn't the -journal be getting larger since  
> I've
> wrapped all the inserts inside a transaction?
>
>
>
> I'm watching the file sizes change and the main file is getting  
> larger, but
> the journal files is staying at 44kb.Is there something  
> different I have
> to do while large  Thanks
>
>
>
> Gene Allen
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


Re: [sqlite] Foreign Key Triggers: ABORT, FAIL, or ROLLBACK

2008-10-14 Thread Dan

On Oct 14, 2008, at 1:07 AM, John Belli wrote:

> What is the recommended conflict to raise during an FK enforcement
> trigger? I'm not asking about how to create the triggers, I've figured
> that part out; I just want to know which conflict should be used. I
> think I'm asking, which do other db systems tend to use?

ABORT seems right to me. Causes the current statement to have no
effect, but does not rollback the current transaction.

Dan.


>
>
>
> JAB
> -- 
> John A. Belli
> Software Engineer
> Refrigerated Transport Electronics, Inc.
> http://www.rtelectronics.com
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


[sqlite] transaction question

2008-10-14 Thread Gene Allen
I have a large sqlite database and I'm inserting a bunch of records into it.
My question is this..shouldn't the -journal be getting larger since I've
wrapped all the inserts inside a transaction?

 

I'm watching the file sizes change and the main file is getting larger, but
the journal files is staying at 44kb.Is there something different I have
to do while large http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Compile SQLite3 for MS Windows Driver Kit user-mode application

2008-10-14 Thread Bjorn Rauch

Hi again,
 
First, I am not an expert in WDK programming. But there are a few nice samples 
very close to what I need and thus I tried to compile a WDK sample with the 
sqlite3 DLL. The sample is a minifilter driver, which consists of two parts: 
the minifilter driver itself, which runs in kernel-mode and a user-mode 
application that catches the filtered requests through a communication port 
bridging the kernel- and user-mode.
 
I changed the user-mode application to use the sqlite3 api. To start with, I 
didn't do much in the code, just open/close a database. In order to build the 
user-mode application, I generated a lib-file for the sqlite3.dll. However, the 
WDK environment ignored the lib-file entirely whatever I tried. I couldn't 
figure out why but I guess this does not really work. For example, the standard 
C libraries and headers are all shipped separately with the WDK.
 
My second approach was to compile sqlite3 myself within the WDK. But the WDK 
build environment requires warnings to be treated as errors which in my opinion 
makes perfect sense. (I woudln't believe 10 agreeing programmers either; I have 
seen examples where all of them agreed that there would be no coding error. But 
of course this was the case. Btw, why checking intellectually that the 
conversions are safe when fixing it properly takes no time in comparison?? I 
don't believe it is checked.) Also, the number of warnings/errors is higher 
than in normal Win32 build (679 warnings to 524 warnings on warning level 4). 
Plus 1 error regarding localtime_s (C4013, undefined). This error is crazy as 
the function is in the defined by the WDK as well as included to the source. 
But this is a side note only.
 
Mark Spiegel wrote: > Not sure why the Win32 DLL is not compatible. I would 
think it should > be. You might want to work that out first. Can you elaborate?
> > As for compiling with the WDK, it can be done. The amalgamated source > is 
> > best.> > The flood of warnings is a pain. SQLite dev claims they are all > 
> > spurious, but with so many I wouldn't venture to guess how they can tell.>  
> > > For W32, you should be able to disable "treat warnings as errors" for > 
> > just the sqlite3.c file if your development organization allows this. > If 
> > you are building 64 bits, then you have more work to do. The last > time I 
> > ported in new SQLite source, it still cast 32 bit integers into > 64 bit 
> > pointers. The WDK compiler isn't going to allow this without > some source 
> > modifications. I did write a ticket so this might be > fixed. As of the 
> > last time I checked, it was not.> > Bjorn Rauch wrote:> > Hello,> > > > Has 
> > anybody tried to compile SQLite3 with the MS WDK? The Win32 
> > DLL is not compatible as far as I understand and recompiling with the 
> > WDK is > necessary. But using the source code as is results in many 
> > warnings (mostly conversion errors). The WDK does not tollerate these.> > > 
> > > Best regards,> > Bjorn
_
Explore the seven wonders of the world
http://search.msn.com/results.aspx?q=7+wonders+world=en-US=QBRE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] problem with sqlite3_exec() and select sql statemant

2008-10-14 Thread John Stanton
Use the correct SQL delimiter for a literal - single quotes, e.g. 
'google.com'.


Hari wrote:
> Hi as i am new to sqlite.
> I have problem when i am using sqlite3_exec() function with select
> as i am using sqlite3_open() to opening database
> then creating table and inserting some information using sqlite3_exec()
> and 'create table' and 'insert into'
> then again if i use sqlite3_exec() with select like
> my sql statement is : select * from my_table where
> Primarykey="google.com"
> where google.com primary key and its related information is not already
> in my table but it is returning
> SQLITE_OK...even it must be return some error as that primary key is not
> in my table.
> .how should i get the error...when using select and if primary key will
> not be there ..after creating table with using sqlite3_exec() function +
> select sql statemant.
> 
> Thanks in advance for any ideas
> Harioum
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


Re: [sqlite] sqlite3_open() problem

2008-10-14 Thread Neo
I run sqlite3.exe under Windows 95 like this:
C:\sqlite3 test
sqlite>create table t(id);

Then sqlite3 crashes.

Leandro dos Santos Ribeiro wrote:
> Leandro dos Santos Ribeiro wrote:
>   
>> D. Richard Hipp wrote:
>>   
>> 
>>> On Oct 10, 2008, at 4:59 PM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED] 
>>>  > wrote:
>>>
>>>   
>>> 
>>>   
 Hello Team.

 I have a problem with running SQLite.
 I am running linux 2.6.17 on *ARM* and basically problem is that my
 application
 is crushing on *sqlite3_open*()  function while the sqlite3 command
 shell is
 running without problems.
 
   
 
>>> The command-line shell uses sqlite3_open() too.  So if it works there,  
>>> I do not understand why it is not working in your program.  Have you  
>>> run your program in a debugger to see exactly where it is crashing?
>>>
>>>
>>>
>>> D. Richard Hipp
>>> [EMAIL PROTECTED]
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>   
>>> 
>>>   
>> Hello D. Richard Hipp,
>>
>> Yes, I did run the program in a debugger and it is crashing when the 
>> program tries to execute the sqlite3_open() function.
>> It seems that I'm not the only one with that problem, but I didn't find 
>> the answer yet.
>> Thanks .
>>
>>  
>>  Best regards.
>>  
>>   Leandro S. Ribeiro
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>   
>> 
> Hello everybody,
>
> I found the problem with the sqlite3_open() crashing.
> To solve it I just added the libs  libpthread and libdl to my project.
> Thanks to all.
>
>  Best regards
>  Leandro S. Ribeiro
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

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


[sqlite] Foreign Key Triggers: ABORT, FAIL, or ROLLBACK

2008-10-14 Thread John Belli
What is the recommended conflict to raise during an FK enforcement
trigger? I'm not asking about how to create the triggers, I've figured
that part out; I just want to know which conflict should be used. I
think I'm asking, which do other db systems tend to use?


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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


Re: [sqlite] Testing for existence of extension

2008-10-14 Thread Christoph Burgmer
Am Tuesday, 14. October 2008 schrieb Roger Binns:
> You may want to create a ticket asking for a method of getting the names
> of available extensions.

See #3436.

Thanks for the answer.

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


Re: [sqlite] problem with sqlite3_exec() and select sql statemant

2008-10-14 Thread Martin.Engelschalk
Hello Hariom,

- use single quotes around text constants: select * from my_table where 
Primarykey='google.com'
- use sqlite3_prepare and sqlite3_step to select data. First call 
sqlite3_prepare for your statement and then sqlite3_step in al loop 
until it returns SQLITE_DONE
- It is not an error if a select statement returns no rows because of a 
where clause. In this case, the first call to sqlite3_stepreturns 
SQLITE_DONE

Martin

Hari schrieb:
> Hi as i am new to sqlite.
> I have problem when i am using sqlite3_exec() function with select
> as i am using sqlite3_open() to opening database
> then creating table and inserting some information using sqlite3_exec()
> and 'create table' and 'insert into'
> then again if i use sqlite3_exec() with select like
> my sql statement is : select * from my_table where
> Primarykey="google.com"
> where google.com primary key and its related information is not already
> in my table but it is returning
> SQLITE_OK...even it must be return some error as that primary key is not
> in my table.
> .how should i get the error...when using select and if primary key will
> not be there ..after creating table with using sqlite3_exec() function +
> select sql statemant.
>
> Thanks in advance for any ideas
> Harioum
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
[EMAIL PROTECTED]
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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


Re: [sqlite] Joining 2 views

2008-10-14 Thread Dan

On Oct 14, 2008, at 4:39 AM, Guenther Schmidt wrote:

> Hi,
>
> unfortunately I've hit a point where I run a query where one VIEW  
> joins
> another VIEW.
>
> Both views are rather large and since there is no index on the fields
> where they join the query takes very very long. (About 15 min).
>
> Does anybody here know a solution to this problem?

What are the view definitions and what query are you running? It may
be that you can add an index to one of the underlying real tables that
will help.

Dan.



> Best regards
>
> Günther
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


[sqlite] problem with sqlite3_exec() and select sql statemant

2008-10-14 Thread Hari
Hi as i am new to sqlite.
I have problem when i am using sqlite3_exec() function with select
as i am using sqlite3_open() to opening database
then creating table and inserting some information using sqlite3_exec()
and 'create table' and 'insert into'
then again if i use sqlite3_exec() with select like
my sql statement is : select * from my_table where
Primarykey="google.com"
where google.com primary key and its related information is not already
in my table but it is returning
SQLITE_OK...even it must be return some error as that primary key is not
in my table.
.how should i get the error...when using select and if primary key will
not be there ..after creating table with using sqlite3_exec() function +
select sql statemant.

Thanks in advance for any ideas
Harioum
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users