<caveat> while not strictly a DBI issue, I'm going to kick this off here, since its pretty relevant (ala SQL::Statement). If I'm out of line, or should restrict this to one or the other lists, please advise </caveat>
A couple years ago, someone posted an RFC and some questions about building an Inline::SQL module. Alas, while I was pretty enthused about it, it never seemed to get off the ground.
Of late, that particular scratch has needed some serious itchin' on my part, esp. since I've been playing with Inline::C, and some Acme modules. So, I've sketched out some POD to get the ball rolling. I'm looking for some feedback, esp related to
- embedded SQL syntax for various databases - possible feature requests/needs for optimal Perl integration - alternate namespace suggestions - possible problems with the syntax I've sketched out in the POD (its certainly possible that pluggable database-specific syntax parsing may need to be accomodated, like DBD::AnyData)
While I won't be able to do serious development for some time, its the sort of thing I can work on to clear my head when other projects fog my brain. And of course, anyone wanting to pitch is welcome!
=head1 NAME
SQL::Preprocessor - Embed SQL in your Perl (ala SQL preprocessors)
SQL::Preprocessor::Chart - use DBIx::Chart instead of DBI
=head1 SYNOPSIS
use SQL::Preprocessor;
use DBI;
use DBI qw(:sql_types);...some code...
EXEC SQL DECLARE SQLDA $sqlda;
EXEC SQL CONNECT TO 'mysql:database=$database;host=$hostname;port=$port'
AS userid IDENTIFIED BY $password AS myconn; EXEC SQL DECLARE CURSOR mycursor AS
SELECT * FROM mytable FOR UPDATE;EXEC SQL OPEN mycursor;
while ($SQLSTATE eq '00000') {EXEC SQL FETCH mycursor INTO $col1, $col2, $col3, $col4;
if ($col1 > $col4) {
EXEC SQL UPDATE mytable SET col4 = col4 + 100
WHERE CURRENT OF mycursor;
}
} EXEC SQL CLOSE mycursor;
#
# and if you used SQL::Preprocessor::Chart:
#
EXEC SQL
SELECT * FROM mytable
RETURNING LINEGRAPH(*), IMAGEMAP
WHERE WIDTH=500
AND HEIGHT=500
AND FORMAT='PNG'
AND LOGO='myimage.png'
AND X_AXIS='Date'
AND Y_AXIS='Stock Price'
AND MAPNAME='stockmap'
AND SIGNATURE='GOWI Systems, Inc.'
AND SHOWPOINTS=1
AND POINT='opencircle'; open(OUTF, '>mychart.png') || die $!;
binmode OUTF;
print OUTF, $_->[0];
close OUTF; open(OUTF, '>mychart.map') || die $!;
print OUTF, $_->[1];
close OUTF;EXEC SQL DISCONNECT myconn;
=head1 PREREQUISITE MODULES
=over 4
=item
DBI 1.43
=item
Filter::Simple
=item
DBIx::Chart (only for SQL::Preprocessor::Chart)
=item
DBI Driver of the target database system
=back
=head1 DESCRIPTION
A SQL preprocessor for Perl. Via Filter::Simple, EXEC SQL statements are converted into DBI calls to perform the specified SQL operation.
=head2 Background
For the unfamiliar, embedded SQL first appeared shortly after relational databases, primarily for use in COBOL programs. In recent years, it is most frequently used with C or C++, and even more recently, with Java, via SQLJ. And, now, with Perl!
=head2 Behaviors
A few usage notes...
=head3 Supported SQL Syntax
Pretty much whatever DBI can consume, plus the following extensions:
=over 4
=item BEGIN DECLARE SECTION
Begins a section of declarations to be used as either placeholder values, or as values to receive results.
=item CLOSE cursorname
Closes the specified cursor.
=item CONNECT TO dsn [ USER user [ IDENTIFIED BY password ] [ WITH attributes ] [ AS name ]
Connects to the specified DSN as user, password, with an optional attributes hash, and optionally assign the given name.
DSN's are the usual DBI suspects, although the 'dbi:' prefix is optional.
=item DECLARE CURSOR name AS select-statement [ FOR UPDATE ]
Declares a cursor, possibly updatable.
=item DECLARE SQLDA
Specifies a variable to be used to receive DESCRIBE information for static executions.
=item DESCRIBE statement-name INTO hash
Deposits the DESCRIBE information for the named PREPARE'd statement.
=item DISCONNECT [ name ]
Disconnects either the current connection, or the specified connection.
=item END DECLARE SECTION
Terminates the DECLARE SECTION started by BEGIN DECLARE SECTION
=item EXECUTE name [ USING variable-list ] | [ USING DESCRIPTOR sqlda ]
Executes the PREPARE'd statement, optionally supplying a variable-list or a SQLDA for input placeholder data
=item EXECUTE IMMEDIATE statement
Immediately executes the specified statement, which is supplied as either a string expression
=item FETCH cursorname [ INTO variable-list ] | [USING DESCRIPTOR sqlda ]
Fetches the current row of the specified cursor into either the specied variable list, or the specified sqlda.
=item OPEN cursorname [ INTO variable-list ] | [USING DESCRIPTOR sqlda ]
Opens the specified cursorname, optionally using the specified variable-list or sqlda to supply input placeholder data.
=item POSITION cursorname ...
Position cursor (TBD)
=item PREPARE statement-name [ INTO sqlda ] FROM statement
Prepares the specified statement, supplied as a string expression, assigning it the specified statement-name, and optionally placing the result set information into the specified sqlda.
=item REWIND cursorname
Positions the specified cursor at the first row of the cursor's result set.
=item SELECT expression INTO variable-list
Selects a single row of data into the specified variable list.
=item SET CONNECTION name
Sets the current connection to the named connection. The current connection is always used for succeding EXEC SQL statements.
=item WHENEVER condition action
Defines an exceptions handler. Conditions may be any of SQLERROR, NOT FOUND, or SQLWARNING. Actions may be any of CONTINUE, GOTO label-expr, PERFORM expression, CALL function. expression is either a Perl block, or single Perl statement. function is either a Perl function call, or an arrayref consisting of a closure, and any arguments to the closure.
=back
=head3 SQLDA
TBD
=head3 $SQLSTATE, $SQLCODE, $SQLMSG
Implicit variables assigned after any database statement execution.
=head3 Default Targets for SELECT/FETCH Operations
If FETCH or SELECT are specified without an expicit INTO Clause, the results are returned in $_ as a hashref, with the column names as keys. Furthermore, SELECT may return multiple rows, in which case $_ is an arrayref of rows, each of which is a hashref of columns.
=head3 Dynamic SQL
TBD
=head3 Parameter Binding
TBD
=cut
