I missed stored procedures till i coded my workaround. I had a table named 
sprocs with the sql statements coded into a text blob in each row with each row 
representing a different sproc. A linked table named params had a column with a 
tag representing a parameter for the associated sproc.
Pull the sproc text and the list of the parameters pass that to a prep routine 
that replaces the tag text with the relevant values and then execute the sql.
Works like a champ.

Sent from my T-Mobile 4G LTE Device
-------- Original message --------From: Alek Paunov <a...@declera.com> Date: 
5/14/18  8:44 PM  (GMT-08:00) To: SQLite mailing list 
<sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Stored Procedures 
On 2018-05-09 03:56, Richard Hipp wrote:


> The other benefit of stored procedures is that it provides a way to
> code up a common operation once (correctly!) and store it in the
> database, rather than having multiple clients all have to work out the
> operating themselves (some of them perhaps incorrectly).  The usual
> way of handling that in SQLite is to store a script in a text column
> someplace, then execute them as needed.  SQLite began life as a TCL
> extension, and so naturally TCL scripts work very well for this kind
> of thing.

To amplify the remark above, for me, the Stored procedures are mostly 
method to add methods :-) to the "cold" data objects encoded in tables, 
just like the OO style SQLite C API defines interfaces and behavior on 
top of "cold" C structures.

That way, schemes and non-trivial calculations on top of them can be 
encapsulated and reused as modules (or packages as named in some design 

Practical example of the benefit is that e.g. same complex turnover 
report which implementation includes bunch of intermediate calculations, 
could be used with same SQL call from Python desktop application, 
directly as Excel Data Source or on partial replica through SQL.js [1] 
in the browser, without coding the same thing 3 times: on Python, VBA 
and JS.

Having said that, Let's take the survey: How many list readers really 
missed the Stored procedures feature?

If there are enough interest, I would be happy to discuss an (possibly 
naive) idea (*) for implementation (of simple e.g. Sybase 12.5 level 
procedures) mostly in pure SQL with just tiny C runner as UDF TValued 
Function [2].

Kind Regards,

(*) On the sqlite mailing list, we see permanent conflict of interests: 
On the one side are 99% of the users, for which sqlite is just better 
(than e.g. compressed json) storage option (usually for small as size or 
simple as structure data).

On the other, are the advanced users - these with complex applications 
who [especially after the great advancements from the last years (CTEs, 
JSON and so on)] try to encapsulate as much as possible business logic 
into the DB.

My humble opinion is that for us, the minority, the best move is to look 
for approaches and collaboration on "incubating" valuable advanced 
features initially outside of the SQLite core, then eventually apply 
result solutions in our own projects and finally propose for inclusion 
in the core library only small key components (which lead to 
inefficiencies or maintenance burden when living outside).

For the Stored procedures, borrowing from the SQLite design, I think 
that it is possible the non SQL, procedural part of the code to be 
translated to very high level VM with just few instructions, which uses 
attached :memory: DB for stack/registers/variables and (tree) table for 
the code. [This can be seen as step further above the approach cited by 
Dr. Hipp SQLs kept in table and used by executed by TCL script.]

That way, at some stage, SQLite could even have several procedural 
dialect implementations - TSQL, MySQL, etc. :-)

[1] https://github.com/kripken/sql.js/
[2] https://www.sqlite.org/vtab.html#tabfunc2
sqlite-users mailing list
sqlite-users mailing list

Reply via email to