Visual Basic: RDO Data Control

  • Article

rdoParameter Object

See Also    Example    Properties    Methods    Events

An rdoParameter object represents a parameter associated with an rdoQuery object.



Remarks

When working with stored procedures or SQL queries that require use of arguments that change from execution to execution, you should create an rdoQuery object to manage the query and its parameters. For example, if you submit a query that includes information provided by the user such as a date range, or part number, RDO and the ODBC interface can insert these values automatically into the SQL statement at specific positions in the query.

Providing Parameters

Your query's parameters can be provided in a number of ways:

  • As hard-coded arguments in the SQL query string.

    "Select Name from Animals Where ID = 'Cat'"

  • As concatenated text or numeric values extracted from TextBox, Label or other controls.

    "Select Name from Animals Where ID = '"  _
        & IDWanted.Text & "'"

  • As the question mark (?) parameter placeholders.

    "Select Name from Animals Where ID = ?"

  • As the question mark (?) parameter placeholders in a stored procedure call that accepts input, output and/or return status arguments.

    "{ ? = Call MySP (?, ?, ?) }"

Note   Stored procedure invocations that use the Call syntax (as shown above) are executed in their "native" format so they do not require parsing and data conversion by the ODBC Driver Manager. Because of this the Call syntax can be executed somewhat faster than other syntaxes.

Using Parameter Markers

The only time you must use parameter markers is when executing stored procedures that require input, output or return status arguments. If the stored procedure only requires input arguments, these can be provided in-line as imbedded values concatenated into the query (as shown below).

When the rdoParameter collection is first referenced (but not before) RDO and the ODBC interface pre-processes the query, and creates an rdoParameter object for each marked parameter. You can also create queries with multiple parameters, and in this case you can mark some parameters and provide the others by hard-coding or concatenation in any combination. However, all marked parameters must appear to the left of all other parameters. If you don't, a trappable error occurs indicating "Wrong number of parameters".

Note   Due to the extra overhead involved in creating and managing rdoQuery objects and their rdoParameters collection, you should not use parameter queries for SQL statements that do not change from execution to execution especially those that are executed only once or infrequently.

Marking Parameters

Each query parameter that you want to have RDO manage must be indicated by a question mark (?) in the text of the SQL statement, and correspond to an rdoParameter object referenced by its ordinal number counting from zero left to right. For example, to execute a query that takes a single input parameter, your SQL statement would look something like this:

SQL$ = "Select Au_Lname, Au_Fname where Au_ID Like ? "
Dim qd as rdoQuery, rd as rdoResultset
Set qd = CreateQuery ("SeekAUID", SQL$)
qd(0) = "236-66-%"
set rd = qd.OpenResultset(rdOpenForwardOnly)

Note   You can also create an rdoQuery object using the Query Connection designer and name and set the data type and direction of individual parameters.

Acceptable Parameters

Not all types of data are passable as parameters. For example you cannot always use a TEXT or IMAGE data type as an OUTPUT parameter. In addition, if your query does not require parameters or has no parameters in a specific invocation of the query, you cannot use parenthesis in the query. For example, for a stored procedure that does not require parameters could be coded as follows:

"{ ? = Call MySP }"

When submitting queries that return output parameters, these parameters must be submitted at the end of the list of your query's parameters. While it is possible to provide both marked and unmarked (in-line) parameters, your output parameters must still appear at the end of the list of parameters.

All in-line parameters must be provided to the right of marked parameters. If this is not the case, RDO returns an error indicating "Wrong number of parameters".

RDO 2.0 supports BLOB data types as parameters and you also can use the AppendChunk method against the rdoParameter object to pass TEXT or IMAGE data types as parameters into a procedure.

Identifying the Parameter's Data Type

When your parameter query is processed by ODBC, it attempts to identify the data type of each parameter by executing ODBC functions that query the remote server for specific information about the query. In some cases, the data type cannot be correctly determined. In these cases, use the Type property to set the correct data type or create a custom query using the User Connection Designer.

