Using PSQL on Linux, macOS, and Raspbian

Working with the Products on Linux, macOS, and Raspbian

The following topics are for PSQL users on the supported Unix-based platforms:

• Finding What You Need

• PSQL Account Management on Linux, macOS, and Raspbian

• Configuration

• Client Information

• Setting Up Web-based Data Access

• Using Perl and ODBC with PSQL

Finding What You Need

Accessing the User Documentation

See How Do I Access the Documentation?

Man Pages

The man pages are installed with PSQL Server or Client. Refer to the directory $PVSW_ROOT/man/man1for the man pages available.

To make these man pages easily accessible, add $PVSW_ROOT/man to your MANPATH environment variable. If you need more detailed information on a utility or application, see Command Line Interface Utilities PSQL User's Guide.

Note Check the man pages for the most current information. Every effort is made to ensure that the information in this guide matches that in the man pages. On occasion, last-minute changes may be included in the man pages after this guide has been published.

PSQL Account Management on Linux, macOS, and Raspbian

The following topics provide information on Linux, macOS, and Raspbian user accounts with respect to operation of PSQL. Use of the .bash shell is assumed, but similar instructions should apply to other shells.

After Installation Behavior

•User psql has no password and can be accessed only through the root account using su.

•You can access the .bash_profile for user psql with ~psql/.bash_profile.

•All PSQL files have user:group ownership psql:pvsw

•You must be logged in as root to run the start and stop scripts for the PSQL engines.

•You can run utilities on other user accounts if you add needed environment variables to the user .bash_profile or system /etc/profile. See Using Utilities from Users Other Than psql.

•In addition to the instructions under Using Utilities from Users Other Than psql, users other than ROOT must be a member of the group pvsw to perform functionality with the following utilities:

•PSQL Control Center (PCC) to administer the local server.

•License Administrator utility (clilcadm) for functions other than displaying current licenses.

•Named Database Maintenance utility (dbmaint) for functions other than displaying current databases.

•PSQL Services Registry Editor (psregedit) for functions other than displaying the registry.

•Command line configuration (bcfg).

The User Environment

The single environment variable $PVSW_ROOT is used to determine the location of installed components.

The generic location for configuration files are $PVSW_ROOT/etc.

For executable files, the location is $PVSW_ROOT/bin.

For shared libraries (32-bit) the location is $PVSW_ROOT/lib; for shared libraries (64-bit) the location is $PVSW_ROOT/lib64.

We recommend that you add $PVSW_ROOT/bin to your Path environment variable.

As described in the next topic, add $PVSW_ROOT/lib64 to LD_LIBRARY_PATH on Linux and Raspbian and to DYLD_LIBRARY_PATH on macOS.

If you are using the 32-bit Client Access package, you also must add $PVSW_ROOT/lib to LD_LIBRARY_PATH. This 32-bit package is not supported on macOS.

Using Utilities from Users Other Than psql

To use utilities from user accounts other than psql, you must first make modifications to the user account configuration. Copy the lines from the following example to either the profile for a specific user or to the /etc profile that all users inherit.

/home/username/.bash_profile /Users/username/.bash_profile	Profile for the user. Similar to /etc/profile but only for the current user. Look for this file in /home/username on Linux and Raspbian or in /Users/username on macOS.
/etc/profile	Default profile for all user accounts on the system. Use this profile to grant access to PSQL utilities to all user accounts. Changing this profile according to the example given does not grant administrative privileges or access to PSQL data.

Examples of a Modified Profile

Be sure to export all variables specific to PSQL.

Linux and Raspbian

PVSW_ROOT=/usr/local/psql
PATH=$PATH:$PVSW_ROOT/bin:/bin:/usr/bin
LD_LIBRARY_PATH=$PVSW_ROOT/lib64:$PVSW_ROOT/lib:$PVSW_ROOT/bin:/usr/lib
MANPATH=$MANPATH:$PVSW_ROOT/man

macOS

PVSW_ROOT=/usr/local/psql
PATH=$PATH:$PVSW_ROOT/bin:/bin:/usr/bin
DYLD_LIBRARY_PATH=$PVSW_ROOT/lib64:$PVSW_ROOT/lib:$PVSW_ROOT/bin:/usr/lib
MANPATH=$MANPATH:$PVSW_ROOT/man

Configuration

Generally, the default configuration settings for PSQL Server and Client are sufficient. You typically do not have to configure any settings for the database engine and clients to communication and function together correctly. This subsection discusses two settings that you may want or need to configure:

• Samba Configuration File

• Authentication

If you want to explore all of the configuration settings, see Configuration Reference in Advanced Operations Guide:

Samba Configuration File

PSQL Server defines the path to the Samba configuration file (smb.conf), which is parsed on engine startup to determine mapping between share names and server directory locations. See Configuration File (Linux, macOS, and Raspbian Engines Only) in Advanced Operations Guide.

Authentication

This option specifies which type of authentication to use for access to the server engine. The available options are:

•Emulate Workgroup Engine. Use this value when Samba is used to authenticate user access on the system.

•Proprietary Authentication (using btpasswd). Use this value when not using Samba and the user does not have an account on the server. This allows a separate password file to be maintained when connecting to the Linux, macOS, or Raspbian system.

•If you are using BTPASSWD or PAM authentication on your Linux, macOS, or Raspbian server, user names and passwords must be set up using the pvnetpass utility on the clients connecting to this server. For more information about pvnetpass, see the PSQL User's Guide.

•Standard Linux Authentication. Use this value when Samba is not used, but users have accounts on the Linux, macOS, or Raspbian system.

Supported Path Formats for Samba

Windows Client

From a PSQL client on a Windows, the order of path parsing is as follows:

•\\server\share\relative\path

Share denotes a valid Samba share, made accessible to a Windows client.

Server reads smb.conf to determine the absolute path to the shared directory, then combines it with the relative path to get a full path. The location of smb.conf is essential for valid resolution of the file path supplied in this format on the client. If the relative path is not correct, status code 12 is returned.

•Drive:\path

Drive must be a Samba drive mapped on the Windows client, from which the PSQL client determines the server where the Btrieve file resides.

Linux, macOS, or Raspbian Client

From a PSQL client on a Linux, macOS, or Raspbian, the order of path parsing is as follows:

•//server/share/relative/path

Share denotes a valid Samba share name on the server.

On systems that use a third-party Samba package, server reads smb.conf to determine the absolute path to the shared directory, then combines it with the relative path to get a full path. The location of smb.conf is essential for valid resolution of the file path supplied in this format on the client.

On macOS systems that use native SMB file sharing instead of smb.conf, server resolves sharing information directly from the operating system.

In all cases, if the relative path is not correct, an error status is returned.

•/Mount/path

Mount must be a Samba drive mounted on the Linux, macOS, or Raspbian client, from which the PSQL client determines the server where the Btrieve file resides.

Note Share names for clients for a Linux-based server are case-sensitive. If the share name on the server and the client do not match exactly, they cannot communicate.

On Linux, macOS, and Raspbian servers, if the PSQL engine cannot find either smb.conf or a share name, it assumes a default of \\server\absolute\path format. If the absolute path is not correct, status 12 is returned.

Client Information

A PSQL Client on Linux, macOS, or Raspbian can connect to any of the PSQL Servers provided that the client and server machines can communicate with a shared protocol.

Authentication to Remote Machines

To connect to a remote machine using the Linux, macOS, or Raspbian client, you must be able to authenticate to the remote machines. This is accomplished by using the pvnetpass utility to send a user name and password to the server. This utility stores the user name and password in an encrypted format for that particular server in the PSQL registry on the client machine. If you do not specify user names and passwords, your applications can receive status code 3119.

See pvnetpass in PSQL User's Guide.

Creating a Client DSN

A client data source name (DSN) is required if applications on the client use the PSQL Relational Engine through ODBC. To create a client DSN, you use the dsnadd utility included with the PSQL Client. See dsnadd in PSQL User's Guide and the man page for dsnadd located in /usr/local/psql/man/man1.

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 macOS. Raspbian is not supported for this use case.

ODBC Behavior

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 topic 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

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

PHP Sample

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

<HTML>

<HEAD>

<TITLE>PHP Sample</TITLE>

</HEAD>

<BODY>

<H1>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'>

<option SELECTED value="Department">Department

<option value="Course">Course

<option value="Room">Room

</select>

<p>

</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 Actian 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

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

PSQL 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 PSQL database using Perl.

# specify use of Perl 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("PSQL Hello World Sample - Perl");

print <<ENDOFMENU;

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

<option SELECTED value="Department">Department

<option value="Course">Course

<option value="Room">Room

</select>

<p>

</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;

Using Perl and ODBC with PSQL

Note This procedure assumes you have a working installation of PSQL, Perl, and an ODBC distribution. A free version of ODBC is available at www.iODBC.org. Perl can be found at www.perl.org.

►To Get PSQL to work with the Perl ODBC Interface

1 Download the database interface (DBI) support for Perl.

Read the readme or installation files for instructions.

2 Download the ODBC DBD database driver for Perl.

Please see the installation instructions in the readme or installation files.

3 Make sure that you have the proper environment variables set, as shown in the following example. These settings are also explained in the iODBC docs.

Code Snippet for Perl and DBI

print "using odbc...\n";

use DBI;

$dbName = "DBI:ODBC:DEMODATA";

$dbUserName = "";

$dbPassword = "";

print "connecting...\n";

$sql = "SELECT * FROM class";

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

$dataObject = $dbh->prepare($sql);

$dataObject->execute();

while(@row=$dataObject->fetchrow_array)

{

print "$row[0]\t$row[1]\t$row[2]\n\n"

}