Re: [sqlite] confused getting started

[Barry Smith]

A less LMGTFY-like response:
First off, using SQLite does require that you become familiar with SQL, and a 
bunch of database ideas: normalization for good schema design, unit of work for 
good use of transactions, how to use indexes, others I'm sure I don't know 
about. But those ideas are not specific to SQLite, and are good to think about 
anyway. And any part of SQL or db design you learn will be far easier than 
trying to deal with the same problems in a file format you've written yourself.

If you want to use SQLite in a c/c++ app, your easiest option is to simply 
download the amalgamation and include it in your project. That's two files - 
SQLite.c and SQLite.h. The basic procedure is:
Open the file: sqlite3_open(...);
prepare your statement: sqlite3_prepare_v2(...);
Bind your parameters: sqlite3_bind(...);
In a loop {
 execute the statement: sqlite3_step(...);
 Retrieve data if it's a select: sqlite3_column(...);
}
Finalize or reset the statement (depending if you want to use it again): 
sqlite3_finalise / sqlite3_reset
Close the file: sqlite3_close(...)

If you're using dotNet, you can use NuGet to install system.data.sqlite.core, 
then #using system.data.sqlite you'll find a ado.net database interface which 
you should more or less use like any other ado.net db interface (with the 
advantage that if you ever migrate to another dbms will be easier).

I can't comment on Python, I haven't used that.

I never thought SQLite difficult to start using. In fact, possibly what I 
didn't want to accept at first was exactly how easy it was and thought there 
were things I was missing*.

For all but the simplest use cases I think SQLite is far easier than direct 
file access for the fact that it keeps track of the contents of the file for 
you, you have an extensible self documenting file format (in the form of your 
db schema), and it takes care of most of your robustness concerns by making 
everything ACID.

* there were things I was missing - like getting all the config right, and 
making sure to reset or finalize statements, and making sure to always do any 
file operations on the log file(s) too. But even with these mistakes it still 
worked leagues better than anything I could have written myself, with a 
fraction of the work.

> On 5 Mar 2017, at 9:20 PM, Jens Alfke  wrote:
> 
> 
>> On Mar 5, 2017, at 3:03 AM, NTrewartha T-Online  
>> wrote:
>> 
>> Any examples of a C,C++,C# or Python usage for sqllite.?
> 
> Have you tried searching? I entered “sqlite c example” and “sqlite python 
> example” into Google and got some useful-looking links in the top few hits.
> Also, there are quite a few books about SQLite.
> 
>> I would like sqllite on my raspberry pi 3 after I have gained experience 
>> under Windows 10.
> 
> If it’s not installed already in the Raspbian OS, you should just need to run 
> “sudo apt-get sqlite”. (Possibly “sudo apt-get sqlite_dev” since IIRC the 
> development resources like header files are in separate packages under 
> Debian-based distros.)
> 
>> Judging what the replies to questions, the very new beginners are left a bit 
>> out in the cold.
> 
> No offense intended, but SQLite isn’t an especially beginner-friendly tool. 
> It’s a powerful relational database with a ton of configurable options, and a 
> somewhat tricky C API, not to mention a sophisticated query language that you 
> also need to master to make effective use of it. (However, using it from 
> Python should be somewhat easier, since the API is a bit higher level and you 
> don’t have to worry about things like memory management.)
> 
> If your data storage needs aren’t too complex, there are simpler ways to 
> implement it. For example, in the past I’ve just used a simple data 
> serialization library to read and write the entire data set to disk. It works 
> great when the data is small enough that it fits easily in memory and doesn’t 
> take too long to read or write (say, under 100MB.) This is the equivalent of 
> using a regular battery-powered drill to make some holes, instead of learning 
> how to use a router or end mill :)
> 
> (Also, in general if you’re moving from Windows to literally any other 
> platform, you’ll have to re-learn some of your development processes. Windows 
> does things differently from Unix, which is what everything else is based on.)
> 
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] confused getting started

