How to Access a Database from Perl on NetWare

Articles and Tips: article

Guruprasad S
Software Consultant
Net Technology Services Group
sguruprasad@novell.com

01 Jul 2001

This article focuses on accessing databases from Perl scripts running on NetWare 5.x.

• Introduction
• Scenarios
• References
• Conclusion

Introduction

A database is an information store. It helps in managing large volumes of information with fast and easy retrieval mechanisms. The amount of information that can be stored in a database is only limited by the available physical disk space.

A database is an important piece of any three-tier application. Even though a Directory can store most of the information that a Database can store, neither can replace the other. In fact both Database and Directory complement each other in building good applications.

The different database options available from NetWare are Oracle and Pervasive.SQL 2000 (henceforth referred to as Pervasive) running on NetWare and MS-Access, SQL Server and Oracle running on Windows. This article focuses on accessing these databases from Perl scripts running on NetWare 5.x.

There are three different ways of accessing a database from Perl on NetWare:

• Database Independent Interface (DBI)

• NetWare Data Object (NDO) UCX Component

• Remote ActiveX Provider and ADO Component on Windows machine

The following table summarizes the different options available on NetWare.

	Local	Remote
	Oracle on NetWare	Pervasive on NetWare	Oracle on NetWare	MS-Access, SQL Server and Oracle on Windows*
DBI	4	4	4	4
NDO	4	4	4	4
ADO on Windows	7	7	7	4

* DBI & NDO can access remote databases running on Windows NT only while ADO can access databases running on both Windows 95 & NT.

Prerequisites

If Oracle, MS-Access or SQL Server is to be accessed from DBI or NDO then SQL Connector should be loaded. If the database is Pervasive then Pervasive.SQL 2000 connection manager has to be loaded. If ADO on Windows is used for database access, then neither of the above is required. However this requires HTTP listener (UCS2Win.exe) of the Remote ActiveX provider to be running on the Windows machine.

SQL Connector

SQL Connector can be loaded by running sqlc.ncf file that is created during install. This basically loads the database monitor (sqlcmon.nlm), the network listener (vtxnetd.nlm) and the ODBC processor (sqlcodbc.nlm), which passes the ODBC requests to the database engine (sqlc.nlm). SQL Connector ships with NetWare 5.1 and above. Additional information about SQL Connector can be obtained from NetWare 5.1 documentation (see http://www.novell.com/documentation/lg/nw51/docui/index.html). Also a related article titled SQL Integrator: A Data Request Broker for Heterogeneous Data Access published in AppNotes (see http://www.novell.com/documentation/lg/nw51/docui/index.html) talks about SQL Connector.

VORTEX_HOME, is an environment variable that is required by SQL Connector to locate odbc.ini file, which contains the ODBC data source names. The SQL Connector looks for odbc.ini file in the lib folder under the current working directory. Presuming that odbc.ini is under sys:\lib directory, type vtxhome sys: at the console prompt to set the VORTEX_HOME variable. This setting is required only if the database is accessed using DBI.

Pervasive.SQL 2000

Pervasive can be loaded by running mgrstart.ncf that is created during install. This basically loads the ODBC driver manager (odbc.nlm), Pervasive.SQL 2000 Connection Manager (nwsqlmgr.nlm) and other related modules. Pervasive is shipped with NetWare 5.1 and above. Information about Pervasive can be obtained from the documentation that ships with it. Also additional information can be obtained from the NDK site Pervasive.SQL Software Developer Kit (see http://developer.novell.com/ndk/psqlsdk.htm). At a time you can run either SQL connector or Pervasive connection manager.

Data Source Name

The database is accessed through a data source name (DSN). A DSN hides the details of driver type, connection information, login information etc. from the user. A DSN has to be created before we can start accessing the database. The SQL connector documentation gives the details of how to create a DSN on NetWare from NetWare Enterprise Server. And the Pervasive documentation gives details about creating a DSN for Pervasive. For accessing database through ADO a DSN has to be created on the Windows machine as well. Once the DSN is setup, we can access the database configured as a DSN from a Perl script irrespective of whether we are using DBI or NDO or Remote ActiveX control.

Sl. No.	Access mechanism	Location	Database	Configuration
1	DBI	Local	Oracle on NetWare	1. DSN should be created and pointing to the database on the same server. This can be accomplished through NetWare Enterprise Web Server administration screen. 2. SQL Connector and Oracle should be running. 3. VORTEX_HOME should be set.
			Pervasive on NetWare	1. DSN should be created. This can be accomplished by running Pervasive.SQL 2000 Control Center. This is a client component that will be installed on the Windows machine. 2. Pervasive.SQL 2000 connection manager should be running on the NetWare server.
		Remote	Oracle on NetWare	1. A database service name with the remote database server details should be created in the server where the script executes. This is accomplished by running the Easycfg configuration utility from NetWare Console. 2. A DSN should be created which points to the database service name created in step 1 above. 3. SQL Connector should be running on both the servers. 4. Oracle should be running on the remote server 5. VORTEX_HOME should be set.
			MS-Access, SQL Server and Oracle on Windows NT	1. DSN should be created on the NT machine. 2. DSN should be created on NetWare server that contains the DSN created in step 1 above. 3. SQL Connector should be running on NetWare server. 4. SQL Connector-server listener should be running on the NT machine. 5. VORTEX_HOME should be set
2	NDO	Local	Oracle on NetWare	1. Oracle should be running.
			Pervasive on NetWare	1. DSN should be created. This can be accomplished by running Pervasive.SQL 2000 Control Center. This is a client component that will be installed on the Windows machine. 2. Pervasive.SQL 2000 connection manager should be running on the NetWare server.
		Remote	Oracle on NetWare	1. A database service name should be created which points to the remote server. 2. Oracle should be running on remote server.
			MS-Access, SQL Server and Oracle on Windows NT	1. DSN should be created on NT machine. 2. DSN should be created on NetWare server that contains the DSN created in step 1 above. 3. SQL Connector should be running on NetWare server. 4. SQL Connector-server should be running on the NT machine.
3	ADO	Remote	MS-Access, SQL Server and Oracle on Windows 95 / NT	1. DSN should be created on the Windows machine for the database to be accessed. 2. The ADO object should be registered in the Windows machine. 3. UCS2Win.exe should be running on the Windows machine.

Now let us take a look at how to access the database using DBI, NDO, and ADO on Windows.

Database Independent Interface (DBI)

DBI is the database access Application Programming Interface (API) for Perl. DBI defines a set of functions, variables and conventions that provide a consistent database interface independent of the actual database. The DBI just provides an interface, while the drivers do the real work. For Perl on NetWare, ODBC is the database driver. DBI and DBD-ODBC together make it possible for a Perl script to access a database.

The following script gives a skeleton program to connect to a DSN.

use DBI;

$dsname="demo";
$absdsname= "dbi:ODBC:$dsname";

$username = "admin";
$password = "novell";

# Making connection with the database. This call will automatically
load the driver
my $dbh = DBI->connect($absdsname, $username, $password, {RaiseError =>1,
AutoCommit =>1,}) ;

if (defined $dbh) {

# TO DO
# Insert your code here

#Disconnecting from the database.
$dbh->disconnect;
} else {
print "Failed to connect\n";
}

In the above sample, the first line, use DBI includes the database independent interface module. This call loads the DBI module, DBI.nlp if it is not already loaded. The variable $dsname holds the name of the DSN which will be used to access the Database. The variable $absdsname holds the complete DSN. The ODBC string in the DSN tells the DBI about the type of driver that is being used. The variables $username and $password represent the SQL Connectors username and password. The connect method loads the required database driver and if the $username and $password fields have valid data then the connection goes through and a database handle is returned for future use. Once all the operations are over, the connection has to be cleared and this is done by calling disconnect method on the database handle. Since connection is a costly operation, it's a good practice to open the connection once at the beginning and close after all the operations are completed.

NetWare Data Object (NDO) UCX Component

NDO is the NetWare Data Object; this is similar to Microsoft's ActiveX Data Object. This is a Universal Component eXtension (UCX) component that encapsulates a set on methods and properties to access the database. Information about NDO UCX component is available at the Novell Script for NetWare (http://developer.novell.com/ndk/nscript.htm)page in the Novell Developer Kit Site. To access any UCX component, the Perl-UCS Extension has to be used. More information about accessing UCX components from Perl can be obtained from the Perl 5 for NetWare page in the Novell Developer Kit Site (http://developer.novell.com/ndk/nscript.htm). A related article Accessing Novell Services from Perl on NetWare gives more information about how to access UCX components from Perl (http://support.novell.com/techcenter/articles/ana20001007.html).

The following script gives a skeleton program to create an NDO object and open connection to the database.

use UCSExt;

# Create the NDO connection object
$connection = UCSExt->new("ucx:ndodb.connection")
or die "Couldn't get connection object: $!";

# Open connection to the database.
$username = "scott";
$password = "tiger";
$connection->Open("DBTYPE=0;DATABASE=ORANW81.WORLD", $username, $password)
      or die "Couldn't open connection to the database: $!";

# TO DO
# Insert your code here

$connection->Close;

In the above sample, the first line, use UCSExt loads the Universal Component System (UCS) Extension for Perl. This call loads UCSExt.nlp if it is not already loaded. The new method creates a new connection object. The open method of the connection object is used to connect to the database. The DBTYPE indicates the database type. Following are the values that DBTYPE, can take:

0 - Oracle 8 driver 1 - SQL Integrator driver 3 - SQL Connector ODBC driver

The DATABASE indicates the database to which to be connected. If DBYTPE is 3, then the DATABASE field will contain the DSN. The $username and $password depends on the DBTYPE field. Once the connection is successful, different operations can be performed on the database. To connect to a remote Database running on NT using NDO component, the DBTYPE value should be 3. When all the operations are completed, the connection has to be closed by calling the close method on the connection object.

Note: If you are using Perl5.6 and above on NetWare, the UCS Extension for Perl is renamed as Perl2UCS and accordingly all occurrences of UCSExt has to be replaced with Perl2UCS.

Remote ActiveX Provider with ADO Component

One other way of accessing a database is through Remote ActiveX provider. This talks to MS-Access, Oracle or SQL Server running on a Windows machine. The remote provider routes the request to a client running on the Window's machine that executes the command and returns the data back. The script to access the remote database will be similar to accessing database using NDO component since both makes use of UCS. The only places where the script differs are

1. In object instantiation.

2. In DSN specification.

Additionally, the second parameter to Execute method (RecordsAffected) is optional in case of ADO but in NDO it is not.

The following script gives a skeleton program to create the ADO object and open connection to the remote database.

use UCSExt;

# Create the ADO connection object
$connection = UCSExt->new("OLE:Adodb.Connection.2.1\@Hostname_Or_IPAddress")
or die "Failed to instantiate object";

$DSN = "DSN=EMPDSN;DefaultDir=D:\\TempWork;DBQ=D:\\TempWork\\MYDB.MDB"; 


$connection->Open($DSN);

# TO DO
# Insert your code here

$connection->Close;

Since this also uses UCS, use UCSExt is required. The "ole:" in the new method tells UCS that it is going to access a remote ActiveX object. The Hostname_Or_IPAddress gives the host name or the IP address of the machine on which the ActiveX control is registered and will be running. If the hostname is used, then the file resolv.cfg in the server should have an entry for the name server. The UCS2Win.exe that runs on the machine processes the request and passes the results back to the script running on the NetWare server.

Scenarios

Till now we have seen the different ways of opening connection to the database from Perl on NetWare. Now we will take a look at how various tasks can be performed. We will take a look at the samples that use DBI and NDO. Writing samples with ADO is similar to NDO except for the deviations mentioned above. We will take a look at how the following activities are performed

• Creating and deleting tables

• Adding, searching, and removing records

The following scripts use Structured Query Language (SQL) to perform the tasks. Refer to standard SQL documentation to perform various tasks on a database. SQL statements have to be written very carefully without any errors; even a minor error will cause the operation to fail. The following code snippets do specific operation. It has to be put in the templates given above to become full-fledged programs. Also use the above skeleton programs as templates and put your code in the "TO DO" portion.

Creating Table

A table can be created once the connection object is obtained. For creating a table, the table name, fields, and field types have to be specified. Additionally primary key, secondary key, and indexes can also be specified.

# Name of the table to be created
$TableName = "NewTable";

# SQL statement to create the table
$Query = "CREATE TABLE " . $TableName . " (EMPID integer primary key,
NAME char(30))";

# Execute the query
$record_set = $connection->Execute($Query , 0)
      or die "Couldn't create the table: $!";

The above sample creates a table named NewTable, which has two fields an integer and a string. Once the query is properly formed, calling the Execute method on the connection object will create the table.

The following sample creates a table using DBI. Calling the do method on the database handle, dbh, creates the table.

# Name of the table to be created
$TableName = "NewTable";

# SQL statement to create the table
$Query = "CREATE TABLE $TableName (EMPID Integer ,
                NAME CHAR(30)) ";
$dbh->do($Query);

Adding records

Once the table is created, records can be added to it. Again a query is formulated using SQL and it is executed to do the task.

# Name of the table into which records are to be inserted
$TableName = "NewTable";

# SQL statement to insert data into the table
$Query = "INSERT INTO " . $TableName . " (EMPID, NAME) VALUES(1,
'FirstName')";

# Execute the query
$record_set = $connection->Execute($Query , 0)
or die "Couldn't insert the record into the table: $!";

The above sample inserts a record into the table using the NDO object. Similarly the following code snippet does the same using DBI.

# Name of the table into which records are to be inserted
$TableName = "NewTable";

# SQL statement to insert data into the table
$Query = "INSERT INTO " . $TableName . " (EMPID, NAME) VALUES(1,
'FirstName')";

$dbh->do($Query);

Searching for records

Once records are entered, we may want to search for records based on some condition. Using SQL, queries can be formulated which can be as simple as getting all the data from the table to complex queries which looks for specific field values.

# Name of the table from which records are to be searched
$TableName = "NewTable";

# SQL statement for searching for data in the table
$Query = "SELECT Name FROM $TableName WHERE EMPID = 1";


# Execute the query
$record_set = $connection->Execute($Query,0)
      or die "Couldn't search for records: $!";

$Fields = $record_set->{"Fields"};
$TotFields = $Fields->{"Count"};