For example, in the following query, the parameter passed to the TSQL Charindex function is typed as an integer. While this is correct for the function itself, the parameter is referencing a string argument of the TSQL function, so it must be set to an ODBC character type to work properly.

Dim SQL as string, qd as rdoQuery
SQL = "Select * From Titles " _
    & "Where Charindex( ?,  Title) > 0
Set qd = cn.CreateQuery("FindTitle", SQL)
qd(0).Type = rdTypeChar

Note   You do not have to surround text parameters with quotes as this is handled automatically by the ODBC API interface.

Handling Output and Return Status Arguments

In some cases, a stored procedure returns an output or return status argument instead of or in addition to any rows returned by a SELECT statement. Each of these parameters must also be marked in the SQL statement with a question mark. Using this technique, you can mark the position of any number of parameters in your SQL query including input, output or input/output.

Whenever your query returns output or return status arguments, you must use the ODBC CALL syntax when setting the SQL property of the rdoQuery object. In this case, a typical stored procedure call would look like this:

Dim qd as rdoQuery, rd as rdoResultset, SQL as String
SQL = "{ ? = Call master..sp_password (?, ?) }"
Set qd = CreateQuery ("SetPassword", SQL)
qd.rdoParameters(0).Direction = rdParamReturnValue
qd(1) = "Fred"      ' the old password
qd(2) = "George"   ' the new password
set rd = qd.Execute
if qd(0) <> 0 then _
   MsgBox "Operation failed"

Tip   Be sure to specifically address stored procedures that do not reside in the current (default) database. In this example, the default database is not Master where the sp_password procedure is maintained, so this procedure is specifically addressed.

When control returns to your application after the procedure is executed, the rdoParameter objects designated as rdParamReturnValue,  rdParamOutput or rdParamInputOutput contain the returned argument values. In the example shown above, the return status is available by examining qd(0)after the query is executed.

Using Other Properties

Using the properties of an rdoParameter object, you can set a query parameter that can be changed before the query is run. You can:

  • Use the Direction property setting to determine if the parameter is an input, output, or input/output parameter, or a return value. In RDO 2.0, the Direction property is usually set automatically, so it is unnecessary to set this value. It is also unnecessary to set it for input parameters which is the default value.

  • Use the Type property setting to determine the data type of the rdoParameter. Data types are identical to those specified by the rdoColumn.Type property. In some cases, RDO might not be able to determine the correct parameter data type. In these cases, you can force a specific data type by setting the Type property.

  • Use the Value property (the default property of an rdoParameter) to pass values to the SQL queries containing parameter markers used in rdoQuery.Execute or rdoQuery.OpenResultset methods. For example:

    MyQuery(0) = 5

Note   RDO requires that your ODBC driver support a number of Level II compliant options and support the SQLNumParams, SQLProcedureColumns and SQLDescribeParam ODBC API functions in order to be able to create the rdoParameters collection and parse parameter markers in SQL statements. While some drivers can be used to create and execute queries, if your driver does not support creation of the rdoParameters collection, RDO fails quietly and simply does not create the collection. As a result, any reference to the collection results in a trappable error.

Addressing the Parameters

By default, members of the rdoParameters collection are named "Parametern" where n is the rdoParameter object's ordinal number. For example, if an rdoParameters collection has two members, they are named "Parameter0" and "Parameter1". However, if you use the User Connection Designer, you can specify names for specific parameters.

Because the rdoParameters collection is the default collection for the rdoQuery object, addressing parameters is easy. Assuming you have created an rdoQuery object referenced by rdoQo, you can refer to the Value property of its rdoParameter objects by:

  • Referencing the Name property setting using this syntax:

    ' Refers to PubDate parameter
rdoQo("PubDate")

    -Or-

    ' Refers to PubDate parameter
rdoQo!PubDate

  • Referencing its ordinal position in the rdoParameters collection using this syntax:

    ' Refers to the first parameter marker
rdoQo(0)