[Jens Alfke]

> On Mar 5, 2017, at 3:03 AM, NTrewartha T-Online  
> wrote:
> 
> Any examples of a C,C++,C# or Python usage for sqllite.?

Have you tried searching? I entered “sqlite c example” and “sqlite python 
example” into Google and got some useful-looking links in the top few hits.
Also, there are quite a few books about SQLite.

> I would like sqllite on my raspberry pi 3 after I have gained experience 
> under Windows 10.

If it’s not installed already in the Raspbian OS, you should just need to run 
“sudo apt-get sqlite”. (Possibly “sudo apt-get sqlite_dev” since IIRC the 
development resources like header files are in separate packages under 
Debian-based distros.)

> Judging what the replies to questions, the very new beginners are left a bit 
> out in the cold.

No offense intended, but SQLite isn’t an especially beginner-friendly tool. 
It’s a powerful relational database with a ton of configurable options, and a 
somewhat tricky C API, not to mention a sophisticated query language that you 
also need to master to make effective use of it. (However, using it from Python 
should be somewhat easier, since the API is a bit higher level and you don’t 
have to worry about things like memory management.)

If your data storage needs aren’t too complex, there are simpler ways to 
implement it. For example, in the past I’ve just used a simple data 
serialization library to read and write the entire data set to disk. It works 
great when the data is small enough that it fits easily in memory and doesn’t 
take too long to read or write (say, under 100MB.) This is the equivalent of 
using a regular battery-powered drill to make some holes, instead of learning 
how to use a router or end mill :)

(Also, in general if you’re moving from Windows to literally any other 
platform, you’ll have to re-learn some of your development processes. Windows 
does things differently from Unix, which is what everything else is based on.)

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

[sqlite] SQLite Recursive Common Table Expression suggestion

[Simon Slavin]

I’ve seen many amusing examples of using Common Table Expressions to solve 
Sudoko puzzles.  Has anyone tried using one to suggest the best next move for 
Minesweeper ?  The idea would be to supply a grid with the solution so far in 
it, and supply the total number of mines, and have SQLite suggest a good next 
move.

No competition.  No prizes.  No fame.  Just thought some of you might enjoy 
trying it.

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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Keith Medcalf]

Yes there is.  Every SQL statement is performing a COMMIT since you have not 
explicitly delimited your transactions.

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Yuri
> Sent: Sunday, 5 March, 2017 08:06
> To: SQLite mailing list
> Subject: Re: [sqlite] Can sqlite3_blob_write be done through SQL?
> 
> On 03/05/2017 03:48, Keith Medcalf wrote:
> > Perhaps because you are committing the transaction?
> 
> There is no COMMIT statement there.
> 
> 
> Yuri
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] confused getting started

[dmp]

> I'm not a computer novice, but also not a nerd/geek.
> Been using PCs since the mid 1980s and have used dBase III+ and Approach
> previously.
>
> I can't seem to grasp what I need to download / install to use SQLite?
>
> Can someone guide me through the process?  Looking to use it with
> RootsMagic.
>
> Thank you.
> John

Hello John,

If you just wish to quickly get access to a SQLite database file
then perhaps Ajqvue may help. I have had no problem with accessing
non-password protected files, such as places.sqlite that Firefox
uses. An encrypted database file though may not work with Aqjvue.

See the Quick Tutorial and use the default example database,
Site | LocalHost_SQLite | test/sqlite_db/factbook.db, to get
started.

http://ajqvue.com/
http://ajqvue.com/docs/Videos/Ajqvue_Quick.mp4

The command line tool as Simon indicated is much more powerful
and can then be explored to futher your goals.

danap.

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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Clemens Ladisch]

Simon Slavin wrote:
> You’re going to have to wait for the developer team to fix the bug,

or change the row IDs so that they are unique in the entire database.


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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Yuri]

On 03/05/2017 09:49, Simon Slavin wrote:

