Setting Up Web-based Data Access on Linux

Pervasive logo

Getting Started with Pervasive.SQL (Server edition)

Setting Up Web-based Data Access on Linux

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

ODBC Behavior

When you first install Pervasive.SQL, 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.SQL directories.

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

User nobody 
Group nobody 
Options ExecCgi Indexes

To add this user to the pvsw group, use the btadmin utility with the administrator permission, substituting the name used in your Apache configuration file.

btadmin a+ nobody

PHP

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.SQL 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.SQL 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);

PHP Sample

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

<HTML> 
<HEAD> 
<TITLE>PVSW PHP Sample</TITLE> 
</HEAD> 
<BODY> 
 
<H1>Pervasive Hello World Samples - PHP using PHP ODBC APIs)</H1> 
<P> 
This sample will display the DEMODATA database tables in the following drop-down  
by using PHP. 
</p> 
 
<? 
 
// -------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 
</select> 
 
<p> 
<input type=submit value="Show table"> 
</p> 
</form> 
 
<? 
 
// ------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 
$i = 0;                                      
 
// 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)  
      { 
       $i++;  
       $colname = odbc_field_name($result, $i); 
       print("<th>$colname</th>");  
     } 
  $i=0;  
 
 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) 
     { 
      $i++; 
      $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----------------- 
 
Else: 
 
// ---------------------------------------------- 
 
print("<p>An Invalid function was entered. Please <a href='$PHP_SELF'>try again</a>.</p>"); 
 
 
Endif; 
 
?> 
 </BODY> 
 </HTML>

Additional PHP Sample

A more comprehensive PHP sample application that simulates the operations of a video store is available online at the location where you downloaded the Linux client:

http://www.pervasive.com/linuxrc

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

Perl

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

Pervasive.SQL Perl Requirements

Perl
ODBC-DBD library
CGI library
DSN pointing to the database

Perl Connection Snippet

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

# specify use of Perl's database interface (DBI) 
use 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();

Perl Sample

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

 
# Perl sample 
 
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.SQL Hello World Sample - Perl");  
 
print <<ENDOFMENU; 
<H1>Pervasive Hello World Samples - Perl</H1> 
 
<P> 
This sample will display the DEMODATA database tables in the following drop-down  
by using Perl/DBI. 
</p> 
ENDOFMENU 
 
# -----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 
</select> 
<p> 
<input type=submit value="Show table"> 
</p> 
</form> 
 
ENDOFTEXT 
   
} #  !($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');          
 
use DBI;  
 
$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}; 
$count = 0; 
 
print "<tr >"; 
while ($count < $num_fields) { 
$column_name = $myRecordSet->{NAME}->[$count]; 
print "<th>$column_name</th>"; 
$count++; 
} 
print "</tr>\n"; 
 
$count = 0; 
 
# PRINT TABLE DATA 
 
while(@row=$myRecordSet->fetchrow_array) { 
  print "<tr>\n"; 
  while ($count < $num_fields) { 
  print "<td>$row[$count]</td>\n"; 
  $count++; 
  } 
  print "</tr>\n";   
  $count = 0; 
}  
 
print "</table>";              # end HTML table 
# END OF SHOWTABLE 
} 
 
# -----CATCH INVALID MENU OPTIONS---------------- 
 
else { 
 
print "<p>An Invalid function was entered. Please <a href='$ENV{'SCRIPT_NAME'}'>try again</a>.</p>"; 
 
} 
 
print &HtmlBot;

Additional Perl Sample

A more comprehensive Perl sample application that simulates the operations of a video store is available online at the location where you downloaded the Linux client:

http://www.pervasive.com/linuxrc

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

Prev
Internationalization with the Linux Client

Contents
Up
Check for Revisions

Next
Using Perl and ODBC with Pervasive.SQL for Linux