question on processing mysql

H

Huub

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
 
C

Ch Lamprecht

Huub said:
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
 
T

Tad McClellan

Huub said:
$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.
 
H

Huub

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

Anno Siegel

Huub said:
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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,900
Latest member
Nell636132

Latest Threads

Top