Creating RDO Parameter Queries

If the SQL query you need to execute includes one or more parameters in the WHERE clause, you can use the rdoQuery object to run it and manage the parameters for each execution. This technique is especially useful when executing queries that are run repeatedly or against a number of connections — and especially when executing parameterized stored procedures.

Tip You can also build up your own queries, concatenating the parameters together to form a complete SQL statement. In some cases, this approach might be the only way that a parameter query can be created — especially in cases where the query is complex or uses the remote database syntax in an unusual way.

In any case, the SQL statement you submit must use the correct syntax. Many problems associated with parameter queries result from improperly coding the native SQL required by the remote server or the ODBC SQL syntax as described below. Queries submitted with incorrect syntax can result in a variety of problems, including syntax errors returned from the remote engine or RDO's inability to create the rdoParameters collection.

Managing Parameters with the rdoParameters Collection

When you want RDO to manage the parameters for you using the rdoParameters collection, you include a question mark for each parameter in your SQL statement. The "?" acts as a placeholder for input, output, and input/output query parameters; your code indicates which is which by setting the Direction property. RDO and the ODBC interface automatically manage these parameters and bind each to an rdoParameter object with a predefined data type. In some cases, your code might have to force a specific data type for certain parameters. This is especially true when your query contains a expression whose arguments are passed as parameters.

When all parameters have been marked and identified, RDO and the ODBC interface automatically create a driver-specific SQL statement and an rdoParameters collection to manage the individual parameter values and data types. For the most part, you do not have to worry about quoting strings used as arguments or other special formatting.

Note When the rdoQuery object is created, no check is made for proper syntax. It is only when the query is executed or the rdoParameters collection is accessed that the query is compiled and its parameters evaluated. If the statement is not coded correctly, you can trigger a trappable 40054 "Invalid parameter was passed" error, or some other ODBC error. In some cases, the rdoParameters collection is not created, so when it is referenced, you might get a trappable error indicating that the object does not exist.

Tip While you might have discovered ODBC drivers for DAO (Access) databases in your list of available ODBC drivers, they are not ODBC Level II compliant. You can use RDO to submit queries and return result sets from this driver, but it is incapable of creating rdoParameter objects or managing query parameters as supported by Level II compliant drivers.

Choosing the Right SQL Syntax for Parameter Queries

When coding the SQL property of an rdoQuery object or the name argument of the OpenResultset method, you can choose between one of three syntax styles to code your parameter query:

Concatenated Strings. Your code builds up the SQL statement and its parameters using the Visual Basic concatenation (&) operator. This statement can be passed to the name argument of the OpenResultset method or the rdoQuery object's SQL property. A concatenated string parameter query might look like this:
```
sSQL = "Select Name, Age From Animals " _ 
& " Where Weight > " & WeightWanted.Text _ 
& " and Type = '" & TypeWanted.Text & "'"
```
Native SQL syntax. The SQL syntax used by the remote server. You can execute your own query or stored procedure and pass in parameters by concatenation, placeholders, or both. The parameters marked with placeholders are managed by RDO as rdoParameter objects. A SQL parameter query might look like this:
```
sSQL = "Select AU_LName from Authors" _ 
& " Where AU_Fname = ?"
```
– or –
```
sSQL = "Execute MyStoredProc 'Arg1', 450, '" _ 
& Text1 & "'"
```
– or –
```
sSQL = "Execute MyStoredProc ?, ?, ?"
```
ODBC CALL syntax. Designed to call stored procedures that return a return status or output parameters. A placeholder can be defined for each input, output, or input/output parameter; the placeholders are automatically mapped to rdoParameter objects. You can also mix in concatenated operators as needed. An ODBC CALL parameter query might look like this:
```
sSQL = "{call ParameterTest (?,?,?) }"
```
– or –
```
sSQL = "{? = call ParameterTest (?,?,?) }"
```
– or –
```
sSQL = "{? = call CountAnimals (?, ?, 14, 'Pig')}
```

Note The SQL Server ODBC driver requires that all nonbound parameters (the parameters you concatenate into the query in code) appear to the right of all placeholder parameters (those marked with a ?). If they don't, a trappable error occurs indicating "Wrong number of parameters."

Benefits and Limitations of the ODBC CALL Syntax

There are a number of benefits to using the ODBC CALL syntax. For instance, ODBC uses an Open Data Systems Remote Procedure Call (ODS RPC) to perform the query. The parameters are passed in their native format and don't have to be parsed or converted into other data types. It also means that ODBC does not have to "prepare" the query for processing, as it already exists in the form of a stored procedure on the remote server. This makes these calls more efficient and allows for better portability across databases.

Using rdExecDirect with Parameter Queries

The rdExecDirect option forces RDO to use the ODBC API SQLExecDirect function when executing the procedure. This bypasses the ODBC API SQLPrepare step, which is used to create a temporary procedure to execute the query. This option can be used in situations where the SQL syntax required is acceptable to the remote server but unacceptable to the ODBC interface. However, when executing a stored procedure parameter query, you should not use the rdExecDirect option because it prevents proper type binding of the parameters.

Note In some cases, the temporary stored procedures created by the ODBC interface might not be removed until the connection is closed. Using the rdExecDirect option can eliminate this problem.

Summarizing the Syntax Options

The following table summarizes the options available when using each of the three syntax styles:

Syntax Options

Feature	Native SQL syntax	ODBC Call syntax	Concatenated strings
Can pass native SQL that does not reference a stored procedure	Yes	No	Yes
Can execute stored procedures	Yes	Yes	Yes
Can use ? placeholders for parameters	Yes	Yes	No
Manage return value	No	Yes	No
Manage output arguments	No	Yes	No
SQL statement can include multiple Select statements?	Yes	No	Yes

Note RDO's ability to manage the parameters of your query in the RDO parameters collection is gated by the ODBC interface's ability to correctly parse the query and determine correct data types for each parameter. In some cases, it is impossible for the ODBC driver manager to properly identify each parameter of an SQL statement. In these cases, converting the statement into a stored procedure, even temporarily, might enable an otherwise unusable query.

Tip While the ODBC Call syntax can be used in situations where you pass no arguments, or have no returned arguments, you should generally use the it when you need to capture the stored procedure return status and output arguments.

Coding a Typical Parameter Query

A parameter query simply substitutes user-supplied or application-supplied parameters into an ordinary query. While this query is usually a SELECT statement, it could be an INSERT, UPDATE, or DELETE query as well. The following example illustrates how to code a simple SELECT query with a single parameter. The query looks up authors by name from the Pubs sample database.

First, set up an SQL query that marks each parameter using the ? parameter marker.

QSQL$ = "SELECT * FROM Authors WHERE Au_Lname = ?"

Next, create an rdoQuery object to manage the query and its parameters.

Set PSAuthors = cn.CreateQuery("",QSQL$)

Next, use the following code to insert the value entered by the user (Text1.Text) into the query.

PSAuthors.rdoParameters(0) = Text1.Text

Note that the rdoParameters object can be implied here, as it is the default collection of the rdoQuery object. The equivalent code would be:

PSAuthors(0) = Text1.Text

Next, create an rdoResultset to fetch the qualifying rows (those whose last name match the parameter value).

Set MyRs = CpwPSAuthors.OpenResultset()

If the user changes the parameter value in Text1.Text, you can re-apply the new parameter and re-execute the query by using the Requery method against the rdoResultset(MyRs) without having to rebuild the rdoQuery object.

PSAuthors(0) = Text1.Text
MyRs.Requery

When RDO executes the Requery method, it refreshes the parameter value(s) in the rdoParameters collection bound to the query parameters, flushes the current result set, sends the query to the data source for execution, and creates a new rdoResultset.

Keep in mind that when the query is first created, RDO and the ODBC layers create a temporary stored procedure on the remote server designed to accept the parameters. Each time the query is executed, this temporary query is simply passed the new argument(s) and executed.

Tip If you used the rdAsyncEnable option with the OpenResultset method, use it also with the Requery method.

Note When executing stored procedures that do not require parameters, do not include the parenthesis in the SQL statement. For example, to execute the "MySP" procedure, which takes no parameters, use the following syntax:

{Call MySP }

If the user changes the parameter value, you can re-apply the parameter value and re-execute the query by using the Requery method against the rdoResultset (MyRs).

Cpw(0) = Text1.Text
MyRs.Requery

Concatenating Parameters

You can also specify parameters in any SQL query by concatenating the parameters to the SQL statement string. For example, to submit a query using this technique, you can use the following code:

QSQL = "SELECT * FROM Authors WHERE Au_Lname = '" _
 & Text.Text & "'"
Set MyRs = Cn.OpenResultSet(QSQL)

In this case, the rdoParameters collection is not created and cannot be referenced. To change the query parameter, you must rebuild the SQL statement with the new parameter value each time the query is executed or before you use the Requery method. In addition, unless you use the rdExecDirect option, RDO creates a new temporary stored procedure to execute the query each time you use the OpenResultset method.

For More Information See "rdoQuery Object," "rdoParameter Object," "Requery Method," and "OpenResultset Method" in the Language Reference". For additional information on stored procedures, see "Using RDO to Execute Stored Procedures."