Installing stored procedures in Adaptive Server databases

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 are the PowerBuilder stored procedure scripts?

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.

PBSYC.SQL script

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_pb190column

Lists the columns in a table.

sp_pb190pkcheck

Determines whether a table has a primary key.

sp_pb190fktable

Lists the tables that reference the current table.

sp_pb190procdesc

Retrieves a description of the argument list for a specified stored procedure.

sp_pb190proclist

Lists available stored procedures and extended stored procedures.

If the SystemProcs DBParm parameter is set to 1 or Yes (the default), sp_pb190proclist displays both system stored procedures and user-defined stored procedures. If SystemProcs is set to 0 or No, sp_pb190proclist displays only user-defined stored procedures.

sp_pb190text

Retrieves the text of a stored procedure from the SYSCOMMENTS table.

sp_pb190table

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_pb190table. If you want to replace the default version of sp_pb190table 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_pb190index

Retrieves information about all indexes for a specified table.


PBSYC2.SQL script

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_pb190table.

The default version of sp_pb190table is installed by the PBSYC.SQL script. PowerBuilder uses the sp_pb190table 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_pb190table 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_pb190table 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_pb190table

Retrieves information about those tables in the database for which the current user has SELECT permission.

This version of sp_pb190table replaces the default version of sp_pb190table installed by the PBSYC.SQL script.


How to run the scripts

You can use the ISQL or SQL Advantage tools to run the stored procedure scripts outside PowerBuilder.

Using ISQL to run the stored procedure scripts

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:

  1. Connect to the sybsystemprocs Adaptive Server database as the system administrator.

  2. Open one of the following files containing the PowerBuilder stored procedure script you want to run:

    PBSYC.SQL

    PBSYC2.SQL

  3. 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

Using SQL Advantage to run the stored procedure scripts

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:

  1. Start the SQL Advantage utility.

  2. Open a connection to the sybsystemprocs Adaptive Server database as the system administrator.

  3. Open one of the following files containing the PowerBuilder stored procedure script you want to run:

    PBSYC.SQL

    PBSYC2.SQL

  4. 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


  5. Execute all of the statements in the SQL script.

  6. Exit the SQL Advantage session.