Home

	Expand Search

Access Service User's Guide DirectConnect for AS/400

Chapter 7 Using Sybase mode commands

Chapter 7

Using Sybase mode commands

This chapter describes the Transact-SQL (T-SQL) syntax subset recognized in the Sybase SQL transformation mode. SQL commands, clauses, and other syntactical elements are presented in alphabetical order.

Many of the commands use up to three-part table names. However, because the AS/400 supports only one-part or two-part names, SQL transformation removes the first part of an AS/400 three-part table name.

All Sybase mode commands are issued as language commands, unless otherwise noted.

The following T-SQL commands are presented in this chapter. Each command section contains a description, syntax, examples, and comments.

For more information about the Sybase SQL transformation mode commands, see the Sybase SQL Server Reference Manual.

Any reference to Adaptive Server applies to SQL Server 11.1 or earlier, unless otherwise indicated.

begin transaction

Description

Marks the starting point of a user-specified transaction.

Syntax

begin transaction [transaction_name]

Parameters

is the name assigned to the transaction. It must conform to the rules for identifiers.

Examples

Example 1

begin transaction

Usage

• The access service accepts this statement but does not generate any corresponding AS/400 SQL statement. When the access service transaction mode is set to short and it receives this statement, it remembers that a transaction is running. As a result, the access service suspends the automatic commitment of SQL statements until it encounters the next commit or rollback statement.

• The access service library accepts the transaction_name, and then strips it off before passing it to the target.

commit transaction

Description

Commits the work resulting from the current transaction.

Syntax

commit transaction {transaction_name}

Parameters

is the name assigned to the transaction. It must conform to the rules for identifiers that are described in the Sybase SQL Server Reference Manual.

Examples

Example 1

commit transaction

Usage

• The access service accepts this statement and translates it to the AS/400 commit statement.

• If a transaction is not currently active, this statement has no effect.

• In Sybase mode, transaction_name is stripped from the statement before it is passed on the target.

• The access service converts both commit transaction transaction_name and commit transaction without a transaction name to a commit statement.

create index

Description

Using a language command, adds a new index to an existing table.

Syntax

create [unique] index index_nameon [[database.]owner.]table_name(column_name [, column_name]...)

Parameters

is an optional keyword that prohibits duplicate index values.

is the name of the index. Index names must be unique within a table but need not be unique within a database.

is the name of the table that contains the indexed column or columns.

is the column to be included in the index. Composite indexes are based on the combined values of up to 16 columns. The sum of the maximum lengths of all the columns used in a composite index cannot exceed 256 bytes.

Examples

Example 1

create index au_id_ind
 on authors (au_id)

Example 2

create index ind1
 on titleauthor (au_id, title_id)

Usage

• Columns of type bit, text, and image cannot be indexed.

• You cannot create an index on a view.

• The access service initially accepts the following T-SQL statement components but then strips them out of the statement:

  • clustered and nonclustered

  • with { fillfactor = x, ignore_dup_key, sorted_data }

  • on segment_name, which specifies the segment where the index is to be created.

create table

Description

Creates a new table.

Syntax

create table [database.[owner].]table_name (column_name
datatype {null | not null}
 [{, next_column }...])
  [on segment_name]

Parameters

is the name of the new table. It conforms to the rules for identifiers and is unique within the database and to the owner.

is the name of the column in the table. It conforms to the rules for identifiers and is unique in the table.

is the datatype of the column. Only system datatypes are used. As shown in Table 7-3 , several datatypes expect a length, n, in parentheses:

     datatype(n)

specifies a null value if a user does not provide a value during an insertion and no default exists (for null), or that a user must provide a non-null value if no default exists (for not null).

indicates that you can include additional column definitions (separated by commas) using the same syntax described for a column definition.

Examples

Example 1

create table titles
     (title_id     tid     not null,
     title     varchar(80)     not null,
     type     char(12)     not null,
     pub_id     char(4)     null,
     price     money     null,
     advance     money     null,
     total_sales     int     null,
     notes     varchar(200)    null,
     pubdate     datetime     not null,
     contract     bit     not null)

Usage

