tonyday has asked for the wisdom of the Perl Monks concerning the following question:

I have lots of code for a project which has sql statements stuck in the code like this ... 
    (my $sql = <<SQLCREATE) =~ s/^\s+//gm;
        CREATE TABLE $table 
    (id INTEGER NOT NULL AUTO_INCREMENT,
     name CHAR(40) NOT NULL,
     address CHAR(40)
     PRIMARY KEY (id)
    )
SQLCREATE
    $sql =~ s/\s+$//gm;

[download]
...and it's gotten very messy. I am playing around with putting all the sql statements in a text file and parsing them in to a hash. After parsing and some double interpolation of $things like $table in the above example I can just say... 
$sth = $dbh->dosql($sqls{CreateNameTable});

[download]
and my code is more manageable.

My goal is to build towards a library of useful and hopefully generic sql statements. I was wondering what other people do within perl to manage lots of sql statements. I imagine one solution would be to put the sql statements in the database and grab them as necessary.

Another question I have is how best to debug them - the debugger gives not very helpful messages like "You have an error in your SQL syntax near ''". Should I be writing and debugging the sql outside of Perl (which makes me nervous) or is there a good perl solution?

thanks, tonyday

Replies are listed 'Best First'.
Re: How to manage sql statements
by VSarkiss (Monsignor) on Jul 13, 2001 at 07:10 UTC

    It's certainly a worthy goal:

    ...to build towards a library of useful and hopefully generic sql statements.
    Sadly, SQL doesn't admit genericity easily. SQL isn't a procedural language; that is both its strength and weakness. It doesn't admit variables (not "pure" SQL), so, for example, you can't create a "generic count-duplicates" statement like this: 
    SELECT @columns, COUNT(*)
FROM $table
GROUP BY @columns
HAVING COUNT(*) > 1

    [download]
    where the above is a mythical SQL-Perl hybrid.

    You can get halfway there. In the past, I've often resorted to managing many statements with a bit of Perl. For example, in one of my scripts to rebuild tables you'll find stuff like this: 

    @key_cols1 = qw(account_id group_id);
@key_cols2 = qw(account_id manager_id asset_id);
# Later...
$set1_sql = 'SELECT ' . join(',', @key_cols1)
   . 'FROM ' . $table1_name
   . 'WHERE ' . $table1_cond
# You get the idea

    [download]
    So, it's not pretty, but it's better to maintain one set of templates in code than 18 separate SQL files.

    You're right, it seems like there should be a better way, but most SQL interpreters seem to stop just a hair short or supporting cool stuff like this. If you can come up with something workable, I'll be the first to grab your stuff!

[reply]
[d/l]
[select]
Re: How to manage sql statements
by adamsj (Chaplain) on Jul 13, 2001 at 07:35 UTC
    I'd been thinking about this lately myself, and you've given me the urge to search CPAN and see what's out there.

    DBIx::Abstract (there's also a DBIx::AbstractLite out there, which I didn't browse) looks useful and interesting, until you reach Supported DBD Drivers and discover it's been reported to work with mySQL, PostgreSQL, and XBase, and that it should work in a lot of other situations. Sigh--it looks so nice.

    Nothing else that I saw looks particularly good, so I've downloaded this and am going to try working with it at home--I'll keep you posted on my adventures. Please do some additional searching in CPAN--I'm willing to give this one a shot, if someone else will look around and see what I've missed.

    adamsj

    They laughed at Joan of Arc, but she went right ahead and built it. --Gracie Allen

    Update: Okay, now I'm thinking DBIx::Abstract maybe isn't worth my efforts, after reading your comments. Does anyone else have suggestions? Or is it time to start rolling our own?

[reply]
      I had a good search around a few weeks back and thought that DBIx::Abstract was the best option. However, I found that I was having to bypass the abstraction a lot of the time because my statements were too complex.

      This article is a good example of the type of idea that could be abstracted (even if I don't understand it all). It shows how data manipulations that are quite easy to express end up being fiendishly complicated sql.

      VSarkiss's perlsql hybrid example was the way I expected to write sql when I first started down the perl/sql path. Just hook into a module and say... 
      use SQL::GenericStatements qw(count_duplicates, cross_tab);
$result = count_duplicates($dbh, $table, @columns);
$result = cross_tab($dbh, @tables);

      [download]
      With VSarkiss's pseudo code under the hood. Another example is the lack of a subselect in mysql. What about a "converter" of sql that overcomes the specific driver limitations.

      Seems like a *lot* of work though...

      tonyday
[reply]
[d/l]
Re: How to manage sql statements
by Masem (Monsignor) on Jul 13, 2001 at 15:03 UTC
    In addition to other methods suggested, I'm also working on DBIx::Pretty, a way to move non-abstracted SQL statements to a single location in a large piece of code. It is by no means a finished product, but you get the idea for cleaning DBI up. (The idea came out of the recent Leashing DBI thread, if you are interested for more). DBIx::Pretty is similar in nature to what you have (a hash of SQL), with some additional benefits (cleaner calls, additional variable replacement abilities above/beyond what DBI offers).

    Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
[reply]
Re: How to manage sql statements
by AidanLee (Chaplain) on Jul 13, 2001 at 08:29 UTC

    I've actually been working on something myself. It's fairly different in approach though. I've outlined some of my thoughts on it here

    This has wound up to be a pretty big project for me with more to go in the not-too distant future, but thought you might be interested in another-WTDI.

    as to debugging, I always _try_ my SQL out from the command line first, just to check out i haven't gotten things all befuddled. Additionally I always check for errors on every step of a sql call, and when one occurrs I dump the full sql query string out at the same time that I output the error message. It's VERY helpful

[reply]
Re: How to manage sql statements
by agoth (Chaplain) on Jul 13, 2001 at 15:15 UTC
    A while ago I went for a package with all the SQL in and an abstraction layer, CRDB which sits on top of DBI, takes a statement title and returns the results.
    Works quite well for a system that doesnt have masses of SQL. 
    package CRSql;

use strict;

use vars qw ( %SQL ) ;

%SQL = ( 

sys => q{
    select count(*) from user_tables
},

donation_count => q{
    select count(*) from donation
},

);


#------------------------------------

use CRSql;
use CRDB;

my $crdbh = CRDB->login('****', '****', '****', $FALSE);

Log::write('FATAL', "DB connection failed \n") unless (ref($crdbh) eq 
+'CRDB');

$crdbh->sql(\%CRSql::SQL);

my ($cols, $ref) = $crdbh->as_list('donation_count', 'A');

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom