Re: [sqlite] I got some trouble with sqlite3 and my C++ program

[Nicolas Jäger]

Hi Clemens,
  I changed my code, now it works fine.

thank you,
regards,
/nicoo

On Thu, 15 Jan 2015 14:35:49 +0100
Clemens Ladisch  wrote:

> Nicolas Jäger wrote:
> > do
> > {
> >   rc = sqlite3_step(stmt);
> >   std::cout << sqlite3_column_text (stmt, 0) <<","
> > < > SQLITE_ROW);
> 
> sqlite3_step() returns SQLITE_ROW when there is a row, or SQLITE_DONE
> when there are no more rows, or an error code.  So after calling it,
> you _must_ check the return value _before_ you try to read from the
> row:
> 
> while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
> {
> ...sqlite3_column_...
> }
> if (rc != SQLITE_DONE)
> cerr << "error: " << sqlite3_errmsg(db) << endl;
> sqlite3_finalize(stmt);
> 
> 
> Regards,
> Clemens
> ___
> 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] Inserting/removing large number of rows with index

[RSmith]

On 2015/01/15 23:18, Baruch Burstein wrote:

Hi,

If I have a table with an index, and INSERT or DELETE a large number of
rows in one statement, does sqlite stop to update the index for each
record, or is it smart enough to update the index just once for all the
changed records?

 In a B-Tree Index this hardly matters much in terms of total time (it just sometimes feels a lot faster when you first insert then 
index, but in reality the total time should not differ  hugely unless you are inserting an insane amount of rows), but it is not a 
question of being "smart" enough or not, it's a question of functionality - if the index is unique or primary (for instance), it 
must fail immediately when a duplication occurs, not at the end. How would it accomplish this if it deferred indexing to after the 
insertions took place?


The SQL's first and foremost responsibility is producing correct answers and strict enforcement of the DB designer's rules - only 
after these are satisfied can an engine attempt optimization. This is why it is suggested sometimes that if the checking does not 
matter to you during insertion, then build the index afterwards.


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

Re: [sqlite] Saving PDF Files in SQlite

[David King]

> How do I save PDF files in SQLIte?

Why do you want to store PDF files in sqlite? What is the "real" problem that 
that solves for you?

There is http://www.sqlite.org/sar/doc/trunk/README.md which does what you're 
literally asking, but I feel that with more context we can give you better 
direction



signature.asc
Description: Message signed with OpenPGP using GPGMail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] damaged database recovery

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/15/2015 12:52 PM, Dave Dyer wrote:
> Of course that's possible, but .dump produced what superficially 
> appeared to be a perfectly consistent text file.

Note that .dump writes the output and then on encountering problems
attempts the table again, but instead starting from the "end" in
reverse order.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlS4Rk8ACgkQmOOfHg372QRlLACg2Qxbn/WFJYkIUq5g/k6hiOGT
XxEAnA5UV6S6OQRBpMrqS1y2f3Gzx8IZ
=dzLI
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] damaged database recovery

[Dave Dyer]

>
>Try doing:  sqlite3 old-database .dump | sqlite3 fixed-database
>
>Then verify that "fixed-database" still contains all of your data.

This doesn't work on these databases, even undamaged ones.  I think
it's a buffer size problem with sqlite3.  The databases contain some
rather long text strings.  

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

[sqlite] damaged database recovery

[Dave Dyer]

>
>Try doing:  sqlite3 old-database .dump | sqlite3 fixed-database
>
>Then verify that "fixed-database" still contains all of your data.

This doesn't work on these databases, even undamaged ones.  I think
it's a buffer size problem with sqlite3.  The databases contain some
rather long text strings.  

Re: [sqlite] Saving PDF Files in SQlite

[tonyp]

From the SQLite3 shell (recent version), use the readfile('filename') 
function to import into a blob field, and the writefile('filename',field) 
for exporting back to a file.


See here: http://www.sqlite.org/cli.html

-Original Message- 
From: John Payne

Sent: Thursday, January 15, 2015 10:24 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Saving PDF Files in SQlite



How do I save PDF files in SQLIte? Is there a preferred method? All the
online suggestions seem to require writing some custom code. Is there an
add-on for saving PDF or other digital objects? I'm not a programmer, do
not know php and rather clueless on how to proceed.

Thanks

John Payne
j...@pde-usa.net
630-815-8849

___
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] damaged database recovery

[Richard Hipp]

On 1/15/15, Dave Dyer  wrote:
> The likely cause of corruption is that this is probably a
> database being accessed on a networked disk.
>
> --
>
> sqlite> select * from preference_table where preferenceset='foo';
> sqlite> drop index preferenceindex;
> (11) database corruption at line 52020 of [2677848087]
> (11) statement aborts at 24: [drop index preferenceindex;]
> (11) database corruption at line 46321 of [2677848087]
> (11) database corruption at line 46359 of [2677848087]
> Error: database disk image is malformed
> sqlite> pragma quick_check;
> *** in database main ***
> On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent
> max of 25141)
> On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745)

More than just index corruption

Try doing:  sqlite3 old-database .dump | sqlite3 fixed-database

Then verify that "fixed-database" still contains all of your data.


> On tree page...
> sqlite> reindex;
> (11) database corruption at line 52020 of [2677848087]
> (11) statement aborts at 3: [reindex;]
> (11) database corruption at line 46321 of [2677848087]
> (11) database corruption at line 46359 of [2677848087]
> Error: database disk image is malformed
> sqlite> pragma integrety_check;
> sqlite> pragma quick_check;
> *** in database main ***
> On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent
> max of 25141)
> On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745)
> On tree page...
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] Inserting/removing large number of rows with index

[Baruch Burstein]

Hi,

If I have a table with an index, and INSERT or DELETE a large number of
rows in one statement, does sqlite stop to update the index for each
record, or is it smart enough to update the index just once for all the
changed records?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] damaged database recovery

[Dave Dyer]

The likely cause of corruption is that this is probably a
database being accessed on a networked disk.

--

sqlite> select * from preference_table where preferenceset='foo';
sqlite> drop index preferenceindex;
(11) database corruption at line 52020 of [2677848087]
(11) statement aborts at 24: [drop index preferenceindex;]
(11) database corruption at line 46321 of [2677848087]
(11) database corruption at line 46359 of [2677848087]
Error: database disk image is malformed
sqlite> pragma quick_check;
*** in database main ***
On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max 
of 25141)
On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745)
On tree page...
sqlite> reindex;
(11) database corruption at line 52020 of [2677848087]
(11) statement aborts at 3: [reindex;]
(11) database corruption at line 46321 of [2677848087]
(11) database corruption at line 46359 of [2677848087]
Error: database disk image is malformed
sqlite> pragma integrety_check;
sqlite> pragma quick_check;
*** in database main ***
On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max 
of 25141)
On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745)
On tree page...

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

[sqlite] damaged database recovery

[Dave Dyer]

The likely cause of corruption is that this is probably a
database being accessed on a networked disk.

--

sqlite> select * from preference_table where preferenceset='foo';
sqlite> drop index preferenceindex;
(11) database corruption at line 52020 of [2677848087]
(11) statement aborts at 24: [drop index preferenceindex;]
(11) database corruption at line 46321 of [2677848087]
(11) database corruption at line 46359 of [2677848087]
Error: database disk image is malformed
sqlite> pragma quick_check;
*** in database main ***
On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max 
of 25141)
On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745)
On tree page...
sqlite> reindex;
(11) database corruption at line 52020 of [2677848087]
(11) statement aborts at 3: [reindex;]
(11) database corruption at line 46321 of [2677848087]
(11) database corruption at line 46359 of [2677848087]
Error: database disk image is malformed
sqlite> pragma integrety_check;
sqlite> pragma quick_check;
*** in database main ***
On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max 
of 25141)
On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745)
On tree page...

