We help IT Professionals succeed at work.

Perl/cgi DBI to mysql database select query with parameter

Dalexan
Dalexan asked
on
Medium Priority
1,205 Views
Last Modified: 2013-12-25
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.
Comment
Watch Question

Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
This may help: http://www.perlmonks.org/?node_id=144315  I have a version that works on the command line.  If I can get it to work in the browser, I'll post it.

Author

Commented:
Awesome stuff there but how do I code to accept the parameter
CERTIFIED EXPERT
Commented:
Use the ? placeholder in-place of the value for salesagents.agent_id field in the select statement and pass that value in the execute statement.

http://search.cpan.org/~timb/DBI-1.616/DBI.pm#Placeholders_and_Bind_Values
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;

Author

Commented:
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 SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
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.

Author

Commented:
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.

Author

Commented:
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

Author

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

Author

Commented:
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.
If you want to get the command value, all you need is

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

Open in new window


You can then use $command in the select statement by coding the statement with a question-mark placeholder as above, and pass the value of $command in to the execute method.

you can also then read in alternative parameters:
my $dv = $cgi->param("displayvalue");

Open in new window


Hope this helps.

Author

Commented:
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
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.
Commented:
Fantastic

Author

Commented:
Thank you
CERTIFIED EXPERT

Commented:
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 BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
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...

Author

Commented:
Any tricks you found to getting ActivePerl working?
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
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.

Author

Commented:
I have tried this numerous times but I will try again as you say.
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
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.
CERTIFIED EXPERT

Commented:
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.

Author

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.