Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Re: creating an array of bound database fields from a SQL statement

by perrin (Chancellor)
on Jun 12, 2009 at 13:45 UTC ( #770970=note: print w/ replies, xml ) Need Help??


in reply to creating an array of bound database fields from a SQL statement

SQL::Statement should give you what you want.


Comment on Re: creating an array of bound database fields from a SQL statement
Re^2: creating an array of bound database fields from a SQL statement
by plonk (Novice) on Jun 12, 2009 at 18:46 UTC
    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;

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://770970]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (14)
As of 2015-07-06 21:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (83 votes), past polls