Re: [sqlite] damaged database recovery

[Richard Hipp]

On 1/15/15, Dave Dyer  wrote:
>
>>
>>
>>> it wasn't possible to drop the index in question
>>
>>what happened when you tried ?  Were you using your own code or the SQLite
>> shell tool ?
>
> sqlite shell tool.  Same complaint, "database corrupted".


First type:  ".log stdout"
That will get you additional diagnostic information.

Also try: "PRAGMA quick_check;" and "PRAGMA integrity_check;" for
additional information about the problem.

If damage is restricted to indexes, it can be repaired using "REINDEX;".

More concerning is how the database got corrupted in the first place.
That isn't suppose to ever happen.  See
https://www.sqlite.org/howtocorrupt.html for possible clues.

>
>
>>My guess is that you actually have file-level corruption which just
>> happened to corrupt data in an index page.  Could have just as easily been
>> a table page and you would have had more trouble recovering your data.
>
> Of course that's possible, but .dump produced what superficially
> appeared to be a perfectly consistent text file.   However, since
> we're dealing with an "impossible" error, it's hard to say definitively.
>
>>A good approach for your situation might have been to use the SQLite shell
>> tool to .dump your database to a text file, then to use .read to create a
>> new database from those commands.  But it may or may not have worked from
>> your particular corrupt database.
>
> That's exactly what I did do.  .read initially failed with a complaint
> about the non-unique indexes (although once again, no indication which
> index).  A process of elimination identified the index, which eventually
> allowed me to remove the duplicates, so .read could succeed.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] damaged database recovery

[Dave Dyer]

>
>
>> it wasn't possible to drop the index in question
>
>what happened when you tried ?  Were you using your own code or the SQLite 
>shell tool ?

sqlite shell tool.  Same complaint, "database corrupted".


>My guess is that you actually have file-level corruption which just happened 
>to corrupt data in an index page.  Could have just as easily been a table page 
>and you would have had more trouble recovering your data.

Of course that's possible, but .dump produced what superficially 
appeared to be a perfectly consistent text file.   However, since 
we're dealing with an "impossible" error, it's hard to say definitively.

>A good approach for your situation might have been to use the SQLite shell 
>tool to .dump your database to a text file, then to use .read to create a new 
>database from those commands.  But it may or may not have worked from your 
>particular corrupt database.

That's exactly what I did do.  .read initially failed with a complaint
about the non-unique indexes (although once again, no indication which
index).  A process of elimination identified the index, which eventually
allowed me to remove the duplicates, so .read could succeed.

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

[sqlite] damaged database recovery

[Dave Dyer]

>
>
>> it wasn't possible to drop the index in question
>
>what happened when you tried ?  Were you using your own code or the SQLite 
>shell tool ?

sqlite shell tool.  Same complaint, "database corrupted".


>My guess is that you actually have file-level corruption which just happened 
>to corrupt data in an index page.  Could have just as easily been a table page 
>and you would have had more trouble recovering your data.

Of course that's possible, but .dump produced what superficially 
appeared to be a perfectly consistent text file.   However, since 
we're dealing with an "impossible" error, it's hard to say definitively.

>A good approach for your situation might have been to use the SQLite shell 
>tool to .dump your database to a text file, then to use .read to create a new 
>database from those commands.  But it may or may not have worked from your 
>particular corrupt database.

That's exactly what I did do.  .read initially failed with a complaint
about the non-unique indexes (although once again, no indication which
index).  A process of elimination identified the index, which eventually
allowed me to remove the duplicates, so .read could succeed.

Re: [sqlite] damaged database recovery

[Simon Slavin]

On 15 Jan 2015, at 8:24pm, Dave Dyer  wrote:

> 1) the generic error 11 "database corrupt" could have been more
> specific.  It would have been handy to know that the complaint was
> about duplicate indexes, and which index, or even which table was
> involved.
> 
> 2) it wasn't possible to drop the index in question.  Or even the
> whole table containing the index.  If I could have dropped the 
> offending index, I could have removed the duplicates and recreated
> the index without requiring major surgery.

You might have checked the extended error code ...



though I don't know what it would have told you about your particular database.

When you write that

> it wasn't possible to drop the index in question

what happened when you tried ?  Were you using your own code or the SQLite 
shell tool ?

My guess is that you actually have file-level corruption which just happened to 
corrupt data in an index page.  Could have just as easily been a table page and 
you would have had more trouble recovering your data.

A good approach for your situation might have been to use the SQLite shell tool 
to .dump your database to a text file, then to use .read to create a new 
database from those commands.  But it may or may not have worked from your 
particular corrupt database.

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

Re: [sqlite] Saving PDF Files in SQlite

[Simon Slavin]

On 15 Jan 2015, at 8:24pm, John Payne  wrote:

> How do I save PDF files in SQLIte?

Read the bytes of the file and save them in a BLOB field.

But I have to warn you ...

> I'm not a programmer,

SQLite is a tool for programmers.  It makes database facilities available to 
programmers.  It's not designed for people who know only how to use programs.

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

Re: [sqlite] damaged database recovery

[Dave Dyer]

I have a case of a damaged database, where the only damage appears to be
that somehow the index uniqueness constraint is violated.  As long as the
operations don't touch the index, the db operates without complaint.

I was eventually able to construct a copy with good indexes, but

1) the generic error 11 "database corrupt" could have been more
specific.  It would have been handy to know that the complaint was
about duplicate indexes, and which index, or even which table was
involved.

2) it wasn't possible to drop the index in question.  Or even the
whole table containing the index.  If I could have dropped the 
offending index, I could have removed the duplicates and recreated
the index without requiring major surgery.

3) maybe I missed something - there was an easier way?

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

[sqlite] Saving PDF Files in SQlite

[John Payne]

 

How do I save PDF files in SQLIte? Is there a preferred method? All the
online suggestions seem to require writing some custom code. Is there an
add-on for saving PDF or other digital objects? I'm not a programmer, do
not know php and rather clueless on how to proceed. 

Thanks 

John Payne
j...@pde-usa.net
630-815-8849
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] damaged database recovery

[Dave Dyer]

I have a case of a damaged database, where the only damage appears to be
that somehow the index uniqueness constraint is violated.  As long as the
operations don't touch the index, the db operates without complaint.

I was eventually able to construct a copy with good indexes, but

1) the generic error 11 "database corrupt" could have been more
specific.  It would have been handy to know that the complaint was
about duplicate indexes, and which index, or even which table was
involved.

2) it wasn't possible to drop the index in question.  Or even the
whole table containing the index.  If I could have dropped the 
offending index, I could have removed the duplicates and recreated
the index without requiring major surgery.

3) maybe I missed something - there was an easier way?

Re: [sqlite] Huge WAL log

[Paul Sanderson]

> I understand that the WAL log must take a lot of space. What I don't
> understand is that it was 7x larger than the resulting DB size. (Actual
> quotient is even larger because I compared to the DB size that contained
> also other tables.)

Unlike a rollback journal a WAL file can have multiple copies of the same page.

So from yor main loop, expanding the following code may help us understand.

"insert all downloaded rows"

If your inserted records is 5million separate insertions then each
insertion could result in a table leaf page being written to the WAL
file. Each insertion could potentially also result in one (or more)
table interior pages being written to the WAL. Subsequent insertions
could result in multiple copies of the same page.

I don't know when any index would be updated - whether at each record
update, or when the transaction is committed. But if the former then
there could be a relatively large number of index leaf and interior
pages. This could become signiifcant if an index is built on a large
string field

