To give a slightly different perspective, here's some thoughts from the DBA side of things.

Programming Pros

• It's nice from the programming point of view to have automatic code generators that do things to remove you from the SQL
• Encapsulating business logic in Perl (or whatever language takes your fancy) modules makes things nice and easy for this project
• Abstracting the SQL to a completely different module away from our nicely structured Perl keeps things looking nice and neat

DBA Cons

• Unfortunatly this kind of code generation leads to stuctures like:

  my $sth1 = MYSQLMODULE::generate_result_set(TABLE => "employees",
                                              COLUMNS => qw(name positio
  +n salary id),
                                              WHERE => "department = 'SA
  +LES'");
  $sth1->execute();
  while (my @employee = $sth1->fetchrow_array) {
     my $sth2 = MYSQLMODULE::generate_result_set(TABLE => "commission",
                                                 COLUMNS => qw(total_com
  +mision),
                                                 WHERE => "id = $employe
  +e[2]");
     $sth2->execute();
     my @commission = $sth2->fetchrow_array();
     print "$employee[0]($employee[1]) = $commission[0]\n";
  }
  ...etc...
  [download]

  which is highly inefficient, and should be replaced with a single SQL statement with a join.
• It doesn't matter which language you code your business logic in, in 2 years time the company will be using something else and someone will have to re-write the business logic in todays latest fad language. Best off to write it in the database's stored procedure language and let everybody, no matter what language they code their appilications in, be subject to the same, consistent, business rules. If the rules change, the code only has to be changed in one place.
• Programmers don't necessarily understand (or even care) about performance issues on a particular RDBMS. Nor should they - it's not their problem, that's what the DBA gets paid for. A DBA having to wade through yards of code in Perl, C, C++, Java, Cobol, Fortran - on the same day! - is not a lot of fun (challenging, but not a lot of fun), to find an offending piece of SQL, and then trying to get the correct programmer to check-out, edit, test, check-in, rebuild, deploy, etc the new application whilst production is on its knees and a swarm of managers is hovering with intent is, again, not a lot of fun (stressful, but not a lot of fun). Again - encapulate the SQL in the database's stored procedures and the DBA will let you sleep in your cubicle.

I haven't adhered to these rules in the past, for instance - not using stored procedures because I want to make to application as portable as possible, but then having to make the SQL highly optimised for a particular platform to get any sort of performance, means that the application is unable to be easily ported anyway. Think of SQL the same way as everybody in PM thinks of HTML (replacing the Web Designer with the DBA, of course) - get it out of your application, so you don't have to worry about it (make it SEP). If you haven't got a DBA up to the job, just /msg me... :)

rdfield

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.