Check out Class::Phrasebook::SQL. You might have
to do some experimentation to get it just right, but it's
a great starting point.
UPDATE:
The docs are bit confusing at first glance, so here is a
quicky example:
use DBI;
use Class::Phrasebook::SQL;
use Data::Dumper;
my $book = Class::Phrasebook::SQL->new(undef,'catalog.xml');
$book->load('MP3');
my $sth = $dbh->prepare($book->get('GET_SONGS'));
$sth->execute('Genesis');
print Dumper $sth->fetchall_arrayref({});
And here is catalog.xml:
<?xml version="1.0"?>
<!DOCTYPE phrasebook [
<!ELEMENT phrasebook (dictionary)*>
<!ELEMENT dictionary (phrase)*>
<!ATTLIST dictionary name CDATA #REQUIRED>
<!ELEMENT phrase (#PCDATA)>
<!ATTLIST phrase name CDATA #REQUIRED>
]>
<phrasebook>
<dictionary name="MP3">
<phrase name="GET_SONGS">
select * from songs where artist = ?
</phrase>
</dictionary>
</phrasebook>
I think you can see how easy it would be to create a
lookup hash that points to the catalog entries.
2UPDATE:
I really should give credit to eduardo for introducing me to this module. Oh, there is also a perl.com article that you should read as well.
| [reply]
[d/l]
[select] |
That's pretty neat! It's not stored procs, but it does help you to hide a lot of the underlying SQL implementation. I think I'll take a closer look at this for future projects.
Michael
| [reply] |
Parking your SQL in stored procedures will fullfil the 'someplace else' requirement if your db engine can handle them. If you are using MySQL or Access then you're out of luck.
Stored procedures have the additional advantage of boosting performance. They also tend to be a bit more secure than dynamic SQL.
Hanlon's Razor - "Never attribute to malice that which can be adequately explained by stupidity"
| [reply] |
How would I do the dynamic building of the statement in stored procedures?
------
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
... strings and arrays will suffice. As they are easily available as native data types in any sane language, ... - blokhead, speaking on evolutionary algorithms
Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.
| [reply] |
The specific way of handling dynamic sql via stored procedures varies depending upon what flavor of sql you are using, but they boil down to eval'ing statments inside of the procedures or if-else blocks. Be forewarned, though, that eval'ing inside of stored procedures causes a performance hit.
To get the most optimal performance, though, it would be best to eliminate the dynamism, if you can do so w/o creating a mess. If your dynamic cases really boil down to a manageable set of statments, then it would probably be best to write/generate separate procs for each!
If, for example, the following Perl/SQL stement:
my $statment = "SELECT COL_1, COL_2 FROM $table";
is really only ever going to give you three SQL statments:
SELECT COL_1, COL_2 FROM TABLE_1
SELECT COL_1, COL_2 FROM TABLE_2
SELECT COL_1, COL_2 FROM TABLE_3
then you may be better off creating three procs (pseudo-SQL follows):
CREATE PROC get_stuff_table1 AS
SELECT COL_1, COL2 FROM TABLE1;
CREATE PROC get_stuff_table2 AS
SELECT COL_1, COL2 FROM TABLE2;
CREATE PROC get_stuff_table3 AS
SELECT COL_1, COL2 FROM TABLE3;
This can get really out of control, though, if you are going to end up many procs. If you are going to end up with more than a handfult then you should consider generating them. Of course, this process can be eased if you generate your procs with a nice language such as, oh... umm.. Perl.
Hanlon's Razor - "Never attribute to malice that which can be adequately explained by stupidity"
| [reply]
[d/l]
[select] |
I use Class::DBI with dynamic SQL and joins. I still have to write the SQL statements, but everything else is done for me. Look at the section of the docs on "Ima::DBI Queries" for more. | [reply] |
DragonChild,
I actually worked this problem here last week. My solution still feels a bit kludgy -- I'm not sure if this is because I don't feel 100% comfortable thinking in SQL or if it due to true kludgyness.
I can recommend one useful approach. If supported by your DB, you can create "views" which perform the table joins. Then you can set up Class:DBI to look at the views as if they were tables. This can result in more straight forward SQL queries than otherwise.
On the otherhand, I'm still having a hard time doing searches in an easy way out of the database when the data is "normalized" into a somewhat complex layout in the DB.
Good luck with your project.
-------------------------------------
Nothing is too wonderful to be true
-- Michael Faraday
| [reply] |