[sqlite] ANN: SQLite 3.6.16.C#

[Noah Hart]

I am pleased to announce that the C# port is done to the point where others
can look at it.

The project is located at http://code.google.com/p/sqlitecs

Enjoy,

Noah Hart
-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24764742.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Reasons for SQLITE_BUSY

[Edzard Pasma]

--- nikol...@rath.org wrote:
> "Edzard Pasma"  writes:
>> --- nikol...@rath.org wrote:
>>> "Igor Tandetnik"  writes:
 Nikolaus Rath  wrote:
> I am accessing the same database from different threads. Each
> thread
> has its own connection. I have set the busy timeout for each
> connection to 5000 milliseconds.
>
> However, in some testcases I still get SQLITE_BUSY errors from
> sqlite3_step. Moreover, the whole testcases run in much less than
> 5
> seconds, to apparently sqlite does not even try to wait for the
> lock
> to disappear.

 You are getting a deadlock. The scenario goes like this: thread A
 runs a
 transaction that starts as a reader (with a SELECT statement) but
 later
 becomes a writer (by executing INSERT, UPDATE or DELETE). Thread B
 also
 runs a transaction like this, or a simple writer transaction. Then
 the
 following sequence of events occurs:

 1. Thread A starts as reader and takes a shared lock
 2. Thread B starts as writer, takes a pending lock and waits for
readers
 to clear.
 3. Thread A tries to become a writer and promote its lock to
reserved,
 but can't because there's already a writer on the database.

 The two threads deadlock. No amount of waiting by either thread
 would
 get them out of the impasse: the only way out is for one of the
 threads
 to roll back its transaction and start from scratch. When SQLite
 detects
 this situation, it returns SQLITE_BUSY immediately, without calling
 the
 busy handler (because, again, waiting won't help any).

 To avoid the possibility of a deadlock, start your
 reader-turning-writer
 transactions with BEGIN IMMEDIATE (this essentially makes the 
 transaction a writer right away).
>>>
>>> Ah, I see. I expected that a deadlock would actually result in both
>>> threads hanging forever, rather than SQLite detecting it and
>>> abandoning
>>> immediately. The later is of course even better once you know about
>>> it.
>>> Thanks for the explanations! I should be able to fix my problem
>>> now..
>>
>> Hi,
>>
>> Just in case it appears difficult to fix, I like to suggest to try
>> using shared cache mode. The shared cache locking model does not have
>> this particular deadlock situation. I'm assuming that the database is
>> accessed from within a single process only.

> Thanks for the idea. But after reading:
> //www.sqlite.org/sharedcache.html it seems to me that to avoid the
>  deadlock, I would not only need to enable shared cache mode but
>  read-uncommitted. Is that right?
>
> I'm hesitating a bit to do that, because I'm not sure what the
> "[read-uncommited] can lead to inconsistent query results" phrase on the
> above page may imply.

Hi again, 

I  don't believe that it is deadlock that you run into when using shared cache 
mode. Likely you face that a table gets locked for reading, immediately after 
the first write. The regular locking model is more tolerant here because 
reading is still possible until a writer starts spilling changes to disk. The 
read_uncommitted pragma is in my opinion a brilliant solution. It is however 
not the only option. As deadlock likely no longer occurs, you can now just wait 
for any lock. Only this must be handled in the application, as the timeout 
setting is not observed in shared cache mode. See ticket 
http://www.sqlite.org/cvstrac/tktview?tn=2010. 

The inconsistency when reading uncommitted data applies to the number of rows. 
Not to the consistency of the data within a row. This is perception, which may 
be too optimistic..

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

Re: [sqlite] Foreign Key constraint checking

[Simon Slavin]

On 31 Jul 2009, at 8:25pm, Shaun Seckman (Firaxis) wrote:

> I am well aware that SQLite doesn't enforce FK's and that a way around
> this is to use triggers.  This lack of enforcing actually has worked  
> to
> my advantage as when populating my database I can insert data out of
> order.  However, after all data has been inserted into the database,  
> I'd
> like to perform some constraint checking to ensure that any FK column
> value correctly matches a row that the FK references.  Is there any
> built-in methods or code snippits I can use for this or will I just  
> have
> to roll my own?

Dump the database with the command-line tool.  Take a look at the file  
it produces and move all the INSERT commands to the end.  Then use the  
'.read.' command to create a new database with these commands.  Then  
check to see you have the same number of records in all tables.

Not easy to automate, but it's good for a one-off check after a  
monster import job.

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

Re: [sqlite] Foreign Key constraint checking

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Shaun Seckman (Firaxis) wrote:
> I am well aware that SQLite doesn't enforce FK's and that a way around
> this is to use triggers.  

Hopefully you are also aware of the genfkey functionality in the shell which
lets you automate generation of those triggers :-)

> Is there any
> built-in methods or code snippits I can use for this or will I just have
> to roll my own?

The triggers are only going to affect new additions/deletions/changes.  My
best guess as to getting it to check existing data is to just set a column
value to itself, which will work until the SQLite optimiser figures out what
is happening.  Something like:

  UPDATE table SET column=column

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkpzUQQACgkQmOOfHg372QRoWgCcD6jHw9VNNoRRiJHs/AZh8e8y
in8An0vHcGyyhd33xUWof0aTHNPwz4dF
=XYyE
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign Key constraint checking

[Shaun Seckman (Firaxis)]

Hello,

 

I am well aware that SQLite doesn't enforce FK's and that a way around
this is to use triggers.  This lack of enforcing actually has worked to
my advantage as when populating my database I can insert data out of
order.  However, after all data has been inserted into the database, I'd
like to perform some constraint checking to ensure that any FK column
value correctly matches a row that the FK references.  Is there any
built-in methods or code snippits I can use for this or will I just have
to roll my own?

 

-Shaun

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

Re: [sqlite] Reasons for SQLITE_BUSY

[Nikolaus Rath]

"Edzard Pasma"  writes:
> --- nikol...@rath.org wrote:
>> "Igor Tandetnik"  writes:
>>> Nikolaus Rath  wrote:
 I am accessing the same database from different threads. Each
 thread
 has its own connection. I have set the busy timeout for each
 connection to 5000 milliseconds.

 However, in some testcases I still get SQLITE_BUSY errors from
 sqlite3_step. Moreover, the whole testcases run in much less than
 5
 seconds, to apparently sqlite does not even try to wait for the
 lock
 to disappear.
>>>
>>> You are getting a deadlock. The scenario goes like this: thread A
>>> runs a
>>> transaction that starts as a reader (with a SELECT statement) but
>>> later
>>> becomes a writer (by executing INSERT, UPDATE or DELETE). Thread B
>>> also
>>> runs a transaction like this, or a simple writer transaction. Then
>>> the
>>> following sequence of events occurs:
>>>
>>> 1. Thread A starts as reader and takes a shared lock
>>> 2. Thread B starts as writer, takes a pending lock and waits for
>>>readers
>>> to clear.
>>> 3. Thread A tries to become a writer and promote its lock to
>>>reserved,
>>> but can't because there's already a writer on the database.
>>>
>>> The two threads deadlock. No amount of waiting by either thread
>>> would
>>> get them out of the impasse: the only way out is for one of the
>>> threads
>>> to roll back its transaction and start from scratch. When SQLite
>>> detects
>>> this situation, it returns SQLITE_BUSY immediately, without calling
>>> the
>>> busy handler (because, again, waiting won't help any).
>>>
>>> To avoid the possibility of a deadlock, start your
>>> reader-turning-writer
>>> transactions with BEGIN IMMEDIATE (this essentially makes the 
>>> transaction a writer right away).
>>
>> Ah, I see. I expected that a deadlock would actually result in both
>> threads hanging forever, rather than SQLite detecting it and
>> abandoning
>> immediately. The later is of course even better once you know about
>> it.
>> Thanks for the explanations! I should be able to fix my problem
>> now..
>
> Hi,
>
> Just in case it appears difficult to fix, I like to suggest to try
> using shared cache mode. The shared cache locking model does not have
> this particular deadlock situation. I'm assuming that the database is
> accessed from within a single process only.

Thanks for the idea. But after reading
http://www.sqlite.org/sharedcache.html it seems to me that to avoid the
above deadlock, I would not only need to enable shared cache mode but
also read-uncommitted. Is that right?

I'm hesitating a bit to do that, because I'm not sure what the
"[read-uncommited] can lead to inconsistent query results" phrase on the
above page may imply.

Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

Re: [sqlite] Multi-master replication with updated Ver sioning extension

[Alexey Pechnikov]

Hello!

On Thursday 30 July 2009 18:17:45 Jim Showalter wrote:
> MD5 hashes can still collide. How does this implementation deal with 
> hash collisions?

Now it is possible to use any hash function:

-- first column is unique key
select versioning('arg1','sessions');
-- use md5 hash of all fields
select versioning('md5','sessions');

Note: function arg1() is coded in versioning extension.
select arg1('a');
a
select arg1('a','b','c');
a

When you will define sha256 function (as example) you can do
select versioning('sha256','sessions');

Thanks for you comment.

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

Re: [sqlite] Multi-master replication with updated Versioning extension

[Alexey Pechnikov]

Hello!

On Friday 31 July 2009 18:34:17 Ken wrote:
> I've looked at your code and discussions on this list about the versioning. I 
> have a few questions.
> 
> 1. How are you moving the data around from one master to another?
> 2. How are you applying the changes once moved to the master?

By the tcl scripts. Databases only store data and versions information.

I'm planning to publish some of the tcl scripts on the next week.

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

Re: [sqlite] stupid me -> corrupted db file

[Simon Slavin]

On 30 Jul 2009, at 3:31pm, Rudi Fay wrote:

> I tried "pragma integrity_check;" with the result "SQL error: database
> disk image is malformed"

But that may not stop you from rescuing (the majority of ?) the data.   
The error may be in an index or something else that isn't used when  
you just list all the data.  So use the command-line tool



and take a look at the .dump command.

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

Re: [sqlite] SQL error: database disk image is malformed - other possibility than dump/load

[Dan Kennedy]

On Jul 31, 2009, at 12:00 AM, rupert.thurner wrote:

> is there another possibility but exporting and importing the data to
> get rid of:
>   SQL error: database disk image is malformed
> ?
>
> here the details of the error and the commands we used. but we are not
> sure why exporting works and vacuum should not work.

Vacuuming a database constructs the indexes in the compacted
database by copying the contents of the existing indexes. But
doing an export/import using the shell tool constructs the
indexes in the new db from scratch based on the data extracted
from the tables in the old db. So if the corruption problem is
confined to an index or indexes, an export/import will fix
problem but a vacuum will not.

Dan.



> we used sqlite
> 3.6.10, solaris.
>
> # sqlite3 trac.db
> sqlite> PRAGMA integrity_check;
> *** in database main ***
> Page 39762: sqlite3BtreeInitPage() returns error code 11
> On tree page 39768 cell 0: 2nd reference to page 39762
> Page 39763 is never used
> Page 39764 is never used
> Page 39765 is never used
> Page 39766 is never used
> Page 39767 is never used
> SQL error: database disk image is malformed
> sqlite> vacuum;
> SQL error: database disk image is malformed
>
>
> # sqlite3 trac.db .dump .quit >> trac.db.dump
> # mv trac.db trac.db.orig
> # sqlite3 trac.db < trac.db.dump
> ___
> 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] Multi-master replication with updated Versioning extension

[Ken]

Alex,

I've looked at your code and discussions on this list about the versioning. I 
have a few questions.

1. How are you moving the data around from one master to another?
2. How are you applying the changes once moved to the master?



--- On Fri, 7/31/09, Alexey Pechnikov  wrote:

> From: Alexey Pechnikov 
> Subject: Re: [sqlite] Multi-master replication with updated Versioning 
> extension
> To: sqlite-users@sqlite.org
> Cc: "D. Richard Hipp" 
> Date: Friday, July 31, 2009, 8:42 AM
> Hello!
> 
> I made some changes:
>     hash field in actions table has always
> name "checksum" (so versioning and replication logic doesn't
> influence of hash algorithm)
>     versioning() function without second
> argument now start "local" mode
>     history and actions tables are renamed
> 
> Updated files is here
> http://mobigroup.ru/files/sqlite-ext/versioning/
> 
> Now there are two problems in the realization:
>     the "replace" conflict resolution algorithm
> for SOURCE table may produce errors - tickets 3964, 3982
>     versioning_drop() function doesn't work -
> ticket 4001
> 
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] SELECT DISTINCT bug in SQLite 3.6.0?

[Lennart Ramberg]

... or might it even be so, that good praxis is when using field
names, to always use the AS clause, even if no table-alias are used?
Like:
rs=db.SQLSelect("SELECT x AS x FROM t")
zstring=rs.Field("x").StringValue

Lennart Ramberg

On Fri, Jul 31, 2009 at 3:12 PM, Lennart
Ramberg wrote:
> Thanks for quick responses,
>
> Actually, removing DISTINCT removes the problem, but anyway, as I
> understand, I should always use the AS clause when using alias to be
> on the safe side, right?
>
> Lennart Ramberg
>
> On Fri, Jul 31, 2009 at 2:40 PM, Igor Tandetnik wrote:
>> Lennart Ramberg wrote:
>>> dim rs as RecordSet
>>> rs=dbEta.SQLSelect("SELECT DISTINCT V.resanr,C.namn"_
>>>  +" FROM Voyages V,Category C WHERE C.kategorinr=V.kategorinr")
>>>
>>> dim namnstr as string
>>>
>>> namnstr=rs.Field("namn").StringValue   'I get a NilObjectException
>>> here in 3.6.0 ...
>>> namnstr=rs.Field("C.namn").StringValue   '... but not here.
>>
>> Unless explicitly specified with AS clause in the statement, column
>> names are implementation-defined and subject to change without notice. I
>> believe the algorithm did change between 3.6.0 and 3.6.3. But you
>> shouln't have been relying on them in the first place.
>>
>>> Yes, at sqlite.org I read:
>>> "SQLite version 3.6.3 fixes a bug in SELECT DISTINCT that was
>>> introduced by the previous version."
>>> So that shouldn't be it, since it was introduced in 3.6.2, right?
>>
>> I don't believe this issue has anything to do with DISTINCT. I'm pretty
>> sure the problem will remain if you remove DISTINCT.
>>
>> 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

Re: [sqlite] Multi-master replication with updated Versioning extension

[Alexey Pechnikov]

Hello!

