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

[Clemens Ladisch]

Yuri wrote:
> commit of the subsequent unrelated transaction fails with error=5
> (SQLITE_BUSY) if the previous open sqlite3_blob object hasn't been
> closed.
>
> This fails:
>
> sqlite3_blob_open // table b with rowids unique in db
> sqlite3_blob_write // table b
> BEGIN
> UPDATE // table a
> UPDATE // table a
> COMMIT // FAILS SQLITE_BUSY!

Please note that a sqlite3_blob_write() call does not behave like
a complete SQL statement; it does not auto-commit what it has written.
I don't know why the BEGIN doesn't complain that there already is an
active transaction, but there is no unrelated transaction.

The sqlite3_blob_close() documentation implies that you should do the
open/write/close calls inside a transaction, or that all three together
comprise an automatic transaction.


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?

[Simon Slavin]

On 8 Mar 2017, at 10:10pm, Yuri  wrote:

> This fails:
> 
> sqlite3_blob_open // table b with rowids unique in db
> 
> sqlite3_blob_write // table b
> 
> BEGIN
> 
> UPDATE // table a
> 
> UPDATE // table a
> 
> COMMIT // FAILS SQLITE_BUSY!

That would appear to be the bug we identified earlier.  You’re going to have to 
wait for it to be fixed before figuring out the consequences.

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 10:19, Clemens Ladisch wrote:

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



Thanks for your suggestion. I followed it, and made rowids unique in the 
table b with a blob field.


But I've ran into another problem: commit of the subsequent unrelated 
transaction fails with error=5 (SQLITE_BUSY) if the previous open 
sqlite3_blob object hasn't been closed.



This fails:

sqlite3_blob_open // table b with rowids unique in db

sqlite3_blob_write // table b

BEGIN

UPDATE // table a

UPDATE // table a

COMMIT // FAILS SQLITE_BUSY!


This succeeds:

sqlite3_blob_open // table b

sqlite3_blob_write // table b

sqlite3_blob_close // table b

BEGIN

UPDATE // table a

UPDATE // table a


COMMIT // SUCCEEDS


Unique rowids were supposed to make blobs independent of updates in 
other tables, but it appears that there is still some dependency.


I am not sure if this is the same bug, or another bug.


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?

[James K. Lowden]

On Sun, 5 Mar 2017 08:28:44 -0800
Yuri  wrote:

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

Can you quantify that?  How big is the blob? How big is the edit?  How
many edits?  How fast are N edits to the blob?  How fast to read &
write the whole blob?  How much RAM available to the process?  

My design question is, why a blob in the first place?  If you are
making frequent incremental changes to a blob, that suggests the blob
has some structure.  If you decomposed the blob into rows, perhaps the
edit-the-blob problem would go away, and speed/complexity issues with
it.  

--jkl
___
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] 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] 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] 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

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

[Clemens Ladisch]

Yuri wrote:
> So far sqlite is failing, because the handle returned by
> sqlite3_blob_write is invalidated by the updates to the other fields,

This would happen regardless of whether you call sqlite3_blob_write()
yourself or through an SQL function.

> and sqlite3_blob_open/sqlite3_blob_close are too slow to call them on
> every fragment.

This would happen regardless of whether you call them yourself or
through an SQL function.


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


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/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

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

[Jens Alfke]

> On Mar 4, 2017, at 8:50 AM, J Decker  wrote:
> 
> All blobs I've used in SQL are themselves atomic and partial updates would
> never be useful.  (images, fingerprints, etc.)  If you're updating parts of
> blobs then probably you mean that blob to be in multiple parts or broken
> into separate fields.


I think you’re over-generalizing from your own experience.
One might be downloading a blob, and receiving the content-length up front but 
the data in chunks.
One might be storing a large mutable set of integers as a bitmap.

(I’m not thinking specifically of updating a blob via SQL, just of use cases 
for partial updates of blobs.)

A fun but impractical use case would be to implement a VFS that lets you store 
a SQLite database within a blob in another SQLite database. SQLinception!

—Jens
___
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?

[J Decker]

On Fri, Mar 3, 2017 at 3:58 PM, Yuri  wrote:

> On 03/01/2017 23:41, Clemens Ladisch wrote:
>
> It would certainly be possible to add your own user-defined SQL function
>> to call sqlite3_blob_write().
>>
>
>
> I think this should be added to sqlite itself. Writing a portion of blob
> is very much like updating a field. There is currently no way to do this in
> SQL fashion in an efficient way. So such command can be chained with other
> SQL statements and make the client code much simpler.
>
>
All blobs I've used in SQL are themselves atomic and partial updates would
never be useful.  (images, fingerprints, etc.)  If you're updating parts of
blobs then probably you mean that blob to be in multiple parts or broken
into separate fields.


> 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?

[Clemens Ladisch]

Yuri wrote:
> On 03/01/2017 23:41, Clemens Ladisch wrote:
>> It would certainly be possible to add your own user-defined SQL function
>> to call sqlite3_blob_write().
>
> I think this should be added to sqlite itself.

So far, nobody except you has requested it.

> Writing a portion of blob is very much like updating a field. There is
> currently no way to do this in SQL fashion in an efficient way.

Neither is there a way to update a text field efficiently; for both types,
you have to handle the pieces manually:

  UPDATE MyTable
  SET Field = substr(Field, 1, 10) || 'xxx' || substr(Field, 14);

And handling blob values does not make much sense in a pure SQL script;
binary data is usually handled in the application from which the SQLite
library is accessed.

> So such command can be chained with other SQL statements and make the
> client code much simpler.

Do you have a concrete example for such a statement?


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/01/2017 23:41, Clemens Ladisch wrote:


It would certainly be possible to add your own user-defined SQL function
to call sqlite3_blob_write().



I think this should be added to sqlite itself. Writing a portion of blob 
is very much like updating a field. There is currently no way to do this 
in SQL fashion in an efficient way. So such command can be chained with 
other SQL statements and make the client code much simpler.



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?

[Clemens Ladisch]

Yuri wrote:
> I have to call sqlite3_blob_write to partially modify blobs.
>
> Wouldn't it have been better if there was a function, for example,
> writeblob(tbl.blob_fld, offset, portion_data)? SQLite could do the
> same that sqlite3_blob_write is doing when writeblob result is
> assigned to the same row/column that is supplied as the argument, and
> rewrite the whole blob otherwise?

The sqlite3_blob* interface is just an optimization, and it looks as if
it was included for some specific application.

It would certainly be possible to add your own user-defined SQL function
to call sqlite3_blob_write().

> Also, do sqlite3_blob_write calls participate in transactions?

Of course they do.


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