Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Querying from a file

by rocky13 (Acolyte)
on Jan 27, 2011 at 02:25 UTC ( #884466=perlquestion: print w/replies, xml ) Need Help??

rocky13 has asked for the wisdom of the Perl Monks concerning the following question:

Is there a better way to use a column of data in a query? Because the following way will run the query the same number of times as the number of row in the first columns. There has to be a better way.

open <R, "<$file_col1.xls"> or die "Can't open file"; while (my $line = <R>) { chomp($line); $ref = (split(/\t/, $line)[0]); my $sql = (select, i.e_id, i.num, i.val, m.code from pro..icon, + emp..main m where = m.code, m.code = '$ref' }

One way is to insert the column(col) into a table(tbl) and use this statement: m.code = tbl..col Is there any other way to do this?

Replies are listed 'Best First'.
Re: Querying from a file
by graff (Chancellor) on Jan 27, 2011 at 04:04 UTC
    Your question is not clear, and there are a lot of mistakes in your code snippet, so I'm not sure if I understand what you want, but I think it's something like this:

    You have a database with a couple tables, and you have a file containing lines of text arranged in columns. You want to run a query on the database tables with an SQL statement whose "where ..." clause uses the first column value from each line of the file. Is that what you mean?

    Running the query once for every line of input from the file might actually be the right idea, if you prepare the SQL statement only one time, and then execute it on each row of input, like this:

    use DBI; my $dbh = DBI->connect( ... ); # get your database handle my $sql = "select, i._eid, i.num, i.val, m.code from ...". " where = m.code and m.code = ?"; # "?" is a "placeh +older" my $sth = $dbh->prepare( $sql ); open( R, "<", $datafile_name ) or die "$datafile_name: $!\n"; while (<R>) { chomp; my ( $ref ) = ( /^([^\t]+)/ ); $sth->execute( $ref ); # value passed to execute() fills the plac +eholder # use one of the DBI methods for getting the query results... }
    There might be other ways to get what you want, but this is the simplest way.

    Another approach would be to create a temporary table with just one field, load it with the values from your data file, and do a single query execution using an sql statement like this, and then drop the temporary table:

    "select ... from ... where = m.code and m.code in (select ref f +rom tmp)"
    One last possibility would be to store your data file values into a hash, run a single query that returns all the available database rows, and filter the rows by checking whether the "m.code" value exists in the hash -- something like:
    my %ref; open( R, "<", $datafile_name ); while (<R>) { if ( /^([^\t]+)/ ) { $ref{$1} = undef; } } my $dbh = DBI->connect( ... ); my $sth = $dbh->prepare( "select m.code, ... from ... where +ode"); $sth->execute; my $rows = $sth->fetchall_arrayref; for my $r ( @$rows ) { my ( $mcode, ... ) = @$r; next unless exists( $ref{$mcode} ); # do something ... }
    As for performance, that depends on things you haven't told us: How many lines are in the data file? How big are the tables, and if they're really big, are they set up to use efficient indexes on the fields that are involved in the query?

    You should probably try the simplest thing first, and if that isn't good enough, either improve the database indexing, or another approach.

Re: Querying from a file
by Generoso (Prior) on Jan 27, 2011 at 03:01 UTC

    Y can read all the file and add

    $sql = ... where = m.code, m.code like (" .'$ref,'

    $sql .= '$ref,',

    and at the end replace the last ',' with ')'

      Unless you have so many lines in that input file that your sql-code exceeds the maximum length your database engine accepts.

      And rather than a LIKE, I'd use the IN function.


      A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://884466]
Approved by planetscape
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (2)
As of 2022-05-20 21:12 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (76 votes). Check out past polls.