$spaces = "       ";

for $i (1..$TotFields) {
      $fld = $Fields->Item($i) or die "Couldn't get Heading\n";
      print $fld->{"Name"}, $spaces;
}
print "\n";

while (! $record_set->{"EOF"} )
{
   for $n (1..$TotFields) {
      my $fld = $Fields->Item($n) or die "Couldn't get Item($n)";
      my $value = $fld->{"Value"};
        print $value, $spaces;
   }
   print "\n";
   $record_set->MoveNext();
}

The above snippet looks for a record with a specific value using NDO. The query will return a recordset that will contain the returned data. The Fields property has to be used to retrieve the returned records.

Similarly the following snippet retrieves records from the table using DBI. Since the query contains a ‘*', it retrieves all the records from the table.

$table_name = "NewTable";

# SQL statement for searching for data in the table
$statement = "SELECT * FROM $table_name";

# Preparing the script for execution and executing
     $sth = $dbh->prepare($statement);
     $rv = $sth->execute; 

     # Fetching the value and displaying
      while (@row_ary = $sth->fetchrow_array){
       print "(@row_ary)\n";
     }

Removing records

Removing records is similar to searching the records except for the ‘DELETE' keyword in the query. This removes the specific record(s) from the table.

# Name of the table from which records have to be deleted
$TableName = "NewTable";


# SQL statement for removing records from the table
$Query = "DELETE FROM $TableName WHERE EMPID = 1";

# Execute the query
$record_set = $connection->Execute($Query , 0)
      or die "Couldn't delete the record(s) from the table: $!";

The above code snippet removes all the records that have EMPID field value of one. Similarly the following code snippet removes the records from the table using DBI.

# Name of the table from which records are to be deleted
$TableName = "NewTable";


# SQL statement for removing records from the table
$Query = "DELETE FROM $TableName WHERE EMPID = 1";

$dbh->do($Query);

Removing Table

‘DROP TABLE' followed by the table name is the SQL syntax for removing the table from the database. The following two code snippets show how to delete a table from the database using NDO and DBI respectively.

# Name of the table to be deleted
$TableName = "NewTable";

# SQL statement for deleting the table
$Query = "DROP TABLE $TableName";

# Execute the query
$record_set = $connection->Execute($Query , 0)
or die "Couldn't delete the table: $!";

# Name of the table to be deleted
$TableName = "NewTable";

# SQL statement for deleting the table
$Query = "DROP TABLE $TableName";

$dbh->do($Query);

References

Additional information about SQL Connector and Oracle can be obtained from NetWare 5.1 documentation. Also the latest binaries and documentation for Perl, UCS, and UCX components are available at the following NDK sites:

• http://developer.novell.com/ndk/Perl5.htm

• http://developer.novell.com/ndk/ucs.htm

• http://developer.novell.com/ndk/nscript.htm

Conclusion

We have seen the different ways in which a database can be accessed from Perl on NetWare. There is flexibility for the user to choose the database best suited for the application. The article also shows that irrespective of the database as well as the access mechanism, writing scripts is easy. Additionally, if ODBC is used for accessing data, moving from one database to another will not have much impact on the existing scripts. The ease of scripting and the power of database can be leveraged to create good applications on NetWare.

* Originally published in Novell AppNotes

---

Disclaimer

The origin of this information may be internal or external to Novell. While Novell makes all reasonable efforts to verify this information, Novell does not make explicit or implied claims to its validity.

• Feedback
• Print
  •  
  • Full
  • Simple
•  
• Request a Call
• Follow Us
  •  
  • Facebook
  • YouTube
  • Twitter
  • LinkedIn
  • Newsletter Subscription
  • RSS