BindSPInput database parameter
Description
Specifies that PowerBuilder bind input parameters in dynamic
SQL statements when executing a stored procedure.
Controls
-
ADO.NET
-
O90 Oracle9i
-
O10 Oracle 10g
-
SNC SQL Native Client for Microsoft SQL Server
Syntax
|
1 |
BindSPInput=<span>value</span> |
|
Parameter |
Description |
|---|---|
|
value |
Specifies whether you want to bind input
|
Default
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 |
DECLARE <span>logical_procedure_name</span> PROCEDURE FOR <br>   [@rc=]<span>procedure_name</span><br>   {@<span>param1</span>=<span>value1</span> [OUTPUT], @<span>param2</span>=<span>value2</span> [OUTPUT], ...}<br>   {USING <span>transaction_object</span>}; |
[@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 online Help.
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
profileSelect the Bind Procedure Parameters check box on the Transaction
page in the Database Profile Setup dialog box. -
Application
Type the following in code:
1SQLCA.DbParameter="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).