Perl Trick for Doing "Exec Immediate" SQL Command Strings

Quite often I see a lot of string concatenation statements being used to build the string for an SQL command, e.g. SQL SELECT statement:

        $Stmt = "select *\n"
        . "    from $UserTable\n"
        . "    where $QueryColumn = '$UserInput'";

This gets much worse for a long INSERT statement with 10 columns.

Suggestion is to use Perl HERE-Doc literal:

        my $SQL_String = <<__END_OF_HERE_DOC__;
        select *
        from  $UserTable
        where $QueryColumn = '$UserInput'
__END_OF_HERE_DOC__

Sample ODBC Code

use strict;
use Win32::ODBC;

#-- Depending on your version of Win32::ODBC, you may need to do
#-- something like the following to suppress -w warnings.
$Win32::ODBC::ODBCPackage = $Win32::ODBC::ODBCPackage;
$ODBCPackage::Version     = $ODBCPackage::Version;

my $Message = '';
my $Column  = '';
my $Value   = '';
my %HashRow = ();
my $db      = '';
my $Source  = 'DSN=Sybase System 11;UID=sa;PWD=';

if ($db = new Win32::ODBC($Source)) {
    print STDERR "\nODBC connection successful for data source $Source.\n\n";
} else {
    $Message =  Win32::ODBC::Error();  #-- use when no $db object ref.
    print STDERR "\nMessage: $Message\n";
    print STDERR "ERROR: ODBC error during connection\n";
    exit;
}

my $TableType = 'U';
my $Sql = "SELECT name, id FROM sysobjects where type = '$TableType'";

if ( $db->Sql($Sql) ) {
    #-- Error on Sql call returns error code else UNDEF for success.
    #-- Note reverse of error handling on "new" method.
    $Message = $db->Error();
    print STDERR "\nERROR:  $Message\n";
    exit;
}

#-- Establish loop to process each row in the result set for the SELECT.
while ($db->FetchRow()) {
    #-- Get one row from result set for SELECT statement and
    #-- then store that row's columns as one hash. Hash key is
    #-- column name and hash value is the column value.
    %HashRow = $db->DataHash();

    #-- Process each column in a row. In this case, just print them.
    while ( ($Column, $Value) = each(%HashRow) )  {
        print STDERR "Column '$Column' = '$Value'\n";
    }
    print STDERR "\n";
}

print STDERR "\n\n*** Create intentional error in SELECT to show error handling:\n";

$Sql = "SELECT name, id FROM sysobjects_misspelled where type = '$TableType'";

if ( $db->Sql($Sql) ) {
    #-- Error on Sql call returns error code else UNDEF for success.
    #-- Note reverse of error handling on "new" method.
    $Message = $db->Error();
    print STDERR "\nERROR:  $Message\n";
    exit;
}

$db->Close() || die Win32::ODBC::Error();

exit;

Older Sybperl Code For SQL SELECT

#-- store SQL statement in command buffer for DB-LIB.
&dbcmd($dbproc, $Query) || return(0);  #-- failure return

#-- send command buffer to SQL Server for processing.
&dbsqlexec($dbproc) || return(0);  #-- failure return

#-- check result for each statement in command batch.
while ( ($RetCode = &dbresults($dbproc)) != $NO_MORE_RESULTS) {

    if ( $RetCode == $SUCCEED ) {

        #-- have successful command so get any rows returned.
        #-- if 2nd command has rows, append to @ResultSet again.
        while ( @ListOfColumnValues = &dbnextrow($dbproc) ) {

            # contat all columns in this row into one array element
            # and add this row (ie column values) to array of rows.
            # In Perl5, would do as list of hashes (hash = one row).
            push(@ResultSet, join($Separator, @ListOfColumnValues) );

        } #-- while dbnextrow

        #-- if current statement really effects row count, save it.
        $TempRowCount = &DBCOUNT($dbproc);
        $RowCount = $TempRowCount if $TempRowCount != -1;

    } else {
        #-- signal error for whole batch
        &dbcancel($dbproc);         #-- make sure no lingering results
        return(0);                  #-- failure return
    } #-- end of IF success for command
}

The Source: http://reference.perl.com

Database Vendor Portability -- Unix and Arriving on Win32 / NT

DBI -- Module. The Database Interface. The Perl DBI initiative has standardized the interface to a number of commercial database engines, so that you can move from, say, Oracle to Sybase with a minimum of effort.

DBD -- Database Drivers for DBI module.

  DBD::DB2
  DBD::Oracle
  DBD::Ingres
  DBD::Informix
  DBD::Sybase
  DBD::QBase
  DBD::mSQL
  DBD::CSV
  DBD::Fulcrum

DBD::Net -- A Perl module for remote database access.

Database Vendor Portability -- Limited to Win32 / NT

Win32::ODBC A Perl 5 Win32 extension that provides access to ODBC API.
ADO/OLE Access to ODBC and OLE servers with native support.

Specific to One Database Vendor (portability decision)

Ingperl When perl is combined with CA-Ingres SQL, the result is a simple yet graceful and dynamic query and data manipulation language, called ingperl.
Msql::RDBMS A relational database management system for Msql, using HTML forms as an interface. Requires CGI-modules, CounterFile, and of course the Msql module.
Msql The MsqlPerl interface between Perl and the mSQL relational database management system. MSSQL::DBlib and MSSQL::Sqllib are two Perl classes for accessing Microsoft SQL Server from Perl.
Mysqlperl The MysqlPerl interface between Perl and the MySQL relational database management system.
ObjStore Easy, flexible, lightning-fast persistent via ObjectStore DBMS.
Pg Perl interface to Postgres, a freely available relational database engine.
PostgreSQL Freeware. A robust, next-generation, Object-Relational DBMS (ORDBMS), derived from the Berkeley Postgres database management system.
Sprite Module to maniuplates simple text-delimited databases.
Sybperl Package of extensions to Perl that basically add the Sybase db_library API calls to the Perl language.

Other Useful Web References