asked on

Perl/cgi DBI to mysql database select query with parameter

Just cracked open some perl/cgi on my server and I would like some help coding a simple cgi script that will select some data and return the results to an html page.

Below is the select query:

SELECT order_item.odi_item_ref_id AS ItemName, Count(order_item.odi_item_ref_id) AS ItemCount
FROM (salesagents INNER JOIN (order_item INNER JOIN order_header ON order_item.ord_id = order_header.ord_id) ON salesagents.agent_id = order_header.usr_id) INNER JOIN item_master ON order_item.item_id = item_master.item_id
WHERE (((order_header.ord_status_id)="C") AND ((order_header.ord_confirm_date)>Date()) AND ((salesagents.agent_id)=10051) AND ((item_master.btClub)<>0))
GROUP BY order_item.odi_item_ref_id
ORDER BY Count(order_item.odi_item_ref_id) DESC;

Open in new window

I want the code in the cgi to connect to my mysql database via dbi using the below credentials:
server: 192.168.1.251
user: test
pass: test1234

The cgi will need to accept a user parameter which will be passed into the select query to the field salesagents.agent_id. so the cgi call should look like 192.168.1.250/cgi-bin/report.cgi&agentid=xxxx where xxxx is the agent_id passed to the select in code. Also I'm not sure how to handle the Date() function criteria in the sql within the cgi select.

Please help with the code needed to get this done and return the resultset of the query back to the page.

SOLUTION

Dave Baldwin

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Dalexan

ASKER

Awesome stuff there but how do I code to accept the parameter

SOLUTION

FishMonger

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

roundel35

Like this:

my $min_salary=50000;

my $sql = qq{ select name, salary from employees where salary > ? };
my $sth = $dbh->prepare($sql) || die "Failed to prepare";
$sth->execute($min_salary) || die "Failed to execute";

while (my ($name, $salary) = $sth->fetchrow_array) {
# do what you want with $name and $salary for this employee
}
$sth->finish;

Dalexan

ASKER

Can someone get me started with the code needed to make this happen, I kinda can get the syntax from the perlmonks page but I still need someone to get me started by coding this. Once its coded I can run with it. I can increase the points to 500 if someone codes the whole thing out.

Nem Schlecht

Well, roundel35 has already done this. Look at this line:

my $sql = qq{ select name, salary from employees where salary > ? };

Open in new window

See the "?" in there? That is replaced by the argument is this line:

$sth->execute($min_salary) || die "Failed to execute";

Open in new window

So, when the SQL executes, the "?" will be replaced by the value of $min_salary.

Let's try a slightly more complex example, using a placeholder in an insert. Say you've got a comma separated file with names, ages, and genders of various people. You've got the database all set up, now you want to insert all of your data into your table. Here's a quick way to use placeholders to insert that data:

$sth = $dbh->prepare("
   INSERT INTO people_data
   VALUES (?,?,?)
");

open(DATAFILE, "datafile.csv");
while (<DATAFILE>) {
    chomp;
    my($name, $age, $gender)=split(/,/);
    $sth->execute($name, $age, $gender);
}
$sth->finish();

Open in new window

See what's going on here? You can use the same thing in queries - put in question marks for the various parameters that you want to change, then just supply those values when you call execute() on the handle.

Dalexan

ASKER

I'm just beginning with the perl syntax though, im use to coding in vba and vb6 this is my first attempt at Perl. I need help coding the full perl cgi file to do this.

Dalexan

ASKER

Ok, I was able to figure out the syntax and get the below working to the point where I need to introduce the parameter from the cgi call in the URL. Please help with how to do this?

I call this code from a browser by typing http://192.168.1.1/cgi-bin/test.cgi

I need to add an optional parameter to the call like this http://192.168.1.1/cgi-bin/test.cgi?status=xxx where xxx would be the status criteria used in the query. The query should default to 1 unless there is the parameter in the url call to the .cgi

#!/usr/bin/perl -w
#use strict;

use DBI;
use CGI;
my $dbh = DBI->connect('DBI:mysql:ncts:192.168.1.1', 'test', 'test2011') or die "Couldn't open database: $DBI::errstr; stopped";

my $sth = $dbh->prepare(<<End_SQL) or die "Couldn't prepare statement: $DBI::errstr; stopped";
SELECT value FROM viewer_text WHERE status = '1'
End_SQL

$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
$cgi=CGI->new;
print $cgi->header;
while ( my ($value) = $sth->fetchrow_array() ) {
     print STDOUT  "$value";
}

$dbh->disconnect();

Open in new window

Dalexan

ASKER

I'm assuming I need to use regex to make the parameter optional and code for if its passed with null values etc...?

Dalexan

ASKER

One change to the above: The syntax of the parameter should conform to the below.

http://192.168.1.1/cgi-bin/test.cgi?command=setstatus=xxx where the value in setstatus is passed to the query criteria.....SELECT value FROM viewer_text WHERE status = xxx

This will allow for more functionality down the road when I need to pass other variables, such as displaying data from a different select using other parameters.

http://192.168.1.1/cgi-bin/test.cgi?command=setstatus=xxx&displayvalue=xxx The displayvalue can be used for future use in a separate select.

ASKER CERTIFIED SOLUTION

roundel35

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Dalexan

ASKER

I added your code but I get an error

I'm calling this from a browser window
192.168.1.251/cgi-bin/test.cgi?command=2

#!/usr/bin/perl -w
#use strict;

use DBI;
use CGI;
my $dbh = DBI->connect('DBI:mysql:ncts:192.168.1.251', 'test', 'test2011') or die "Couldn't open database: $DBI::errstr; stopped";
my $command = $cgi->param("command");
my $dv = $cgi->param("displayvalue");
my $sth = $dbh->prepare(<<End_SQL) or die "Couldn't prepare statement: $DBI::errstr; stopped";
SELECT value FROM viewer_text WHERE status = ?
End_SQL

$sth->execute($command) or die "Couldn't execute statement: $DBI::errstr; stopped";
$cgi=CGI->new;
print $cgi->header;
while ( my ($value) = $sth->fetchrow_array() ) {
     print STDOUT  "$value";
}

$dbh->disconnect();

Open in new window

The error I get when I run this from the command line is
Can't call method "param" on an undefined value at test.cgi line 9

roundel35

You need to move your line 14 further up the code. You can't reference $cgi until you've set it using the "new" method.

Move line 14 to underneath "use CGI;" and all should be well.

SOLUTION

Dalexan

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Dalexan

ASKER

Thank you

FishMonger

That here doc is not even coming close to doing what you want.

Here's a cleaned up and corrected version.

#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

my $cgi = CGI->new;
my $cmd = $cgi->param("command");

print $cgi->header, $cgi->start_html,
      $cgi->h1("testing db connection and query");

my $dbh = DBI->connect('DBI:mysql:ncts:192.168.1.251', 'test', 'test2011',
                        { RaiseError => 1 } )
        or die "Couldn't open database: $DBI::errstr; stopped";

my $sth = $dbh->prepare("SELECT value
                         FROM viewer_text
                         WHERE status = ?");

$sth->execute($cmd);

while ( my ($value) = $sth->fetchrow_array() ) {
    print $cgi->p($value);
}

$dbh->disconnect();
print $cgi->end_html;

Open in new window

Dave Baldwin

I finally got ActivePerl working on IIS on this computer. The code above by FishMonger was the most complete and error-free example I could find. I had a couple of examples from somewhere else that I was able to fix by referring to FishMonger's example. Good question, helped me a lot...

Dalexan

ASKER

Any tricks you found to getting ActivePerl working?

Dave Baldwin

See the instructions here: http://docs.activestate.com/activeperl/5.14/install.html#installing%20activeperl%20on%20windows%20%28x86%29

If you have a previous version, uninstall and Delete Everything in the Perl directory. For some reason, you can't simply upgrade versions, you must get rid of the old one first. And you have kill IIS while you are doing the install for everything to register properly. I'm running Windows XP and I reboot between each of these steps.

After you have ActivePerl installed, then and only then can you use 'ppm' to go get the DBD extension for MySQL. When I tried to install the new version over the old version, something kept trying to use a DLL from the old version which simply didn't work. Not until I completely cleaned out the old versions and reinstalled the newest version and drivers did things work as they should.

Dalexan

ASKER

I have tried this numerous times but I will try again as you say.

Dave Baldwin

It is really important that you uninstall the program AND delete all of the files and directories under Perl. Make sure your own Perl programs are somewhere else on your system. I had to try this 3 times before I got it all working.

Most other programs take care of uninstalling incompatible previous files but ActivePerl does not.

FishMonger

Ok, now we're getting really far off topic of the original question. Issues regarding installing perl and or IIS and tying them together should be handled in a new thread (or threads). What I will say on that topic is that you'll have far less headaches if you use apache instead of IIS.

Dalexan

ASKER

Agreed, I'm going to stick with coding my perl In vi while ssh into my ubuntu server running apache.