in reply to Re: (z) Separation of SQL code
in thread (z) Separation of SQL code

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.

Replies are listed 'Best First'.
Re: Re: Re: (z) Separation of SQL code
by barbie (Deacon) on Sep 11, 2003 at 15:19 UTC
    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]
Re: Re: Re: (z) Separation of SQL code
by mpeppler (Vicar) on Sep 11, 2003 at 16:54 UTC
    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]
        Good idea - although I suspect that most of the DBD::Sybase users here really use MS-SQL... :-)

        Michael

[reply]
        If you ever get the time I would love to see an advanced Sybase DB tutorial on PM.

        It might be interesting to know that all Sybase documentation is also available on the web at http://www.sybase.com/support/manuals/, at no charge and no sign up required.

        Obviously, this doesn't include DBD::Sybase.

        Abigail

[reply]
Re: Separation of SQL code
by Abigail-II (Bishop) on Sep 11, 2003 at 15:20 UTC
    Well, you also have far less SQL code in your program. If all you call is stored procedures, you only need one function that contains creates SQL to call a stored procedure.

    Too bad DBI doesn't support calling of stored procedures yet, otherwise, you wouldn't have to create SQL in your program at all.

    Abigail

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

In Section Seekers of Perl Wisdom