question on processing mysql

Discussion in 'Perl Misc' started by Huub, Jun 16, 2006.

  1. Huub

    Huub Guest

    Hi,

    From some perl site I got this (changed some to fit my database):

    $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
    $dbh = DBI->connect($dsn,$username,$password)
    or die "Connection Error: $DBI::errstr\n";
    $record = 101;
    $sql = "select voorvoeg,naam from hvw where lidnr = $record";
    $sth = $dbh->prepare($sql);
    $sth->execute or die "SQL Error: $DBI::errstr\n";
    @row = $sth->fetchrow_array;
    print "Naam: @row\n";

    This works OK. However, when I put the lines "$sql = " and further in a
    (while-)loop it doesn't do anything. Is this correct?
    Also I have tried to read all database fields at once, but that didn't
    work either, since "prepare" seems only to accept 1 parameter. How can I
    do this effectively/efficiently?
    Hints or directions on where to find this info are appreciated. Tried to
    search on CPAN, but I don't know what to look for exactly.

    Thanks for helping out,

    Huub
     
    Huub, Jun 16, 2006
    #1
    1. Advertising

  2. Huub

    Ch Lamprecht Guest

    Huub wrote:
    > Hi,
    >
    > From some perl site I got this (changed some to fit my database):
    >
    > $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
    > $dbh = DBI->connect($dsn,$username,$password)
    > or die "Connection Error: $DBI::errstr\n";
    > $record = 101;
    > $sql = "select voorvoeg,naam from hvw where lidnr = $record";
    > $sth = $dbh->prepare($sql);
    > $sth->execute or die "SQL Error: $DBI::errstr\n";
    > @row = $sth->fetchrow_array;
    > print "Naam: @row\n";
    >
    > This works OK. However, when I put the lines "$sql = " and further in a
    > (while-)loop it doesn't do anything. Is this correct?
    > Also I have tried to read all database fields at once, but that didn't
    > work either, since "prepare" seems only to accept 1 parameter. How can I
    > do this effectively/efficiently?
    > Hints or directions on where to find this info are appreciated. Tried to
    > search on CPAN, but I don't know what to look for exactly.


    The documentation for the DBI module should be available on your system:

    'perldoc DBI'

    HTH,
    Christoph

    --

    perl -e "print scalar reverse q//"
     
    Ch Lamprecht, Jun 16, 2006
    #2
    1. Advertising

  3. Huub <> wrote:

    > $record = 101;
    > $sql = "select voorvoeg,naam from hvw where lidnr = $record";
    > $sth = $dbh->prepare($sql);
    > $sth->execute or die "SQL Error: $DBI::errstr\n";
    > @row = $sth->fetchrow_array;
    > print "Naam: @row\n";
    >
    > This works OK. However, when I put the lines "$sql = " and further in a
    > (while-)loop



    If you are having a problem with code that contains a while loop,
    then you should post code that contains a while loop.

    We cannot (generally) debug code that we cannot see.


    > it doesn't do anything. Is this correct?



    That depends on _why_ you put those lines in a loop. (and how
    you coded the loop)

    If you want to get the 1st result from different queries (ie. you
    plan to have multiple values for $record), then putting those lines
    in a loop is correct.

    If you want to get all of the results from one particular query,
    then you would put only the fetchrow_array() and print() parts
    in a loop.


    > Also I have tried to read all database fields at once, but that didn't
    > work either,



    How did you try to do it?

    We cannot (generally) debug code that we cannot see.


    > since "prepare" seems only to accept 1 parameter.



    That is fine, since 1 parameter is all you need:

    $sql = "select * from hvw where lidnr = $record";


    > How can I
    > do this effectively/efficiently?



    You have not told us what it is that you _want_ to happen, that is,
    we do not know what "this" you are asking about.

    You should use placeholders (or bind variables) in your queries
    so that the DBI can handle proper quoting for you:

    $sql = 'select * from hvw where lidnr = ?';
    ...
    $sth->execute($record) or die "SQL Error: $DBI::errstr\n";


    > Hints or directions on where to find this info are appreciated.



    Read the documentation for the modules that you use:

    perldoc DBI

    contains this code:

    $sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");

    $sth->execute( $baz );

    while ( @row = $sth->fetchrow_array ) {
    print "@row\n";
    }

    Which is probably what you meant to ask for.


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
     
    Tad McClellan, Jun 16, 2006
    #3
  4. Huub

    Huub Guest

    Ok, found the problems....had $ where I should use @ and used the wrong
    recordnumber.
    Other question: which function should I use to print to a printer?
    FileHandle or IO:: ?
     
    Huub, Jun 16, 2006
    #4
  5. Huub

    Anno Siegel Guest

    Huub <"v.niekerk at hccnet.nl"> wrote:

    > Ok, found the problems....had $ where I should use @ and used the wrong
    > recordnumber.
    > Other question:


    If you have a new question, please start another thread with a
    meaningful subject.

    > which function should I use to print to a printer?
    > FileHandle or IO:: ?


    These aren't functions, they are modules (with benevolent interpretation
    of "IO::"). What makes you think it makes a difference which one you
    use specifically with a printer?

    Anno
     
    Anno Siegel, Jun 19, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JL
    Replies:
    0
    Views:
    1,151
  2. Ravi
    Replies:
    6
    Views:
    1,414
    Suchandra Thapa
    Jul 21, 2003
  3. Hubert Hung-Hsien Chang
    Replies:
    2
    Views:
    517
    Michael Foord
    Sep 17, 2004
  4. Nico Grubert

    Processing a MySQL Dump file with Python

    Nico Grubert, Oct 4, 2004, in forum: Python
    Replies:
    1
    Views:
    728
    Jeffrey Froman
    Oct 4, 2004
  5. Nico Grubert
    Replies:
    1
    Views:
    355
    Alex Martelli
    Oct 4, 2004
Loading...

Share This Page