Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > question on processing mysql

Reply
Thread Tools

question on processing mysql

 
 
Huub
Guest
Posts: n/a
 
      06-16-2006
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
 
Reply With Quote
 
 
 
 
Ch Lamprecht
Guest
Posts: n/a
 
      06-16-2006
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/(E-Mail Removed)/"
 
Reply With Quote
 
 
 
 
Tad McClellan
Guest
Posts: n/a
 
      06-16-2006
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
http://www.velocityreviews.com/forums/(E-Mail Removed) Perl programming
Fort Worth, Texas
 
Reply With Quote
 
Huub
Guest
Posts: n/a
 
      06-16-2006
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:: ?
 
Reply With Quote
 
Anno Siegel
Guest
Posts: n/a
 
      06-19-2006
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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Post-Processing RAW vs Post-Processing TIFF Mike Henley Digital Photography 42 01-30-2005 08:26 AM
DBD:mysql doesn't read mysql option file /etc/my.cnf file JL Perl 0 01-28-2005 03:19 AM
Re: Processing a MySQL Dump file with Python Nico Grubert Python 1 10-04-2004 04:59 PM
Processing a MySQL Dump file with Python Nico Grubert Python 1 10-04-2004 02:32 PM
Question: processing HTML, re-write default processing action of many tags Hubert Hung-Hsien Chang Python 2 09-17-2004 03:10 PM



Advertisments