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

Re: [sqlite] Stored Procedures

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

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

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

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

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.

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

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

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

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

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

Re: [sqlite] Stored Procedures

nglists.sqlite.org> > Subject: Re: [sqlite] Stored Procedures > Message-ID: >

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

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

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

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

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

[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

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

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

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

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

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

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

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

[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

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

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

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

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

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

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

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

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

[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

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

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

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

Re: [sqlite] "Stored procedures" performance issue

mail.com >> Date: Mon, 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

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

[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);

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

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

[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

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

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

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

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

[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

; To: <sqlite-users@sqlite.org> > Sent: Wednesday, June 15, 2005 12:40 AM > Subject: Re: [sqlite] stored procedures > > > >> BTW I wonder why SQLite doesn't support Stored Procedure. > >> Through Trigger it does already support some similar functionality. > >