CGI Script for Postgrsql on RedHat Linux 7.2

progolf069 · May 3, 2002

I am now in the stages I am ready to start accessing one of my many Postgrsql Databases that I have installed on my Redhat Linux 7.2 machine. I have CGI working correctly, and my databases are working just fine from the command line (in telnet), but now I want to integrate the databases to the web side of things.

Currently I have this script written:

Code:

#!/usr/bin/perl

# database.cgi -- does database lookups!
# my first -- database to web output script!+

#PATH='/usr/local/pgsql/bin:/bin';

print &quot;Content-type: text/html\n\n&quot;;

print <<&quot;EOF&quot;;

print '<html><head><title>ICD9 Med Codes</title></head><body>';

print 'psql med2000 -U ian -H -c &quot;SELECT * FROM icd9;&quot;';

print '</body></html>';

EOF

When I execute the above script, I don't access the server, I only print out the psql med2000..... line. This is understood, and I know why that is happening. Is there anyway that I can do like a system call for that command to be processed on the server and then have it display the results? When I execute the command on the server's command line (in telnet) it automatically generates (the -H attribute of that string) all of the HTML code for you example of the output:

Code:

<table border=1>
  <tr>
    <th align=center>trans_num</th>
    <th align=center>icd9_diag</th>
  </tr>
  <tr valign=top>
    <td align=left>001    </td>
    <td align=left>Cholera^M</td>
  </tr>
  <tr valign=top>
    <td align=left>001.0  </td>
    <td align=left>Due to Vibrio cholerae^M</td>
  </tr>
  <tr valign=top>
    <td align=left>001.1  </td>
    <td align=left>Due to Vibrio cholerae el tor^M</td>
  </tr>
  <tr valign=top>
    <td align=left>001.9  </td>
    <td align=left>Cholera, unspecified^M</td>
  </tr> ...

continues on....

Any suggestions?

Basic question-- is there anyway that I can run that command (psql med2000 -U ian -H -c "SELECT * FROM icd9;&quot

on the server side and return the results to the user (on the web)?

Thanks for your assistance and any feedback you give!

rycamor · May 3, 2002

You've got the command-line arguments in the wrong order. If you tried to actually run the command itself from telnet, you would see something like:

psql: warning: extra option ian ignored
psql: warning: extra option -H ignored
psql: warning: extra option -c ignored
psql: warning: extra option SELECT * FROM icd9; ignored
psql: FATAL 1: user "-U" does not exist

Try it in this order:
psql -U ian -H -c "SELECT * FROM icd9;" med2000 -------------------------------------------

"Now, this might cause some discomfort..."
(

http://www.wired.com/news/politics/0,1283,51274,00.html)

Leland123 · May 7, 2002

Hi,

Below is a snippit of a perl script I use to capture sales order info from an HTML page. The HTML page passes the info to the perl script that make a connection to a postgres database and insert the info into it. Immediately after the info is inserted into the database, it is selected with the sequence number that postgres assigned it. If the assigned sequence number is greater that 0, then the record is retrived for displayed in an HTML page for the purchaser to let him know that his sales order info was accepted.

######################## Connect to the database. ######################

$dbh = DBI->connect("DBIg:dbname=$the_db;host=$the_host", $the_user,
$the_pass_wd, {'RaiseError' => 1});

$dbh->do("INSERT INTO $the_table
(date_it,first,last,company,address,city,state,postal,phone,
email,pass_key,payby,cardtype,cardholder,cardnumber,cardmont
h,cardyear,amount,processed) VALUES
('now',$lfirst,$llast,$lcompany,$laddress,$lcity,$lstate,$lp
ostal,$lphone,$lemail,$lpass_key,$lpayby,$lcardtype,$lcardho
lder,$lcardnumber,$lcardmonth,$lcardyear,$lamount,$lprocesse
d)"

############################################################
$sth = $dbh->prepare("SELECT currval
('smorders_cno_seq')"

$sth->execute;

$array_ref = $sth->fetchall_arrayref();

foreach my $row (@$array_ref) {
@temp = @$row;
}

$leland=$temp[0];
##########################################################

$sth = $dbh->prepare("SELECT cno,first,last,company,email
FROM smorders WHERE cno=$leland AND first=$lfirst AND
last=$llast AND company=$lcompany AND email=$lemail"

$sth->execute;

############################################################
#####################
print "<html>";

print "<head>";

print "<title>Software - Master (TM)
Metacharacter</title>\n";

print "</head>";

print "<body TEXT='black' BACKGROUND='/pattern2.jpg'>";

print "<h3><B>";

print "<TABLE bgcolor=$the_color ALIGN='center'
cellpadding='0' cellspacing='0'
bordercolorlight=$the_borderlight
bordercolordark=$the_borderdark BORDER='7' WIDTH='100%'>\n";

print "<CAPTION><H1>Sales Order Update</H1></caption>";

print "<TR bgcolor=$headcolor> $thetitle</TR>";

$array_ref = $sth->fetchall_arrayref();

foreach my $row (@$array_ref) {

@$therow = @$row;

print sprintf "$thesprintf", @$therow;
}

$dbh->disconnect();

print "</TABLE>";
if ($leland>0) {

print "<h3><b>Enter the registration number below
into your program.</b></h3>\n";

print "<H1>$factor</H1>";

print "<h3><b>Thank You\! Your Card Has Passed
Validation. It will now be submitted for Charge
Authorization.</b></h3>\n";

print "</body>\n</html>\n";

# Remember mail to purchaser

&send_mail;

# Remember mail to Seller

&send_mailx;

} else {

print "<h3><b>Your Submission did not update our
database. Please go <FONT COLOR='\#FF0000\'>Back</font>
and resubmit your order. Thanks.</b></h3>\n";

print "</body>\n</html>\n";

}

exit;

Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

CGI Script for Postgrsql on RedHat Linux 7.2

progolf069

Programmer

rycamor

Programmer

Leland123

Programmer

Similar threads

Part and Inventory Search

Sponsor