Looking for some basic cgi scripts for mysql needed

[Steven E. Protter]

I have a website I'm working on and it needs a database. I've just installed mysql as the database because as usual there is no budget for oracle.

I have actually managed to pull some sql off a google search and created the database on the sql database (4.0.x from software.hp.com).

I have the CGI module installed. I need a couple of simple perl scripts that I can modify for my own purposes to perform the following functions:

1) Add a record to the database.
2) remove a record from the database.
3) pull data out of the database (which I will display for my users to edit).

I seem to be finding complex examples and I need something basic so I can program and learn.

Critera: I will take your script and test it. If I can make it perform its function without major modification its a bunny.

I've also been seeing some strange behavior when I put my cqi scripts on my play hp9000 box. Normal perl statements do not work. I think the perl I'm pointing to is very old and needs an upgrade. Here is the ouptut from the perl -v statement.

--------
[8206#] /usr/local/bin/perl5 -v

This is perl, v5.8.3 built for PA-RISC2.0

Copyright 1987-2003, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on
this system using `man perl' or `perldoc perl'. If you have access to the
Internet, point your browser at http://www.perl.com/, the Perl Home Page.

[8207#] swlist -l product | grep -i perl
PHKL_26104 1.0 VX_FREEZE timeout does not work properly
Perl5 D.5.8.0.C Perl for HP-UX
perl 5.8.3 perl
perl2html 0.8 perl2html

---------------

It sure seems like I'm pointing at relatively recent binaries.

If thats true, I'd like an explanation as to why this code works on Linux and not HP-UX back end websites.

-----begin sample code--------
# unless (open(INFILE, "/home/webusers/tehillimsongs/data/text")) {
# die (print "cannot open input file text\n");
# }

ll /home/webusers/tehillimsongs/data/text

-r-xr-xr-x 1 root web 677 Nov 16 21:32 /home/webusers/tehillimsongs/data/text

----

The above code works great on Linux and will not work on hpux.

A bunny for the explanation on that one.

Before anyone recommends it, I'm going to check software.hp.com for a current verson of perl and get it installed and re-test the script. No bunny for that suggestion, I just had that idea.

SEP

[Steven E. Protter]

The plot thickens.

software.hp.com's latest perl release for pa-risc is:

HP-UX 11i PA-RISC version 5.8.2

So how the heck did 5.8.3 get on my system? Obviously I installed it.

I DID NOT install anything from Merijn's website.

Is 5.8.3 stable?

I truly am at a loss as to what to do now.

SEP

[Steven E. Protter]

If its not obvious, I'm asking if I should remove perl 5.8.3 from the 11i v1 system and install 5.8.2.

SEP

[H.Merijn Brand (procura · Accepted solution]

- perl 5.8.3 and up are all stable. That is at least from *my* point of view. I'm now using 5.8.5 in production environment everywhere. I skipped 5.8.4. The main reason for me to upgrade from 5.8.3 to 5.8.5 is a fix in the 'format' stuff in Perl, which nobody else uses anyway (@* now enables code strings like escapes without eating actual width in the line, so one can embed printer controls in reports, like setting bold, underline and moving to specific locations)

- The MySQL stuff I used on HP-UX is too old to serve as reference, but I did not hit any trouble by then

- I never used CGI, on any platfor, yet

--8<---
my $txtfile = "/home/webusers/tehillimsongs/data/text";
-f $txtfile && -s _ && open my $fh, "< $txtfile" or die "invalid text file '$txtfile': $!\n";
whil;e (<$fh>) {
chomp;
# ...
}
-->8---

--8<---
use strict;
use warnings;

use DBI;

# Connect to MySQL database
my %attr = (
RaiseError => 1,
PrintError => 1,
ChopBlanks => 1,
ShowErrorStatement => 1,
);
my $db = exists $ENV{MYSQLDB} ? $ENV{MYSQLDB} : "test";
$dbh = DBI->connect ("DBI:mysql:database=$db", $ENV{LOGNAME}, undef, \%attr) or croak "connect: $!";

# Select from database
my ($code, $desc);
my $sth = $dbh->prepare ("select code, description from table where code > ?");
$sth->bind_columns (\$code, \$desc);
$sth->execute (4);
while ($sth->fetch) {
printf "%6d %s\n", $code, $desc;
}

# Update the database
my $upd = $dbh->prepare ("update table set description = ? where code = ?");
$upd->execute ("Foo", 4);
$dbh->commit;

# insert into the database
my $ins = $dbh->prepare ("insert into table values (?, ?)";
$ins->execute (4, "Foo");
$ins->commit;

# delete from database
my $del = $dbh->prepare ("delete table where code = ?");
$del->execute (4);
$dbh->commit;

$sth->finish;
$upd->finish;
$del->finish;
$ins->finish;
$dbh->disconnect;
-->8---

Enjoy, Have FUN! (And a prosperous 2005) H.Merijn

[Steven E. Protter]

Thanks Merijn,

I will give this stuff a try in my test environment.

Hopefully I can integrate my perl based mail scripts with a database write to save the information. Once I get started I should be fine.

There are bunnies to be had with more working code.

Start off 2005 with a SEP awarded bunny.

:-)... Yeah team.

SEP