Sybase Views

Stored Procedures

After making the model definitions, you must run the Create Table facility to generate SQL command scripts to create the associated database tables and stored procedures for that view. The output from the Create Table facility can be modified so that a view is created instead of a table. The create table and create index statements must be removed from the Create Table output.

If a view already exists in the DBMS, the view can be imported into a Uniface entity using the Load Definitions utility. If the view is loaded into Uniface, the entity representing the view must be modified to include a primary key. This primary key is never actually used, but is required by Uniface.

Referential Integrity

Sybase does not allow the creation of referential integrity controls on views. The view merely displays data in base tables. The referential integrity controls must be created on the base tables.

Renaming Fields in a Table

If a table has uppercase or mixed-case field names, a Sybase view can be created on top of the table to rename the fields to lowercase names which Uniface can use.

The following conditions must be met when using views to rename fields:

• Segmented fields (Text or Image) can never be renamed.

• If a single table view contains a segmented field and a renamed field, the SYB user option delay textptr check must be on . (Multi-table views always use the delay textptr check method, so it is not necessary to specify this option when using multi-table views.)

• If a view contains a segmented field, the primary key fields cannot be renamed.

Updating the View

If you want to use the view for update, delete or insert, you have to be able to update the view. For this to work, the following rules apply:

• The view must be over a single table only.

• The view must contain all mandatory fields.

Text or Image Data Types

When a view is used which includes Text or Image fields, the connector must use the base table name when determining if the text pointer is valid. The SYB connector must query the database to determine the name of the base table.

The fetch stored procedure cannot be used for views which contain Text or Image fields since it contains the view name instead of the base table name. Instead, dynamic SQL is built so that the entity can be referred to by the base table name.

If the view is across more than one table, the SYB connector must determine the name of the base table for each individual Text or Image data type. Also, the textvalid() check is postponed until the actual Write of a Text or Image field, instead of during the Fetch. In this way, the appropriate view name can be used for the Fetch, because the fields do not all have the same base table.

Multi-Table Views

The insert and update procedures must be modified so that they perform multiple inserts and updates, one for each individual base table.

The following examples describe the modified procedures:

• Base table 1

  create table base_table1
  (pk int not null, name1 char(10) null,
  seg1 text null)
  

• Base table 2

  create table base_table2
  (pk int not null, name2 char(10) null,
  seg2 text null)
  

• View

  create view view_seg as
  select base_table1.pk, name1, name2, seg1, seg2
  from base_table1, base_table2
  where base_table1.pk = base_table2.pk
  

• Update ProcScript

  create procedure view_seg_UPD
  @update1 int, @update2 char(10)
  @where1 int
  as begin
  update base_table1
  set pk = @update1, name1 = @update2
  where (pk = @where1)
  update base_table2
  set pk = @update1, name2 = @update3
  where (pk = @where1)
  end
  

• Insert ProcScript

  create procedure view_seg_INS
  @insert1 int, @insert2 char(10)
  as begin
  insert into base_table1
  values (@insert1, @insert2, NULL)
  insert into base_table2
  values (@insert1, @insert3, NULL)
  end