On Sun, 16 Apr 2017 12:01:01 +0200
Darko Volaric <[email protected]> wrote:
> There are good reasons to have stored procedures other than reducing
> connection latency - developers like to encapsulate logic that is
> associated entirely with the database in the database, use them to do
> extended checking, to populate denormalized or derived data, or to
> provide a level of abstraction, for instance.
Exactly so. A stored procedure can serve the same purpose as a
function in C: to assign a name to a particular body of code.
But the same effect can be had in SQLite without stored procedures per
se. In a few projects I've used the build repository to accomplish
much the same thing.
Choose a directory, say, "sql" for the queries that will be used in
the application. Each file has a name and contains one query. In that
way, every query has a name. A bit of awk transforms that directory
into a C source code module with a contant array of strings. The
filenames become an enumeration that serves to index the array by name.
(A C++ std::map also works.) Calling the "stored procedure" is a simple
matter:
sqlite3_prepare(db, sql[name], ...)
One nice feature of this approach is that testing queries is simple.
It also confines all the SQL to one module, and avoids writing queries
"in line" as C strings. And, not for nothing, a well chosen query
name renders the code clearer than embedded SQL does.
--jkl
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