I made some changes:
hash field in actions table has always name "checksum" (so versioning 
and replication logic doesn't influence of hash algorithm)
versioning() function without second argument now start "local" mode
history and actions tables are renamed

Updated files is here
http://mobigroup.ru/files/sqlite-ext/versioning/

Now there are two problems in the realization:
the "replace" conflict resolution algorithm for SOURCE table may produce 
errors - tickets 3964, 3982
versioning_drop() function doesn't work - ticket 4001

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

Re: [sqlite] SELECT DISTINCT bug in SQLite 3.6.0?

[Lennart Ramberg]

Thanks for quick responses,

Actually, removing DISTINCT removes the problem, but anyway, as I
understand, I should always use the AS clause when using alias to be
on the safe side, right?

Lennart Ramberg

On Fri, Jul 31, 2009 at 2:40 PM, Igor Tandetnik wrote:
> Lennart Ramberg wrote:
>> dim rs as RecordSet
>> rs=dbEta.SQLSelect("SELECT DISTINCT V.resanr,C.namn"_
>>  +" FROM Voyages V,Category C WHERE C.kategorinr=V.kategorinr")
>>
>> dim namnstr as string
>>
>> namnstr=rs.Field("namn").StringValue   'I get a NilObjectException
>> here in 3.6.0 ...
>> namnstr=rs.Field("C.namn").StringValue   '... but not here.
>
> Unless explicitly specified with AS clause in the statement, column
> names are implementation-defined and subject to change without notice. I
> believe the algorithm did change between 3.6.0 and 3.6.3. But you
> shouln't have been relying on them in the first place.
>
>> Yes, at sqlite.org I read:
>> "SQLite version 3.6.3 fixes a bug in SELECT DISTINCT that was
>> introduced by the previous version."
>> So that shouldn't be it, since it was introduced in 3.6.2, right?
>
> I don't believe this issue has anything to do with DISTINCT. I'm pretty
> sure the problem will remain if you remove DISTINCT.
>
> 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

Re: [sqlite] SELECT DISTINCT bug in SQLite 3.6.0?

[Igor Tandetnik]

Lennart Ramberg wrote:
> dim rs as RecordSet
> rs=dbEta.SQLSelect("SELECT DISTINCT V.resanr,C.namn"_
>  +" FROM Voyages V,Category C WHERE C.kategorinr=V.kategorinr")
>
> dim namnstr as string
>
> namnstr=rs.Field("namn").StringValue   'I get a NilObjectException
> here in 3.6.0 ...
> namnstr=rs.Field("C.namn").StringValue   '... but not here.

Unless explicitly specified with AS clause in the statement, column 
names are implementation-defined and subject to change without notice. I 
believe the algorithm did change between 3.6.0 and 3.6.3. But you 
shouln't have been relying on them in the first place.

> Yes, at sqlite.org I read:
> "SQLite version 3.6.3 fixes a bug in SELECT DISTINCT that was
> introduced by the previous version."
> So that shouldn't be it, since it was introduced in 3.6.2, right?

I don't believe this issue has anything to do with DISTINCT. I'm pretty 
sure the problem will remain if you remove DISTINCT.

Igor Tandetnik



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

Re: [sqlite] SELECT DISTINCT bug in SQLite 3.6.0?

[Mike Eggleston]

On Fri, 31 Jul 2009, Lennart Ramberg might have said:

> Hello,
> 
> I'm new to this list and what prompted me to sign up was a SELECT
> DISTINCT problem I experience in REALbasic (Linux), which has SQLite
> built-in.
> 
> REALbasic downgraded their latest version from SQLIte 3.6.3 to 3.6.0
> Now, 3.6.0 behaves differently than 3.3.6 and 3.6.3 in the following manner:
> (excuse me for using RB code, but I think it is readable)
> 
> dim rs as RecordSet
> rs=dbEta.SQLSelect("SELECT DISTINCT V.resanr,C.namn"_
>   +" FROM Voyages V,Category C WHERE C.kategorinr=V.kategorinr")
> 
> dim namnstr as string
> 
> namnstr=rs.Field("namn").StringValue   'I get a NilObjectException
> here in 3.6.0 ...
> namnstr=rs.Field("C.namn").StringValue   '... but not here.
> 
> namnstr=rs.Field("C.namn").StringValue   'I get a NilObjectException
> here in 3.3.6 and 3.6.3 ...
> namnstr=rs.Field("namn").StringValue   '... but not here.
> 
> There are workarounds, but is this a bug in SQLite 3.6.0?
> 
> Yes, at sqlite.org I read:
> "SQLite version 3.6.3 fixes a bug in SELECT DISTINCT that was
> introduced by the previous version."
> So that shouldn't be it, since it was introduced in 3.6.2, right?
> 
> Thanks
> Lennart Ramberg

Just a thought. Have you tried the same sql select statement in the
sqlite3 command line tool? Does the tool report the same different values?

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

[sqlite] SELECT DISTINCT bug in SQLite 3.6.0?

[Lennart Ramberg]

Hello,

I'm new to this list and what prompted me to sign up was a SELECT
DISTINCT problem I experience in REALbasic (Linux), which has SQLite
built-in.

REALbasic downgraded their latest version from SQLIte 3.6.3 to 3.6.0
Now, 3.6.0 behaves differently than 3.3.6 and 3.6.3 in the following manner:
(excuse me for using RB code, but I think it is readable)

dim rs as RecordSet
rs=dbEta.SQLSelect("SELECT DISTINCT V.resanr,C.namn"_
  +" FROM Voyages V,Category C WHERE C.kategorinr=V.kategorinr")

dim namnstr as string

namnstr=rs.Field("namn").StringValue   'I get a NilObjectException
here in 3.6.0 ...
namnstr=rs.Field("C.namn").StringValue   '... but not here.

namnstr=rs.Field("C.namn").StringValue   'I get a NilObjectException
here in 3.3.6 and 3.6.3 ...
namnstr=rs.Field("namn").StringValue   '... but not here.

There are workarounds, but is this a bug in SQLite 3.6.0?

Yes, at sqlite.org I read:
"SQLite version 3.6.3 fixes a bug in SELECT DISTINCT that was
introduced by the previous version."
So that shouldn't be it, since it was introduced in 3.6.2, right?

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

[sqlite] SQL error: database disk image is malformed - other possibility than dump/load

[rupert.thurner]

is there another possibility but exporting and importing the data to
get rid of:
   SQL error: database disk image is malformed
?

here the details of the error and the commands we used. but we are not
sure why exporting works and vacuum should not work. we used sqlite
3.6.10, solaris.

# sqlite3 trac.db
sqlite> PRAGMA integrity_check;
*** in database main ***
Page 39762: sqlite3BtreeInitPage() returns error code 11
On tree page 39768 cell 0: 2nd reference to page 39762
Page 39763 is never used
Page 39764 is never used
Page 39765 is never used
Page 39766 is never used
Page 39767 is never used
SQL error: database disk image is malformed
sqlite> vacuum;
SQL error: database disk image is malformed


# sqlite3 trac.db .dump .quit >> trac.db.dump
# mv trac.db trac.db.orig
# sqlite3 trac.db < trac.db.dump
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] stupid me -> corrupted db file