What is the schema for this table and what indexes are on it?

The above is based on my knowledge of the file formats and what I have
seen examining these files, not on the workings/code in SQLite so
perhaps Dr Hipp could correct me if I'm wrong.


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using a hex integer as input to DateTime

[Paul Sanderson]

Sorry Carlos - vanilla sqlite is required.

Its not a big issue for me.

Cheers
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 15 January 2015 at 13:08, Carlos Milon Silva  wrote:
> Hi, I do use Xojo (Realbasic) to develop applications with SQLite databases.
> I also use some SQLite extensions from:
>
> http://www.monkeybreadsoftware.de/SQLiteExtension/index.shtml
>
> I guess they would be useful (as generic SQLite load extension) in Your
> Toolkit.
> You may ask Christian to support a particular need.
>
> Carlos.
>
> On 1/15/2015 5:30 AM, Paul Sanderson wrote:
>>
>> Thanks Peter
>>
>> Coding outside of SQLite is easy - it's doing it with just SQLite/SQL
>> that I was after :(
>>
>> Cheers
>> Paul
>> www.sandersonforensics.com
>> skype: r3scue193
>> twitter: @sandersonforens
>> Tel +44 (0)1326 572786
>>
>> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>> -Forensic Toolkit for SQLite
>> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>> processing made easy
>>
>>
>
> ___
> 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] Huge WAL log

[Simon Slavin]

On 15 Jan 2015, at 3:44pm, Jan Slodicka  wrote:

> Index rebuild (using sqlite3 shell) took 123 sec. This would suggest that it
> might be better to run with deleted indexes and rebuild them at the end.

That is as expected, and is standard advice for cases where you are adding huge 
numbers of rows.

> WAL size started at 65MB and slightly increased once upon a time ending
> finally at 177MB. Very good.

Yes, that's better.  I assume you set a journal_size_limit value.

However, other information in your message suggests that you have a resource 
leak of some type somewhere.  Especially, it should not take 12 minutes to 
insert 3.5M rows into a simple table with an index or two unless really long 
strings or blobs are involved.

Unfortunately, I'm only really familiar with the C and PHP interfaces to 
SQLite.  But in both of those you can check the result code of each API call to 
make sure it is SQLITE_OK.  Are you able to do this with whatever interface 
you're using ?

> DB size increased by roughly 17-18K after each commit. This suggests that
> WAL needs 10x more memory than the DB itself.


Very variable.  Depends on whether the changes in one transaction change many 
different pages or change fewer different pages multiple times.  At least, I 
think so.

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

Re: [sqlite] Huge WAL log

[Jan Slodicka]

I'll add the results from additional tests.

First of all, I forced a commit after each 100,000 records inserted into a
single table. (A complication for us.)

Some numbers for a table with a single index and 3,423,000 inserted records:

Intermediate commits took subsequently 764 msec, 2164 msec, 4462... 38532
msec. In other words the commit time more or less regularly increased. I
attribute this to the index building. 
Total time spent in all commits is 730 secs.

Index rebuild (using sqlite3 shell) took 123 sec. This would suggest that it
might be better to run with deleted indexes and rebuild them at the end.

WAL size started at 65MB and slightly increased once upon a time ending
finally at 177MB. Very good.

DB size increased by roughly 17-18K after each commit. This suggests that
WAL needs 10x more memory than the DB itself.

Other large tables yielded similar results as above. 

I made one additional observation worth of reporting.

After an hour or so the debugger (not the application) stopped working
leaving thus active WAL. Running SELECT COUNT(*) on currently processed
table took 44 secs. However, after the WAL was commited, the same select
took 2 secs.







--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p80024.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] I got some trouble with sqlite3 and my C++ program

[Clemens Ladisch]

Nicolas Jäger wrote:
> do
> {
>   rc = sqlite3_step(stmt);
>   std::cout << sqlite3_column_text (stmt, 0) <<"," < (stmt, 2) << std::endl;
> } while(rc == SQLITE_ROW);

sqlite3_step() returns SQLITE_ROW when there is a row, or SQLITE_DONE
when there are no more rows, or an error code.  So after calling it, you
_must_ check the return value _before_ you try to read from the row:

while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
{
...sqlite3_column_...
}
if (rc != SQLITE_DONE)
cerr << "error: " << sqlite3_errmsg(db) << endl;
sqlite3_finalize(stmt);


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

[sqlite] System.Data.SQLite for Pocket PC & RTREE

[Green Fields]

Hi

 

I'm new to the list and newish to SQLite and would appreciate some tips.

I'm attempting to create an application that requires a spatial rtree query,
and this works extremely well using the x86 version of the
System.Data.SQLite library
(sqlite-netFx40-static-binary-bundle-Win32-2010-1.0.94.0.zip). However, when
I attempt to run the same query using
(sqlite-netFx35-binary-PocketPC-ARM-2008-1.0.94.0.zip) in a pocketPC port,
the app crashes, and I have been unable to get any feedback from the debug
because the connection to the device is broken. Standard queries and
sqlite_version() work fine.

 

I'm pretty sure rtree would be enabled in the cf binary looking at the
source config, but I don't know of a way to check for this in the compiled
binary short of running an rtree query.

 

I am new to debugging on mobile devices so there may be a way to log the
error that I'm not aware of, but so far all attempts to trap the error have
failed.

 

Does anyone have any suggestions where the problem might lie?

 

Thanks for any help

 

Duncan

 

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

Re: [sqlite] using a hex integer as input to DateTime

[Carlos Milon Silva]

Hi, I do use Xojo (Realbasic) to develop applications with SQLite databases.
I also use some SQLite extensions from:

http://www.monkeybreadsoftware.de/SQLiteExtension/index.shtml

I guess they would be useful (as generic SQLite load extension) in Your 
Toolkit.

You may ask Christian to support a particular need.

Carlos.

On 1/15/2015 5:30 AM, Paul Sanderson wrote:

Thanks Peter

