Using a SQL template and a datalink

ClDbStmt is the ODL representation of a database-specific SQL statement. A SQL statement is constructed with the creation of a clDbStmt, which is done using MSG-IDE or TRAN-IDE.

Building a SQL statement

1. Start MSG-IDE. Select Start | Programs | Sybase | e-biz Impact 5.4 | Msg-IDE.

2. If Database Interface does not appear in the object list on the left of the MSG-IDE Browse window, select Tools | Database Interface in the Message Interface Development window.

3. In the Browse window, select Database Interface in the object list and click Add. The Database Interface Object window opens.

4. Click New. The SQL Statement Builder window appears.

5. In the Stmt Name field, type a descriptive name for the statement object that you are building.

6. In the Stmt ID# field, type the ID number of the statement object that you are building. The ID number allows other objects to reference the statement object.

7. In the Tables section, click the table that you want to use. The columns in the selected table appear in the Columns list. To insert a table name in the SQL syntax, double-click the table name.

8. Click a column in the list to display the column’s datatype in the status bar at the bottom of the window. Double-click the column name to insert it into the SQL syntax.

9. Click a procedure in the list to display its parameters in the status bar. To insert a procedure into the SQL syntax, double-click the procedure. The procedures section contains a list of the available procedures in the database. A procedure is a piece of user-defined logic contained in its database.

10. To insert the contents of the data object into the SQL syntax, double-click the Data Object. The Data Object name enclosed by << >> appears in the Syntax Box. This is an output Object. Field objects, indicated by type clTrFld, are read-only.

11. To insert the return value from a procedure into the data object in the SQL syntax, press Shift and double-click the Data Object. The Data Object name enclosed by >> << appears in the Syntax Box. This is an input object. For example, >>object<<

12. To insert the content of the Data Object, while at the same time inserting the return value from a stored procedure into the Data Object, type the name of the stored procedure, enclosed with <> <>. For example, <>object<>

13. Insert SQL into the syntax box by clicking the SQL verbs and the keypad, and by double-clicking items in the Tables, Columns, Procedures, and Data Objects lists.

    To replace text in the box, highlight the text you want to replace and click the replacement

    The Database Interface Object statement can contain more than one SQL statement. The maximum size for the SQL in this box is 2000 bytes.

    Buttons are provided for common SQL syntax and functions. Click these buttons to build the SQL syntax with a minimal amount of typing

Datalink types

The following table lists all Datalink types.

SQL statement options

You expand the Statement Object definition by changing the source for SQL, expanding the SQL syntax, or automatically committing to database changes.

Defining SQL statement options

1. In the SQL Statement Options dialog box,

2. Click the down-arrow in the SQL Data Obj field to select an existing Data Object, or click Detail (...) to open the Define window and create a new Data Object

   If your SQL commands vary depending on the result of other operations, use a Data Object to store the SQL and reference the Data Object. The SQL commands in the Data Object selected in this dialog box is executed. The SQL commands in the Syntax box of the SQL Statement Builder dialog box is ignored.

3. Click the auto commit check box to automatically commit the changes to the database upon completion.

   This guarantees the changes are permanent without a manual confirmation.

4. Click the inline data obj. references check box to enable inline Data Object references.

   References in the SQL Data Object can be expanded by substituting values for existing parameters.

   For example, given {string foo;} and {foo=“nextus”}, then “select * from sales where id like <<foo>>“ is expanded to “select * from sales where id like ‘nextus’”.

   This is useful if your ODBC or database does not support extended parameters.

5. Do one of the following:

   • Click Done to accept the settings.

   • Click Cancel to exit without accepting the settings.

SQL statement result options

In the SQL Statement Result Options dialog box, you can assign a name to the result set of the statement object, execute a function on the result set, define column and row separators, determine a maximum number of rows to be included in the result, and specify other aspects of the statement output.

Defining SQL statement result options

1. Click the down arrow in the Data Obj field to select an existing Data Object or click Detail to build a new Data Object.

   Individual column data can be bound to Data Objects using the Column Bindings dialog box.

   The Data Object stores all of the result rows generated by the Statement Object. The result rows include all separators, and are concatenated as they are processed,; ensure that the Data Object is large enough to hold all of the result data. Use a Data Object type of either BLOB or string as both grow as needed.

2. Click the down arrow in the Callback field to select an existing function to execute against each result row, or click Detail to build a new function. The function executes after each result row is gathered and acts on the data in the new row. The following table lists possible callback events generated by the Database Interface Object.

   Event	Condition
   DBE_SELECT	Got a row of data on a select or stored procedure.
   DBE_ERR_OOM	Out of memory error.
   DBE_ERR_SQL	SQL syntax error at run time.
   DBE_ERR_ARG	Invalid arguments.
   DBE_ERR_NOROWS	No rows affected on insert/update/delete command.
   DBE_NOTIFY	Insert/update/delete completed successfully.
   DBE_EMPTY	SELECT has returned empty result set.
   DBE_RESULT	SELECT has returned entire result set, which has at least one result row.

   See “Defining function objects” for more information about building a function.

3. In the Col. Separator field, type the string to use as a column separator. The string can include Escape sequences.

4. In the Row Separator field, type the string to use as a row separator. The string can include Escape sequences.

5. In the Maximum Rows field, type the maximum number of rows to be included in the result-set row count.

   The default value is 0, which means all rows. The maximum value is 2,147,483,647.

6. Click the No trunc check box to leave trailing white space on each result column.

7. Click the Multirow check box to return all result rows. Otherwise, only the first result row is returned.

8. Select the No Fetch option if you do not want to automatically fetch each result row after executing the Callback function. Otherwise, result rows are automatically fetched.

   You can use the fetch( ) method to step through the result set. The fetch() method triggers the callback function each time it is called.

9. Select the Skip Last Row Sep option if you do not want a row separator placed at the end of the last row.

10. Select the Skip Last Col Sep option if you do not want a column separator placed at the end of the last column in each row.

11. Do one of the following:

    • Click OK to accept the settings.

    • Click Cancel to exit without accepting the settings.

SQL bindings

In the SQL Bindings dialog box, when you include a column-generating procedure in the SQL syntax, you can move a piece of column data into a Data Object after each result row is returned. This Data Object can then be used by the SQL Statement Result Options callback function or other objects outside the database interface object.

Binding column data to a data object

1. Click the column name in the Column Box, choose a data object from the Data Object drop-down list, and click Set.

   The column box shows which column data is bound to which data object. The columns appear on the left side of the box and the Data Objects bound to those columns appear on the right side of the box. The link is represented by ==>.

2. Click on the down arrow in the Data Object field to open a drop-down list of Data Objects and select the Data Object that you want to bind to a column.

3. Click the Auto Bind check box to automatically bind any column data to a Data Object that has the same name as the column.

   For example, if the Auto Bind box is selected, the result set has a column named FName, and you have a Data Object named FName, the contents of the column is placed into the Data Object of the same name every time a result row is returned. The name of the column must be identical to the name of the Data Object. Selecting Auto Bind automatically matches identically named column and Data Objects and disallows a match in which the names vary in any respect.

4. Do one of the following:

   • Click Accept to accept the settings.

   • Click Clear to clear all the bindings on all columns.

   • Click Cancel to exit without accepting the settings.

SQL statement syntax

SQL statements are passed to the underlying system database when executed, therefore, they must conform to the system database syntax. SQL statements can contain, input, inline, and datalinks. Embedded datalinks within the SQL statement are translated or converted to appropriate system database values.

SQL statements, such as select, insert, update, and drop, should not contain outbound (output, in-out) datalinks. Use these with Stored Procedures only.

Example of a SQL statement with a datalink:

Select * from customers where id = <<odl_int>>

Stored procedure syntax

A stored procedure template syntax requires:

• Stored procedure name on the system database.

• The correct number of parameters the stored procedure on the system database expects.

• An optional output datalink to store the return value of the procedure.

Stored procedure templates can contain all four types of datalinks.

The format for the stored procedure template is:

{ >>rv<< = call storedProcedureName (parameters) }

where rv is an optional datalink to hold the return value of the stored procedure. Not all stored procedures have a return value.

storedProcedureName is the name of the stored procedure on the system database.

parameters are parameters for the stored procedure. Parameters can include string, numeric literals, or any of the four datalink types.

Examples of a stored procedure call:

{ call sp1('foo', 'bar', 1, 2, 3, <<int_datalink>>) }

{ >>int_rv<< = call sp2(>>string_value<<,<int_value>>)}

Advanced datalink features

New datalinks features are transparent to existing and older project files. The new features are designed to give you more precise control of the data being sent to and retrieved from the system database. Use of the new features is optional.

A datalink consists of four parts:

• Datalink direction. One of four types: in, in-out, out, and inline.

• (Optional) Pre-cast to represent the datalink as a particular database column type. This is used for input type datalinks to represent ODL data as a different datatype in the system database.

  An example is a datalink that a string type in ODL. You can represent this as a type of varchar data to be used in a SQL statement instead of the default type of char. Casting is used in this situation.

• ODL variable name the datalink is bound to. Variables used as datalinks must be of global scope.

• (Optional) Post-cast to represent data from the system database as a particular ODL datatype. Use this for outbound datalinks to represent the data retrieved from system database as a particular ODL datatype.

  An example is in a stored procedure, to store binary values from an outbound datalink as sequence of null terminated characters to store in an ODL string, cast this into a char.

All datalinks have a default ODL type to system database type conversion. These are listed in the following table.

ODL datatypes not listed in conversion table above are treated as SQL_char.

Pre-cast and post-cast syntax

Pre-cast

Datalink syntax:

<< (Pre-cast) datalink_name [Post-cast] >> 

Pre-cast represents ODL datatypes in the datalink as a different datatype on the system or a datatype with a particular size and precision, which is also known as “sql_digit.”

Pre-cast is optional, it may not appear in the datalinks.

Syntax and options

Allowable syntax for pre-cast:

(cast_type)

(cast_type, sql_size)

(cast_type, sql_size, sql_digit)

Possible cast_type for pre-cast are:

• char

• varchar

• longvarchar

• binary

• varbinary

• longvarbinary

sql_size and sql_dIgit

The sql_size is the user-specified size for the data being sent to the system database. The sql_size can be used to limit the amount of data being sent to the system database if the specified size is smaller than the ODL data size.

sql_digit is used for precision.

For fixed length datatypes, these two options are disregarded.An example of a pre-cast datalink is:

<<(varchar, 10)odl_string>>

Datalink Syntax:

>> (Pre-cast) datalink_name [Post-cast] <<

where:

>> is the datalink type. This could be one of four: <<. >>, <>, <|

Pre-cast is optional.

datalink_ name is the ODL variable name bound to the datalink.

Post-cast is optional.

Post-cast

Post-cast fine tunes the results retrieved from the system database so they can be stored in outbound datalinks in a certain way.

Syntax and options

Allowable syntax for post-cast:

[cast_type]

[cast_type, varible_buffer_size]

Possible cast_type for post-cast:

• char

• long

• binary

• float

The variable_buffer_size is the buffer size to retrieve from system database.

An example of a post-cast datalink is:

>>odl_string[char, 10]<<

Additional performance considerations are:

• Each clDbi object owns multiple clDbStmts during run time. At any given time, only one clDbStmt can be executed. ClDbStmts and associated SQL statements not in use are cached until they are selected to be the active statement and executed.

• Caching of statements significantly improves performance, however, ClDbStmts and associated SQL statements can only be cached in the following scenarios:

  • The SQL statement in the ClDbStmt does not contain inline datalinks.

    Because values of inline datalinks are expanded within the statement and values may change during run time, this type of SQL statement cannot be cached.

  • The SQL statement within the ClDbStmt is constructed and defined by the SQL Statement Builder before run time.

    ClDbStmts using an ODL datalink string to hold SQL statement cannot be cached because its value may change during run time.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF