SQLite Forum

binding by reference instead of value

binding by reference instead of value

(1) By Rick Gumpertz (rgumpertz) on 2022-01-23 20:37:02 [link] [source]

When one is INSERTing or REPLACEing (or UPDATEing), one often does it in a loop.  For these situations, one currently has to call sqlite3_bind_XXX once for each parameter for each loop.  If the loop is of the form {READ record; multiple binds to values in RECORD; INSERT record; sqlite3_reset(pStmt)}, where the record is always in the same place, it seems that having an alternative form of bind that binds to its parameters by reference instead of value would be quite useful.  For example, sqlite3_bind_int(sqlite_stmt*pStmt, int index, int* pValue).  Such an interface would allow removal of the "multiple binds to values in RECORD" in the middle of the loop.

Note that there appears to be no reason that the current "by value" binds could not be intermixed with "by reference" binds within a statement. 

Of course the bind functions that take length parameters (text, blob, and zero) would have to receive references to the length variables so they could be updated inside the loop, if necessary.  If the length is contained within the RECORD or the length stays constant within the loop, it might not be necessary for the caller to explicitly update it within the loop, but in other cases the caller might have to put the length in a separate variable and update that variable within the loop.

(1) Does such an interface already exit?
(2) Would it be hard to add to SQLite3?
(3) Would it be worthwhile to add to SQLite?  (For example, might it end up slowing down execution of the loop?  I would hope not.)
(4) Is anybody interested in doing the work to add it?

(2) By Keith Medcalf (kmedcalf) on 2022-01-23 21:40:05 in reply to 1 [link] [source]

Do you mean sqlite3_bind_value? https://sqlite.org/c3ref/bind_blob.html

(3) By Larry Brasfield (larrybr) on 2022-01-23 22:52:20 in reply to 1 [link] [source]

When one is INSERTing or REPLACEing (or UPDATEing), one often does it in a loop.
For these situations, one currently has to call sqlite3_bind_XXX once for each parameter for each loop.

One also often does binding in code that is not directly part of such a loop. For example, the binding may be deferred a model/view adaptor of some kind which, when running, would have a different set of automatic variables than where the sqlite3_step() is being done. Or, an example from the SQLite shell: Binding is done in a subroutine that consults a DB table. Or the values being bound may come from a dynamic source or some sequence of sources (such as a tree walk or iteraction over a std::vector, where an unnecessary copy would have to be made so that those "by reference" bindings could keep working.)

To answer your numbered questions:
(1) No.
(2) No, but it would have very limited utility.
(3) I don't think so, but you could do it.
(4) Speaking only for myself, no.

To answer a question you might have asked: "Is there a good way to accomplish the same thing in an application using SQLite?"

This would be child's play with some templatized C++ and/or a vector of polymorphic binder objects. The pattern is "Set of do_binding objects which each know how to get the value to be bound." This "know how to get" is (and should be) much more general than "deref a pointer", IMO.

(4) By anonymous on 2022-01-23 23:24:53 in reply to 1 [link] [source]

