Re: [sqlite] SQLite & ISO8859-x characters (Linux, C)

2008-10-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Zbigniew Baniewski wrote:
> How one should handle this? SQLite has UTF-8 by default. 

You seem to doubt being all Unicode is a good thing :-)  Read this
http://www.joelonsoftware.com/articles/Unicode.html

> What C-function (Linux) could be considered as most convenient? Perhaps
> there's a doc with explanation (in the context of SQLite-usage)?

SQLite does not include conversion from random non-Unicode encodings to
or from Unicode.  (It does include conversion between 8 bit and 16 bit
Unicode encodings).

If you just want a simple bytes in give the same bytes out then use
blobs in SQLite.  If you think your bytes are actually strings then
reread the link above again :-)

To do the conversion within your code you should use iconv
http://en.wikipedia.org/wiki/Iconv

If you want to do manipulation of the text (once it is in unicode) such
as upper/lower casing or sorting then you need to know about locales.
This is because the exact same sequence of characters sort, upper case,
lower case etc differently depending on where you are.  As an example
Turkic languages have multiple letter i, German has ß which behaves like
s, various accents sort differently in different European countries.
Fortunately there is a libary you can ask to do the right locale
specific thing
http://en.wikipedia.org/wiki/International_Components_for_Unicode
A default SQLite compilation only deals with the 26 letter Roman
alphabet.  If you enable ICU with SQLite then you get good stuff
http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt  (*)

You Linux distribution almost certainly has iconv binary and libraries
already installed.  ICU should be installed already or easily
installable via your package manager.

(*) Viewing that page is a good example of how messy this gets.  The
actual README.txt is encoded in UTF8.  However the cvstrac web server
tells the browser that it is encoded as ANSI_X3.4-1968 (a fancy name for
 ASCII).  If you scroll to just before section 1.2 you can see the
Turkish lower case dotless i being mangled.  I like to test using the
front page of http://Wikipedia.org as it contains the names of a wide
variety of languages in those languages and hence uses a wide sampling
of Unicode characters.

In summary, never confuse bytes with strings (which C sadly treats as
the same thing).  Either always uses bytes (and SQLite blobs) for
everything or use strings (and SQLite strings) for everything.  If you
take the latter approach and have to deal with external input/output
then you must know what encodings are being used and it is best to
convert to Unicode as early as possible on input and late as possible on
output.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFI9qkcmOOfHg372QQRAkO+AJ9rXxdLkyjgZGYUS+W3RMmOJel0ZgCg44e2
7FpA+U2cn0DusHMSR0ZEl8Q=
=a9T7
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLITE_SCHEMA question

2008-10-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Fin Springs wrote:
> Under what circumstances could one expect to get SQLITE_SCHEMA back  
> from sqlite3_exec()?

When using the sqlite3_prepare_v2 api, SQLite attempts to reprepare
statements five times when the schema changes(*).  In theory another
connection repeatedly changing the schema could cause all 5 tries to
fail.  The main cause of them failing is if the statement becomes
invalid.  One example would be if it uses a collation that was unregistered.

sqlite3_exec uses the old deprecated v1 of sqlite3_prepare and only
makes one retry attempt.  I have no idea why it doesn't just use v2 of
the function.

(*) A schema change is far more than actually changing a table.
Prepared statements are low level code and include things like "use
index 12 to get column 3".  Any schema change to any table or index
could make those numbers wrong and the simplest solution is for the
internals to return SQLITE_SCHEMA and have the statements reprepared.
Even things like changing collations or user defined functions are
sufficient to need repreparation even if your query doesn't use them.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFI9qLjmOOfHg372QQRAscmAKDkeFt/PAD2im09AQ2cllXkhyaq6wCg2NLC
g9wqVCe9u03oGFPpHL2hamA=
=FMms
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] SQLite & ISO8859-x characters (Linux, C)

2008-10-15 Thread Zbigniew Baniewski
How one should handle this? SQLite has UTF-8 by default. This makes
a bit of conversion necessary before INSERTion (and the opposite after
SELECTion, and so on...) - am I right? Or perhaps it can be notified at
C-level about client-encoding - and is able to convert on its own?

What C-function (Linux) could be considered as most convenient? Perhaps
there's a doc with explanation (in the context of SQLite-usage)?
-- 
pozdrawiam / regards

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


Re: [sqlite] Intermittent SQLITE_CANTOPEN on Windows

2008-10-15 Thread Filip Navara
Hmm, it didn't help. I can still easily reproduce the TortoiseSVN bug.
The only reliable workaround is to use different journal_mode (or
implementing renaming of the journal file before deleting it).

Best regards,
F.

On Wed, Oct 15, 2008 at 2:33 PM, Shane Harrelson <[EMAIL PROTECTED]> wrote:
> Version 3.6.1 (IIRC) had changes to improve the error detection and retry
> logic for this condition (typically caused when a 3rd party  application
> opens the SQLite journal file.)
> HTH.
> -Shane
>
>
> On Mon, Oct 13, 2008 at 12: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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] SQLITE_SCHEMA question

2008-10-15 Thread Fin Springs
I have an application that uses SQLite 3.5.7. I have recently started  
seeing occasional SQLITE_SCHEMA errors being returned by  
sqlite3_exec() that I don't understand.

The application doesn't do ALTER TABLE. It creates the database file  
afresh on each run (it's created in a RAM file system). All  
connections are from the same application process, across several  
threads. I do have some long-living connections , on the order of  
hours, and there are some DROP TABLE IF EXISTS / CREATE TABLE calls in  
the application, although normally only at startup. Most changes to  
data are either via explicit UPDATEs or by INSERTs to tables that have  
an ON CONFLICT REPLACE in their CREATE TABLE definition. All  
transactions against the database are initiated with explicit BEGIN  
IMMEDIATEs.

Under what circumstances could one expect to get SQLITE_SCHEMA back  
from sqlite3_exec()?

Cheers,
Dave

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


Re: [sqlite] Seeking documentation on writing own Tokenizer

2008-10-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jonathon wrote:
> I am interested in writing my own tokenizer for sqlite3.  I've gone through
> Google, as well as the sqlite documentation and unfortunately, I haven't
> found anything helpful. 

See the source directory
http://www.sqlite.org/cvstrac/dir?d=sqlite/ext/fts3 specifically the
README.tokenizers file.  You can use the builtin tokenizers as well as
the icu one as examples.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFI9nx+mOOfHg372QQRAmH/AJ99iK5yV8R/clJmVoYTqrqdbY0PGQCbBjgC
jj9cElxK/hxNdnkWtaBMImU=
=NeQt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] basic insert questions...

2008-10-15 Thread David Clark
At 03:01 PM 10/15/2008, Jay A. Kreibich wrote:
>On Wed, Oct 15, 2008 at 01:38:16PM -0500, David Clark scratched on the wall:
> > Ok I want to convert a large application which uses ad-hoc data
> > structures and file formats to a sqlite database.  But before I do that
> > I am working on a proof of concept test to show that yea it will work
> > once conversion is done.
> >
> > But alas my first test is failing miserably.  Well, it works it just
> > takes way too long.  Like at least all night.
> > I create table tbl2
> >  f1 varchar(30)
> >  f2 varchar(30)
> >  f3 varchar(30)
> >  identity integer primary index autoincrement
> >
> > That works I have the table.
> > I go to insert into with
> > insert into tbl2 values ("0001", "0001", "0001", NULL);
> >
> > insert into tbl2 values ("0002", "0002", "0002", NULL);
> >
> > Ok I did each insert as separate query using the 5 minute example and
> > it took all night and never did complete.
>
>   You need to wrap them up in a transaction.

ok will look into that.




>   Also, if you want to store integers, store integers.  SQLite allows
>   the same column to store records of different types.

No the real application uses mostly string data.  I am using the 
numbers just to give me something different to put in.


>   Also, string-literals in SQL use single quotes.

Yes that was just a typeo on the post...sorry about that...the app 
uses ' quotes.


> > So then I decided...ok the fsync() for serialization is the problem.
> >
> > So I looked at limits.html and make a single query out of as many as
> > would fit in a 100 byte buffer.  That turned out to be
> > 15625 inserts.  That should be one fsync per query so should be much
> > faster...but alas I don't think it is.
>
>   That won't process them as one statement.  Even if you pass them into
>   SQLite as a big command buffer, they're still processed one at a
>   time.
>
>   Issue the command "BEGIN", do 100 to 1 INSERTs, issue a "COMMIT".
>   You should see a very noticeable difference in speed.
>
> > My second test would be a random query into this large table to see
> > how long that took.  Then random queries from a large
> > number of threads.  Get a query time under 1 second for pulling up a
> > record...and this product is so in my application.
>
>   Depending on your query needs, you might need to look into indexes.
>
>-j
>
>--
>Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
>"Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
>___
>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] basic insert questions...

2008-10-15 Thread Doug
I don't mean to pick on the OP, but this is such a commonly asked question
for people new to SQLite (including me not so long ago) that maybe putting
something like what you just said many places on the website (besides just
the Wiki) would help.  Perhaps on the INSERT doc page?  And maybe make it
bold?

Doug

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:sqlite-users-
> [EMAIL PROTECTED] On Behalf Of D. Richard Hipp
> Sent: Wednesday, October 15, 2008 3:16 PM
> To: [EMAIL PROTECTED]; General Discussion of SQLite Database
> Subject: Re: [sqlite] basic insert questions...
> 
> 
> On Oct 15, 2008, at 4:01 PM, Jay A. Kreibich wrote:
> >
> >  Issue the command "BEGIN", do 100 to 1 INSERTs, issue a
> "COMMIT".
> >  You should see a very noticeable difference in speed.
> >>
> 
> Just to amplify Jay's words: On a workstation, SQLite should do at
> least 50,000 INSERTs per second.  But due to limitations of spinning
> disk drives, you can get at most 60 transactions per second.  If you
> do not use BEGIN...COMMIT, then each INSERT is a separate transaction,
> regardless of whether or not they are in the same string.  By doing
> the BEGIN...COMMIT with the INSERT statements in between, you can do
> thousands and thousands of fast INSERTs for each relatively slow
> COMMIT.
> 
> 
> D. Richard Hipp
> [EMAIL PROTECTED]
> 
> 
> 
> ___
> 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] Error:"Expression cannot be evaluated" withsqlite3_exec()

2008-10-15 Thread Igor Tandetnik
Alberto Tellaeche <[EMAIL PROTECTED]>
wrote:
> Tank you very much for the response.
> the array is as expected, sprintf is woking fine. Also I get the same
> error if I write the SQL command directly in the sqlite3_exec()
> function, so this is not the error...

Show the CREATE TABLE statement for ALUMNO table. Show the exact text of 
the statement you run, after sprintf.

Igor Tandetnik 



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


Re: [sqlite] basic insert questions...

2008-10-15 Thread D. Richard Hipp

On Oct 15, 2008, at 4:01 PM, Jay A. Kreibich wrote:
>
>  Issue the command "BEGIN", do 100 to 1 INSERTs, issue a "COMMIT".
>  You should see a very noticeable difference in speed.
>>

Just to amplify Jay's words: On a workstation, SQLite should do at  
least 50,000 INSERTs per second.  But due to limitations of spinning  
disk drives, you can get at most 60 transactions per second.  If you  
do not use BEGIN...COMMIT, then each INSERT is a separate transaction,  
regardless of whether or not they are in the same string.  By doing  
the BEGIN...COMMIT with the INSERT statements in between, you can do  
thousands and thousands of fast INSERTs for each relatively slow COMMIT.


D. Richard Hipp
[EMAIL PROTECTED]



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


Re: [sqlite] basic insert questions...

2008-10-15 Thread Jay A. Kreibich
On Wed, Oct 15, 2008 at 01:38:16PM -0500, David Clark scratched on the wall:
> Ok I want to convert a large application which uses ad-hoc data 
> structures and file formats to a sqlite database.  But before I do that
> I am working on a proof of concept test to show that yea it will work 
> once conversion is done.
> 
> But alas my first test is failing miserably.  Well, it works it just 
> takes way too long.  Like at least all night.
> I create table tbl2
>  f1 varchar(30)
>  f2 varchar(30)
>  f3 varchar(30)
>  identity integer primary index autoincrement
> 
> That works I have the table.
> I go to insert into with
> insert into tbl2 values ("0001", "0001", "0001", NULL);
> 
> insert into tbl2 values ("0002", "0002", "0002", NULL);
> 
> Ok I did each insert as separate query using the 5 minute example and 
> it took all night and never did complete.

  You need to wrap them up in a transaction.



  Also, if you want to store integers, store integers.  SQLite allows
  the same column to store records of different types.

  Also, string-literals in SQL use single quotes.

> So then I decided...ok the fsync() for serialization is the problem.
> 
> So I looked at limits.html and make a single query out of as many as 
> would fit in a 100 byte buffer.  That turned out to be
> 15625 inserts.  That should be one fsync per query so should be much 
> faster...but alas I don't think it is.

  That won't process them as one statement.  Even if you pass them into
  SQLite as a big command buffer, they're still processed one at a
  time.

  Issue the command "BEGIN", do 100 to 1 INSERTs, issue a "COMMIT".
  You should see a very noticeable difference in speed.

> My second test would be a random query into this large table to see 
> how long that took.  Then random queries from a large
> number of threads.  Get a query time under 1 second for pulling up a 
> record...and this product is so in my application.

  Depending on your query needs, you might need to look into indexes.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Error:"Expression cannot be evaluated" with sqlite3_exec()

2008-10-15 Thread D. Richard Hipp

On Oct 15, 2008, at 11:31 AM, Igor Tandetnik wrote:

> Alberto Tellaeche <[EMAIL PROTECTED]>
> wrote:
>> work perfectly well, but I am having a very annoying problem with
>> sqlite3_exec() function. The code just after the example above is:
>>
>>   memset(orden_SQL,0,200*sizeof(char));
>>   sprintf(orden_SQL,"update ALUMNO set nota=%0.1f where
>>   dni='%s';",nota,dni);
>>   res=sqlite3_exec(db,orden_SQL,0,0,&errorMsg);
>>   sqlite3_free(errorMsg);
>
> Examine orden_SQL after sprintf, check that it's as expected. For
> example, sprintf is locale sensitive: if your locale, say, uses comma
> for decimal separator rather than a period, the query would end up  
> with
> something like "set nota=1,1". But SQLite expects to see a period for
> decimal separator.
>

You can avoid the locale problem by using sqlite3_snprintf() instead  
of sprintf().  sqlite3_snprintf() always uses "." for the radix point  
regardless of what locale says - for exactly the reason that Igor  
cites.  Also with sqlite3_snprintf() you can use %Q instead of '%s' to  
avoid SQL injection attacks.

D. Richard Hipp
[EMAIL PROTECTED]



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


Re: [sqlite] Error:"Expression cannot be evaluated" with sqlite3_exec()

2008-10-15 Thread Alberto Tellaeche
Tank you very much for the response.
the array is as expected, sprintf is woking fine. Also I get the same error if 
I write the SQL command directly in the sqlite3_exec() function, so this is not 
the error...

Thank you again for the interest,

Regards

alberto



- Mensaje original 
De: Igor Tandetnik <[EMAIL PROTECTED]>
Para: sqlite-users@sqlite.org
Enviado: miércoles, 15 de octubre, 2008 17:31:59
Asunto: Re: [sqlite] Error:"Expression cannot be evaluated" with sqlite3_exec()

Alberto Tellaeche <[EMAIL PROTECTED]>
wrote:
> work perfectly well, but I am having a very annoying problem with
> sqlite3_exec() function. The code just after the example above is:
>
>memset(orden_SQL,0,200*sizeof(char));
>sprintf(orden_SQL,"update ALUMNO set nota=%0.1f where
>dni='%s';",nota,dni);
>res=sqlite3_exec(db,orden_SQL,0,0,&errorMsg);
>sqlite3_free(errorMsg);

Examine orden_SQL after sprintf, check that it's as expected. For 
example, sprintf is locale sensitive: if your locale, say, uses comma 
for decimal separator rather than a period, the query would end up with 
something like "set nota=1,1". But SQLite expects to see a period for 
decimal separator.

Igor Tandetnik



___
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] Seeking documentation on writing own Tokenizer

2008-10-15 Thread Jonathon
Hello all,

I am interested in writing my own tokenizer for sqlite3.  I've gone through
Google, as well as the sqlite documentation and unfortunately, I haven't
found anything helpful.  I am interested in writing my own tokenizer so that
I could perform "partial string" matches in sqlite's full-text search
(FTS3).  For example, I would like to find the word 'some' in the string:
'/path/to/some/file'.

Can someone point me in the right direction on what I would need to do to
implement my own Tokenizer?

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


Re: [sqlite] SQLite version 3.6.4

2008-10-15 Thread Dave Toll
Sorry, I should have stated that I am not defining SQLITE_MUTEX_NOOP
explicitly. SQLITE_MUTEX_NOOP is defined implicitly if you let
SQLITE_THREADSAFE default to 1 and define SQLITE_OS_OTHER=1 (i.e. do not
use SQLITE_OS_UNIX, SQLITE_OS_WIN or SQLITE_OS_OS2). I know this won't
affect many people, but it's an issue if you're building for an embedded
platform.

Cheers,
Dave.


-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: 15 October 2008 10:52
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite version 3.6.4


On Oct 15, 2008, at 1:31 PM, Dave Toll wrote:

> I found a small compile error when SQLITE_MUTEX_NOOP is defined with  
> the
> amalgamation source:
>

SQLITE_MUTEX_NOOP is an internal symbol that gets set if and only if  
you define SQLITE_THREADSAFE=0.  You should not set SQLITE_MUTEX_NOOP  
yourself.  Instead set SQLITE_THREADSAFE=0 and let SQLITE_MUTEX_NOOP  
be set automatically.

D. Richard Hipp
[EMAIL PROTECTED]




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


[sqlite] question: remote clients reading and writing to a sqlite database.

2008-10-15 Thread David Clark
Ok I know this is not what sqlite was designed for.   But via a odbc 
driver or any other extension tool is it possible to have remote 
clients read/write/query sqlite tables?

The reason I ask.  We have remote installations that generate data in 
local tables.  Then we have hit those installations with what is 
currently a Microsoft SQL connection and access there 
data.  Unfortunately...for unknown reasons sometimes Microsoft SQL 
goes into a funk and a query locally that should be compiled in 
milliseconds can take like 6 minutes.  The idea here is if we could 
wedge SQLite into the situation it most likely would not happen but 
if it did...because it is open source and has so many tools built 
into the way it is built.   We can figure out and resolve what is going on.

Thanks,
David Clark

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


[sqlite] basic insert questions...

2008-10-15 Thread David Clark
Ok I want to convert a large application which uses ad-hoc data 
structures and file formats to a sqlite database.  But before I do that
I am working on a proof of concept test to show that yea it will work 
once conversion is done.

But alas my first test is failing miserably.  Well, it works it just 
takes way too long.  Like at least all night.
I create table tbl2
 f1 varchar(30)
 f2 varchar(30)
 f3 varchar(30)
 identity integer primary index autoincrement

That works I have the table.
I go to insert into with
insert into tbl2 values ("0001", "0001", "0001", NULL);

insert into tbl2 values ("0002", "0002", "0002", NULL);

Ok I did each insert as separate query using the 5 minute example and 
it took all night and never did complete.

So then I decided...ok the fsync() for serialization is the problem.

So I looked at limits.html and make a single query out of as many as 
would fit in a 100 byte buffer.  That turned out to be
15625 inserts.  That should be one fsync per query so should be much 
faster...but alas I don't think it is.

My second test would be a random query into this large table to see 
how long that took.  Then random queries from a large
number of threads.  Get a query time under 1 second for pulling up a 
record...and this product is so in my application.

Any ideas,
Thanks,
David

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


[sqlite] Malformed schema error on sqlite3odbc.dll with version 0.79

2008-10-15 Thread Richard Kim

Hi guys, 

We are using sqlite as our database and noticed some weird odbc driver
problem.

"malformed database schema (deleteUserGroupsByUsers) - no such
table: main.Users (11)"

If I open the database with other tool such as sqlite studio, or firefox
plug in, there is no problem - the schema check seems to be okay, and
all the tables are there.. etc. 
Also If I use S3ODBC.dll, we don't see ths issue of the malformed
database schema either.

It happens only if I use sqlite3odbc.dll with version 0.79.

Is this known bug ??

Thanks 

Richard K

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


Re: [sqlite] SQLite version 3.6.4

2008-10-15 Thread Dave Toll
I found a small compile error when SQLITE_MUTEX_NOOP is defined with the
amalgamation source:

sh4gcc -ansi -fsigned-char -mruntime=os21 -ml -c  -O0
-DSQLITE_OS_OTHER=1 -DSQLITE_32BIT_ROWID=1 -DSQLITE_INT64_TYPE=long
-DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_AUTOINCREMENT
-DSQLITE_OMIT_AUTOINIT -DSQLITE_OMIT_AUTOVACUUM -DSQLITE_OMIT_CAST
-DSQLITE_OMIT_CHECK -DSQLITE_OMIT_COMPLETE -DSQLITE_OMIT_COMPOUND_SELECT
-DSQLITE_OMIT_CONFLICT_CLAUSE -DSQLITE_OMIT_DECLTYPE
-DSQLITE_OMIT_FLAG_PRAGMAS  -DSQLITE_OMIT_FOREIGN_KEY
-DSQLITE_OMIT_GET_TABLE -DSQLITE_OMIT_INCRBLOB
-DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_PROGRESS_CALLBACK
-DSQLITE_OMIT_SCHEMA_VERSION_PRAGMAS -DSQLITE_OMIT_TCL_VARIABLE
-DSQLITE_OMIT_XFER_OPT -DUSE_RAWPPP_DL -DUSE_PSW_DL -DEVENTBRK_TEST
-DINCLUDE_TEST_POPUP -DOPENTV_FUSION -DFNS_REAL_SKEPTIC  -DDHCP
-DNO_PACKED_STRU -DNO_STASS -DRAWPPP_MODEM_TEST -DHTTP
-DSECURITY_PRESENT -DFUSION_CALLS_REDEFINE -DUSE_RAWSTK_MODEM_ERRORS
-DRAWPPP -DFUSION_CALLS_REDEFINE -DUSE_THE_XSOCKET -DDVB_SI
-DUSE_THE_HDMI -DUSE_THE_SQLDB -DUSE_THE_SQLITE -D__st40__
-Dinline=__inline -D__LITTLE_ENDIAN__ -D__LONG_INT__ -D__LONG_ALIGN__
-D__FLAT__ -D_ANSI_C_SOURCE -DKERNEL_HAS_ANSI -DUSE_THE_FONT16EXT
-DUSE_THE_EITS -DUSE_THE_HTTP -DUSE_THE_PPPLINK -DUSE_THE_XSOCKET
-DUSE_THE_ETHERNET -DUSE_THE_EVENTBRK -DUSE_THE_SVSCALE -DUSE_THE_IMAGE
-DUSE_THE_XYMAN -DUSE_THE_FEP -DUSE_THE_FEMGR -DUSE_THE_FONT16_GLUE
-DUSE_THE_NATAPP -DUSE_THE_MODEMSVC -DUSE_THE_USERPROF
-DUSE_THE_ATTRIBUTE -DUSE_THE_DEBTRACE -DUSE_THE_VOD -DUSE_THE_PVR
-DUSE_PVR_MPEG_MEDIUM -DUSE_CORE_2_DRIVERS  -DUSE_THE_SSLEXT
-DUSE_THE_OPENTV_SI -DUSE_THE_VKBENH -DUSE_THE_SCARTSW -DUSE_THE_APPSTOR
-DUSE_THE_EEFILSYS -DUSE_THE_FMFILE -DUSE_THE_RAMFSYS
-DUSE_THE_BC_SOURCE -DUSE_THE_RSB_LIB -DUSE_THE_RAWSTK -DUSE_THE_SCHED
-DUSE_THE_RESMAN -DUSE_THE_DSM -DUSE_THE_SLM -DUSE_THE_USB
-DUSE_THE_USB_COMM -DUSE_THE_USB_MSD -DUSE_THE_HDMI -DUSE_THE_OTHREAD
-DUSE_THE_GRAPHICS_JPEG_SUPPORT -DUSE_THE_GRAPHICS_PNG_SUPPORT
-DUSE_THE_SQLITE -DUSE_THE_SQLDB
-I/projects/OTV_OS/Core2/DEVELOP/mdal_db/opentv/external/opensrc/include
-I../src/include  -I. -g -DUSE_NO_ROMED_APPLICATIONS -osqlite3.o
/projects/OTV_OS/Core2/DEVELOP/mdal_db/opentv/external/opensrc/sqlite/sq
lite3.c
/projects/OTV_OS/Core2/DEVELOP/mdal_db/opentv/external/opensrc/sqlite/sq
lite3.c: In function `sqlite3DefaultMutex':
/projects/OTV_OS/Core2/DEVELOP/mdal_db/opentv/external/opensrc/sqlite/sq
lite3.c:14997: error: `noopMutexLeave' undeclared (first use in this
function)
/projects/OTV_OS/Core2/DEVELOP/mdal_db/opentv/external/opensrc/sqlite/sq
lite3.c:14997: error: (Each undeclared identifier is reported only once
/projects/OTV_OS/Core2/DEVELOP/mdal_db/opentv/external/opensrc/sqlite/sq
lite3.c:14997: error: for each function it appears in.)
/projects/OTV_OS/Core2/DEVELOP/mdal_db/opentv/external/opensrc/sqlite/sq
lite3.c:14997: error: initializer element is not constant
/projects/OTV_OS/Core2/DEVELOP/mdal_db/opentv/external/opensrc/sqlite/sq
lite3.c:14997: error: (near initialization for `sMutex.xMutexLeave')


Renaming debugMutexLeave() to noopMutexLeave() at line 14987 seems to
fix it.

Cheers,
Dave.


-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: 15 October 2008 05:12
To: [EMAIL PROTECTED]; General Discussion of SQLite Database
Subject: [sqlite] SQLite version 3.6.4

SQLite version 3.6.4 is now available for download on the SQLite  
website:

  http://www..sqlite.org/download.html

SQLite version 3.6.4 is considered a stable release.  Upgrading from  
version 3.6.3 is optional.  For a summary of changes and enhancements  
that have occurred in version 3.6.4 visit

 http://www.sqlite.org/3_6_4.html

D. Richard Hipp
[EMAIL PROTECTED]




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


Re: [sqlite] SQLite version 3.6.4

2008-10-15 Thread D. Richard Hipp

On Oct 15, 2008, at 1:31 PM, Dave Toll wrote:

> I found a small compile error when SQLITE_MUTEX_NOOP is defined with  
> the
> amalgamation source:
>

SQLITE_MUTEX_NOOP is an internal symbol that gets set if and only if  
you define SQLITE_THREADSAFE=0.  You should not set SQLITE_MUTEX_NOOP  
yourself.  Instead set SQLITE_THREADSAFE=0 and let SQLITE_MUTEX_NOOP  
be set automatically.

D. Richard Hipp
[EMAIL PROTECTED]



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


[sqlite] SQLite and Liberty Basic

2008-10-15 Thread epankoke
I searched all the archives to no avail, so I'm probably just making this up, 
but I was positive that someone posted an SQLite interface written in Liberty 
Basic in the last month or two.  If you are the person who posted such a 
program, if you know who did, or if you are neither but have done work with 
SQLite in Liberty Basic, maybe you can help me out.  I'm looking for a couple 
of good, practical examples of how to use SQLite with LB.  Any URLs, tips or 
code would be greatly appreciated.

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Problem with inserting and integer primary key

2008-10-15 Thread Igor Tandetnik
Karl Lautman <[EMAIL PROTECTED]> wrote:
> So, I create a table as follows:
>
> CREATE TABLE IF NOT EXISTS Lists (
> wID integer,
> ltitle text,
> llink text,
> units text,
> date text,
> ListsID integer,
> new integer
> , CONSTRAINT Lists_pk PRIMARY KEY (ListsID))

Be aware that, in order to create a column that aliases ROWID (and thus 
has special properties in SQLite, such as getting an automatically 
assigned unique integer) it has to be spelled precisely INTEGER PRIMARY 
KEY. Doing it the way you show, with a CONSTRAINT clause, won't do the 
trick. Make it

CREATE TABLE IF NOT EXISTS Lists (
wID integer,
ltitle text,
llink text,
units text,
date text,
ListsID integer primary key,
new integer);

Scott Baker already addressed the rest of your problem.

Igor Tandetnik 



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


Re: [sqlite] Problem with inserting and integer primary key

2008-10-15 Thread Scott Baker
On 10/15/2008 09:27 AM, Karl Lautman wrote:
> To insert data I do (this is in Python):
>
>
>
> x = dcur.execute('insert into Lists values (?, ?, ?, ?, ?, ?)', (123,
> 'YouTube Videos','http://www.youtube.com', 'views', '10/14/08', 4))

Just add NULL for the primary key value. SQLite will autopopulate the 
number, but you have to tell it to do it. Just because it's a primary key 
doesn't mean you can't ALSO provide it a value (like 73). So you have to 
tell it to pick one itself by using NULL.

-- 
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Problem with inserting and integer primary key

2008-10-15 Thread Karl Lautman
So, I create a table as follows:

 

CREATE TABLE IF NOT EXISTS Lists (

wID integer,

ltitle text,

llink text,

units text,

date text,

ListsID integer,

new integer

, CONSTRAINT Lists_pk PRIMARY KEY (ListsID))

 

To insert data I do (this is in Python):

 

x = dcur.execute('insert into Lists values (?, ?, ?, ?, ?, ?)', (123,
'YouTube Videos', 'http://www.youtube.com', 'views', '10/14/08', 4))

 

And I get:

 

OperationalError: table Lists has 7 columns but 6 values were supplied

 

I only supplied 6 values because, according to the sqlite documentation:

 

"If a table contains a column of type INTEGER PRIMARY KEY, then that column
becomes an alias for the ROWID. You can then access the ROWID using any of
four different names, the original three names described above or the name
given to the INTEGER PRIMARY KEY column. All these names are aliases for one
another and work equally well in any context.

 

"When a new row is inserted into an SQLite table, the ROWID can either be
specified as part of the INSERT statement or it can be assigned
automatically by the database engine."

 

So, if I want the engine to automatically assign the ROWID, do I have to
specify the PK column or not? If the former, how do I get the table to
automatically assign the ROWID? Thanks.

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


Re: [sqlite] experiencing SQLite crash (Apache/mod_perl/Catalyst)

2008-10-15 Thread Dami Laurent (PJ)

>Hi all,
>
>I have a Perl Catalyst app using SQLite as storage. It has been running
>for several months without any problem.
>Now in the last few days we had several crashes of SQLite (file gets
>corrupted). This is just incomprehensible because all development and
>production engineers claim that nothing has changed in 
>application code,
>nor in Catalyst, DBD::Sqlite, Apache, or operating system 
>(Solaris), and
>we can't reproduce the problem in dev. So we don't even know where to
>start our investigations.
>
>Any hints, suggestions, related info, etc. would be welcome.
>
>The production server is running Apache 2.2.8 Solaris / mod_perl 2.04 /
>Perl 5.7.8 / Catalyst 5.7007 / DBD::SQlite 1.13.
>
>Thanks in advance, 
>
>   Laurent Dami
>


For info : we partially understood the problem.

The production server uses a private Apache authentication module
written in C.
That module was accidentally compiled with a debug flag and therefore
was sending
printf(..) statements to STDOUT (probably a very bad idea, instead of
going through Apache logging methods!). 
This went undetected for several months -- the printf were written God
knows where.

What happened in the last few days is that in some occasions the file
descriptor
for accessing SQLite was the same as the stdout used by C printf --
so some logging data was overwriting some blocks inside the SQLite
binary file!
No idea if this sharing of file descriptor is because of Apache or
mod_perl or 
DBD::Sqlite or still something else, nor why this sharing only started a
few
days ago  (maybe a difference in load of the server).

Anyway, the fix is of course to disable debug flag in that C module, so
that
it no longer prints to stdout.

Many thanks to people who responded to my previous call for help.

Best regards,

Laurent Dami





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


Re: [sqlite] Error:"Expression cannot be evaluated" with sqlite3_exec()

2008-10-15 Thread Igor Tandetnik
Alberto Tellaeche <[EMAIL PROTECTED]>
wrote:
> work perfectly well, but I am having a very annoying problem with
> sqlite3_exec() function. The code just after the example above is:
>
>memset(orden_SQL,0,200*sizeof(char));
>sprintf(orden_SQL,"update ALUMNO set nota=%0.1f where
>dni='%s';",nota,dni);
>res=sqlite3_exec(db,orden_SQL,0,0,&errorMsg);
>sqlite3_free(errorMsg);

Examine orden_SQL after sprintf, check that it's as expected. For 
example, sprintf is locale sensitive: if your locale, say, uses comma 
for decimal separator rather than a period, the query would end up with 
something like "set nota=1,1". But SQLite expects to see a period for 
decimal separator.

Igor Tandetnik



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


[sqlite] Error:"Expression cannot be evaluated" with sqlite3_exec()

2008-10-15 Thread Alberto Tellaeche
Hello colleagues;

I am new to sqlite, and I have recently started to use it from the command line 
(sqlite3.exe) and in C programs.

In my C example program I open de database at the beginning and close it 
beforte the end of the program.
 Examples like:

memset(orden_SQL, 0, 200*sizeof(char));
sprintf(orden_SQL,"select nBlancos from CORRECCION_EXAMEN where 
modelo='%c';",modelo);
sqlite3_get_table(db,orden_SQL,&Resultado,&Rows,&Cols,&errorMsg);
for(i=1;i<=Rows;i++)
{
examen.blancos[i-1]=atoi(Resultado[i]);
}

work perfectly well, but I am having a very annoying problem with 
sqlite3_exec() function. The code just after the example above is:

memset(orden_SQL,0,200*sizeof(char));
sprintf(orden_SQL,"update ALUMNO set nota=%0.1f where dni='%s';",nota,dni);
res=sqlite3_exec(db,orden_SQL,0,0,&errorMsg);
sqlite3_free(errorMsg);


and it does not work. "res" value is 0 and "errorMsg" is Error:"Expression 
cannot be evaluated".

I have also tried to substitute sqlite3_exec with sqlite3_prepare() + 
sqlite3_step() with the same result.
The command "update ALUMNO set nota=9 where dni='12345678P';" works perfect in 
the comand line.

What problem I am having here? It is very important for me for the project I am 
developping, so I would appreciate a lot any kind of advise.

Best regards,

Alberto



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


Re: [sqlite] How to speed up read-only databases?

2008-10-15 Thread Cory Nelson
That's a tricky one.  Databases like random access, but DVD-ROM not so
much :)  Maybe delete indexes to force a full table scan?

On Wed, Oct 15, 2008 at 5:47 AM, Christophe Leske <[EMAIL PROTECTED]> wrote:
> Hi there,
>
> i am using a 120MB database in an embedded project (a DVD-ROM project)
> and was wondering what I can do to speed up its reading using diverse
> PRAGMA statements.
> The database is locked, meaning that no data is being inserted or
> deleted from it. I am solely after speeding up its reading performance.
>
> Indices have been set, would augmenting the cache size for Sqlite do
> something?
>
> Grateful for any info,
>
> --
> Christophe Leske

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


Re: [sqlite] Problem : SQLite Database error

2008-10-15 Thread Mihai Limbasan

Hello, Aaron.

Have you tried Mr. Griggs' solution yet? Please be aware that posting 
the same question for the *third* time in a row will not make it any 
more visible, but will dramatically increase the chance that people will 
become annoyed and will not help you.


Thank you for your consideration.

Mihai Limbasan

Please be aware

Aaron Smith wrote:

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
  


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


[sqlite] How to speed up read-only databases?

2008-10-15 Thread Christophe Leske
Hi there,

i am using a 120MB database in an embedded project (a DVD-ROM project) 
and was wondering what I can do to speed up its reading using diverse 
PRAGMA statements.
The database is locked, meaning that no data is being inserted or 
deleted from it. I am solely after speeding up its reading performance.

Indices have been set, would augmenting the cache size for Sqlite do 
something?

Grateful for any info,

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


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


Re: [sqlite] SQL "UPDATE" question

2008-10-15 Thread Igor Tandetnik
"jm cuaz" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Is there a workaround to execute this kind of SQL statement inSQLite :
>
> "UPDATE VariablesPointages  INNER JOIN Sociétés ON Sociétés.Mois =
> VariablesPointages.Mois
> SET VariablesPointages.CodSoc = Sociétés.CodSoc,
> VariablesPointages.HCalc = 1 ;"

UPDATE VariablesPointages
SET HCalc = 1, CodSoc =
(SELECT CodSoc FROM Sociétés s
 WHERE VariablesPointages.Mois = s.Mois)
WHERE Mois IN (SELECT Mois FROM Sociétés);

Igor Tandetnik 



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


Re: [sqlite] experiencing SQLite crash (Apache/mod_perl/Catalyst)

2008-10-15 Thread Shane Harrelson
Possibly HW (memory, disk, etc.) failures?


On Wed, Oct 15, 2008 at 2:54 AM, Dami Laurent (PJ) <
[EMAIL PROTECTED]> wrote:

> Hi all,
>
> I have a Perl Catalyst app using SQLite as storage. It has been running
> for several months without any problem.
> Now in the last few days we had several crashes of SQLite (file gets
> corrupted). This is just incomprehensible because all development and
> production engineers claim that nothing has changed in application code,
> nor in Catalyst, DBD::Sqlite, Apache, or operating system (Solaris), and
> we can't reproduce the problem in dev. So we don't even know where to
> start our investigations.
>
> Any hints, suggestions, related info, etc. would be welcome.
>
> The production server is running Apache 2.2.8 Solaris / mod_perl 2.04 /
> Perl 5.7.8 / Catalyst 5.7007 / DBD::SQlite 1.13.
>
> Thanks in advance,
>
>Laurent Dami
>
>
>
>
>
> ___
> 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] Intermittent SQLITE_CANTOPEN on Windows

2008-10-15 Thread Shane Harrelson
Version 3.6.1 (IIRC) had changes to improve the error detection and retry
logic for this condition (typically caused when a 3rd party  application
opens the SQLite journal file.)
HTH.
-Shane


On Mon, Oct 13, 2008 at 12: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


[sqlite] experiencing SQLite crash (Apache/mod_perl/Catalyst)

2008-10-15 Thread Dami Laurent (PJ)
Hi all,

I have a Perl Catalyst app using SQLite as storage. It has been running
for several months without any problem.
Now in the last few days we had several crashes of SQLite (file gets
corrupted). This is just incomprehensible because all development and
production engineers claim that nothing has changed in application code,
nor in Catalyst, DBD::Sqlite, Apache, or operating system (Solaris), and
we can't reproduce the problem in dev. So we don't even know where to
start our investigations.

Any hints, suggestions, related info, etc. would be welcome.

The production server is running Apache 2.2.8 Solaris / mod_perl 2.04 /
Perl 5.7.8 / Catalyst 5.7007 / DBD::SQlite 1.13.

Thanks in advance, 

Laurent Dami





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


[sqlite] SQL "UPDATE" question

2008-10-15 Thread jm cuaz
Hello,

In SQLite, it seems it's not possible to associate several tables in an 
"update" statement.

Is there a workaround to execute this kind of SQL statement inSQLite :

"UPDATE VariablesPointages  INNER JOIN Sociétés ON Sociétés.Mois = 
VariablesPointages.Mois
SET VariablesPointages.CodSoc = Sociétés.CodSoc, 
VariablesPointages.HCalc = 1 ;"

Many Thanks


Jean-Marie

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


Re: [sqlite] Problem : SQLite Database error

2008-10-15 Thread Griggs, Donald
 Regarding:

"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, ..."




Hi, Aaron,

You may want to resort to your latest backup.  

What you *can* do with the corrupted database is to use the sqlite3
command-line utility and its ".dump " command to try and
export each table to an ascii file.  Any tables that are corrupted won't
fully dump, but the idea is that you *might* nonetheless be able to
scavenge some good data that's newer than your last backup.  Making that
partial data play nicely with the "BluePhoneElite" software is another
issue entirely.   :-(
___
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-15 Thread Aaron Smith
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] SQLite version 3.6.4

2008-10-15 Thread Shane Harrelson
Try

http://sqlite.org/releaselog/3_6_4.html

On Wed, Oct 15, 2008 at 8:17 AM, Alexey Pechnikov <[EMAIL PROTECTED]>wrote:

> Hello!
>
> В сообщении от Wednesday 15 October 2008 16:11:47 D. Richard Hipp
> написал(а):
> >  For a summary of changes and enhancements
> > that have occurred in version 3.6.4 visit
> >
> >  http://www.sqlite.org/3_6_4.html
>
> Document Not Found
>  The document /3_6_4.html is not avaivable on this server
>
> Best regards, Alexey.
>  ___
> 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] SQLite version 3.6.4

2008-10-15 Thread D . Richard Hipp

On Oct 15, 2008, at 8:11 AM, D. Richard Hipp wrote:

> SQLite version 3.6.4 is now available for download on the SQLite  
> website:
>
> http://www..sqlite.org/download.html
>
> SQLite version 3.6.4 is considered a stable release.  Upgrading from  
> version 3.6.3 is optional.  For a summary of changes and  
> enhancements that have occurred in version 3.6.4 visit
>
>http://www.sqlite.org/3_6_4.html


Corrected URL:  http://www.sqlite.org/releaselog/3_6_4.html

D. Richard Hipp
[EMAIL PROTECTED]



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


Re: [sqlite] SQLite version 3.6.4

2008-10-15 Thread Alexey Pechnikov
Hello!

В сообщении от Wednesday 15 October 2008 16:11:47 D. Richard Hipp написал(а):
>  For a summary of changes and enhancements  
> that have occurred in version 3.6.4 visit
>
>      http://www.sqlite.org/3_6_4.html

Document Not Found
 The document /3_6_4.html is not avaivable on this server

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


[sqlite] SQLite version 3.6.4

2008-10-15 Thread D. Richard Hipp
SQLite version 3.6.4 is now available for download on the SQLite  
website:

  http://www..sqlite.org/download.html

SQLite version 3.6.4 is considered a stable release.  Upgrading from  
version 3.6.3 is optional.  For a summary of changes and enhancements  
that have occurred in version 3.6.4 visit

 http://www.sqlite.org/3_6_4.html

D. Richard Hipp
[EMAIL PROTECTED]



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


Re: [sqlite] UNION QUERY

2008-10-15 Thread Igor Tandetnik
"TW" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> I have this query:
>
> SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
> FROM music
> WHERE classnumber=6 OR classnumber=7
> AND hd="B"
> UNION
> SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
> FROM music
> WHERE classnumber=8 OR classnumber=9
> AND hd="A"
> ORDER BY random() LIMIT 2;

Try

SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE
classnumber=6 OR
(classnumber=7 AND hd='B') OR
classnumber=8 OR
(classnumber=9 AND hd='A')
ORDER BY random() LIMIT 2;

At the very least, use UNION ALL instead of UNION.

> I actually need to have a limit on each SELECT clause so that I only
> get one
> record from either classnumbers 6 or 7 and then one record from
> classnumbers 8 or 9, randomly chosen.

Ah. Then you need something like this:

select title, ... from
(select * FROM music
 WHERE (classnumber=6 OR (classnumber=7 AND hd='B'))
 ORDER BY random() LIMIT 1)
UNION ALL

select title, ... from
(select * FROM music
 WHERE (classnumber=8 OR (classnumber=9 AND hd='A'))
 ORDER BY random() LIMIT 1);

Igor Tandetnik 



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


[sqlite] UNION QUERY

2008-10-15 Thread TW
Hi,

I have this query:

SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=6 OR classnumber=7
AND hd="B"
UNION
SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=8 OR classnumber=9
AND hd="A"
ORDER BY random() LIMIT 2;

And I get this error:

SQL near line 1: 1st ORDER BY term does not match any column in the 
result set

The data that I need is all in the same table, but I need to run 
queries based 
on the classnumber.  I tried a number of different ways but couldn't figure 
this out, even 
after googling.  I figured that this was similar to the query I used in MySQL, 
except I 
learned that the ORDER BY clause had to be put at the end of the SELECT 
statements.

I actually need to have a limit on each SELECT clause so that I only 
get one 
record from either classnumbers 6 or 7 and then one record from classnumbers 8 
or 9, randomly 
chosen.  I thought that I needed to write it like this:

SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=6 OR classnumber=7
AND hd="B"
ORDER BY random() LIMIT 1
UNION
SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=8 OR classnumber=9
AND hd="A"
ORDER BY random() LIMIT 1;

...but that doesn't work either.  Notice that I put only 1 semicolon at 
the very end 
of the compound statement.  If I order it by one of the columns, the query 
works, but only for 
one SELECT statement (I can't get the UNION to work at all).

What is wrong with the way I wrote this query?  Thanks.

-- 
VR~
TW
Email: [EMAIL PROTECTED]
"Knowledge Is Power"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users