• Transact-SQL allows you to specify null or not null, with a default of not null. The AS/400 allows only not null to be specified, and the default is null. The following table shows how the access service transforms this clause.

  Null transformations during T-SQL to AS/400 create table

  Transact-SQL Specification	Transformed to...
  null	<nothing>
  not null	not null
  <nothing>	not null

• The following table shows the access service transformation of datatype specifications.

  Datatype conversions during T-SQL to AS/400 create table

  Transact-SQL Datatype	AS/400 Datatype
  tinyint	SMALLINT
  smallint	SMALLINT
  int	INT
  numeric(p,s)	NUMERIC(p,s)
  decimal(p,s)	DECIMAL(p,s)
  float (double precision)	FLOAT
  real	REAL
  smallmoney	DECIMAL(10,4)
  money	DECIMAL(19,4)
  smalldatetime	TIMESTAMP
  datetime	TIMESTAMP
  char(n)	CHAR( n)
  varchar(n)	VARCHAR(n)
  text	LONG VARCHAR(n)
  binary(n)	CHAR(n) FOR BIT DATA
  varbinary(n)	VARCHAR(n) FOR BIT DATA
  image	LONG VARCHAR(n) FOR BIT DATA
  bit	SMALLINT

• Be aware that the AS/400 hard-codes text as varchar(32704). As a result, any table with a text field has only a limited amount of space available for any other columns in that table. For example, the AS/400 translates the SQL statement create table test (col1 text, col2 char(37)) to the following:

  create table test (col1
  varchar(32704), col2 char(63))

  The preceding statement fails, because the AS/400 has a total restriction of 32,776 bytes for all columns, and text is already taking up 32,704 bytes of the total space allowed.

create view

Description

Creates a new view.

Syntax

create view [database_name.][owner.]view_name[(column_name [, column_name]...)]
 as select [distinct] select_statement[with check option]

Parameters

is the name of the view. The view name cannot include the database name. It must conform to the rules for identifiers.

is the name of the column in the view. It must conform to the rules for identifiers.

begins the select statement that defines the view.

specifies that the view cannot contain duplicate rows (optional).

completes the select statement that defines the view. It can include more than one table and other views.

indicates that all data modification statements are validated against the view selection criteria. All rows inserted or updated through the view must remain visible through the view.

Examples

Example 1

The following example creates the new view from old view.

create view new_view (col1, col2)
 as select col1, col2 from old_view

Usage

You can use views as security mechanisms by granting authorization on a view but not on its underlying tables.

delete (cursor command)

Description

Using a cursor command, delete removes a row from a table. The row affected must have been made current by a read cursor.

Syntax

Any valid object in the catalog can be substituted for table_name or view_name.

1. delete [[database.]owner.]{table_name | view_name}

2. delete [from] [[database.]owner.]{table_name|view_name}
     where current of cursor_name

Parameters

(after delete) is an optional keyword used for compatibility with other versions of SQL. Follow it with the name of the table or view from which you want to remove rows.

is a standard where clause.

Examples

Example 1

declare c1 cursor for
 select * from tablea for update of col1

delete tablea where current of c1

Usage

The cursor can be reused multiple times before it is deallocated.

delete (dynamic command)

Description

Using a dynamic command, delete removes a row from a table.

Syntax

delete [[database.]owner.]{table_name | view_name} 
 [where search_conditions]

Any valid object in the catalog can be substituted for table_name or view_name.

Parameters

is a standard where clause.

is a valid where clause component. It sets the conditions for the rows that are retrieved. A search condition can include column names, constants, joins, the keywords is null, is not null, or, like, and, or any combination of these items.

Examples

Example 1

  delete from tablea 
 where col1 = "test"

Usage

• Following are relational operators that are supported in search conditions: =, <>, <, >, <=, >=, and like.

• The prepared statement can execute multiple times before it is deallocated.

delete (language command)

Description

Using a language command, delete removes a row from a table.

Syntax

delete [from] [[database.]owner.]{table_name|view_name}
     [where search_conditions]

delete [[database.]owner.]{table_name | view_name}
     [from [[database.]owner.]{table_name | view_name}
     [, [[database.]owner.]{table_name | view_name}]...]
     [where search_conditions]

Parameters

(after delete) is an optional keyword used for compatibility with other versions of SQL. Follow it with the name of the table or view from which you want to remove rows.

(after table_name or view_name) allows you to name more than one table or view to use with a where clause when specifying the rows to delete. The from clause allows you to delete rows from one table based on data stored in other tables, giving you much of the power of an embedded select statement.

is a standard where clause.

is a valid where clause component. It sets the conditions for the rows that are retrieved. A search condition can include column names, constants, joins, the keywords is null, is not null, or, like, and, or any combination of these items.

Examples

Example 1

delete from authors
 where au_lname = "McBadden"

Usage

• You cannot use delete with a multi-table view.

• If you do not use a where clause, all rows are deleted from the table or view that is named after the delete [from] T-SQL keyword parameter.

drop index

Description

Removes an index from a table in the current database.

Syntax

drop index table_name.index_name

The preceding syntax works only in Sybase mode.

Parameters

is the table in which the indexed column is located. The table must be in the current database.

is the name of the index to be dropped.

Examples

Example 1

drop index authors.au_id_ind

Usage

Not defined

drop table

Description

Removes a table definition and all of its data, indexes, triggers, and authorization specifications from the database.

Syntax

drop table [[database.]owner.]table_name

Parameters

is the name of the table to be dropped.

Examples

Example 1

drop table authors

Usage

Not defined

drop view

Description

Removes one or more views from the database.

Syntax

drop view [databasename], [owner].view

Parameters

is the name of the view to be dropped. The name must be a legal identifier and cannot include a database name.

Examples

Example 1

drop view new_price

Usage

Each time a view is referenced, another view or stored procedure checks the existence of the view.

grant

Description

Assigns authorizations to users.

Syntax

To grant authorization to access database objects:

grant {all [privileges]| permission_list}
 on {table_name [(column_list)]
 | view_name[(column_list)]
 | stored_procedure_name}
 to {public | name_list}
 [with grant option]

To grant authorization to create database objects:

grant {all [privileges] | command_list}
 to {public | name_list}

Any valid object in the catalog can be substituted for table_name or view_name.

Parameters

when used to assign authorization to access database objects (first syntax format), all specifies that all privileges applicable to the specified object are granted or revoked.

is a list of authorizations granted.

is a list of commands granted.

is the name of a table in the database.

is a list of columns, separated by commas, to which the privileges apply.

is the name of a view in the current database. Only one view can be listed for each grant command.

is the name of a stored procedure in the database.

is all users of the "public" group, which includes all users of the system.

is a list of users' database names or group names or both, separated by commas.

allows the users specified in name_list to grant the privileges specified by permission_list to other users.

Examples

Example 1

grant insert, delete
 on titles
 to mary, sales

Example 2

grant update
 on titles (price, advance)
 to public

Example 3

grant create database, create table
 to mary, john

Example 4

grant all on titles
 to public

Example 5

grant all
 to public

Example 6

grant update on authors
 to mary
 with grant option

Example 7

grant select, update on titles(price)
 to bob
 with grant option

Usage

• You can substitute the word from for to in the grant syntax.

• You can only grant or revoke authorizations on objects in the current database.

• role_name, which allows you to grant authorizations to all users who have been granted a specific role, is not supported. However, if you include it in the command, an error does not occur.

insert (dynamic command)

Description

Using a dynamic command, insert adds a new row to a table or view.

Syntax

insert [database.[owner.]]{table_name|view_name}
 [(column_list)] 
 values (? [, ?]...) 

Any valid object in the catalog can be substituted for table_name or view_name.

Parameters

is a list of one or more columns to which data is to be added. The columns can be in any order, but the incoming data (whether in a values clause or a select clause) is in the same order.

is a keyword that introduces a list of expressions.

specifies a parameter marker passed by the application.

Examples

Example 1

insert titles
 (title_id, title, type, pub_id, notes, pubdate,
         contract) 
 values (?, ?, ?, ?, ?, ?, ?)

Usage

insert (language command)

Description

Using a language command, insert adds a new row to a table or view.

Syntax

insert [into] database.[owner.]{table_name|view_name}
 [(column_list)] 
 values (value1, [,value2]...) 

Any valid object in the catalog can be substituted for table_name or view_name.

Parameters

is optional.

is a list of one or more columns to which data is to be added. The columns can be in any order, but the incoming data (whether in a values clause or a select clause) is in the same order.

is a keyword that introduces a list of expressions.

Examples

Example 1

insert titles (title_id, title, type, pub_id, notes, pubdate, contract)
 values (docid, docno, docdate)

Usage

prepare transaction

Description

Determines whether a server is still connected.(not supported)

Syntax

prepare transaction 

Examples

Example 1

prepare transaction 

Usage

revoke

Description

Revokes authorizations from users.

Syntax

revoke [grant option for]
 {all [privileges]| permission_list}on {table_name [(column_list)]
 | view_name [(column_list)]
 | stored_procedure_name}
 from {public | name_list}
 [cascade]

revoke {all [privileges] | command_list}
 from {public | name_list}

Parameters

(in the first syntax format) specifies that all privileges applicable to the specified object are revoked when used to revoke authorizations to access database objects. The second syntax format can revoke create command authorizations:

• When the System Administrator uses this command, all create authorizations are revoked.

• When the database owner uses this command, all create authorizations are revoked except create database.

is a list of authorizations to be revoked.

is a list of commands for which authorizations are to be revoked.

is the name of the specified table in the database.

is a list of columns, separated by commas, to which the privileges apply. If columns are specified, only select and update authorizations can be revoked.

is the name of a view in the current database. Only one view can be listed for each revoke statement.

is the name of a stored procedure in the database. Only one object can be listed for each revoke statement.

is all users of the "public" group, which includes all users of the system.

is a list of users' database names and group names, separated by commas.

prohibits the users specified in name_list from granting the privileges specified by permission_list to other users.

revokes grant authorization for the privileges specified in permission_list from the users specified in name_list, and from all users to whom they granted privileges.

Examples

Example 1

revoke insert, delete
 on titles
 from mary, sales

Example 2

revoke all on titles
 from public

Usage

• Authorizations can only be revoked on objects in the current database.

• grant and revoke commands are order-sensitive. When a conflict occurs, the most recently issued command takes effect.

• The word to can be substituted for the word from in the revoke syntax.

• The access service does not support role_name.

rollback transaction

Description

Rolls back a user-specified transaction to the beginning of the transaction.

Syntax

rollback {transaction | tran | work}
 [transaction_name]

Parameters

is another term for transaction.

is another term for transaction.

is the name assigned to the transaction. It must conform to the rules for identifiers.

Examples

Example 1

rollback transaction

Usage

• In Sybase mode, transaction_name is stripped from the statement before it is passed to the target.

• The access service converts rollback transaction rollback work, and rollback tran statements to a rollback statement.

select

Description

Retrieves rows from database objects. You can issue this command either as language command or a CT-Library cursor request.

Syntax

select select_list 
 [from [[database.]owner.]{table_name |view_name}
 [,[[database.]owner.]{table_name|view_name}]...] 
 [where search_conditions] 

Parameters

is one or more of the following items:

• A list of column names in the order in which you want them returned

• An aggregate function

• Any combination of the items listed previously

indicates the particular tables and views to use in the select statement.

lists tables and views used in the select statement. If more than one table or view is in the list, their names are separated by commas. Table names and view names are given correlating names. This is done by providing the table or view name, then a space, then the correlation name, such as:

    select * 
         from publishers t1, authors t2

sets the conditions for the rows that are retrieved. A search condition can include column names, constants, joins, the keywords is null, is not null, or, like, and, or any combination of these items.

finds a value for each group. These values appear as new columns in the results, rather than as new rows.

sorts the results by columns.

sets conditions for the group by clause, similar to the way that where sets conditions for the select clause. There is no limit on the number of conditions that can be included.

returns a single result set that combines the results of two or more queries. Duplicate rows are eliminated from the result set unless the all keyword is specified.

indicates that the cursor is a read-only cursor and that updates cannot be applied to rows made current by it.

indicates that the cursor is an updatable cursor, and that the rows it makes current can be deleted or updated.

Examples

Example 1

select count(*) from publishers for read only

Example 2

select pub_id, pub_name, city, state from publishers for read only

Example 3

select pub_name, pub_id
 from publishers 

Example 4

select type, price from titles 
 where price > @p1 for update of price

Example 5

select stor_id, stor_name from sales union
 select stor_id, stor_name from sales_east

Usage

• The TEXTPTR() function cannot appear in the select list.

• All SQL Server 10.x aggregate functions are supported:

  • sum ( [all | distinct] )

  • avg ( [all | distinct ] )

  • count ( [all | distinct] )

  • count (*)

  • max (expression)

  • min (expression)

• The access service does not transform correlation names.

• You can issue the select command either as a language command or a client-based cursor request.

• If a cursor is passed a new set of parameters before it is opened, it can be reused multiple times.

• If passed as a cursor command, the data values used in the where clause search conditions are passed as cursor parameters. These parameters use the datatype associated with the column.

• Cursor parameters are indicated with an @ (at) symbol.

• Refer to sp_capabilities for specific functions that the access service supports.

truncate table

Description

Removes all rows from a table.

Syntax

truncate table [[database.]owner.]table_name

Parameters

is the name of the table to be truncated.

Examples

Example 1

truncate table authors

Usage

• The client application passes the truncate table command to the access service as a language command.

• In Sybase mode, the access service converts truncate table to a delete command without a where clause.

update (cursor command)

Description

Changes data in a row made current by a read cursor command, either by adding data or by modifying existing data.

Syntax

update [[database.]owner.]{table_name | view_name}
     set column_name1 = @p1]
     [, column_name2 = @p2]...

Any valid object in the catalog can be substituted for table_name or view_name.

Parameters

specifies the column name and assigns the new value. The value passes as a cursor parameter.

Examples

Example 1

update authors 
 set au_lname = @p1

The row made current by the cursor authors_cursor is modified, and the column au_lname is set to the value of the parameter @p1.

Usage

• The cursor can be reused multiple times before it is deallocated.

• You can include a from clause in the update statement, but because the AS/400 does not support it, the access service will ignore it.

update (dynamic command)

Description

Using a dynamic command, update changes data in existing rows of the referenced table.

Syntax

update [[database.]owner.]{table_name | view_name} 
 set     column_name1 = ?
     [, column_name2 = ?]...[ where search_conditions...] ]

Parameters

specifies the column name and assigns the new value. The value passes as a parameter.

is a standard where clause.

sets the conditions for the rows that are retrieved. A search condition can include column names, constants, joins, the keywords is null, is not null, or, like, and, or any combination of these items.

Examples

Example 1

update authors 
 set au_lname = ?
 where au_id = ?

The au_lname column is set to the value of <parameter 1> (indicated by a "?"), where the value of au_id is equal to the value of <parameter 2> (indicated by the second "?").

Usage

• You can include a from clause in the update statement, but because the AS/400 does not support it, the access service will ignore it.

• You can substitute table_name and view_name with any valid object in the catalog.

• Following are relational operators that are supported in search conditions: =, <>, <, >, <=, >=, and like.

update (language command)

Description

Changes data in existing rows, either by adding data or by modifying existing data. Use this as a language command.

Syntax

update [[database.]owner.]{table_name | view_name}
     set [[[database.]owner.]{table_name.|view_name.}]    column_name1 =
     {expression1|NULL|(select_statement)}
     [column_name2 =
     {expression2|NULL|(select_statement)}]...
     [,[[database.]owner.]{table_name|view_name}]...]
     [where search_conditions]

Parameters

specifies the column name and assigns the new value. The value can be an expression o r a null. When more than one column name and value pair are listed, they must be separated by commas.

is a standard where clause.

sets the conditions for the rows that are retrieved. A search condition can include column names, constants, joins, the keywords is null, is not null, or, like, and, or any combination of these items.

Examples

Example 1

update authors
 set au_lname = "MacBadden"
 where au_lname = "McBadden"

Usage

• You can include a from clause in the update statement, but because the AS/400 does not support it, the access service ignores it.

• You cannot update views defined with the distinct clause. However, when the view is created, the select language command allows the term distinct to be used with it. For more information, see the Sybase SQL Server Reference Manual.

use

Description

Accesses an existing database.

Syntax

use database_name 

Parameters

is the name of the database you want to access.

Examples

Example 1

use authors

Usage

• The access service returns the database name as part of the result set in an sp_sqlgetinfo call. It is located in the SQL_DATABASE_NAME attribute.

• If you issue a use database_name command with more than 127 characters, DirectConnect stores only the first 127 characters that you enter on this command.