Setting Up Web-based Data Access

Setting Up Web-based Data Access

The following topics cover the configuring of web servers to provide access to PSQL data and provide connection snippets and samples for accessing PSQL data from web applications on Linux or OS X.

When you first install PSQL, the odbc.ini file is written to /usr/local/psql/etc.

If you have other ODBC driver managers such as unixODBC, they may use an odbc.ini file in a different location, such as /etc/odbc.ini.

One way to unify the ODBC setup is to add soft links from between the PSQL directories and the location where unixODBC expects to find the odbc.ini file, as shown in the following example:

su
cd /etc
ln -s /usr/local/psql/etc/odbc.ini

Configuring Web Server

This section shows how you should set up the machine where the web server such as Apache resides.

You should make the user account under which you run any web server such as Apache a member of the group pvsw. These user accounts run under restricted accounts such as nobody

To find the user account, see your Apache configuration file, typically located at /etc/httpd/conf/httpd.conf.

In this file, the following lines show the user that the Apache server operates under:

User nobody
Group nobody
Options ExecCgi Indexes

You should add this user to the pvsw group, substituting the name used in your Apache configuration file.

/usr/bin/gpasswd -a nobody pvsw

PHP allows for easy development of web applications, using a style that is similar to both ASP in the Microsoft world and JSP in the Java world. Using PHP, you enclose database calls in special tags and format the output using HTML.

PSQL PHP Requirements

•

PHP - obtain from http://www.php.net

•

DSN pointing to the database (use dsnadd)

PHP Connection Snippet

This code segment shows the essential part of connecting to a PSQL database using PHP.

// connect to DEMODATA database no uid or password
$connect = odbc_connect("demodata", "", "");

// set the query variable to your SQL
$query = "SELECT * from Department";

// obtain a result object for your query
$result = odbc_exec($connect, $query);

This complete sample presents the user a choice of three DEMODATA tables and then displays the table.

<TITLE>PVSW PHP Sample</TITLE>

<H1>PSQL Hello World Samples - PHP using PHP ODBC APIs)</H1>

This sample will display the DEMODATA database tables in the following drop-down

// -------MAIN MENU----------------------------

// if there is no function specified in the URL

if (!(isset ($HTTP_GET_VARS["_function"]))):

// --------------------------------------------

<p>Please select from the following tables</p>

<form method=post action='<?=$PHP_SELF?>?_function=showtable'>

<select name="selecttable">

<option SELECTED value="Department">Department

<option value="Course">Course

<option value="Room">Room

<input type=submit value="Show table">

// ------SHOWTABLE-----------------------------

Elseif ($HTTP_GET_VARS["_function"] == "showtable"):

// --------------------------------------------

print("<p>Return to <a href='$PHP_SELF'>Sample 1 Main menu</a></p>");

$thetable = $HTTP_POST_VARS["selecttable"];

// determine from FORMS data which table to open

$connect = odbc_connect("demodata", "", "");

// connect to DEMODATA database no uid or password

$query = "SELECT * from $thetable";

// set the query variable to contain the SQL you

// want to execute

$result = odbc_exec($connect, $query);

// perform the query

// print out the entire resultset as HTML table

// (uncomment following line)

// odbc_result_all($result);

// or format the output yourself and display

// a nicer table (but more code required)

// initialize row counter

// determine number of columns

$numcols = odbc_num_fields($result);

// start HTML table

print("<table border=1 cellpadding=5>");

// PRINT COLUMN HEADINGS

print("<tr>"); // start of row

while ($i < $numcols)

$colname = odbc_field_name($result, $i);

print("<th>$colname</th>");

print("</tr>"); // end of row

// PRINT TABLE DATA

// while there are still rows

while(odbc_fetch_row($result))

print("<tr>"); // start row

while ($i < $numcols)

$tablecell = odbc_result($result, $i);

print("<td>$tablecell</td>");

print("</tr>"); // end row

$i = 0; // reset counter

} // end odbc_fetch_row

print("</table>"); // end HTML table

odbc_close($connect); // CLOSE THE CONNECTION

// END OF SHOWTABLE

// ---CATCH INVALID MENU OPTIONS-----------------

// ----------------------------------------------

print("<p>An Invalid function was entered. Please <a href='$PHP_SELF'>try again</a>.</p>");

Additional PHP Sample

A more comprehensive PHP sample application that simulates the operations of a video store is available online at the PSQL website.

This sample uses the Pvideo database that is included with the PSQL SDK. If you do not have the SDK installed, you can download the Pvideo database separately with the sample application.

Perl allows for both command line and web-based applications using PSQL.

PSQL Perl Requirements

•

•

ODBC-DBD library

•

•

DSN pointing to the database

Perl Connection Snippet

This code segment shows the essential part of connecting to a PSQL database using Perl.

# specify use of Perl’s database interface (DBI)

# connect to DEMODATA database no uid or password

$dbInfo = "DBI:ODBC:DEMODATA";

$dbUserName = "";

$dbPassword = "";

# set the query variable to your SQL

$query = "SELECT * FROM Department";

# Connect to the server

$connect = DBI->connect($dbInfo, $dbUserName, $dbPassword);

# Prepare the SQL query

$myRecordSet = $connect->prepare($query);

# Execute the query and obtain a recordset

$myRecordSet->execute();

This complete sample presents the user a choice of three DEMODATA tables and then displays the table.

use CGI":cgi-lib";

$cgiquery = new CGI;

$functionreq = $cgiquery->url_param('_function');

# use 'url_param' for GET and 'param' for POST

print &PrintHeader;

print &HtmlTop("PSQL Hello World Sample - Perl");

print <<ENDOFMENU;

<H1>PSQL Hello World Samples - Perl</H1>

This sample will display the DEMODATA database tables in the following drop-down

by using Perl/DBI.

# -----MAIN MENU-------------------------------

# if there is no function specified in the URL

if (!$functionreq) {

# ---------------------------------------------

print <<ENDOFTEXT;

<p>Please select from the following tables</p>

<form method=post action="$ENV{'SCRIPT_NAME'}?_function=showtable">

<select name="selecttable">

<option SELECTED value="Department">Department

<option value="Course">Course

<option value="Room">Room

<input type=submit value="Show table">

} # !($function)

# ------SHOWTABLE-------------------------------

elsif ($functionreq eq "showtable") {

print("<p>Return to <a href='$ENV{'SCRIPT_NAME'}'>Perl Hello World Sample - Main Menu</a></p>");

# determine from FORMS data which table to open

$thetable = $cgiquery->param('selecttable');

$dbInfo = "DBI:ODBC:DEMODATA";

$dbUserName = "";

$dbPassword = "";

$query = "SELECT * FROM $thetable";

$connect = DBI->connect($dbInfo, $dbUserName, $dbPassword);

$myRecordSet = $connect->prepare($query);

$myRecordSet->execute();

# start HTML table

print "<table border=1 cellpadding=5>";

# PRINT COLUMN HEADINGS

$num_fields = $myRecordSet->{NUM_OF_FIELDS};

while ($count < $num_fields) {

$column_name = $myRecordSet->{NAME}->[$count];

print "<th>$column_name</th>";

print "</tr>\n";

# PRINT TABLE DATA

while(@row=$myRecordSet->fetchrow_array) {

print "<tr>\n";

while ($count < $num_fields) {

print "<td>$row[$count]</td>\n";

print "</tr>\n";

print "</table>"; # end HTML table

# END OF SHOWTABLE

# -----CATCH INVALID MENU OPTIONS----------------

print "<p>An Invalid function was entered. Please <a href='$ENV{'SCRIPT_NAME'}'>try again</a>.</p>";

print &HtmlBot;