Pipes

Pipes enable the results fetched from a connection to be used as the parameters for another statement. All statements are executed against the Connection window that owns the Results window. Pipes cannot span multiple connections but could be expanded to do the following:

Copy table contents from one Connection window to another.
Perform administrative tasks. For example, you could select user names from one Connection window and apply them as a parameter to a CREATE USER statement in another Connection window.
Support any function that requires a result set as an input parameter.

Defining Pipes

A pipe consists of an SQL statement (which may or may not take parameters), a list of the parameters to use, and a parameter option. This information is stored in the Admndemo.ini file. To create a new pipe or modify an existing pipe, choose the Edit command from the Pipe menu.

Pipe Definition Example

The following example shows a pipe (as defined in the Admndemo.ini file) that can be used in conjunction with the Tables list to drop all selected tables:

[Drop Table/View]
sql=DROP ? ?.?.?
parms=4,1,2,3
parmopt=value
delete=1

The following table describes the items in the example pipe definition.

Item	Description
sql	The SQL statement to be executed. Use a parameter marker (?) for each parameter.
parms	The list of parameters to be placed into the SQL statement. These values are 1-based and correspond to the columns in the result set produced by the function. (For example, in the result set produced by the Tables item, column 1 is Qualifier, column 2 is Owner, column 3 is Name, and column 4 is Type.)
parmopt	The parameter passing method to be used, either VALUE or ADDRESS. For VALUE, a textual substitution is performed so that the SQL statement is executed as a complete statement with no unused parameter markers. For ADDRESS, the parameter markers are left in and the statement is prepared. The parameters are defined with SQLBindParameter, and SQLExecute substitutes the values of each parameter.
delete	If this is set to 1, the row is deleted from the result set after processing the pipe. If this is set to 0, the row is not deleted.

Piping By Value Example

Using the Drop Table/View pipe as an example, assume that you have created a result set with the following values:

(column)	Qualifier	Owner	Table	Type
(data)	accounting	joe	sales	table

When you choose Do from the Pipe menu and click the Pipe button, Admin Demo loads the pipe information and processes each selected row of the result set. When the row is processed, the parameter method of 'value' indicates that each parameter marker is to be replaced before execution. The 'parms' entry indicates that the order of parameters is 4, 1, 2, 3, which, when correlated to the result set, is Type, Qualifier, Owner, and Table. Therefore, the resulting SQL statement is:

DROP table accounting.joe.sales

Because the statement is complete, it is passed directly to SQLExecDirect, which executes the statement.

Passing data by value is often required due to the SQL involved. For example, most data sources do not allow parameter markers to mark keywords, as in this example.

Piping By Address Example

Parameters can be used to execute SQL statements many times. For example, parameters are useful for copying a table from one data source to another, as in the following code:

SQLPrepare(hstmt2, "INSERT INTO SALES VALUES (?,?,?)", SQL_NTS);
SQLExecDirect(hstmt1, "SELECT * FROM SALES", SQL_NTS);

/* Bind columns returned on hstmt1 to output buffers. */
/* Bind parameters markers to the same buffers. */

rc=SQLFetch(hstmt1);
while(rc != SQL_NO_DATA) {
   SQLExecute(hstmt2);
   rc=SQLFetch(hstmt1);
}

where:

hstmt1 is on a connection from server1
hstmt2 is on a connection from server2

This example does not show calls to SQLBindCol to bind buffers to the result set columns or calls to SQLBindParameter to bind the same buffers to the parameter markers. It also does not show error checking.

Although this is not a very sophisticated piece of code, it does show the power of the ODBC API: The results from a single SELECT statement were easily inserted into a table in another data source.

Admin Demo provides this type of pipe interface by allowing the 'address' parmopt keyword value. As an example, here is a pipe called Record Select that takes all selected rows and inserts their values into another table:

[Record Select]
sql=INSERT INTO CatchSelection VALUES (?,?,?,?)
parms=1,2,3,4
parmopt=address

In this example, the SQL statement is an INSERT statement that inserts four values into the CatchSelection table. Because the parmopt keyword is 'address', Admin Demo prepares the statement and calls SQLBindParameter for each parameter marker. The parms keyword specifies that columns 1, 2, 3, and 4 of the result set (Qualifier, Owner, Name, and Type) be linked with parameters 1, 2, 3, and 4, in that order. For each row of the result set, Admin Demo then copies the row values to the parameter buffers and calls SQLExecute.

Using the result set in the previous example, the parameter data would be as shown in the following table.

(column)	Qualifier	Owner	Table	Type
(data)	accounting	joe	sales	table

Selecting the data from CatchSelection would show this row in the table when the pipe was finished running.

Limitations of Pipes

Parameter Count

Only 18 parameters are allowed per statement.

Parsing

The parsing engine of the SQL statement for the VALUE parameter option is not robust. It simply identifies the parameter marker '?' and assumes it is the next parameter to replace. This means that the statement:

SELECT '?', ? FROM bigset

where only one parameter, Owner, was selected, will result in a final statement of:

SELECT 'joe', ? FROM bigset

This is not the intended result. If your needs exceed this limitation, you should either modify Admin Demo or create your own application.