Re^2: arranging code that works with SQL databases in Perl

Replies are listed 'Best First'.
Re^3: arranging code that works with SQL databases in Perl by metaperl (Curate) on Jul 25, 2009 at 21:09 UTC
Yes, the thing is how much SQL manipulation do you need to do? For instance, do you sometimes need to add LIMIT clauses? Or count rows instead of just returning them? Or maybe the columns you need back change dynamically? Stored procedures and phrasebooks share some similar issues that dave mentions here	[reply]
Re^4: arranging code that works with SQL databases in Perl by mje (Curate) on Jul 27, 2009 at 07:45 UTC
We don't do any SQL manipulation outside the database. If we need a result-set where one of the columns = X or like Y or some other comparison, the value being tested against is passed as an argument although the cases other than equality are almost non-existent. We have no cases of changing the number of rows in the result-set since there is a procedure/function which provides exactly what is required. I believe we've also got no occurrences of dynamic SQL in the packages. Perhaps we are lucky in being able to do this but I think it was more by design. The outside Perl knows nothing at all about the structure of the database tables, it only knows about the procedures and functions we expose from the packages. Even though this application is fairly large we've had no real problems making it this way and as the procedure and function interfaces are well defined we can work on the database side independently from the Perl (and other languages) on the outside.	[reply]
Re^3: arranging code that works with SQL databases in Perl by metaperl (Curate) on Sep 01, 2009 at 18:43 UTC
Putting everything into Oracle procedures is the approach the project I'm working on right now has taken. Can you comment on how fast ORacle stored procs are compared to SQL? I know that the query optimizer has no access to stored functions in MySQL and that things often run many times slower. Also, how easy is it to dynamically generate SQL with stored procedures? What if you had a search form with an number of optional fields and various comparison operators? Could you build up the SQL piece-wise and then evaluate it after constructing it? Finally, I find the stored procedure language quite a bit more wordy than Perl. It would seem that if the database knew the relations of your tables that it could generate common things you would want: given this id, get the related info in all tables which use this id as a foreign key instead of returning a cursor to a result set, give me the count of the result set paginate the result set: given an offset and row count, point the return cursor there Now, the SP approach does have its advantages, but I'm just exploring some potential cons.	[reply]
Re^4: arranging code that works with SQL databases in Perl by mje (Curate) on Sep 02, 2009 at 17:04 UTC
As far as I am aware there is no disadvantage to using cursors inside oracle stored procedures or functions. In fact, the cursors can be cached. I don't use mysql so cannot comment on that. You can use dynamic sql in oracle stored procedures but it is generally not such a good idea as oracle cannot know the sql you will be running so optimization of it prior to running it is less likely. Our project uses no dynamic sql at all (we have gone out of our way to avoid it). If a procedure/function which generates and returns a cursor can take optional arguments (e.g., ones we we use in the SQL) then we default them to NULL and ensure the SQL does not perform the comparison on the column if it is NULL e.g., (where (param is NULL) or (param is not null and param = column). I don't really see any cons to using procedures in oracle and for a) speed and optimization, b) keeping logic in the database near the actual schema and data it is unmatched. However, if your only option is building dynamic sql then this is easily done in Perl.	[reply]