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 tools). 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, Alek (*) 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@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
