Igor Tandetnik wrote:
"Vishal Kashyap" wrote
Is their any way we can write simple stored procedures or functions
in sqlite. If yes please do guide me I need this functionality in one
of my open source project.
Not in the usual sense, meaning some language that gets stored in the
database itself together with the data. The only thing that comes
somewhat close is a trigger. It is possible to create a poor man's
stored procedure like this:
create table sp_dosomething (param1 int, param2 char);
create trigger sp_dosomething_impl
instead of insert on sp_dosomething
begin
-- one or more sql statements possibly referring to
-- new.param1 and new.param2
end;
-- To invoke:
insert into sp_dosomething values(1, 'hello');
Note that triggers are rather limited in what they can do. They are just
a bunch of SQL statements, there is no control flow (loops, if then
else, goto) beyond what little you can implement in pure SQL. They
cannot return values, except indirectly by inserting or updating some
table. SQLite does not support cascading triggers, so if your "stored
procedure" manipulates some table to which regular triggers are attached
(perhaps ensuring data integrity), those triggers won't run.
SQLite supports custom functions - see sqlite3_create_function[16]. You
write them in C (or any other language that has bindings to SQLite API)
and you have to install them every time you open a DB handle with
sqlite3_open, before you can refer to them in your SQL statements. They
are not stored in the database file itself.
Finally, SQLite prepared statements (sqlite_prepare) can be thought of
as simple stored procedures defined in your program. Similar to custom
functions, you can prepare a statement right after opening the database,
then keep it around.
Igor Tandetnik
Maybe it would be fine and useful to add lua scripting language to
sqlite (maybe in shared library to not broke current code )
Regards
Boguslaw Brandys
