Stored procedures vs Functions 2

[vj]

hi everyone,

i have never used functions but i'v used stored procedures .. i was wondering if both functions and stored procedures work, perform and output in a similar way ... i dont know ,, iam not sure !!! can anyone explain what big differences they really have and which one is a better choice among most programmers and for what special reasons. i find stored procedures interesting i guess they are stored in the database itself .. right ! no external files !

thankx alot
Vijay

 

[Mike Lewis]

Well, a stored procedure is a function - but it's one that's stored in the database rather than in a PRG file or a form or class.

The pros and cons of stored procedures are similar to those of validation rules that we were discussing on your other thread. Like validation rules, the main advantage is that they centralise the logic, and make it available from any application that can access the database.

But personally I never use them. I much prefer to keep my logic where it belongs - in the application. If you have got some good reason to store your code in a database, go ahead and do so, otherwise don't bother. But that's just my opinion. No doubt others will have different views.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vj]

hi mike,

so i guess the best thing would be to have all the procedures in a .prg file instead of having them as stored procedures inside the .dbc and call/run them from that file when needed ... i guess that would make it easy to manage changes in times of migration from native vfp .dbc to any other database ... right !

thankx
vijay

 

[vgulielmus]

Another point of view.
I use stored procedures and I have similar procedures stored in VFP's DBC and in other DBMS.
There are some actions that are better managed by the DB engine.
I believe the most trivial example is a log of interventions on some sensible tables.
A stored procedure will produce log entries even for actions made by the DB administrator outside the application.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[JRB-Bldr]

Personally I seldom use Stored Procedures since they are a BIG challenge to debug.
(NOTE - I assume we are talking about VFP Stored Procedures, instead of MS SQL Server Stored Procedures)
You cannot set Breakpoints, etc. in a Stored Procedure like you can within a Function/Procedure.

In order to debug the code in a Stored Procedure you have to use things like WAIT WINDOW's, etc. which only offer a limited 'view' of what is going on compared to using the TRACE/WATCH WINDOW.

But if your code is already 100% working, putting it into a Stored Procedure works fine.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

One place where I might use a stored procedure is for a routine that is called from a trigger, or perhaps from a DBC-resident validation rule. But in practice I have never done so.

Vejay, you said: "so i guess the best thing would be to have all the procedures in a .prg file". That depends on what procedures you are referring to. If they are generic routines that can be called from anywhere in the application, a PRG file is certainly an option. Alternatively, you could create a so-called application object, which is an object that is available throughout the application, and which contains values (properties) and procedures (methods) that can be called from anywhere.

Personally, I use a PRG file, which I reference from SET PROCEDURE. But I know there are people here who frown on that approach.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

I suggest a separate PRG for each function rather than a procedure file. Procedure files can get unwieldy and can give you some weird behaviors.

Tamar

 

[vj]

hi tamargranor,

that is something new i'v heard now ... about ... "procedure files getting unwieldy and can give you some weird behaviors." .. hmmm actually i use a .prg file with all my procedures inside it ... Procedure <procedure name> EndProc .. i have about over 30 to 40 procedures in there ... so according to you i should put each of them .. all seperately in different .prg files ?

thankx
vijay

 

[Mike Lewis]

I've never known any "weird behaviors" with procedure files. But there are issues with management.

With a single procedure file, it can be more difficult to find the particular function or procedure you are interested in, plus you have the problem of every application having to carry all your procedures and functions, even if you don't always use all of them.

Those issues don't apply if you make each procedure or function a separate PRG. Then again, you could end up with hundreds of PRGs, which could clutter up your code folder and your project manager window.

So, take your choice. Personally, I use a single PRG because that's the way I've always done it. But I can see that having separate PRGs would be a good choice.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Freeman]

Proc file vs. individual PRG files is mostly an organizational (or religious) issue, but I do prefer individual PRG files and have preferred that since the FoxBase+ days. Back then, the product shipped with a utility named FoxBind that accepted a list of files to combine into one for compiling.

The VFP project manager takes care of that without the interim step or utility.

