Let us take a magnifying glass to this innocent line of Perl code:
This seems simple enough, but it actually shows 3 topics of developing Perl database applications which are beyond the DBI API.my @user_id = CompanyDB::Query('USER_ID_FROM_USERNAME', $cgi->param('username'));
Though this code makes use of a SQL SELECT query, the query is not (and should not be) anywhere in the main body of the application code. There are 3 CPAN modules which cover this issue: <cpan>Ima::DBI</cpan>, <cpan>DBIx::Librarian</cpan>, and <cpan>SQL::Catalog</cpan>
Executing a query against a database requires a database handle (it may require a statement handle if you $dbh->prepare, instead of $dbh->do, but in all cases the database handle must be created or already exist for this code to work. This issue is trickier than it seems. In fact if you value the holy grail of view-independent applications, then somehow your means of database handles must work both when using web applications and when using command-line apps. CPAN modules related to this task are <cpan>Ima::DBI</cpan>, <cpan>DBIx::Password</cpan>, and <cpan>Apache::DBI</cpan>.
One could also say that <cpan>AppConfig</cpan> is somewhat related because it might contain all of an application's configuration data. Which makes me want to create an alternative to <cpan>DBIx::Password</cpan> which accesses password information from an <cpan>appconfig</cpan> file instead of using a Perl module with config information in it. After all, why would I want some of my application configuration information in a Perl module somewhere and the rest in my AppConfig file? This violates the principle of centralization.
As you can see, the line of code always returns an array of results to the user, thus simplifying the application code. The module that works most similar to this on CPAN is <cpan>DBIx::DWIW</cpan>. However, there are a host of others: <cpan>DBIx::Broker</cpan>, <cpan>DBIx::Easy</cpan>, and <cpan>DBIx::AbstractLite</cpan>. Two modules which supposedly make SELECT query massaging easier are <cpan>EZDBI</cpan> and <cpan>DBIx::Abstract</cpan> but for me their principal problem is that they do not support pure SQL, but require that the SELECT part of the SQL be removed. E.g.
use EZDBI; for my $row (Select('* from users')) { } use DBIx::Abstract; $dbh->select('* from users');
That looks like sexy code until you realize that it flies in the face of separating SQL from Perl: no longer can database experts work on complicated, tested, and well-tuned SQL and provide it to the Perl application developer via a label. Instead he has to create "truncated" SQL because of the way these two modules work.
It is not obvious from this line of code, but the database application that I copied this line of code from, actually has a company policy that only SELECT statements are run by Perl/DBI and that any sort of UPDATE or DELETE is run via stored procedures only.
Why might a company do this? Well, I can think of a few reasons:
As you can see, i am grabbing for straws in that reason list. The reason for this is that there are clearly cases where dynamic SQL is better. For instance what if you have a "MyApplication" section where the person goes thru a bunch of pulldowns and checkboxes and changes a huge ream of config info? Do you really want to write every permutation of update SQL for this? I'm assuming you are not dressed in leather and have whip in your hand as you answer this question.. Hheheh.
In this installation of the Wide-Wide World of DBI, we have taken one line of code and managed to understand what it implies about how you handle para-DBI issues in database application development. Inquiring minds want to know how you handle such issues.
</body> </html>In reply to 1 line of code, 3 para-DBI database application issues... how do you handle 'em? by princepawn
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |