Setting Up Web-based Data Access

Setting Up Web-based Data Access

This section contains information about configuring web servers to provide access to Pervasive PSQL data and provides connection snippets and samples for connecting to Pervasive PSQL data from web applications on Linux.

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

If you have other ODBC driver managers such as unixODBC, they might be using a different odbc.ini file located, for example, at /etc/odbc.ini.

One way to unify the ODBC setup is to add soft links from where unixODBC expects the odbc.ini file to be located over to the Pervasive PSQL directories.

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 what user the Apache server uses to operate under.

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.

Pervasive 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 Pervasive 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>Pervasive 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 Pervasive Software Web site.

This sample uses the Pvideo database that is included with the Pervasive 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 Pervasive PSQL.

Pervasive 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 Pervasive 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("Pervasive PSQL Hello World Sample - Perl");

print <<ENDOFMENU;

<H1>Pervasive 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;