One wrinkle about proc/prg to keep in mind is the sequence of where Foxpro looks for code when you call a function. First in the currently executing PRG, next in the current folder, next on the Foxpro path. If you have a function *both* in your proc file and as a standalone, will different code execute when called from inside a class method vs. inside one of your procs?

You can also use the search sequence to force a form of polymorphism on procedural code. If most places in your app need version A of a function but ONE program needs different functionality you can put that special function at the bottom of that one PRG file. There are almost always better ways to do things, but it's a thing that's there and can be used (particularly with Someone Else's Code). As long as it's CLEARLY COMMENTED.

(It's horribly bad code management to have two copies of code, yes, but I'm working on an inherited app that has exactly that. There's a proc called ErrorHandler, among others, that live in both places. )

 

[IlyaRabyy]

If I may "insert my 2c"?
One of the uses for SP is to store a query in there, that is - an SQL SELECT statement. I've seen it done, and did it myself with DBC.
As for having each and every procedure/function in a separate PRG file... just imagine you have to deal with 200-300-400 separate PRG files! Let alone having to have SET PROCEDURE TO ... ADDITIVE for everyone of them!
OTOH, it's easier to ship out a single FXP file to your client than the whole EXE, if you need to fix something in a single procedure. (I just know by experience how clients' Sys Admins react (with utmost hostility!) on EXEs. I guess they would be more tolerant if it were just a small FXP file.) So, again, it depends on the circumstances. If you have just, say 10-15 procs - t's OK to have them in separate PRG files; but if it's 10-15 times more than that - I'd rather have them in a - not even a PRG! - VCX/VCT file (that is - in a class library).

HTH.

Regards,

Ilya

 

[TamarGranor]

Ilya, you don't need to SET PROCEDURE at all if every PRG contains exactly one routine. You just refer to them by name. So MyFunc.PRG contains the routine MyFunc and is called with either DO MyFunc or MyFunc(). In the IDE, it just needs to be in the path; at runtime, it's in the EXE.

As for weird behavior, I do the vast majority of my testing in the Command Window by issuing DO <the main program>. I've had issues with the version of a procedure file that's built into the EXE getting used, even after I've made changes to the procedure file. I've had to actually delete or rename the EXE to get the newer version to work. (I won't swear to it, but I think it happened even in a session where I hadn't actively built the EXE. It existed from a previous build.)

I don't care how many files are listed in the Project Manager or the folder since I never really look at those. That's especially true since IntelliSense was added and I can find most of the file I want to work on via MRU lists (after all, most often, I'm working on what I was just working on). Personally, I open the PM when I need to build an EXE and pretty much no other time.

Tamar

 

[Olaf Doschke]

The topic has moved to procedures and functions in general, which isn't wrong, but let me go back to your initial question.

It's not wrong to use the dbc for database related code aka stored procedures. Don't focus too much on the term procedure here, stored procs are also called this way for other databases, even if it's not about procedures but about scalar or even table valued functions. The majaor difference of procedure vs function has become obsolete in several ways in VFP. For one, you can RETURN values from both a function and procedure. The major difference in the past was about passing parameters by value vs by reference, I'd rather SET UDFPARMS TO VALUE and pass things by reference via @ prefix. Objects are passed by reference only, but all that has still very low impact on DB stored procs. Also open up a scx or vcx as table and look into the code memos, you'll see every method or event is stored there as a procedure. The keyword function has become obsolete, if you ask me, but I tend to make the difference of functions returning a value and procedures not.

The main downside of stored procs is, if you want to move to Sybase with dbfs, you can't reuse much of the code there, also clients using DBC via the original MS VFP ODBC driver are limited to VFP6. IIRC TaxRI is a third party referential integritiy code builder using code compatible to VFP6. But if that's not your concern, you can even use it to prevent ODBC access to your data by simply enabling DBC evens. It's not a strong protection, as that can be turned off in the DBC, which can be read any modified as a free table.

Anyway, the DBC allows storage of the code in itself, it'll be in one of the first three records of the DBC file (more precise in the DCT). You can only store code related to the DBC Events in an external PRG. But this is not a big problem about the stored procs location. The major entry points to stored procs are by expressions as default values of fields, table and field rules and insert-, update-, and delete-triggers. All those can call into anything in scope, not only into the code of the stored proc memo or the external dbc events prg file. If you use a application class, you can call into goApp.somemethod or anything you want from all the calls into dbc related code. Remember, in case of VFP there is no database server separated from the client side code, everything including stored procs runs at the client and can be linked to application code, too. This advantage is a disadvantage, if you later would like to port the database including the database related code to a db server. Also, if you use the db from several apps, you either shae some part of your code/framework or you put it to the db.

The only third party tool making much use of stored procs and dbc events is the dbcx extension, which eg is used by stonefield database toolkit. It puts a vcx file to the dbc, which is used by dbc embedded code. And that's a very valid approach to combine the native outset with oop design. Don't look out for DBCX, but DBCX2, see

http://doughennig.com/papers/..\Pub\199808dhen.pdf

Bye, Olaf.

 

[Mike Lewis]

The main downside of stored procs is, if you want to move to Sybase with dbfs, you can't reuse much of the code there, also clients using DBC via the original MS VFP ODBC driver are limited to VFP6.

I would make a more general point. If you want to move your database to any back end, you will have to either: (i) rewrite your stored procedures; or (ii) take them out of the database entirely and make them ordinary FoxPro procedures or functions.

Option (i) might be easy or difficult, depending on how many stored procs you have and how complex they are. Option (ii) should be reasonably easy most of the time. But migrating to a new back end is never a trivial task, so whichever of those two options you choose, it will be yet another chore to worrry about during what will probably be a difficult time.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

That's of course true, I shouldn't have focused on Advatange Database. But it'd be the only path of migration, were you at least could stay with DBFs. I already mentioned several times we moved to SQL Server with most databases. Actually all that remained a dbc is one software replaced with some standard software in the next few years and another database is a data warehouse we create from several databases, so we have a DBC summarizing data from several SQL Server databases, which isn't yet moved to OLAP because of lack of time and importance.

Bye, Olaf.

 

[vj]

hi everyone ,

wow ... it's really nice to have soooo many views and ideas coming in ... very interesting ... well actually it's been over 6 years now . i guess .. when i was working on a telecom database where i use to pull and merge data from sql-server , clipper dbf's , ms access mdb files .. and some foxpro tables ...... to generate various types of reports. then one day i had to write a little code to generate 10 digit unique pin numbers for pre-paid cards .. and this is where i saw the power of stored procedures in sql-server .. initially i had written a code of couple of lines to generate the pin .. in vfp6 ... it worked fine but started to grow slower and slower .... after a year or soo.... and then after some ideas and advice ... i put the code into the stored procedure in the sql-server database ... as default value for that field and it was lightening fast .. i was amazed ! so that's what i was thinking and wondering if running a code from a stored procedure inside a dbc would be more faster than otherwise ??? if let's say we'r talking about a couple of hundred million records ?

thankx alot guys
vijay

 

[Olaf Doschke]

>so that's what i was thinking and wondering if running a code from a stored procedure inside a dbc would be more faster than otherwise ???
Not in VFP stored procs, as they run on the client anyway, no matter where you put the code, as I already said.

VFP is not a database server, you don't build up a connection to a dbc, even if you would use ODBC or OLEDB towards a DBC, the driver runtime, essentially a part of the VFP runtime, running on the client doing the connection will also do the insert and compute default values etc. via stored procs. The only advantage is, you have code centralized in the DBC and everyone using it, even .NET applications, run these stored procs (via the driver talking foxpro, not in themselves).

You can make use of the concept of doing things server side, if your file server storing the DBFs is a Windows server, of course you can also run code there. But you would then need to trigger that somehow. One way is CREATEOBJECTX() to instanciate a remote server, a module running server side. That involves configuration about remote access and needs highered permissions, which is why this is a bit cumbersome. It also involves an overhead of OLE automation. Also, if some server side process inserts data and thereby also executes the stored procedures, you don't have the new data, until you query it after it has been inserted.

So most of the time it's not a problem the clients operate on the files. We just have locking problems because of that, network bandwidth is a bottle neck, but not CPU power, as 100 users run on 100 pcs with 100 processes.

Bye, Olaf.