<@BIND>

Syntax

<@BIND NAME=varname [DATATYPE=datatype] [SCOPE=scope] [BINDTYPE=bindtype] [PRECISION=number] [SCALE=number] [BINDNAME=bindname]>

Description

The <@BIND> meta tag is used to pass a value in the Direct DBMS action using the parameter binding capabilities of ODBC or OCI. This meta tag instructs Tango Server to generate the appropriate binding calls based on the assigned data source type. Binding is useful for passing values to, and retrieving values from, stored procedures.

The NAME attribute is the name of a Tango variable to be used for parameter binding. The SCOPE attribute is an optional attribute defining the scope of the variable named in the NAME attribute.

The BINDTYPE attribute can have one of the following values:

• IN. Input parameter only. The execution of the SQL never affects the contents of the Tango variable. This is the default value for the BINDTYPE attribute.
• IN/OUT. The parameter is used by the SQL (normally, a stored procedure call) for both input and output.
• OUT. Output parameter only. Output parameters are set by the stored procedure being called. The value before execution of the <@BIND> tag is irrelevant to the execution.

The DATATYPE attribute defines how the contents of the Tango variable will be interpreted when actual DBMS binding is performed. Valid datatypes are INTEGER, VARCHAR, CHAR, DATE, TIME, TIMESTAMP, REAL, FLOAT, NUMERIC, and DECIMAL.

If the DATATYPE is set to TIMESTAMP, the timestamp text for ODBC data sources should be in the following form:

YYYY-MM-DD HH:MI:SS[.FFFFFF].

For OCI data sources, the default format is the one for the current session; for example, DD-MONTH-YY.

The PRECISION attribute is the size of the bound parameter in bytes. The default value for this attribute is that of the corresponding datatype, that is, 255 for VARCHAR and CHAR, 10 for INTEGER, and so on, and needs to be changed only if the corresponding bound DBMS parameter has the precision less than the default value.

The SCALE attribute is the number of the decimal digits after the floating point for numeric data types. The value of the SCALE attribute is ignored for textual parameters.

The following table gives the default values for PRECISION and SCALE for various values of DATATYPE:

DATATYPE	Default PRECISION	Default SCALE
INTEGER	10	0
FLOAT, REAL, NUMERIC, DECIMAL	15	2
VARCHAR, CHAR	255	-
DATE, TIME, TIMESTAMP	-	-

In case of an input-only (IN) parameter, PRECISION has no effect and is overridden by the actual parameter length. For IN/OUT text parameters, the PRECISION attribute defines the maximum length of the text returned by the DBMS.

The BINDNAME attribute is an optional name used as a bound parameter placeholder when the SQL statement is processed. This attribute is used only if the underlying DBMS driver supports this functionality. Currently, only OCI supports named parameters.

Since both OCI and ODBC provide implicit type castings, handling integer, float and varchar columns allow you to bind virtually any data necessary, with the exception of VARBIN columns, because the length is currently limited to 32767.

The use of <@BIND> with TYPE=IN is valuable in that the contents of the bound parameter can contain characters such as quotes, commas, and control characters that do not affect the execution of the stored procedure: there is no need to quote a bound parameter.

---

Caution: If an error occurs that prevents the successful completion of an action using bound output variables, the values in those variables are undetermined and no assurance is given that they have or have not been modified. Furthermore, a Transaction Rollback or Commit action issued to the data source does not affect the values in variables previously bound within a Direct DBMS action involved in the transaction.

---

Limitations

Since Tango Server does not parse the SQL inside a Direct DBMS action other than to perform the above substitutions, the OUT parameters of one stored procedure call cannot be used as input to another stored procedure call within the same action.

For example, if the same Direct DBMS action specified above also called another function:

CALL UpdatePersonalExpenses(<@VAR premium>);

The value for premium that was calculated by the CalculateMortgagePremium() function would not be available for this stored procedure.

Caution: Only ODBC v2.0 or above drivers support IN/OUT parameters through the SQLBindParameter() call.If BINDTYPE=IN/OUT is specified for the TIMESTAMP datatype, the Oracle ODBC driver returns an error message. The bind type IN works correctly.

Example

Create a SQL Server stored procedure sp_testproc:

create procedure sp_testproc
@param1 varchar(64) output,
@param2 integer output,
@param3 float output,
@param4 numeric(10,2) output,
@param5 datetime output
as
select @param1 = @param1 + ': param1'
select @param2 = @param2 + 2
select @param3 = @param3 + 3.3
select @param4 = @param4 + 4.4
select @param5 = CONVERT(datetime, getdate())

The preceding stored procedure can be called using this syntax:

{CALL sp_testproc(
<@BIND NAME=Param1 BINDTYPE=IN/OUT BINDNAME=StringParam PRECISION=32>,
<@BIND NAME=Param2 BINDTYPE=IN/OUT DATATYPE=INTEGER>,
<@BIND NAME=Param3 BINDTYPE=IN/OUT DATATYPE=FLOAT SCALE=3>,
<@BIND NAME=Param4 BINDTYPE=IN/OUT DATATYPE=DECIMAL PRECISION=6 SCALE=2>,
<@BIND NAME=Param5 BINDTYPE=IN/OUT DATATYPE=TIMESTAMP>
)}