Description
Specifies that PowerBuilder bind input parameters in dynamic SQL statements when executing a stored procedure.
Applies to
ADO.NET
O90 Oracle9i
O10 Oracle 10g
SNC SQL Native Client for Microsoft SQL Server
MSOLEDBSQL Microsoft OLE DB Driver for SQL Server
Syntax
BindSPInput=value
|
Parameter |
Description |
|---|---|
|
value |
Specifies whether you want to bind input parameters in dynamic SQL statements when executing stored procedures. Values are:
|
Default value
BindSPInput=0
Usage
For SNC, when BindSPInput is set to 0, you can use the same syntax to declare a stored procedure in a script as you can when using the PowerBuilder OLE DB interface. When BindSPInput is set to 1, the SNC interface supports SQL Server large value datatypes as procedure IN/OUT parameters or function return values.
The syntax for declaring a procedure with SNC is:
DECLARE logical_procedure_name PROCEDURE FOR
[@rc=]procedure_name
{@param1=value1 [OUTPUT], @param2=value2 [OUTPUT], ...}
{USING transaction_object};[@rc=] indicates that you want to get the procedure's return value.
Use the keyword OUTPUT or OUT to indicate an output parameter if you want to get the output parameter's value.
If BindSPInput=0, value1, value2,... can be either PowerBuilder script variables or literal values. If BindSPInput=1, value1, value2,... must be PowerBuilder script variables. If you specify literal values, the interface returns a runtime error.
When you declare a dynamic SQL statement with a procedure, enter a question mark (?) for each IN/OUT parameter in the statement. Value substitution is positional. For examples, see Dynamic SQL Format 3 and 4 in the section called “Using dynamic SQL” in PowerScript Reference.
For Oracle, set BindSPInput to 1 to ensure that CLOB, NCLOB, and BLOB parameters work correctly as stored procedure parameters.
For ADO.NET:
-
When BindSPInput is set to 1, parameter values must be PowerBuilder script variables, not literal values.
-
The IBM.Data.Informix driver (used to access an ADO.NET compliant Informix database) does not support the BindSPInput dbparm.
-
The ADO.NET Microsoft SQL Server interface does not support Text, NText, or Image parameters. Use VarChar(max) or VarBinary(max) instead.
Examples
Setting BindSPInput
To specify that PowerBuilder should bind parameters in dynamic SQL statements when executing a stored procedure:
-
Database profile
Select the Bind Procedure Parameters check box on the Transaction page in the Database Profile Setup dialog box.
-
Application
Type the following in code:
SQLCA.DBParm="BindSPInput=1"
Using the ADO.NET SQL Server interface
Consider the following two SQL statement fragments:
create procedure p_1 (@inparm1 TEXT) AS
The preceding statement does not work if BindSPInput is set to 1, because the SQL Server interface does not support Text.
create procedure p_2 (@inparm1 VARCHAR(MAX)) AS
The preceding statement can work with BindSPInput set to 1, because the SQL Server interface does support VARCHAR(MAX).
See also
