BindSPInput
database parameter
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
|
1 |
BindSPInput=value |
|
Parameter |
Description |
|---|---|
|
value |
Specifies whether you want to bind input parameters
|
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:
|
1 2 3 4 |
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:
1SQLCA.DBParm="BindSPInput=1"
Using the ADO.NET SQL Server interface
Consider the following two SQL statement fragments:
|
1 |
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.
|
1 |
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