Re: [sqlite] Stored Procedures

On May 14, 2018, at 9:44 PM, Alek Paunov wrote: > > 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

Re: [sqlite] Stored Procedures

) To: SQLite mailing list 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 hav

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 in

Re: [sqlite] Stored Procedures

On 5/14/18, Warren Young wrote: > > Your benchmark doesn’t address the primary problems pointed out in the > Mozilla article: The point of my article is that many people assume *without measuring* that reading and writing directly to the filesystem will be faster than using a database. That assu

Re: [sqlite] Stored Procedures

On May 14, 2018, at 1:19 PM, Richard Hipp wrote: > > On 5/14/18, Warren Young wrote: >> >> https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature > > That's an older article. These days, it is generally faster to use > SQLite than fopen(). See, for example, > https://

Re: [sqlite] Stored Procedures

On 5/14/18, Warren Young wrote: > > https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature > That's an older article. These days, it is generally faster to use SQLite than fopen(). See, for example, https://www.sqlite.org/fasterthanfs.html -- D. Richard Hipp d...@sqlit

Re: [sqlite] Stored Procedures

On May 13, 2018, at 10:15 PM, Rowan Worth wrote: > > ...I wouldn't call the extra i/o imposed by sqlite "very very > cheap" either - it doubles writes (once to the rollback journal, once to > the DB), forces syncs, and likely results in a more seek heavy i/o pattern > (this depends a bit on schem

Re: [sqlite] Stored Procedures

> That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very > cheap" either - it doubles writes (once to the rollback journal, once to > the DB), forces syncs, and likely results in a more seek heavy i/o pattern > (this depends a bit on schema design and whether the app requires/f

Re: [sqlite] Stored Procedures

On 14 May 2018 at 01:08, Richard Damon wrote: > On 5/13/18 12:55 PM, Rowan Worth wrote: > > On 9 May 2018 at 08:56, Richard Hipp wrote: > > > >> But with > >> SQLite, there is no round-trip latency. A "round-trip" to and > >> database is just a function call, and is very very cheap. > >> > >

Re: [sqlite] Stored Procedures

On 5/13/18 12:55 PM, Rowan Worth wrote: > On 9 May 2018 at 08:56, Richard Hipp wrote: > >> But with >> SQLite, there is no round-trip latency. A "round-trip" to and >> database is just a function call, and is very very cheap. >> > I want to emphasise that Dr. Hipp's usage of "round-trip" only i

Re: [sqlite] Stored Procedures

On 9 May 2018 at 08:56, Richard Hipp wrote: > But with > SQLite, there is no round-trip latency. A "round-trip" to and > database is just a function call, and is very very cheap. > I want to emphasise that Dr. Hipp's usage of "round-trip" only includes the latency of _communication_ between t

Re: [sqlite] Stored Procedures

Very useful comments in this thread. I recommend adding this to the SQLite FAQ, if it exists. -- Craig H Maynard Rhode Island, USA 401-413-2376 > Date: Tue, 8 May 2018 20:56:45 -0400 > From: Richard Hipp > To: SQLite mailing list > Subject: Re: [sqlite] Stored Procedures

Re: [sqlite] Stored Procedures

> The usual way of handling that in SQLite is to store a script in a text > column someplace, then execute them as needed. Is there a simple way to do this from SQLite shell? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://maili

Re: [sqlite] Stored Procedures

On 5/8/18, Jens Alfke wrote: > > >> On May 8, 2018, at 1:02 PM, Mike Clark wrote: >> >> Has there been any thought of implementing some kind of stored procedure >> feature for SQLite? > > That's more of a server thing. In an embedded database, a stored procedure > is literally a function in your

Re: [sqlite] Stored Procedures

> On May 8, 2018, at 1:02 PM, Mike Clark wrote: > > Has there been any thought of implementing some kind of stored procedure > feature for SQLite? That's more of a server thing. In an embedded database, a stored procedure is literally a function in your programming language, which runs a SQLi

Re: [sqlite] Stored Procedures

maybe Virtual tables? can register functions of various types... mostly since the typical usage of sqlite is as a tightly coupled library, a function in your application is a 'stored procedure'. http://www.sqlite.org/c3ref/update_hook.html There are hooks which would trigger callbacks like trigge

Re: [sqlite] Stored Procedures

On 5/8/2018 4:02 PM, Mike Clark wrote: Has there been any thought of implementing some kind of stored procedure feature for SQLite? Or does this feature exist in some other form, with another name? Triggers are kind of like stored procedures. You can create a dedicated view and put INSTEAD OF

[sqlite] Stored Procedures

Has there been any thought of implementing some kind of stored procedure feature for SQLite? Or does this feature exist in some other form, with another name? -- Mike Clark Twitter: @Cyberherbalist Blog: Cyberherbalist's Blog - "Free will, though it makes e

Re: [sqlite] Stored Procedures

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 --

Re: [sqlite] Stored Procedures

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

Re: [sqlite] Stored Procedures

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 tri

Re: [sqlite] Stored Procedures

Thanks ALL. This has been very helpful Om Kolluri On Thu, Oct 9, 2014 at 1:19 PM, big stone 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 enou

Re: [sqlite] Stored Procedures

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 sqlite-us

Re: [sqlite] Stored Procedures

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 (s

Re: [sqlite] Stored Procedures

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

Re: [sqlite] Stored Procedures

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

Re: [sqlite] Stored Procedures

Views, yes. Stored Procedures, no. On Thu, Oct 9, 2014 at 1:04 PM, 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 SQ

[sqlite] Stored Procedures

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 O

Re: [sqlite] stored procedures implementation for SQLite

On Wednesday, January 26, 2011 5:38 PM, Chris Wolf wrote: > but if anyone is interested, I checked in my work on GitHub, including > pre-compiled > binaries for MacOS and Linux. > > http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended > Yes, very interesti

Re: [sqlite] stored procedures implementation for SQLite

Thanks for bringing that to my attention - that sample was left over from when I was trying to use APSW rather then sqlite2. The actual test program, sqlite-3.7.3/src/createproc_test.c, is correct. I updated the blog page to reflect the proper code. -Chris On Jan 26, 2011, at 12:55 PM, Jim W

Re: [sqlite] stored procedures implementation for SQLite

It looks interesting. Should your except stmt reference apsw? -Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com On Wed, Jan 26, 2011 at 11:38 AM, Chris Wolf wrote: > > > I know this is an old thread, but shortly after I read it, I attempted to > implement > sto

Re: [sqlite] stored procedures implementation for SQLite

I know this is an old thread, but shortly after I read it, I attempted to implement stored procedures in SQLite! I only did it to see if I could, not because I necessarily think it's a good idea... It's very experimental and not fully implemented, but if anyone is interested, I checked in my

Re: [sqlite] Stored procedures

"BareFeetWare" schrieb > On 13/11/2010, at 10:33 AM, Olaf Schmidt wrote: > >> If you have code (in either environment) that is > >> looping or grabbing one result and sticking it in a > >> second query, then your approach is probably flawed. > > > > As you say: "probably" ... because "it depend

Re: [sqlite] Stored procedures

On 13/11/2010, at 10:33 AM, Olaf Schmidt wrote: From: "Olaf Schmidt" Wednesday, November 10, 2010 9:07:19 AM >>> There was a somewhat similar sounding post (from BareFeetWare, >>> sent on 20.Oct to this list) who also encouraged, to include >>> "more logic" into the SQLite-Files it

Re: [sqlite] Stored procedures (was: Question about SQLite features.)

On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare wrote: > IMO, if you're implementing database logic (ie constraints and triggers) in > application code, then you're reinventing the wheel, making your package > unnecessarily complex and grossly inefficient. If you're just using SQLite > to store your

Re: [sqlite] Stored procedures (was: Question about SQLite features.)

"BareFeetWare" schrieb > On 12/11/2010, at 6:30 AM, Olaf Schmidt wrote: > > > "jeff archer" schrieb > >> From: "Olaf Schmidt" > >> Wednesday, November 10, 2010 9:07:19 AM > >> > >>> [Stored procedures in SQLite] > >>> > >>> IMO stored procedure-support only makes > >>> sense in "Server-Instances

[sqlite] Stored procedures (was: Question about SQLite features.)

On 12/11/2010, at 6:30 AM, Olaf Schmidt wrote: > "jeff archer" schrieb >> From: "Olaf Schmidt" >> Wednesday, November 10, 2010 9:07:19 AM >> >>> [Stored procedures in SQLite] >>> >>> IMO stored procedure-support only makes sense in "Server-Instances" which >>> run on their own... > >> I disag

Re: [sqlite] "Stored procedures" performance issue

Darren Duncan wrote: > Igor Tandetnik wrote: >> Kristoffer Danielsson >> wrote: >>> When I create my own "stored procedures" using >>> sqlite3_create_function, I get horrible performance (which I >>> expected) even though the column of interest is INDEXED. >>> >>> Consider this sample (it's stupid

Re: [sqlite] "Stored procedures" performance issue

Igor Tandetnik wrote: > Kristoffer Danielsson wrote: >> When I create my own "stored procedures" using >> sqlite3_create_function, I get horrible performance (which I >> expected) even though the column of interest is INDEXED. >> >> Consider this sample (it's stupid, but it shows my problem): >> >

Re: [sqlite] "Stored procedures" performance issue

Kristoffer Danielsson wrote: > When I create my own "stored procedures" using > sqlite3_create_function, I get horrible performance (which I > expected) even though the column of interest is INDEXED. > > > Consider this sample (it's stupid, but it shows my problem): > > SELECT * FROM MyTable WHERE

Re: [sqlite] "Stored procedures" performance issue

On Mon, 5 Oct 2009 18:01:46 +0200, Kristoffer Danielsson wrote: > This makes sense. Though, I think the > documentation should cover this. Much of this is implicitly or explicitly covered in http://www.sqlite.org/optoverview.html . And what Scott Hess said. -- ( Kees Nuyt ) c[_]

Re: [sqlite] "Stored procedures" performance issue

on, 5 Oct 2009 09:31:10 -0400 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] "Stored procedures" performance issue >> >> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this >> > function, if I encounter a date greater than m

Re: [sqlite] "Stored procedures" performance issue

This makes sense. Though, I think the documentation should cover this. Thanks for your response. > From: paiva...@gmail.com > Date: Mon, 5 Oct 2009 09:31:10 -0400 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] "Stored procedures" performance issue > > >

Re: [sqlite] "Stored procedures" performance issue

> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this > function, if I encounter a date greater than my birthday, then I want to tell > SQLite to stop searching, since the date is indexed. > > Is this possible? If so, how? Even if this was possible it would be useless because

[sqlite] "Stored procedures" performance issue

When I create my own "stored procedures" using sqlite3_create_function, I get horrible performance (which I expected) even though the column of interest is INDEXED. Consider this sample (it's stupid, but it shows my problem): SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); IS_M

Re: [sqlite] Most wanted features of SQLite?: Stored procedures

On 20/09/2009, at 6:57 AM, Simon Slavin wrote: > > Ah. Okay, so in SQLite3 you can emulate stored procedures using > triggers. Just define a trigger to operate on something that > doesn't matter to you. For instance inserting a record in a table > that you never bother reading. Every so o

Re: [sqlite] Stored procedures

Sqlite supports user loaded functions and triggers but not stored procedures. You can probably achieve the functionality you need with functions and/or triggers. VIGNY Cecilia wrote: Hi, Does SQLite supports stored procedures ? If it does, what is the appropriated syntax ? Thanks. Ce m

[sqlite] Stored procedures

Hi, Does SQLite supports stored procedures ? If it does, what is the appropriated syntax ? Thanks. Ce message est protégé par les règles relatives au secret des correspondances. Il est donc établi à destination exclusive de son destinataire. Celui-ci peut donc contenir des informations co

Re: [sqlite] Stored procedures in triggers

Is there any chance that CREATE TEMP TABLE will be available within triggers? The idea is that those tables can be used only within the triggers themselves. Thanks, Ran On 3/24/06, Ralf Junker <[EMAIL PROTECTED]> wrote: > > > >Would it be useful to have recursive DELETE triggers > >even without

Re: [sqlite] Stored procedures in triggers

>Would it be useful to have recursive DELETE triggers >even without recursive INSERT or UPDATE triggers? Recursive DELETE triggers would certainly be usefull and have in fact already been asked for on this list occasionally. They would allow to move referential integrity of hierarchical data o

RE: [sqlite] Stored procedures in triggers

> > Thoughts? Would making recursive triggers an error rather > than just silently ignoring them break anybody's code? even if it does, it should. otherwise, people may assume that the functionality exists,and rely on it. > I'm also looking at making DELETE triggers recursive. I can > do th

Re: [sqlite] Stored procedures in triggers

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > Cascading triggers are supported [in SQLite], recursive triggers are not. > That is, if you have an insert trigger on table A which, say, inserts > into table B, and there's an insert trigger on table B, it will run. But > if this latter trigger turn

Re: [sqlite] Stored procedures in triggers

See the email of Igor Tandetnik from 18-Dec-2005: "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 ge

[sqlite] Stored procedures in triggers

 Hi, Can you tell me how to create a stored procedure in an sqlite3 database and use the same in a trigger? Please provide an example (as complete as possible). In the stored procedure I need to execute few queries on some tables. Can you tell me how to do that also? Any help is deeply

Re: [sqlite] stored procedures

> created as well in PROPERTY_TABLE (with some link ID updated in both table). > Whereas a stored Procedure would have enable me to clearly look at the whole > procedure as one single operation. > > - Original Message - > From: "Jay Sprenkle" <[EMAIL PROT

Re: [sqlite] stored procedures

ve enable me to clearly look at the whole procedure as one single operation. - Original Message - From: "Jay Sprenkle" <[EMAIL PROTECTED]> To: Sent: Wednesday, June 15, 2005 12:40 AM Subject: Re: [sqlite] stored procedures BTW I wonder why SQLite doesn't support

Re: [sqlite] stored procedures

> BTW I wonder why SQLite doesn't support Stored Procedure. > Through Trigger it does already support some similar functionality. > Certainly, while writing trigger code it won't have been that much code to > write stored procedure code as well. > That kind of puzzle me.. is there any rationale

Re: [sqlite] stored procedures

ionale for the lack of stored procedure? - Original Message - From: "Puneet Kishor" <[EMAIL PROTECTED]> To: Sent: Wednesday, June 15, 2005 12:18 AM Subject: [sqlite] stored procedures searching on the 'net reveals that SQLite3 doesn't support storedprocs, but

Re: [sqlite] stored procedures

http://www.sqlite.org/lang.html follow the link for CREATE TRIGGER - Original Message - From: "Puneet Kishor" <[EMAIL PROTECTED]> To: Sent: Wednesday, June 15, 2005 12:18 AM Subject: [sqlite] stored procedures searching on the 'net reveals that SQLite3 doesn&

[sqlite] stored procedures

searching on the 'net reveals that SQLite3 doesn't support storedprocs, but they could be somewhat emulated via TRIGGERs. Is there a tutorial on doing so? I want to wrap multiple, but logically single, SELECTs, UPDATEs, INSERTs, and DELETEs into one db call. preamble question -- is there a per