drop table - Component Integration Services User s Guide

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

Syntax drop table [[database.]owner.]table_name [, [[database.]owner.]table_name ]...

Usage Usage

• Component Integration Services processes the drop table command when the table on which it operates has been created as a proxy table.

Component Integration Services forwards the entire request (or part of it) to the server that owns the actual object if the table was not created with the existing keyword.

• When Component Integration Services forwards the drop table command to a remote server, the table name used is the remote table name. This name may not be the same as the local proxy table name.

• If multiple tables are dropped in a single command, each table is sent as an individual drop table command.

• A table in use by another user or process cannot be dropped and an error stating that the table is in use is returned.

Server Class ASEnterprise •Component Integration Services forwards the following drop table syntax to a remote server configured as class ASEnterprise:

drop table database.owner.table_name Server Class ASAnywhere

• Component Integration Services forwards the following drop table syntax to a remote server configured as class ASAnywhere:

drop table

Server Class direct_connect

• Component Integration Services requests a capabilities response from a remote server with server class direct_connect, but support for drop table is not optional. The behavior of the DirectConnect is database dependent. Server Class db2

• Component Integration Services forwards the following drop table syntax to a remote server configured as class db2:

drop table owner.table_name

execute

Description Runs a system procedure or a user-defined stored procedure. Syntax [execute] [@return_status = ]

[[[server.]database.]owner.]procedure_name[;number] [[@parameter_name =] value |

[@parameter_name =] @variable [output] [,[@parameter_name =] value |

[@parameter_name =] @variable [output]...]] [with recompile]

Usage Usage

• When the execute command is used to issue an RPC to a remote server, Adaptive Server issues the RPC via one of two methods. The method used to issue the RPC determines whether the work performed by the RPC can be part of an on-going transaction. The two methods are as follows: • The RPC is issued via the Adaptive Server’s site handler. This is the

Adaptive Server’s default method of issuing RPCs. In this case, the RPC cannot be part of an on-going transaction.

• The RPC is issued via Component Integration Services. In this case, the RPC can be part of an on-going transaction. To issue RPCs using this method, cis rpc handling must be turned on. This is done via the set command or the sp_configure system procedure.

fetch

Description Returns a row or a set of rows from a cursor result set. Syntax fetch cursor_name [ into fetch_target_list ]

Usage Usage

• When the first fetch is received, Component Integration Services constructs the query defined by the declare cursor command and sends it to the remote server.

If the remote server supports Client-Library cursors, Component Integration Services takes the following steps:

a Declares a cursor:

ct_cursor(command, CS_CURSOR_DECLARE...) b Establishes the cursor row count:

ct_cursor(command, CS_CURSOR_ROWS,... cursor_row_count)

c Opens a Client-Library client cursor to the remote server: ct_cursor(command, CS_CURSOR_OPEN...)

If the remote server does not support Client-Library cursors, Component Integration Services sends a language request to the server. This may require an additional connection to that server.

• If the declare cursor command included a for update clause, the cursor row count is set to 1; otherwise, it is set to the value of the configuration parameter cis_cursor_rows.

• After the cursor is opened or the language request is sent, Component Integration Services issues a Client-Library ct_fetch command to obtain the first row. Client-Library array binding is used to establish the buffer in which to place the fetched results, whether Client-Library cursors or language requests are used to generate the fetchable results. The number of rows that are buffered by a single fetch is determined by the cursor row count discussed above.

Subsequent fetch requests retrieve rows from the buffered results, until the end of the buffer is reached. At that time, Component Integration Services issues another Client-Library ct_fetch command to the remote server. • A fetch against a cursor that has no remaining rows in its result set causes

Component Integration Services to close the remote cursor. Server Class ASEnterprise

If the cursor is read only, Component Integration Services sends a language request to the remote server when the first fetch is received after the cursor is opened. Otherwise, Component Integration Services declares a cursor to the remote server by means of Client-Library.

Server Class ASAnywhere

• Handling of the fetch statement is the same as for ASEnterprise. Server Class ASIQ

• Component Integration Services sends a language request to the remote server when the first fetch is requested after the cursor is opened. Server Class sql_server

• For pre-version 10.0 SQL Server, Component Integration Services sends a language request to the remote server when the first fetch is received after the cursor is opened.

• For version 10.0 or later servers, Component Integration Services declares a cursor to the remote server by means of Client-Library.

Server Class direct_connect

• Component Integration Services treats servers in class direct_connect as if they were version 10.0 or later of class sql_server.

Server Class db2

• Component Integration Services sends a language request to the remote server when the first fetch is requested after the cursor is opened.