question on processing mysql
 

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

Re: question on processing mysql
 

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/ed.enilno@ergn.l.hc/"

Re: question on processing mysql
 

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
tadmc@augustmail.com Perl programming
Fort Worth, Texas

Re: question on processing mysql
 

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:: ?

Re: question on processing mysql
 

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