On 5 Mar 2017, at 4:28pm, Yuri  wrote:


So, to be clear, blob writes can only be used when no other statements are 
executed in the middle? Because the latest testcase only changes the other 
table, not the table where the blob is.

I’m glad to see that someone else has identified this as a bug.  Whether you’ll 
be able to do what you want when the bug is fixed I can’t tell.  You’re going 
to have to wait for the developer team to fix the bug, then test again.



Thanks for your help!

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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Simon Slavin]

On 5 Mar 2017, at 4:28pm, Yuri  wrote:

> So, to be clear, blob writes can only be used when no other statements are 
> executed in the middle? Because the latest testcase only changes the other 
> table, not the table where the blob is.

I’m glad to see that someone else has identified this as a bug.  Whether you’ll 
be able to do what you want when the bug is fixed I can’t tell.  You’re going 
to have to wait for the developer team to fix the bug, then test again.

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

Re: [sqlite] BUG: blob handles are invalidated in too many tables (was: Can sqlite3_blob_write be done through SQL?)

[Clemens Ladisch]

Yuri wrote:
> even updates to other tables invalidate the blob handle. Please see the 
> example below.

This is a bug.

Apparently, it was introduced in commit f17ef37897da9bca, where the
function invalidateIncrblobCursors() forgets to check the pgnoRoot
parameter, which means that blob cursors are invalidated by changes to
any row with the same rowid, even in some other table.  (Commit
257e9b575029f632 then removed that unused parameter without noticing
that it should have been used.)


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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Yuri]

On 03/05/2017 08:22, Simon Slavin wrote:

Yes, as we already established it’s because you changed the values of other 
columns in the row.  I think you’re using SQLite’s blob calls in an unexpected 
way.  They’re not meant to be used as an ongoing editor.  Adding the kind of 
support you’ve asked for would involve slowing down the routines for all the 
existing users.  Not really worth it.


So, to be clear, blob writes can only be used when no other statements 
are executed in the middle? Because the latest testcase only changes the 
other table, not the table where the blob is.



I think you’re better off reading the existing BLOB value into memory using 
SELECT, editing it using standard memory-manipulation routines, then writing it 
back with an UPDATE when you’re ready.



This is very slow.


Yuri

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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Simon Slavin]

On 5 Mar 2017, at 4:11pm, Yuri  wrote:

> On 03/05/2017 07:20, Simon Slavin wrote:
>> If you want to delay the ending of the transaction you have to put the BEGIN 
>> in before it, and then you must put the COMMIT in after it or it won’t be 
>> committed.
> 
> The second blob write fails even after adding BEGIN before sqlite3_blob_open 
> and COMMIT after sqlite3_blob_close.

Yes, as we already established it’s because you changed the values of other 
columns in the row.  I think you’re using SQLite’s blob calls in an unexpected 
way.  They’re not meant to be used as an ongoing editor.  Adding the kind of 
support you’ve asked for would involve slowing down the routines for all the 
existing users.  Not really worth it.

I think you’re better off reading the existing BLOB value into memory using 
SELECT, editing it using standard memory-manipulation routines, then writing it 
back with an UPDATE when you’re ready.

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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Yuri]

On 03/05/2017 07:20, Simon Slavin wrote:

If you want to delay the ending of the transaction you have to put the BEGIN in 
before it, and then you must put the COMMIT in after it or it won’t be 
committed.


The second blob write fails even after adding BEGIN before 
sqlite3_blob_open and COMMIT after sqlite3_blob_close.



Yuri

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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Simon Slavin]

On 5 Mar 2017, at 3:06pm, Yuri  wrote:

> On 03/05/2017 03:48, Keith Medcalf wrote:
>> Perhaps because you are committing the transaction?
> 
> There is no COMMIT statement there.

If you try to execute a command without putting it in a transaction (i.e. using 
BEGIN) then SQLite wraps it in a transaction of its own.  So a command like

UPDATE …

is processed as if it is

BEGIN
UPDATE …
COMMIT

If you want to delay the ending of the transaction you have to put the BEGIN in 
before it, and then you must put the COMMIT in after it or it won’t be 
committed.

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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Yuri]

On 03/05/2017 03:48, Keith Medcalf wrote:

Perhaps because you are committing the transaction?


There is no COMMIT statement there.


Yuri

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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Gerry Snyder]

Would it be possible for you to write the other columns just once, after
all the blob has been inserted?

On Mar 4, 2017 4:34 PM, "Yuri"  wrote:

> On 03/04/2017 01:22, Clemens Ladisch wrote:
>
>> Do you have a concrete example for such a statement?
>>
>
>
> Yes. I need to re-assemble the large data packet that comes in portions. I
> would like to use a blob field for this. I don't want to store parts in
> separate records, because this is essentially one file.
>
> So far sqlite is failing, because the handle returned by
> sqlite3_blob_write is invalidated by the updates to the other fields, and
> sqlite3_blob_open/sqlite3_blob_close are too slow to call them on every
> fragment.
>
> I would like to maintain the record like this:
>
> table file {
>
> id integer,
>
> num_done integer,
>
> num_total integer,
>
> data blob
>
> }
>
> File metadata and data all in one record. Blob handle gets invalidated
> when metadata is updated, and it's too expensive to open/close the blob
> every time.
>
> Due to these two problems sqlite can't support this reasonable, IMO, use
> case.
>
>
> Yuri
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Keith Medcalf]

Perhaps because you are committing the transaction?

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Yuri
> Sent: Sunday, 5 March, 2017 02:27
> To: SQLite mailing list
> Subject: Re: [sqlite] Can sqlite3_blob_write be done through SQL?
> 
> On 03/04/2017 23:08, Clemens Ladisch wrote:
> > I think you can work around these problems by moving the blob value into
> > a separate table.
> 
> 
> I thought so too. However, even updates to other tables invalidate the
> blob handle. Please see the example below.
> 
> Variable breakMe breaks the second sqlite3_blob_write call, even though
> the added update statement is for the other table.
> 
> 
> Yuri
> 
> 
> #include 
> #include 
> #include "sqlite3.h"
> 
> int breakMe = 1;
> 
> char *exec_errmsg;
> 
> void execSql(sqlite3 *db, const char *sql) {
>int rc;
>if (SQLITE_OK != (rc = sqlite3_exec(db, sql, NULL, NULL,
> _errmsg))) {
>  fprintf(stderr, "Error while executing sql=%s, error=%d\n", sql, rc);
>  exit(1);
>}
> }
> 
> int main(int argc, char **argv) {
>sqlite3 *db = NULL;
>int rc;
> 
>rc = sqlite3_open(argv[1], );
>if (SQLITE_OK != rc) {
>  fprintf(stderr, "Can't open database %s (%i): %s\n", argv[1], rc,
> sqlite3_errmsg(db));
>  exit(1);
>}
> 
>execSql(db, "CREATE TABLE fragmented_data ( friend_id INTEGER NOT
> NULL, frags_id  INTEGER NOT NULL, message   BLOB, PRIMARY KEY (
> friend_id, frags_id));");
>execSql(db, "CREATE TABLE fragmented_meta ( friend_id INTEGER NOT
> NULL, frags_idINTEGER NOT NULL, timestamp_first INTEGER NOT
> NULL, timestamp_last  INTEGER NOT NULL, frags_done  INTEGER NOT
> NULL, frags_num   INTEGER NOT NULL, message BLOB, PRIMARY
> KEY ( friend_id, frags_id));");
> 
>execSql(db, "INSERT INTO fragmented_data (friend_id, frags_id,
> message) SELECT 0, 1488703606591, zeroblob(2070) WHERE NOT EXISTS
> (SELECT 1 FROM fragmented_meta WHERE friend_id=0 AND
> frags_id=1488703606591);");
>execSql(db, "INSERT INTO fragmented_meta (friend_id, frags_id,
> timestamp_first, timestamp_last, frags_done, frags_num) SELECT 0,
> 1488703606591, 1488703647556, 1488703647556, 0, 2 WHERE NOT EXISTS
> (SELECT 1 FROM fragmented_meta WHERE friend_id=0 AND
> frags_id=1488703606591);");
> 
>sqlite3_blob *blob1;
>rc = sqlite3_blob_open(db, "main", "fragmented_data", "message", 1,
> 1, );
>if (SQLITE_OK != rc) {
>  fprintf(stderr, "Couldn't get blob handle (%i): %s\n", rc,
> sqlite3_errmsg(db));
>  exit(1);
>}
>if (SQLITE_OK != (rc = sqlite3_blob_write(blob1, "1", 1, 0x10))) {
>  fprintf(stderr, "Error writing to blob handle #1. rc=%d\n", rc);
>  exit(1);
>}
> 
>if (breakMe)
>  execSql(db, "UPDATE fragmented_meta SET
> timestamp_last=max(timestamp_last,1488703647556), frags_done =
> frags_done+1 WHERE friend_id=0 AND frags_id=1488703606591;");
> 
>if (SQLITE_OK != (rc = sqlite3_blob_write(blob1, "2", 1, 0x250))) {
>  fprintf(stderr, "Error writing to blob handle #2. rc=%d\n", rc);
>  exit(1);
>}
> 
>sqlite3_blob_close(blob1);
>printf("Successfully wrote to blob\n");
>sqlite3_close(db);
>return 0;
> }
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] confused getting started

[NTrewartha T-Online]

I too am new to DB's and Sqllite.
I have downloaded the binaries for win 10 and there is a dll and def file.
I clicked on the def file hoping this would integrate the dll into VS 
2015 community, but all what
happened was that the def file get listed. and I cannot seem to find an 
import def  tool.

Have to google that but *I would be grateful for any pointers*.

There is a GUI tool - which I think is better for learners which ought 
to be mentioned.

http://sqlitebrowser.org/ The "DBBrowser for sqllite".

Any examples of a C,C++,C# or Python usage for sqllite.?

I would like sqllite on my raspberry pi 3 after I have gained experience 
under Windows 10.


Judging what the replies to questions, the very new beginners are left a 
bit out in the cold.

Perhaps the documentation ought to cover the need of pure beginners.

Regard to you all,

NT


On 05.03.2017 10:54, a...@zator.com wrote:

Besides the need to include RDBMS engine inside your application, and manage it 
from the code. You have a standalone application (sqlite3.exe) who let manage 
your databese from the CLI (command line interpreter) of your system, and play 
whit the majority options who SQLite offer.

HTH.

--
Adolfo


 Mensaje original 
De: John Albertini 
Para:  sqlite-users@mailinglists.sqlite.org
Fecha:  Sat, 4 Mar 2017 19:10:26 -0500
Asunto:  [sqlite] confused getting started

I'm not a computer novice, but also not a nerd/geek.

Been using PCs since the mid 1980s and have used dBase III+ and Approach
previously.

I can't seem to grasp what I need to download / install to use SQLite?

Can someone guide me through the process?  Looking to use it with
RootsMagic.

Thank you.
John
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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



--

Nigel Trewartha
Sonnenweg 3
33397 Rietberg
Germany
Tel: 05244/3631 Fax: 05244/9063266
ntrewar...@t-online.de

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

Re: [sqlite] confused getting started

[ajm]

Besides the need to include RDBMS engine inside your application, and manage it 
from the code. You have a standalone application (sqlite3.exe) who let manage 
your databese from the CLI (command line interpreter) of your system, and play 
whit the majority options who SQLite offer.

HTH.

--
Adolfo

>
>  Mensaje original 
> De: John Albertini 
> Para:  sqlite-users@mailinglists.sqlite.org
> Fecha:  Sat, 4 Mar 2017 19:10:26 -0500
> Asunto:  [sqlite] confused getting started
>
> I'm not a computer novice, but also not a nerd/geek.

Been using PCs since the mid 1980s and have used dBase III+ and Approach
previously.

I can't seem to grasp what I need to download / install to use SQLite?

Can someone guide me through the process?  Looking to use it with
RootsMagic.

Thank you.
John
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Yuri]

On 03/04/2017 23:08, Clemens Ladisch wrote:

I think you can work around these problems by moving the blob value into
a separate table.



I thought so too. However, even updates to other tables invalidate the 
blob handle. Please see the example below.


Variable breakMe breaks the second sqlite3_blob_write call, even though 
the added update statement is for the other table.



Yuri


#include 
#include 
#include "sqlite3.h"

int breakMe = 1;

char *exec_errmsg;

void execSql(sqlite3 *db, const char *sql) {
  int rc;
  if (SQLITE_OK != (rc = sqlite3_exec(db, sql, NULL, NULL, 
_errmsg))) {

fprintf(stderr, "Error while executing sql=%s, error=%d\n", sql, rc);
exit(1);
  }
}

int main(int argc, char **argv) {
  sqlite3 *db = NULL;
  int rc;

  rc = sqlite3_open(argv[1], );
  if (SQLITE_OK != rc) {
fprintf(stderr, "Can't open database %s (%i): %s\n", argv[1], rc, 
sqlite3_errmsg(db));

exit(1);
  }

  execSql(db, "CREATE TABLE fragmented_data ( friend_id INTEGER NOT 
NULL, frags_id  INTEGER NOT NULL, message   BLOB, PRIMARY KEY ( 
friend_id, frags_id));");
  execSql(db, "CREATE TABLE fragmented_meta ( friend_id INTEGER NOT 
NULL, frags_idINTEGER NOT NULL, timestamp_first INTEGER NOT 
NULL, timestamp_last  INTEGER NOT NULL, frags_done  INTEGER NOT 
NULL, frags_num   INTEGER NOT NULL, message BLOB, PRIMARY 
KEY ( friend_id, frags_id));");


  execSql(db, "INSERT INTO fragmented_data (friend_id, frags_id, 
message) SELECT 0, 1488703606591, zeroblob(2070) WHERE NOT EXISTS 
(SELECT 1 FROM fragmented_meta WHERE friend_id=0 AND 
frags_id=1488703606591);");
  execSql(db, "INSERT INTO fragmented_meta (friend_id, frags_id, 
timestamp_first, timestamp_last, frags_done, frags_num) SELECT 0, 
1488703606591, 1488703647556, 1488703647556, 0, 2 WHERE NOT EXISTS 
(SELECT 1 FROM fragmented_meta WHERE friend_id=0 AND 
frags_id=1488703606591);");


  sqlite3_blob *blob1;
  rc = sqlite3_blob_open(db, "main", "fragmented_data", "message", 1, 
1, );

  if (SQLITE_OK != rc) {
fprintf(stderr, "Couldn't get blob handle (%i): %s\n", rc, 
sqlite3_errmsg(db));

exit(1);
  }
  if (SQLITE_OK != (rc = sqlite3_blob_write(blob1, "1", 1, 0x10))) {
fprintf(stderr, "Error writing to blob handle #1. rc=%d\n", rc);
exit(1);
  }

  if (breakMe)
execSql(db, "UPDATE fragmented_meta SET 
timestamp_last=max(timestamp_last,1488703647556), frags_done = 
frags_done+1 WHERE friend_id=0 AND frags_id=1488703606591;");


  if (SQLITE_OK != (rc = sqlite3_blob_write(blob1, "2", 1, 0x250))) {
fprintf(stderr, "Error writing to blob handle #2. rc=%d\n", rc);
exit(1);
  }

  sqlite3_blob_close(blob1);
  printf("Successfully wrote to blob\n");
  sqlite3_close(db);
  return 0;
}

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