Time:
Scotland: Wed, 2:41 pm
Rhode Island: Wed, 9:41 am
Florida: Wed, 9:41 am
New Mexico: Wed, 7:41 am
California: Wed, 6:41 am
Buy this Ad Space. 180px wide. Please get in touch with KH@ if you are interested and make an offer. 
US$10 per year - Save $100s!
The Fabulously Unfair
WebX Auction Bar. For Ebay etc.
Ro-Sham-Bo the opposition. Laugh like Eric Cartman when you win! CLICK HERE NOW!More information and sign-up.
WebXpertz Hosting. Custom fit from $5pm. PHP/MySQL You'll save money, we'll save money. Seems fair to me. Interested? If so Please PM me here and tell me what you need. Thanks!
|
For continued disscussion on this topic : Getting connected with Perl...
boywonder 08-01-2002, 02:32 PM I'm trying to read my database but somehow it seems that it won't work... any ideas what might be wrong??
#!/usr/bin/perl
#!C:\PERL\bin\perl.exe
#
#
#
#####################################################
use DBI;
##### html-sivujen vaatima lause
print "Content-type:text/html\n\n";
##### html sivu
print "
<html>
<head>
<title>MENU - PÄÄTASO</title>
<link rel='stylesheet' type='text/css' href='http://www.loota.com/style.css'>
</head>
<body>
<h1>testisivu</h1>";
#### TAULUNLUONTIYRITYS
my $sqlCode = "
DROP TABLE Postinumero;
CREATE TABLE Postinumero (
Postinumero CHAR(5) NOT NULL,
Postitoimipaikka VARCHAR(64) NOT NULL
);
";
my $dbh = DBI->connect("DBI:mysql:database=mytestdb;host=localhost","user", "passwd");
my $cursor = $dbh->prepare($sqlCode);
$cursor->execute();
$cursor->finish;
##### KAMANLAITTOYRITYS
$sqlcode ="INSERT Postinumero (Postinumero, Postitoimipaikka) VALUES( '123', 'Halssuva');";
$cursor = $dbh->prepare($sqlCode);
$cursor->execute();
$cursor->finish;
##### KAMANLUKUYRITYS
$sqlcode = "SELECT Postinumero, Postitoimipaikka FROM Postinumero";
$cursor = $dbh->prepare($sqlCode);
$cursor->execute();
print "<h3>kamat</h3>\n";
my @row;
while (@row = $cursor->fetchrow_array())
{
my ($a, $b) = @row;
print "$a $b<br>\n";
}
$cursor->finish;
$dbh->disconnect;
print "</body></html>";
boywonder 08-02-2002, 08:51 AM Doesnt' anyone know how to use MySQL with Perl?? :( Or can't you even give a good tutorial about Perl/MySQL?? :confused:
Phineus 08-02-2002, 04:01 PM >Doesnt' anyone know how to use MySQL with Perl?
Calm down. First problem I see is here:
#### TAULUNLUONTIYRITYS
my $sqlCode = <<SQL;
DROP TABLE Postinumero;
CREATE TABLE Postinumero (
Postinumero CHAR(5) NOT NULL,
Postitoimipaikka VARCHAR(64) NOT NULL
);
SQL
You also use a similar method of quoting in your ##### html sivu part. I'm not sure that's valid. I'll retract if it is.
Benahimvp 08-03-2002, 02:53 AM Well I see a couple of problems. First off, is what Phineus mentioned. I'm fairly sure that you cannot store
multiple lines into a variable the way you're doing it. The way Phineus did it is definitely the correct way
and I'm sure it'll work because I do it that way. The second problem I see is that you end your SQL statements with semicolons.
DBI does that for you. It automatically ends your queries so you don't have to put semicolons. This brings me to the the next problem.
Since it automaticallly ends queries for you, you can only store, prepare, and execute one query at a time. So in your code
where you drop and create, you'll have to split that up. Fourth, why do you have two shebang lines? One looks like one for a
UNIX-based system and the second looks like it's for Windows.
I'm assuming that you have properly loaded all the modules that you'll need to perform queries to a MySQL database via Perl.
Most of the modules come packaged with newer versions of Perl, but I'm pretty sure you have to get the MySQL DBI module yourself.
Anyway, because I went through some difficultly working with MySQL via Perl, I'm going to show you how I connect and it works perfectly.
It involves creating a subroutine file called common.sub. This file contains common subroutines that you use in your Perl scripts.
There'll be a connection sub and a execute sub within common.sub. All you have to do is require it in your main Perl scripts.# common.sub file
sub connect {
use DBI;
$DSN = "DBI:mysql:$db_name";
$user = "user";
$pw = "pass";
$dbh = DBI->connect($DSN,$user,$pw,{RaiseError => 1, AutoCommit => 1}) || die("Cannot connect: $DBI::errstr\n");
return;
}
sub Do_SQL {
$sth = $dbh->prepare($SQL);
$rv = $sth->execute;
return $sth;
}
1;First I'll explain the connect subroutine. It sets different variables that are actualled used in the connection process.
$db_name is actually passed globally (I know it's wrong but I do it anyway) from the Perl script. I have more than one database,
so this way I can use the same subroutine to connect to all of them. Also, take a look at the connection process.
I have RaiseError which displays any errors that occur while trying to connect, prepare, or execute. The errors will be just like
the ones you'd get from the command line. The Do_SQL subroutine is farily straightforward. $SQL is the actual SQL statement
passed globally as well. The 1; at the very end of the file is very important because required files always have to return a value.
Anyway, on to a sample script:# Sample Perl script
#!/perl/bin/perl
use CGI;
require "../common.sub"; #relative path to common.sub file
$query = new CGI;
$db_name = "mytestdb";
&connect; #connect to MySQL database
&selecttbl; #subroutine that's going to select info
$dbh->disconnect;
sub selecttbl {
print $query->header;
$SQL =<<SQL
SELECT Postinumero, Postitoimipaikka
FROM Postinumero
SQL
&Do_SQL;
while ($pointer = $sth->fetchrow_hashref) {
$postinumero = $pointer->{'postinumero'};
$postitoimipaikka = $pointer->{'postitoimipaikka'};
print "$postinumero - $postitoimipaikaa<br />";
}
}You should really get into the habit of putting blocks of codes in subroutines instead of just having one long page of running code.
It organizes the script much better and it makes it look cleaner. Plus you can just look at the top and see all that the script does
by the subroutines. Also, having the connection, preparation, and execution of the queries outside the actual script
makes things a little more cleaner as well. All you need is &Do_SQL;.
Just to let you know, you should wait at least a day or two before complaining that nobody is helping you.
You have to remember that nobody has to help you. Maybe the people who actually could help you out hadn't made it to the board yet...
boywonder 08-04-2002, 08:48 AM *calming down* - okay. thank you guys. :) It is just so that MySQL gets me soooo enthusiastic ;)
Well anyway - a brief explanation: I don't have my own server and I just copied this stuff from... somewhere :) Now, thankyouthankyouthankyou. No hard feelings. :p
boywonder 08-04-2002, 09:11 AM (my fourthhunderd message):
okay I tried your code and made some changes.
Here's the attachment...
(somehow I can't make it work :()
Phineus 08-05-2002, 07:44 AM I don't think I'd use sub routines for something this basic. And I probably wouldn't drop and re-create a table every time I ran a script. Anyway, here's what I came up with. This assumes a database exists in the first place.
#!/usr/bin/perl
use DBI;
$dsn = "DBI:mysql:myDatabase:localhost";
$user = "myUsername";
$pw = "myPassword";
sub connect {
$dbh = DBI->connect($dsn,$user,$pw,{RaiseError => 1});
}
sub Do_SQL {
$sth = $dbh->prepare($SQL);
$rv = $sth->execute;
return $sth;
}
sub droptbl {
$SQL = <<SQL;
DROP TABLE Postinumero
SQL
&Do_SQL;
}
sub createtbl {
$SQL = <<SQL;
CREATE TABLE Postinumero (
Postinumero CHAR(5) NOT NULL,
Postitoimipaikka VARCHAR(64) NOT NULL
) TYPE=MyISAM;
SQL
&Do_SQL;
}
sub inserttbl {
$SQL = <<SQL;
INSERT Postinumero (Postinumero, Postitoimipaikka) VALUES( '123', 'Halssuva')
SQL
&Do_SQL;
}
sub selecttbl {
$SQL = <<SQL;
SELECT Postinumero, Postitoimipaikka
FROM Postinumero
SQL
&Do_SQL;
print "Content-type:text/html\n\n";
print "<b>starts here</b>:<br><br>";
while ($pointer = $sth->fetchrow_hashref) {
$postinumero = $pointer->{'postinumero'};
$postitoimipaikka = $pointer->{'postitoimipaikka'};
print "$postinumero - $postitoimipaikaa<br>";
}
print"<br><br><b>done</b>";
}
&connect; #connect to MySQL database
&droptbl; #drops table
&inserttbl; #adds stuff
&selecttbl; #subroutine that's going to select info
$dbh->disconnect;
Good luck.
boywonder 08-05-2002, 11:39 AM Thanks for your help, but I'm still out of luck. :(
"And I probably wouldn't drop and re-create a table every time I ran a script. Anyway, here's what I came up with. This assumes a database exists in the first place." --> Yes, I agree - this is just because I want to learn how to use this :)
I got Internal server error 501 when tried that script of yours... I only added &createtbl; after the line &droptbl; #drops table... ???
boywonder 08-07-2002, 03:43 PM I asked it from the server admins, it's all clear now. Thank you all. :)
Phineus 08-08-2002, 08:26 AM So what was the answer? Jeez, man, you make people jump through hoops then keep the answer a secret.
boywonder 08-08-2002, 08:40 AM oh, sorry: well as I said: I asked the server admins and the result was:
#!/usr/bin/perl
print "Content-type:text/html\n\n";
print "<b>starts</b><br>";
use DBI;
my $dbh = DBI->connect("DBI:mysql:database=mydb;host=localhost", "user","passwd");
my $sth = $dbh->prepare("CREATE TABLE testitaulukko (id INTEGER NOT NULL, name VARCHAR(64) NOT NULL)");
$sth->execute;
my $sth = $dbh->prepare("INSERT INTO `testitaulukko` VALUES ('3', 'alakatsama') ");
$sth->execute;
my $sth = $dbh->prepare("INSERT INTO `testitaulukko` VALUES ('4', 'esa vainio') ");
$sth->execute;
my $sth2 = $dbh->prepare("SELECT * FROM testitaulukko");
$sth2->execute;
while(my $ref = $sth2->fetchrow_hashref()) {
$id = $ref->{'id'};
$name = $ref->{'name'};
print "$id, $name<br>";
}
# pois...
$dbh->disconnect();
print "<br><b>ends</b>";
And again: thank you all :)
Theres some links to tutorials on DBI at:
http://www.wush.net/resources/db.shtml
Pay special attention to the link to Placeholders and Bind Values, as that will make your code Super Special
vBulletin® v3.6.4, Copyright ©2000-2009, Jelsoft Enterprises Ltd.
|