[Rudi Fay]

Hey there,

i have just done a stupid thing - messing with the main db file without
a backup. I was "modifying" the db file with an hex-editor (done
before), unfortunately this time something went wrong and the db file
became corrupted.
I tried "pragma integrity_check;" with the result "SQL error: database
disk image is malformed"
Is there any way to get the data back, like repairing the file or just
extracting the core data?

Any idea/suggestion is welcome!


thx,



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

Re: [sqlite] Looking for a w_char alternative to the sqlite3_mprintf famili of functions

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

a...@zator.com wrote:
> I need verify that the "bind" method does not have problems with that kind of 
> strings.

The method to bind a string has no problem with any kind of string.  Behind
the scenes it does not convert what you supply into some sort of formatted
string.  Whatever you bind is what goes directly into the database - there
is no interpretation, tidying, escaping or anything else.  Heck you can even
include embedded nulls if you want!

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkpyqV8ACgkQmOOfHg372QQOzACgrdHGDyUewW4k40rJjLQP8ABr
rMoAmwZ+rjNy+X9qn6ocsPvcyvUIyMBp
=eqR5
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for a w_char alternative to the sqlite3_mprintf famili of functions

[ajm]

Roger:

Thanks for your input.

Really already I was using the suggested functions, but for some reason, the 
sections with strings where user inputs can be adds single quotes, have 
maintained that legacy code.

Any way thanks again,althought I need verify that the "bind" method does not 
have problems with that kind of strings.

A.J. Millan


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

A.J.Millan wrote:
> Actually I have an application that regularly uses expresions as:
> 
> char *zUserName = "My name";
> char *zSQL = sqlite3_mprintf ("INSERT INTO table VALUES('%q')", zUserName);

Why aren't you using bound parameters?  It is far harder to do what you are
doing above, less efficient and has the potential for SQL injection attacks
(unless you remembered %q everywhere!)

These are the functions you need:

 http://www.sqlite.org/c3ref/bind_blob.html

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