Coding outside of SQLite is easy - it's doing it with just SQLite/SQL
that I was after :(

Cheers
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy




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

[sqlite] I got some trouble with sqlite3 and my C++ program

[Nicolas Jäger]

Hi,
I'm discovering/using sqlite3 since three days, and I got a problem
that I don't understand. I have this code :

bool
DataTable_manager::searchByTags(const std::vector 
&tags_list) {
  int rc;
  sqlite3_stmt *stmt;

  for (auto & tag : tags_list)
  {
std::cout << tag << std::endl;
Sqlite3::insertQueryAndGetResults(database, "SELECT * FROM
 __TAGS_TABLE__  WHERE TAG = '"+tag+"'", stmt); 

do
{

  rc = sqlite3_step(stmt);
//  std::cout << "ok" << std::endl;
  std::cout << sqlite3_column_text (stmt, 0) <<","
  < test_vector;
  test_vector.push_back("tag01");
  test_vector.push_back("tag02");

  dataTable_manager.searchByTags(test_vector);

the problem is, I get this :

tag01
44,tag01

so tag02 is missing. However, if I comment the line,
//  std::cout << sqlite3_column_text (stmt, 0) <<","
  

Re: [sqlite] Best Practice: Storing Dates

[Tim Streater]

On 14 Jan 2015 at 23:13, Simon Slavin  wrote: 

> On 14 Jan 2015, at 10:40pm, Baruch Burstein  wrote:
>
>> Of course, this is just at the theoretical level. As yo said, your app
>> probably wouldn't need to worry about this.
>
> I think a previous poster had it right.  If you need to do lots of maths with
> the timestamps store unix epochs as INTEGERs (or Julian dates if you care only
> about date and not time).  If, on the other hand, your database is read
> directly by a human a lot, store the dates as ISO format in TEXT.

I would have thought that storing them as integers and then presenting them in 
a user-selected format is much more user-friendly.



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

Re: [sqlite] Huge WAL log

[Jan Slodicka]

Simon Slavin-3 wrote
>> - WAL log size 7.490 GB
> 
> Please repeat your tests but as the first command after opening your
> database file issue
> 
> PRAGMA journal_size_limit = 100
> 
> With this change the WAL file may still grow to 7 GB while that particular
> transaction is being executed but should be reduced in size after the
> transaction is completed.

Thanks for the tip. I'll do so.




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p80018.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] Huge WAL log

[Jan Slodicka]

Richard Hipp-3 wrote
> What is your page size?

1024


Richard Hipp-3 wrote
> Your original post said you inserted two rows for each transaction.
> How big are those two rows?

Sorry for misleading information. Here is a more formal algorithm:

foreach table
{
BEGIN
insert all downloaded rows
COMMIT
}

No. of downloaded rows per table is unknown. In this extreme case it was
3.5+ mil records.

I understand that the WAL log must take a lot of space. What I don't
understand is that it was 7x larger than the resulting DB size. (Actual
quotient is even larger because I compared to the DB size that contained
also other tables.)






--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p80017.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] using a hex integer as input to DateTime

[Paul Sanderson]

Thanks Peter

Coding outside of SQLite is easy - it's doing it with just SQLite/SQL
that I was after :(

Cheers
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 15 January 2015 at 00:27, Peter Aronson  wrote:
> If you're accessing SQLite from your own program, adding a hextoint function 
> would be pretty easy.  Even if you were using sqlite3, defining your own 
> extension isn't that hard (and is documented on the SQLite website to some 
> extent).  That would allow you to do most of the things you've been asking 
> about.  Here's a rough cut at one (untested, and somewhat platform dependent):
>
> /***
> *
> *  S_hextoint_function -- Convert a hex number string starting with 0x to int.
> *
> ***/
> static void S_hextoint_function (sqlite3_context  *ctx,
>  int  num_values,
>  sqlite3_value**values)
> {
>   const char *input_string;
>   char   *error_string;
>   sqlite3_int64  value;
>   long long  llvalue;
>   intconverted;
>   size_t string_length;
>
>   /* The integer value of a NULL is NULL. */
>
>   if (sqlite3_value_type (values[0]) == SQLITE_NULL) {
> sqlite3_result_null (ctx);
> return;
>   }
>
>   /* Get the hex string and make sure it starts with 0x. */
>
>   input_string = (const char *)sqlite3_value_text (values[0]);
>   if ((const SE_WCHAR *)NULL == input_string) {
> sqlite3_result_error_nomem (ctx);
> return;
>   }
>
>   string_length = strlen (input_string);
>   if (strlen < 3 || '0' != input_string[0] ||
>   ('x' != input_string[1] && 'X' != input_string[1])) {
> error_string = sqlite3_mprintf ("'%s' is not a legal hex constant.",
> input_string);
> sqlite3_result_error (ctx,error_string,-1);
> sqlite3_free (error_string);
> return;
>   }
>
>   /* Convert the hex string. */
>
>   converted = sscanf (input_string,"%llx",&llvalue);
>   if (1 != converted)
> error_string = sqlite3_mprintf ("'%s' is not a legal hex constant.",
> input_string);
> sqlite3_result_error (ctx,error_string,-1);
> sqlite3_free (error_string);
> return;
>   }
>   value = (sqlite3_int64)llvalue;
>
>   /* Return the integer value. */
>
>   sqlite3_result_int64 (ctx,value);
> }
>
> And, if in your own program, you'd just execute
>
>   sqlite3_create_function (hdbc,
>"hextoint",
>-1,
>SQLITE_ANY,
>globalfunc_info,
>S_hextoint_function,
>NULL,
>NULL);
>
> After connecting, but before executing your SQL.
>
> Peter
>
>
> On Wednesday, January 14, 2015 4:32 PM, Paul Sanderson 
>  wrote:
>
>
>>
>>
>>Thanks all
>>
>>I am running the latest version :)
>>
>>I am just getting back to this and have a related problem
>>
>>I have a table
>>
>>create table (base int, hex text)
>>
>>and I want to create a trigger such that if hex is updated (or a new
>>row inserted) with a text string in the form 0x12345abcd this value is
>>converted into an integer and copied to base.
>>
>>I have tried various methods such as
>>
>>CREATE TRIGGER hex_trig after insert on dates
>>when (select hex from dates where hex is not null)
>>begin
>>update dates set base = cast(new.hex as int);
>>end
>>
>>but so far have drawn a blank
>>
>>Can this be done?
>>
>>
>>Paul
>>www.sandersonforensics.com
>>skype: r3scue193
>>twitter: @sandersonforens
>>Tel +44 (0)1326 572786
>>http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>>-Forensic Toolkit for SQLite
>>http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>>processing made easy
>>
>>
>>
>>On 8 January 2015 at 00:33, Richard Hipp  wrote:
>>> On 1/7/15, Paul Sanderson  wrote:
 Evening all

 I dont think this can be done, but would love to be corrected. I have
 a column with integer dates stored in hex format so 1234567890 is
 stored as 49962d2

 Obviously
 DateTime(1234567890, 'unixepoch')

 work OK, but what I would like to do is something like

 DateTime(0x49962d2, 'unixepoch')
>>>
>>> This should work fine, provided you are using SQLite 3.8.6 or later.
>>> Support for hexadecimal literals was added in version 3.8.6.  Release
>>> date for 3.8.6 was 2014-08-15, so this is not the version running on
>>> your phone.  :-\
>>>
>>>

 or

 DateTime(HexToInt(0x49962d2), 'unixepoch')

 Is this possible? 

Re: [sqlite] Huge WAL log

[Dan Kennedy]

On 01/15/2015 12:28 AM, Jan Slodicka wrote:

Richard Hipp-3 wrote

No other active readers or writers.

Are you sure?

Writers for sure.

As far readers are concerned, the things are too complex to make an absolute
statement. (I shall check once more.)


Some APIs that might be helpful:

  * sqlite3_get_autocommit() returns 0 if you have an explicit 
transaction open.
  * sqlite3_next_stmt() can be used to iterate through all statements 
belonging to a db connection.
  * sqlite3_stmt_busy() can be used to determine if a statement has 
been stepped but not reset (and so may be holding open an implicit 
transaction).


https://www.sqlite.org/c3ref/get_autocommit.html
https://www.sqlite.org/c3ref/next_stmt.html
https://www.sqlite.org/c3ref/stmt_busy.html

Dan.






However, I can add a few observations
I made:

WAL file size was about 70 MB (as reported by the OS) until the critical
table started.

The OS started to report 7GB after the commit of the critical table
finished.

The commit itself took more than 30 min. After the commit the DB size grew
by several 100MB. What else could explain this except moving data from WAL
to DB? (I.e. WAL reset.)

Afterwards several other tables were written (total number of records close
to 1 mil), but the WAL file did not grow anymore.

After the last table finished, the application was responsive nearly
instantly. (No big data movement between WAL and DB.)

Finally, the application closed without any delay while deleting the WAL
file. (Apparently the WAL file did not contain large amount of unsaved
data.)




Because the WAL file should reset automatically after a commit...

To make sure that I understand: You mean moving a file pointer, not
shrinking of the file itself? (AFAIK, WAL file never shrinks.)



P.S. We still use v3.7.15.2. (Would like to upgrade shortly.)

P.P.S. I have a copy of all SQLite files. If it helped I could try to read
WAL file manually.






--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p79993.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


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