|
Re: Re: (z) Separation of SQL code
by dragonchild (Archbishop) on Sep 11, 2003 at 15:06 UTC
|
There will always be SQL in the Perl code, if only to call the stored procedures. So, the design question remains as to where that SQL to call the stored procedure should live. Granted, you now have business-level SQL calls with well-designed names, so it's a simpler problem.
------
We are the carpenters and bricklayers of the Information Age. The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6 Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified. | [reply] |
|
|
There will always be SQL in the Perl code
Unless you decide to use Class::Phrasebook, which I've also discussed here. It has meant all the fiddling about is done in one file not in several modules. Plus porting between CVS, MySQL and MS SQL7 has simply been a matter of changing the phrasebook.
--
Barbie | Birmingham Perl Mongers | http://birmingham.pm.org/
| [reply] |
|
|
Well, as Abigail mentioned, if you use stored procs you can limit the amount of database-specific code to something very small.
At eCircles (unfortunately dead now) we had a web site with around 80k lines of perl code, of which 400 were database-specific (using Sybase::CTlib), and with table-driven definitions for each stored procedure (i.e. logical database request). And because we were using Sybase's RPC functionality to call the procs there was 0 risk of SQL injection as there was no SQL parsing involved anywhere in the execution path.
DBD::Sybase is capable of using the RPC functionality as well
Michael
| [reply] |
|
|
DBD::Sybase is capable of using the RPC functionality as well
If you ever get the time I would love to see an advanced Sybase DB tutorial on PM.
please, please, please, pretty please with sugar on top?
---
demerphq
<Elian> And I do take a kind of perverse pleasure in having an OO assembly language...
| [reply]
[d/l] |
|
|
|
|
|
|
|
|
| [reply] |
|
|
DBD::Oracle does support calling stored procedures and getting return values back from them. I've heard that DBD::Sybase (for Sybase and MS-SQL) does as well, though I don't have personal experience with that.
------
We are the carpenters and bricklayers of the Information Age. The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6 Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.
| [reply] |
(z) Re^2: Separation of SQL code
by zigdon (Deacon) on Sep 11, 2003 at 15:17 UTC
|
I believe that's not an option in MySQL? And this project probably can't use anything else, due to technical and budget constraints.
-- zigdon
| [reply] |
|
|
Have you looked at PostgreSQL? It's also free (as in beer) and supports stored procedures in a number of languages, including Perl, and apparently works nicely with PHP as well.
There are a number of visual DBA tools available for it (some also free) which make it pretty easy to use.
I've been using the Eclipse IDE lately, and like the Eclipse plugin, Quantum, which extends the basic IDE to allow working with most databases that provide JDBC support.
- mikeB
| [reply] |
|
|
Oh, your question suggested you were working on a serious
project. MySQL is a feature-poor database that I can't
take seriously.
Abigail
| [reply] |
|
|
I can't take seriously anyone who'd suggest putting everything into stored procedures.
Calling a stored proc for a simple select is often more expensive than executing it on its own, and procs should typically not be called from loop constructs or other stored procs due to the decision making the DBMS needs to perform on each procedure call.
Sure, your code may look a bit simpler, but your DB might not be so happy.
| [reply] |
|
|
