in reply to Re: Learning DBI
in thread Learning DBI

Like I said, I've read through the tutorials here at perlmonks.

I need help right down to the syntax. It's mostly greek to me.

I connect to the DB ok, but that's where my success seems to end.
here's a snipet (keep in mind I have little idea what I'm doing here): 
my $sth = $dbh->prepare( qw/ SELECT date, title, entry FROM News / );

$sth -> execute() or die "Couldn't execute statement: $DBI::errstr; st
+opped";

while ( my ($date, $title, $entry) = $sth->fetchrow_array() ) {
        print STDOUT "Date: $date  Title: $title  Entry: $entry\n";
}

[download]
I'm just trying (at this point) to read the three fields (date, title, and entry) from every row of the News table in this database.

Replies are listed 'Best First'.
Re: Re: Re: Learning DBI
by gjb (Vicar) on Nov 07, 2002 at 21:40 UTC

    You're setting up a SELECT SQL statement selecting date, title and entry fields from a table called News. This is what prepare does. In the next statement this gets executed, i.e. the query is actually submitted to the database. The while loops over the rows fetched from the database and prints them out.

    Of course, your database should have a table News with fields date, title and entry to start with.

    First you should check what tables have been defined, if any. This can be done using the mysql client that comes with mysql, or even by just peeking into the file system (if memory serves). There's also an SQL query to do this, but I don't remember it of hand.

    Hope this helps, -gjb-

    Update: You really don't want qw since this evaluates to a list of words while prepare is expecting just a single string. Use q or qq instead.

[reply]
[d/l]
[select]
Re: Re: Re: Learning DBI
by rdfield (Priest) on Nov 08, 2002 at 10:01 UTC
    Actually, it looks like your problems are a bit more fundemental than just DBI - your using the quoting operator qw to set up your SQL. That's probably not what you want. Please have a look at perlop, specifically the part about "Quote and Quote-like Operators".

    rdfield

[reply]
[d/l]
Re: Re: Re: Learning DBI
by Bishma (Beadle) on Nov 07, 2002 at 21:47 UTC
    I get the following error when the above code runs:
    DBI prepare: invalid number of parameters: handle + 6

    Thanks for the translation, it helps. Tell me if I have this correct:
    The prepare statement stores an address into $sth then the execute command gets the information from the address in the database and translates it back to the data I need. Am I understanding it correctly?
[reply]
[d/l]

      For the syntax error, see my update reply above.

      I'd not say that prepare stores an address. The following is taken from the DBI module docs

      .

      The DBI allows an application to ``prepare'' statements for later execution. A prepared statement is identified by a statement handle held in a Perl variable. We'll call the Perl variable $sth in our examples.

      The typical method call sequence for a SELECT statement is: 

        prepare,
    execute, fetch, fetch, ...
    execute, fetch, fetch, ...
    execute, fetch, fetch, ...
      for example: 
        $sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");
  $sth->execute( $baz );
  while ( @row = $sth->fetchrow_array ) {
    print "@row\n";
  }

      [download]
      The typical method call sequence for a non-SELECT statement is: 
        prepare,
    execute,
    execute,
    execute.
      for example: 
        $sth = $dbh->prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)"
+);
  while(<CSV>) {
    chop;
    my ($foo,$bar,$baz) = split /,/;
        $sth->execute( $foo, $bar, $baz );
  }

      [download]

      You can view prepare as a kind of compilation step. (The INSERT query puts something into the table.

      Hope this helps, -gjb-

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom