This section describes how to install PowerBuilder stored procedures in an Adaptive Server Enterprise database by running SQL scripts provided for this purpose.
Appeon recommends that you run these scripts outside PowerBuilder before connecting to an Adaptive Server database for the first time through the Adaptive Server (ASE or SYC DBMS identifier) native database interface. Although the database interface will work without the PowerBuilder stored procedures created by these scripts, the stored procedures are required for full functionality.
What you do
In order to work with an Adaptive Server database in PowerBuilder, you or your system administrator should install certain stored procedures in the database before you connect to Adaptive Server from PowerBuilder for the first time.
You must run the PowerBuilder stored procedure scripts only once per database server, and not before each PowerBuilder session. If you have already installed the PowerBuilder stored procedures in your Adaptive Server database before connecting in PowerBuilder on any supported platform, you need not install the stored procedures again before connecting in PowerBuilder on a different platform.
PowerBuilder stored procedures
A stored procedure is a group of precompiled and preoptimized SQL statements that performs some database operation. Stored procedures reside on the database server where they can be accessed as needed.
PowerBuilder uses these stored procedures to get information about tables and columns from the Adaptive Server system catalog. (The PowerBuilder stored procedures are different from the stored procedures you might create in your database.)
SQL scripts
PowerBuilder provides SQL script files for installing the required stored procedures in the sybsystemprocs database:
|
Script |
Use for |
|---|---|
|
PBSYC.SQL |
Adaptive Server databases |
|
PBSYC2.SQL |
Adaptive Server databases to restrict the Select Tables list |
Where to find the scripts
The stored procedure scripts are located in the Server directory on the PowerBuilder installation package. The Server directory contains server-side installation components that are not installed with PowerBuilder on your computer.
What it does
The PBSYC.SQL script contains SQL code that overwrites stored procedures that correspond to the same version of PowerBuilder in the Adaptive Server sybsystemprocs database and then re-creates them.
The PBSYC.SQL script uses the sybsystemprocs database to hold the PowerBuilder stored procedures. This database is created when you install Adaptive Server.
When to run it
Before you connect to an Adaptive Server database in PowerBuilder for the first time using the ASE or SYC DBMS identifier, you or your database administrator must run the PBSYC.SQL script once per database server into the sybsystemprocs database.
Run PBSYC.SQL if the server at your site will be accessed by anyone using the PowerBuilder or by deployment machines.
If you or your database administrator have already run the current version of PBSYC.SQL to install PowerBuilder stored procedures in the sybsystemprocs database on your server, you need not rerun the script to install the stored procedures again.
For instructions on running PBSYC.SQL, see How to run the scripts.
Stored procedures it creates
The PBSYC.SQL script creates the following PowerBuilder stored procedures in the Adaptive Server sybsystemprocs database. The procedures are listed in the order in which the script creates them.
|
PBSYC.SQL stored procedure |
What it does |
|---|---|
|
sp_pb170column |
Lists the columns in a table. |
|
sp_pb170pkcheck |
Determines whether a table has a primary key. |
|
sp_pb170fktable |
Lists the tables that reference the current table. |
|
sp_pb170procdesc |
Retrieves a description of the argument list for a specified stored procedure. |
|
sp_pb170proclist |
Lists available stored procedures and extended stored procedures. If the SystemProcs DBParm parameter is set to 1 or Yes (the default), sp_pb170proclist displays both system stored procedures and user-defined stored procedures. If SystemProcs is set to 0 or No, sp_pb170proclist displays only user-defined stored procedures. |
|
sp_pb170text |
Retrieves the text of a stored procedure from the SYSCOMMENTS table. |
|
sp_pb170table |
Retrieves information about all tables in a database, including those for which the current user has no permissions. PBSYC.SQL contains the default version of sp_pb170table. If you want to replace the default version of sp_pb170table with a version that restricts the table list to those tables for which the user has SELECT permission, you can run the PBSYC2.SQL script, described in PBSYC2.SQL script. |
|
sp_pb170index |
Retrieves information about all indexes for a specified table. |
What it does
The PBSYC2.SQL script contains SQL code that drops and re-creates one PowerBuilder stored procedure in the Adaptive Server sybsystemprocs database: a replacement version of sp_pb170table.
The default version of sp_pb170table is installed by the PBSYC.SQL script. PowerBuilder uses the sp_pb170table procedure to build a list of all tables in the database, including those for which the current user has no permissions. This list displays in the Select Tables dialog box in PowerBuilder.
For security reasons, you or your database administrator might want to restrict the table list to display only those tables for which a user has permissions. To do this, you can run the PBSYC2.SQL script after you run PBSYC.SQL. PBSYC2.SQL replaces the default version of sp_pb170table with a new version that displays a restricted table list including only tables and views:
-
Owned by the current user
-
For which the current user has SELECT authority
-
For which the current user's group has SELECT authority
-
For which SELECT authority was granted to PUBLIC
When to run it
If you are accessing an Adaptive Server database using the ASE or SYC DBMS identifier in PowerBuilder, you must first run PBSYC.SQL once per database server to install the required PowerBuilder stored procedures in the sybsystemprocs database.
After you run PBSYC.SQL, you can optionally run PBSYC2.SQL if you want to replace sp_pb170table with a version that restricts the table list to those tables for which the user has SELECT permission.
If you do not want to restrict the table list, there is no need to run PBSYC2.SQL.
For instructions on running PBSYC2.SQL, see How to run the scripts.
Stored procedure it creates
The PBSYC2.SQL script creates the following PowerBuilder stored procedure in the Adaptive Server sybsystemprocs database:
|
PBSYC2.SQL stored procedure |
What it does |
|---|---|
|
sp_pb170table |
Retrieves information about those tables in the database for which the current user has SELECT permission. This version of sp_pb170table replaces the default version of sp_pb170table installed by the PBSYC.SQL script. |
You can use the ISQL or SQL Advantage tools to run the stored procedure scripts outside PowerBuilder.
ISQL is an interactive SQL utility that comes with the Open Client software on the Windows platforms. If you have ISQL installed, use the following procedure to run the PowerBuilder stored procedure scripts.
For complete instructions on using ISQL, see your Open Client documentation.
To use ISQL to run the PowerBuilder stored procedure scripts:
-
Connect to the sybsystemprocs Adaptive Server database as the system administrator.
-
Open one of the following files containing the PowerBuilder stored procedure script you want to run:
PBSYC.SQL
PBSYC2.SQL
-
Issue the appropriate ISQL command to run the SQL script with the user ID, server name, and (optionally) password you specify. Make sure you specify uppercase and lowercase exactly as shown:
isql /U sa /S SERVERNAME /i pathname /P { password }
Parameter
Description
sa
The user ID for the system administrator. Do not change this user ID.
SERVERNAME
The name of the computer running the Adaptive Server database.
pathname
The drive and directory containing the SQL script you want to run.
password
(Optional) The password for the sa (system administrator) user ID. The default Adaptive Server installation creates the sa user ID without a password. If you changed the password for sa during the installation, replace password with your new password.
For example, if you are using PowerBuilder and are accessing the stored procedure scripts from d:\server, type either of the following:
isql /U sa /S TESTDB /i d:\server\pbsyb.sql /P isql /U sa /S SALES /i d:\server\pbsyc.sql /P adminpwd
SQL Advantage is an interactive SQL utility that comes with the Open Client software on the Windows platform. If you have SQL Advantage installed, use the following procedure to run the PowerBuilder stored procedure scripts.
For complete instructions on using SQL Advantage, see your Open Client documentation.
To use SQL Advantage to run the PowerBuilder stored procedure scripts:
-
Start the SQL Advantage utility.
-
Open a connection to the sybsystemprocs Adaptive Server database as the system administrator.
-
Open one of the following files containing the PowerBuilder stored procedure script you want to run:
PBSYC.SQL
PBSYC2.SQL
-
Delete the use sybsystemprocs command and the go command at the beginning of each script.
SQL Advantage requires that you issue the use sybsystemprocs command by itself, with no other SQL commands following it. When you open a connection to the sybsystemprocs database in step 2, you are in effect issuing the use sybsystemprocs command. This command should not be issued again as part of the stored procedure script.
Therefore, to successfully install the stored procedures, you must delete the lines shown in the following table rom the beginning of the PowerBuilder stored procedure script before executing the script.
Before executing this script
Delete these lines
PBSYC.SQL
use sybsystemprocs
go
PBSYC2.SQL
use sybsystemprocs
go
-
Execute all of the statements in the SQL script.
-
Exit the SQL Advantage session.