Note that if you want to read data from outside of SQLite and insert them into a SQLite database, then one possibility would be that a virtual table might be used (if it is suitable for your usage, and I don't know that it is); then, the virtual table provides the data and you can use something like INSERT INTO ... SELECT * FROM ... to add the data into the database. (A virtual table can also accept constraints which can be used as inputs, if needed.)

(5) By Gunter Hick (gunter_hick) on 2022-01-24 08:21:13 in reply to 1 [link] [source]

What is wrong with INSERT INTO .. SELECT?

There is no need to emulate this in procedural code, SQLite does that very much faster than having the values detour through the application.

Even if your data source is outside SQLite, it is quite easy to write a virtual table to bring it in. If it is just an array, there is even an extension which does that painlessly.

It seems you are stuck in procedural thinking, which is causing you to think of complicated ways to do "simple" stuff.

(6) By Stephan (stephancb) on 2022-01-24 10:27:40 in reply to 1 [source]

A binding by reference could perhaps be useful for some kind of realtime application, where the data are always fetched from the same address, but do of course change with time. Then, when it is time to insert a row with updated realtime data, the application just calls sqlite3_step. This would simplify the application code.

(7) By ddevienne on 2022-01-24 10:39:37 in reply to 6 [link] [source]

That's already how it works. If you don't rebind something else, the binds persist.

(8) By Ryan Smith (cuz) on 2022-01-24 11:19:38 in reply to 7 [link] [source]

I think what the OP (and subsequent posters) are talking about is a binding where only the reference persists, but the value may change.

i.e. if they do some pseudo-code like this:

  int myValue = 5;
  sqlite3_bind_integer(statement, 1, pointerTo(myValue));
  sqlite_step(); // At this point the SQL executes with the bound value = 5
  myValue = 7;
  sqlite_step(); // Here the same executes without rebinding but now the bound value = 7

This is a fool's errand though and I cannot see how this will ever work well in SQLite3. To name but a few initial concerns:

  • SQLite now has to keep an array of bound pointer references in memory, over and above the memory allocated for the physical values.
  • What if some parameters are bound by reference, but some by value, should re-binding "some" values work?
  • What if the referenced memory changes right inside SQLite's routines, who takes responsibility for that memory being accessible and not altering it ever? SQLite's internal multi-thread semaphores can never control for this.
  • What do we really gain? No real efficiency is saved, no real cpu cycles, but perhaps some programmer typing can be saved.
  • Adding a lot of new code to achieve something that is inherently simple to do currently, will require added testing and this seems like a great target for trouble and new vulnerabilities.

Add to that the fact that it is so easy to emulate yourself in your own code. Just make a bind function that has all your references, binds it and step, so you can easily have this pseudo code in your main:

  function int bind_and_step() 
      for n in bindProperty[]
          sqlite3_bind_int(statement, n, int(bindProperty[n]));
      return sqlite3_step();


  int myValue = 5;
  bindProperty[0] = pointerTo(myValue);
  myValue = 7;
  etc. etc.

Here the programmer's bind_and_step() procedure and changing of any of the bound values take full responsibility for the memory so assigned/dereferenced, neatly putting the legion of possible problems squarely into the domain of the programmer's code and away from SQLite.

What is more, if the function also took care of return values and errors, the resulting main section can have even less typing than if SQLite was doing this internally.

(10) By Stephan (stephancb) on 2022-01-24 12:45:00 in reply to 8 [link] [source]

Exactly, this I had in mind, persistent reference of course.

If the reference is bound, but not the value, then SQLite does not need to have memory allocated for the physical value (which could for example be a large image to be inserted as a BLOB)? When sqlite3_step is executed, SQLITE3 has of course to read off the physical value and do whatever is required to get it safely into the database. But wouldn't this save an extra copying of the data, that SQLite does when the value (instead of the reference) is bound?

Of course it would the applications responsibility to ensure that the values don't change until sqlite3_step is has returned.

(12) By Ryan Smith (cuz) on 2022-01-24 13:27:55 in reply to 10 [link] [source]

Well that already works like that for blobs, they do not bind like the rest, you have a blob writer and reader function which directly writes those bytes where they should go (i.e. no internal memory cache exists for it like other bound values, as far as I can tell).

Your argument can still work for, say, a very large TEXT value bound with one of the text binding functions, but that is the tiniest percentage of niche binds that happen, still if that is ALL your application does, I can see the implied need, but that will only help memory usage a bit (which isn't really constricted on most systems) and the actual writing I/O bottleneck will be umpteen times more responsible for any delays than moving the memory. No real gain here.

... it would [be] the applications responsibility to ensure that the values don't change until...

That is an easy statement to make, but a much less easy rule to adhere/enforce/test for in reality, especially with the gain being insignificant.

(13) By Larry Brasfield (larrybr) on 2022-01-24 13:48:38 in reply to 12 [link] [source]

... it would [be] the applications responsibility to ensure that the values don't change until...

That is an easy statement to make, but a much less easy rule to adhere/enforce/test for in reality, especially with the gain being insignificant.

My initial thought when I saw this thread appear was much like that, and reminded me of a bit of prescience I had when multi-threading was first becoming a thing and microprocessors were getting more than one "core". I figured it would be a potent source of bugs because it required discipline not evident in a significant fraction of programmers I knew.

(9) By Richard Hipp (drh) on 2022-01-24 11:30:07 in reply to 1 [link] [source]

I suspect that the bytecode would be upset if values changed out from under it while it was running, which might happen if parameters were bound by reference.

(11) By TripeHound on 2022-01-24 13:08:45 in reply to 9 [link] [source]

For string/blob fields at least, would passing SQLITE_STATIC achieve what the OP wants? I.e. would something like:

strcpy( mybuffer, "fred" ) ;
sqlite3_bind_text( stmt, 1, mybuffer, -1, SQLITE_STATIC ) ;
sqlite3_step() ;
strcpy( mybuffer, "bill" ) ;
sqlite3_step() ;

work? I guess, in part, it depends on whether SQLITE_STATIC will always re-read from the address that was passed in the bind() call, or whether it might have "cached" the original value. This section of Binding Values To Prepared Statements:

SQLITE_STATIC, may be passsed to indicate that the application remains responsible for disposing of the object. In this case, the object and the provided pointer to it must remain valid until either the prepared statement is finalized or the same SQL parameter is bound to something else, whichever occurs sooner. (3) The constant, SQLITE_TRANSIENT, may be passed to indicate that the object is to be copied prior to the return from sqlite3_bind_*(). The object and pointer to it must remain valid until then. SQLite will then manage the lifetime of its private copy.

says the data will be copied when using SQLITE_TRANSIENT, and by implication that it won't be copied when using SQLITE_STATIC, but doesn't explicitly state that changing the data between step() calls would be safe.

Also noted in passing: "may be passsed" has one too many "s"s.