<html> <head> <title>SQL::Catalog</title> </head> <body BGCOLOR="#fffff8" TEXT="#000000">

What you must do
What SQL::Catalog does
AUTHOR
SEE ALSO

NAME

SQL::Catalog - test, label, and retrieve SQL queries

SYNOPSIS

shell% cd sql_lair/city,date/weather/1/

 shell% cat concrete.sql 
 select city, date from weather where temp_lo < 20;
 shell% sql_test concrete.sql 
 shell% cat testexec.out # see results of prepare, execute on this
[download]

 shell% cat abstract.sql
 select city, date from weather where temp_lo < ?;
 shell% sql_test abstract.sql 55 # send in placeholder value
 shell% cat testexec.out # to see results... looks good
[download]

 shell% sql_register abstract.sql basic_weather
 [hi_and_low] inserted as 
 [select city from weather where temp_lo > ? and temp_hi > ? LIMIT 10]
[download]

 ... then in a Perl program (e.g. test.pl in this distribution)
 my $dbh = SQL::Catalog->db_handle; # optional - get the handle as you
+ please
 my $sql = SQL::Catalog->lookup('hi_and_low');
 my $sth = $dbh->prepare($sql);
 $sth->execute(55);
[download]

my $rows = $sth->rows;

DESCRIPTION

Over time, it has become obvious that two things about SQL queries are necessary. One, you want to be able to get a query by a label. Two, you want to be able to look through old queries to see if someone else has written one similar to what you want. Three, you want the database guru to develop queries on his own and be able to register them for your use without interfering with him. Four, you want to be able to answer questions such as "what queries are doing a select on such-and-such tables".

Well, wait no longer, for your solution has arrived.

Common Steps to Usage

Develop your "concrete query in a db shell"

The first step to developing a database query is to play around at the db shell. In this case, you normally dont have any placeheld values. You just keep mucking with the query until it gives you what you want.

When you finally get what you want, save it in a file, say concrete.sql for example. Here is a concrete query:

select city, date from weather where temp_hi > 20

Abstract your query with placeholders

Now it's time to make your query more abstract. So we do the following:

select city, date from weather where temp_hi > ?

and save in a different file, say abstract.sql.

But let's test this query next:

sql_test abstract.sql 34

And let's cat testexec.out to see the results.

Register your query

sql_register abstract.sql city_date_via_temp_hi

and the system tells you

 [city_date_via_temp_hi] saved as
 [select city, date from weather where temp_hi > ?]
[download]

Use your query from DBI:

use SQL::Catalog;

 my $dbh = SQL::Catalog->db_handle;
 my $SQL = SQL::Catalog->lookup('city_date_via_temp_hi') or die 'not f
+ound';
 my $sth = $dbh->prepare($SQL, $cgi->param('degrees'));
  .... etc
[download]

What you must do

* edit sub db_handle so it gets a database handle.
* copy the sql_* scripts to a place on your $PATH
* create a table named sql_catalog. a script for Postgresql is provided.

What SQL::Catalog does

It stores each query in a database table. I could have gone for something more fancy in database design but wanted to maintain database independence without requiring extra tools for schema creation and database use.

The queries are stored in this table:

 CREATE TABLE sql_catalog (
    query varchar(65535) , # the actual query
    tables varchar(255) ,  # tables used
    columns varchar(255) , # fields selected
    cmd varchar(40) ,      # SELECT, INSERT, UPDATE, etc
    phold int4   # number of bind_values
 );
[download]

Query field omitted for brevity. It has (wouldya guess) the SQL query.

 mydb=# select label,cmd,columns,tables,phold from sql_catalog;
      label     |  cmd   | columns                          | tables  
+| phold 
 ---------------+--------+--------------------------------------------
+-------
 weather_hi    | SELECT | weather.city,weather.date        | weather |
+     1
 hi_and_low    | SELECT | weather.city                     | weather |
+     2
[download]

AUTHOR

T. M. Brannon, <tbone@cpan.org>