Thanks for the nudge in the right direction - I have taken another look at SQL::Statement and I can extract the fields names requiring data binding for insert statements. But for SELECT and UPDATE statements my problem is that I don't understand how to recursively trawl through the 'where' clause to dig out the 'param' fields. I enclose a sample SQL text file and a sample perl program - can you help?
/* SQL Library File
used via the Perl Module SQL::Library
Refer to the SQL::Library documentation before modifying this file
*/
[Get_Client_Master_Limits_Brief]
/* gets the master limits given a client id */
SELECT C.CLIENT_ID,
C.CLIENT_NAME,
C.CLIENT_SHORTCODE,
M.MASTER_ID,
M.MASTER_NAME,
CT.COMFIN_TYPE_ID,
CT.COMFIN_TYPE_NAME,
ML.MASTER_LIMIT_AMOUNT,
CU.CURRENCY_CODE
FROM TBL_CLIENT C,TBL_MASTER M, TBL_MASTER_LIMITS ML, TBL_COMFIN_TYPE
+CT, TBL_CURRENCY CU
WHERE ML.MASTER_LIMIT_COMFIN_TYPE_ID=CT.COMFIN_TYPE_ID
AND ML.MASTER_LIMIT_MASTER_ID = M.MASTER_ID
AND ML.MASTER_LIMIT_CURRENCY_ID = CU.CURRENCY_ID
AND M.MASTER_ID=C.CLIENT_MASTER_ID
AND C.CLIENT_ID=?
[Client_Contact_Save]
/* add a contact to the trade */
INSERT INTO TBL_TRADE_CLI_CON
(TRADE_CLI_CON_TRADE_ID,
TRADE_CLI_CON_CC_ID)
VALUES
(?,?)
[User_Update_Password]
/* updates tbl_users with a password change */
UPDATE TBL_USERS
SET USERS_CURRENT_PWD = ?,
USERS_LAST_PWD_CHG_DATE = ?,
USERS_LOGIN_EXPIRE_DATE = ?,
USERS_GRACE_LOGIN = ?
WHERE USERS_ID = ?
and my test perl program...
use strict;
use SQL::Library;
use SQL::Statement;
use SQL::Parser;
use SQL::Eval;
use Data::Dumper;
#text file which has the SQL statements
my $sqlfile =q{TEST_SQL.TXT};
#use SQL::Library::BEGIN to read in the statements
my $sqllib = new SQL::Library { lib => $sqlfile };
#create a new SQL::Parser object
my $parser = SQL::Parser->new();
$parser->{RaiseError}=0;
$parser->{PrintError}=0;
#process each SQL Statement in turn (to test which ones do or do not w
+ork)
for my $sql_stmt_name($sqllib->elements){
print "Processing $sql_stmt_name\n";
my $sql_stmt=$sqllib->retr($sql_stmt_name);
next if($sql_stmt=~/\[\%/); #some statement use template variables
+ and are therfore not ANSI standard
#parse the statement
my $stmt;
eval{
$stmt = SQL::Statement->new($sql_stmt,$parser)
}; next if $@; #some SQL statements can't be parsed (e.g BEGIN TRA
+N )
next if(! scalar($stmt->params)); # we are not intersted if there
+are no placeholders
my @bind_field_names=(); #array to hold the field names which map
+to the placeholders
if($sql_stmt=~m{^\s*INSERT\s*INTO | #insert statement
^\s*UPDATE\s* #update statement
}ixms){
my @icolumns=map{$_->name}$stmt->columns();
my @ivalues=$stmt->row_values();
for my $n(0..$#ivalues){
if($ivalues[$n] eq q{?}){
push @bind_field_names, $icolumns[$n];
}
}
}
if(scalar($stmt->params) == scalar(@bind_field_names)){
# we got all the bind field names
print q{OK: SQL Statement }.$sql_stmt_name.q{ has the followin
+g fields requiring data binding:}.join(q{,},@bind_field_names).qq{\n}
+;
}else{
print q{FAILED: SQL Statement has }.scalar($stmt->params).q{ b
+indings and we have }.scalar(@bind_field_names).qq{ fields!\n };
# the number of bind fields is less than the place holders -
+we probably have some
# placeholders in the 'WHERE' clause(s).
my $where = $stmt->where();
print Dumper($where); #how do I extract the fields with param
+eter bindings???
}
}
exit;
| 