Installing PowerBuilder stored procedures in Adaptive Server databases
This section describes how to install PowerBuilder stored procedures
in an Adaptive Server database by running SQL scripts
provided for this purpose.
Sybase recommends that you run these scripts outside PowerBuilder before connecting
to an Adaptive Server database for the first time through the Adaptive
Server (SYC DBMS identifier) native database interface. Although the
PBSYC development environment will run 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 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 CD-ROM. 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 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 development
environment 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_pb100column | Lists the columns in a table. |
| sp_pb100pkcheck | Determines whether a table has a primary key. |
| sp_pb100fktable | Lists the tables that reference the current table. |
| sp_pb100procdesc | Retrieves a description of the argument list for a specified stored procedure. |
| sp_pb100proclist | Lists available stored procedures and extended stored procedures. If the SystemProcs DBParm parameter is set to 1 or Yes (the default), sp_pb100proclist displays |
| sp_pb100text | Retrieves the text of a stored procedure from the SYSCOMMENTS table. |
| sp_pb100table | Retrieves information about all tables in a database, including those for which the current user has no permissions. PBSYC.SQL contains |
| sp_pb100index | 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_pb100table.
The default version of sp_pb100table is
installed by the PBSYC.SQL script. PowerBuilder uses
the sp_pb100table 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_pb100table 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
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_pb100table 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_pb100table | Retrieves information about those tables in the database for which the current user has SELECT permission. This version of sp_pb100table replaces |
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 procedurescripts
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:1<b>isql /U sa /S</b> <i>SERVERNAME</i> <b>/i</b> <i>pathname</i> <b>/P</b> {<i> password</i> }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 the product CD-ROM, type either
of the following (assuming D is your CD-ROM drive):1isql /U sa /S TESTDB /i d:serverpbsyb.sql /P <br />isql /U sa /S SALES /i d:serverpbsyc.sql /P adminpwd
Using SQL Advantage to run the stored procedurescripts
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 from 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.