ODBC_BIND_PARAMS Function

ODBC_BIND_PARAMS is used to bind PV-WAVE variables to the parameter markers in an SQL statement.

Restriction: See "Supported Platforms Guide" on the PV-WAVE Documentation website to check if ODBC is available on your operating system.

Usage

Param_list = ODBC_BIND_PARAMS(connect_handle, STMT_HANDLE, proc_name, par1, par2, .....parN)

Input Parameters

Connect_handle—Connection handle returned from ODBC_CONNECT.

STMT_HANDLE—The statement handle returned from ODBC_PREPARE.

Proc_name—Stored procedure name. It should match the name in the SQL statement.

Par1...ParN—Parameters you want to bind with the statement handle. Number, order, and tpe of parameters should match the number, order, and type of parameter markers in the SQL statement.

Returned Value

Param_list—A PV-WAVE list containing the parameters that bind to the parameters of a SQL stored procedure. If the function fails –1 is returned.

Keywords

Output_params—A PV-WAVE list returned from ODBC_BIND_PARAMS. This list is used to get the output values from a stored procedure. See ODBC_BIND_PARAMS for examples.

Discussion

In order to get the output parameter values, you must set the output_params keyword of the ODBC_FETCH to the parameter list returned by this function.

Example 1

This example binds two PV-WAVE variables to the two input parameter markers in an SQl statement.

; Create PROCEDURE TestInputParams(@c varchar(10),@z int)

; select population from state where city=@c and zip=@z. 

@odbc_startup

oid=odbc_init()

; Connect to data source. It might be different on your system.

oid2=odbc_connect(oid,"<DSN>","")

; Call stored procedure auth_city_zip.

; Two parameter markers are in this SQL statement.

stmt = odbc_prepare(oid2,"{call TestInputParams(?,?)}")

; Notice that the number of parameters match the number of 

; parameter markers. Name of stored procedure should also 

; match the name of the procedure in the SQL statement.

Myvar = "Denver"

Myvar2 = 80501

; For input parameter marker, PV-WAVE variables must contain an

; appropriate value. 

Param_list =odbc_bind_params(oid2,stmt,'TestInputParams',myvar1,myvar2)

; Get the resultset.

t=odbc_fetch(stmt,1)

Example 2

This example binds two PV-WAVE variables to the two output parameter markers in an SQl statement.

; CREATE PROCEDURE TestOutputParams @OutParm1 int

; OUTPUT,@OutputParm2 

; int OUTPUT AS

; SELECT name FROM emp

; select @OutParm=88

; select @parm2=-88

; RETURN 99

@odbc_startup

oid=odbc_init()

oid2=odbc_connect(oid,"sqlserver","")

; SQL statement with two output parameter markers.

stmt = odbc_prepare(oid2,"{call TestOutputParams(?,?)}")

; Bind PV-WAVE variables.

param_list = odbc_bind_params(oid2,stmt, 'TestParm2', myvar, $
myvar2)

; Fetch first result set.

t=odbc_fetch(stmt,1)

; Call odbc_fetch until no more data is available.

rc = odbc_fetch(stmt,5,output_params=param_list)

; Param_list should be populated with the output parameters.

print,param_list 

; Close statement.

rv = odbc_fetch(stmt,6)