Stored Procedures

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Stored Procedures

Omprakash Kolluri
Hi,

I am new to SQLite. I am working on an app that I am developing and plan to
use SQLite as an embedded database. My Question - Does SQLite support
stored procedures similar to those in MS SQL Server etc. Any suggestions OR
pointers to information links woill be greatly appreciated. Thank you

Om Kolluri
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Stored Procedures

Stephen Chrzanowski
Views, yes.  Stored Procedures, no.

On Thu, Oct 9, 2014 at 1:04 PM, Omprakash Kolluri <[hidden email]>
wrote:

> Hi,
>
> I am new to SQLite. I am working on an app that I am developing and plan to
> use SQLite as an embedded database. My Question - Does SQLite support
> stored procedures similar to those in MS SQL Server etc. Any suggestions OR
> pointers to information links woill be greatly appreciated. Thank you
>
> Om Kolluri
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Stored Procedures

R Smith
In reply to this post by Omprakash Kolluri

On 2014/10/09 19:04, Omprakash Kolluri wrote:
> Hi,
>
> I am new to SQLite. I am working on an app that I am developing and plan to
> use SQLite as an embedded database. My Question - Does SQLite support
> stored procedures similar to those in MS SQL Server etc. Any suggestions OR
> pointers to information links woill be greatly appreciated. Thank you

Hi Om,

Not directly since it is at the API level very integrated and integratable with any C or other common compiler languages, so doing
anything procedural is usually an easy task, and as the name implies it is "Lite" which means some
higher-CPU-Cycle-and-Memory-consumption additions are foregone specifically so it could work well on embedded systems, as you seem
to be implementing.

A good read in this regard would be here:
http://www.sqlite.org/whentouse.html

Check out the recent CTE additions which does allow a level of procedural querying here:
http://www.sqlite.org/lang_with.html#rcex2

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Stored Procedures

J Decker
they can be implemented through registered extensions..(well no probably
not how you're thinking)..

I have a few functions to mimic MySQL functions like now(), curdate() ....
pretty simple
learned you can sqlite3_create_module()  which is a virtual recordset...
but apparently can't add syntax like 'EXEC"  ... but could make them be
like "select * from (stored_proc)" as an alias for "exec (stored proc)"
 but it wouldn't be SQL defining the procedure.... well it could be, but it
would be a C routine that then issued the sql statements (maybe make
loadable modules like shaders?)

For lack of portability I haven't used them; what's a good use case for
stored procedures?

On Thu, Oct 9, 2014 at 10:12 AM, RSmith <[hidden email]> wrote:

>
> On 2014/10/09 19:04, Omprakash Kolluri wrote:
>
>> Hi,
>>
>> I am new to SQLite. I am working on an app that I am developing and plan
>> to
>> use SQLite as an embedded database. My Question - Does SQLite support
>> stored procedures similar to those in MS SQL Server etc. Any suggestions
>> OR
>> pointers to information links woill be greatly appreciated. Thank you
>>
>
> Hi Om,
>
> Not directly since it is at the API level very integrated and integratable
> with any C or other common compiler languages, so doing anything procedural
> is usually an easy task, and as the name implies it is "Lite" which means
> some higher-CPU-Cycle-and-Memory-consumption additions are foregone
> specifically so it could work well on embedded systems, as you seem to be
> implementing.
>
> A good read in this regard would be here:
> http://www.sqlite.org/whentouse.html
>
> Check out the recent CTE additions which does allow a level of procedural
> querying here:
> http://www.sqlite.org/lang_with.html#rcex2
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Stored Procedures

nomad
On Thu Oct 09, 2014 at 11:29:49AM -0700, J Decker wrote:
> they can be implemented through registered extensions..(well no probably
> not how you're thinking)..
>
> but apparently can't add syntax like 'EXEC"  ... but could make them be
> like "select * from (stored_proc)" as an alias for "exec (stored proc)"

I use BEFORE INSERT triggers that end with a SELECT RAISE(IGNORE). That
way I can use the following syntax to perform multiple operations
within a single statement:

    INSERT INTO
        my_function_name(
            arg1,
            arg2,
            arg3
        )
    VALUES (
        val1,
        val2,
        val3
    )

Obviously you can't get a result from that, but it would of course be
possible to let the insert succeed or to insert a "result row"
somewhere. Unfortunately CTEs don't work inside triggers so complicated
logic is somewhat limited and/or must be spread across multiple
triggers.

> For lack of portability I haven't used them; what's a good use case
> for stored procedures?

I generally use them for storing entities that must be entered in many
tables, where it nicely presents an API to the caller that closely maps
to the single action they want to take. This provides:

    Simplicity: a single statement can replace many individual
    statements, putting more of your application inside the database
    and can often remove the need for an explicit transaction.

    Efficiency: for non-C languages there is less translation between
    the language/SQLite boundary.

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Stored Procedures

big stone
In reply to this post by Omprakash Kolluri
Hi,

Here is an example of stored procedure made in Python for SQLite.

https://pypi.python.org/pypi/sqlite_bro/0.8.7.4

I Hope it will help you figure out quickly  if SQLite is ok enough for your
use-case.

Sheers,
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Stored Procedures

Omprakash Kolluri
Thanks ALL. This has been very helpful

Om Kolluri

On Thu, Oct 9, 2014 at 1:19 PM, big stone <[hidden email]> wrote:

> Hi,
>
> Here is an example of stored procedure made in Python for SQLite.
>
> https://pypi.python.org/pypi/sqlite_bro/0.8.7.4
>
> I Hope it will help you figure out quickly  if SQLite is ok enough for your
> use-case.
>
> Sheers,
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Stored Procedures

Nico Williams
In reply to this post by Omprakash Kolluri
I use triggers, particularly INSTEAD OF INSERT triggers on VIEWs.  The
values of the columns of the rows to be "inserted" are the "stored
procedure's" arguments.  I use WHERE clauses judiciously to make up
for the lack of IFs.  It works well enough.

I've used this in combination with recursive triggers to implement
recursive CTEs before SQLite3 added support for them.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Stored Procedures

nomad
On Thu Oct 09, 2014 at 05:38:57PM -0500, Nico Williams wrote:
> I use triggers, particularly INSTEAD OF INSERT triggers on VIEWs.
> The values of the columns of the rows to be "inserted" are the
> "stored procedure's" arguments.

I would like to able to do this too, but INSTEAD OF INSERT on a view
does not support default values for arguments the same way that BEFORE
INSERT on a regular table does.

What would really be nice is if one could run the following inside a
BEFORE or INSTEAD OF trigger:

    UPDATE
        NEW
    SET
        NEW.name = COALESCE(NEW.name, new_value)
    ;

Could the SQLite team perhaps comment on how difficult this would be to
implement?

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Stored Procedures

Nico Williams
You can't change the NEW "row" in trigger bodies.

Since you can't make "SELECT"s (or virtual tables) this way, all your
"stored procedure" can do is INSERT/UPDATE/DELETE anyways.  Using
coalesce(NEW.foo, "default value") works fine (and it's how you'd
default "SP arguments").

Nico
--
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users