Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

OK, OK, I'm taking the DBI plunge. Now what?

by peppiv (Curate)
on Jan 10, 2002 at 21:23 UTC ( #137774=perlquestion: print w/replies, xml ) Need Help??

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

I recently posted a question about querying a text file (which amused a few people) and as tilly pointed out I really need to use DBI.

I understand SQL a little, but not how to query a delimited text file.

Here's at least something to start with, but it's still miles away from a working solution. Please help point me in right direction. (I installed DBI and DBD::CSV on my server - Apache/FreeBSD).

use strict; use DBI; $dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\\|}) or die "Cannot connect: " . $DBI::errstr; $sth = $dbh->prepare("SELECT * FROM info WHERE field_3 = Yes") or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); $sth->finish(); $dbh->disconnect();

Here's the type of file I'm working with (posted again for apparently more amusement)

I have a pipe delimited text file. Looks like this:

Jimmy | yes | | | yes | yes | yes
Robert | yes | yes | | | |
JohnPaul | yes | yes | yes | yes | |
Bonzo | | | yes | yes | yes | |
How can I make a list from multiple field choices?
example - List 1 = those who have "yes" in field #2 but nothing in field #3
List 2 = those who have "yes" in field #2 and field #7
List 3 = "yes" in field #2, nothing in field #3, but yes in field #6

"Thanks for all the fish"

Replies are listed 'Best First'.
Re: OK, OK, I'm taking the DBI plunge. Now what?
by gmax (Abbot) on Jan 10, 2002 at 22:25 UTC
    When you send a request to the DBI ($sth->execute()), you should collect your data, before using $sth->finish().
    Something like the (untested)
    $dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\\|}, {RaiseError => 1} ); # here goes the $sth->prepare .... $sth->execute(); while (my @result = $sth->fetchrow_array()) { print $result[0], ", " # first field $result[1], ", " # second field and so on "\n" } $sth->finish(); $dbh->disconnect();
    Repeat the above proceedings, changing the query, to get the lists that you need.
    I didn't follow the amusement that you are referring to, but I can see that you are trying to solve a problem without appropriate tools.

    Please, allow me to give you some friendly advice:
    1 If you ask for help, try to give some information about what you want to achieve. From the little you are saying, we see that you want to use the DBI to query text files. Everyone with some experience of database will tell you that this is not a sound idea. You don't leave us a choice. You present the problem whose only solution is to munge a text file, thus preventing some volunteer from pointing to some alternative path.
    2 Using a tool like the DBI makes sense if you want to store data in a database and then retrieve it. Putting it up only for querying a CSV file could be seen as overkill. Again this is a result of not knowing enough about your problem.
    3 If you don't have to store the information somewhere else, just parse the text file with split or using Text::xSV (see tilly's node and the module itself for more info) and use the resulting lists.
    4 If you really must use the DBI, try to organize your application to have the data supplied in a more sensible way. If you have no choice but to use this file format, try to make it just temporary, and use a real database for storing data.

    How can I make a list from multiple field choices?
    SQL accepts multiple clauses in the WHERE statement (eg: WHERE field_1 = "Yes" and field_3 IS NULL). But check the documentation of the DBD driver you are using to see that this syntax is accepted. Some drivers don't allow the IS NULL clause, but want Field_3 = "" instead.
    There is little more I can tell you, without info about what you really want to achieve. If you don't need to store data in a database, and especially if you are not confortable with SQL, you should rely on some paradigm that you know better.
     _  _ _  _  
    (_|| | |(_|><
      Thanks gmax for the advice. I do plan on putting this into a real database sometime soon. I thought that since it already was in a text file, I'd learn to DBI query it first as the transition to converting and querying a real database might be a bit too much to handle in one fell swoop.

      I know I don't always put enough info in my questions. I try to be courteous and keep it short and simple.

      thanks for the replies
      if time is it safe to say it's five dollars past three hundred?
        Putting this into a database should be trivial once you are ready to do so. bcp is your friend (use "|" as your field terminator and "\n" as your row terminator and you're golden)
Re: OK, OK, I'm taking the DBI plunge. Now what?
by cfreak (Chaplain) on Jan 10, 2002 at 21:56 UTC

    Are you getting an error with this code? If so we can help you more if you post it.

    Just looking through it: you seem to be headed in the right direction. The only thing that i see is that you need single quotes around your "Yes" in your query so it reads:

    SELECT * FROM info WHERE field_3='Yes'

    Actually an even better way to do it is to use place holders, like this:

    SELECT * FROM info WHERE field_3=?

    Then in your execute statement:

    $sth->execute('Yes') or die ....

    The use of place holders is really helpful because it escapes characters that can potentionally cause your query to fail. Not as important with this example since you have a hard-coded string but it is very important when you start using variables.

    UPDATE: Helps to read the last part of the question :)

    To get your data out you need to fetch it. I typically fetch data into array of hash references like this:

    #execute statement here my @data = (); # define an array while(my $row = $sth->fetchrow_hashref()) { push(@data,$row); } # finish and disconnect

    Then its a pretty simple matter to access all your data. Each hash key is the name of the field. So if you wanted the first field of the first entry in your data base you just dereference the hash like so:

    my $field_1 = $data[0]->{'field_1'};

    Of course if you want to process the data all at once you don't really need an array at all:

    Hope that helps

Re: OK, OK, I'm taking the DBI plunge. Now what?
by runrig (Abbot) on Jan 10, 2002 at 22:31 UTC
    Unless the first row of your text file contains the column names, you'll need to use the col_names attribute. See the DBD::CSV docs and examples.
      use strict; use DBI; use CGI::Carp qw(fatalsToBrowser); use CGI qw (:standard); print "Content-type: text/html\n\n"; my $dbh = DBI->connect(qq{DBI:CSV:f_dir=/usr/local/etc/httpd/cgi- +bin/}, {RaiseError => 1} ); $dbh->{'sep_char' => "|", 'col_names' => ["timestamp", "email", " +name", "address", "city", "state", "zip"]}; my $sth = $dbh->prepare( q{SELECT * FROM export_list WHERE state += ? }); my $rc = $sth->execute ('OH'); print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n"; while (my @result = $sth->fetchrow_array()) { print "@result\n"; } die $sth->errstr if $sth->err; $dbh->disconnect;

      I've used the col_names attribute and I don't receive any errors for that line. However I do get this error:
      Attempt to fetch row from a Non-SELECT statement at /usr/local/etc/httpd/cgi-bin/ line 23.(*the fetchrow_array statement line).I'm researching this now. Question - If an error statement comes up for line 23, is it safe to assume that the lines of code before that are executing OK? Assuming you're not jumping around with subs.
        You are not specifying which table you are setting the column names on. Look at the DBD::CSV docs. Your columns should be set like so:
        $dbh->{'csv_tables'}->{'export_list'}={ 'sep_char' => "|", 'col_names' => [qw(timestamp email name address city state zip)]};
        Also, execute returns no useful information on a select statement, so there is no point in saving its return value. On a non-select statement (e.g. update, delete), it returns the number of rows affected.

        And your die() statement is useless since you have RaiseError set. If there is a DBI error, DBI will execute a die() on its own before you ever get to that die statement.

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (3)
As of 2021-06-19 08:26 GMT
Find Nodes?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)

    Results (91 votes). Check out past polls.