On Tue, Aug 21, 2012 at 11:02:26AM +0200, Marco Maggi scratched on the wall:
> I am a True Beginner with SQLite and I am writing a
> binding to it for a programming language. While wrapping
> the functions accepting "sqlite3_value" arguments, I am not
> sure if I understand from where "sqlite3_bind_value()" (and
> "sqlite3_result_value()") should take their arguments.
> Is it actually possible and safe to execute a statement
> from the implementation of an app-defined SQL function?
Yes. This is a common thing to do.
> So SQL function arguments can be bound to statement parameters,
> and results from a statement can be used as return values of
> app-defined SQL functions?
Yes.
> Can I think of instances of "sqlite3_value" as shared data
> structures whose life is correctly handled by SQLite with
> some mechanism like reference counting?
Not exactly. The scope and lifetime of sqlite3_value objects are
usually predetermined by the context. For example, the parameter
values passed into an application defined function are only valid for
the lifetime of the function call. Inside that function call they
can be bound to a statement and used, but their lifetime ends when
the application defined function returns. You cannot, for example,
use an sqlite3_value to prepare a statement in one application
defined function and then execute the statement in another-- by the
time the second function is called, the sqlite3_value used in the
binding will not longer be valid. You could extract and save the
value held by the sqlite3_value structure, but you'd have to make
your own copy.
Also be aware that some instances of sqlite3_value are protected with
a mutex, and some are not. Specifics:
http://www.sqlite.org/c3ref/value.html
> If the answers are "yes":
>
> * Is there some simple and "known" example of such mechanism
> I can put in my test suite and documentation to show how
> it is done?
Anything you want. Use a function parameter for a "SELECT rowid FROM
table WHERE col = ? LIMIT 1" and have the function return the rowid.
Think of that as a really complex way to implement a VIEW.
> * If the nested statement execution fails for some reason:
> is there some convention about how the app-defined SQL
> function should signal the error to its caller?
Same way you indicate any error condition-- set a code and/or message
with one of the sqlite3_result_error*() functions.
> If the answers are "no": are "sqlite3_bind_value()" and
> "sqlite3_result_value()" there only for SQL function
> implementations embedded in the source code of SQLite?
> So is it better if I do not expose them at the foreign language
> level?
Ahh... that's an interesting question. I would not. Most language
wrappers-- especially for dynamically typed script languages--
convert the sqlite3_value objects into native types. So it would be
common for the function handler to "unwrap" the parameters before
calling the native language function. Handling all that within the
wrapper also eliminates the need to expose the sqlite3_bind_*(),
sqlite3_column_*(), sqlite3_result_*(), and sqlite3_value_*()
functions (which add up pretty quickly). This does mean if a
native-language function calls back into SQLite the value is
extracted from the sqlite3_value, passed into the native function,
passed back to prepare where the language native value is converted
back and bound. While there is some overhead in that, it also tends
to be clean and clear within the native language (which is usually
the most important thing).
For strongly typed languages, it might be better to expose the
sqlite3_value object as an opaque object and provide the standard
bind, column, result, and value APIs. You don't have a lot of choice
in that case. Just be aware there is no way to create an
sqlite3_value object from scratch. You can only get one from an
sqlite3_value_*() or sqlite3_column_*() call.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
