rdoQuery Object

An rdoQuery object is a query definition that can include zero or more parameters.

Remarks

The rdoQuery object is used to manage SQL queries requiring the use of input, output or input/output parameters. Basically, an rdoQuery functions as a compiled SQL statement. When working with stored procedures or queries that require use of arguments that change from execution to execution, you can create an rdoQuery object to manage the query parameters. If your stored procedure returns output parameters or a return value, or you wish to use rdoParameter objects to handle the parameters, you must use an rdoQuery object to manage it. For example, if you submit a query that includes information provided by the user such as a date range or part number, RDO can substitute these values automatically into the SQL statement when the query is executed.

Note The rdoQuery object replaces the outdated rdoPreparedStatement object. The rdoQuery object remains similar to the rdoPreparedStatement in its interface, but adds the ability to be persisted into a Visual Basic project, allowing you to create and manipulate it at design time. Additionally, the rdoQuery objects can be prepared or not, allowing the you to choose the most appropriate use of the query.

Creating rdoQuery Objects

To create an rdoQuery object, use the CreateQuery method which associates the rdoQuery with a specific rdoConnection object and adds it to the rdoQueries collection. Once created, you must fill in required parameters using the rdoParameters collection, and then use the OpenResultset method to create resultsets from the query, or the Execute method to simply run the query if it does not return rows.

You can also use the User Connection Designer (CQD) to create rdoQuery objects in your project. The CQD takes your SQL query and permits you to specify the data types for each parameter. It then inserts appropriate code in your application to expose these procedures very much like methods off of the rdoQuery object.

Note Due to the extra overhead involved in creating and managing rdoQuery objects and the 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.

Stand Alone rdoQuery Objects

You can declare a stand-alone rdoQuery object using the Dim statement as follows:

Dim MyQuery as New rdoQuery

Stand-alone rdoQuery objects are not assigned to a specific rdoConnection object, so you must set the ActiveConnection property before attempting to execute the query, or to use the OpenResultset object against it. The CursorType and ErrorThreshold properties are set from default values established by the rdoEngine default settings. In addition, new rdoQuery objects are not automatically appended to the rdoQueries collection until you use the Add method.

For example, the code shown below creates an rdoQuery object, associates it with a connection, and executes it. Next, the rdoQuery object is associated with a different connection and executed again. The query object becomes more of an encapsulation of any kind of query, and thus can be executed against any kind of connection, provided the SQL statement would be appropriate for the connection.

Dim MyQuery As rdoQuery '
MyQuery.SQL = "Update customers " _
   & " Set LastTouched = GetDate()"
MyQuery.Prepared = False    'don't prepare it, 
                           'just SQLExecDirect
'assume that cnSomeConnection 
'is an rdoConnection or stand-alone object
MyQuery.ActiveConnection = cnSomeConnection
MyQuery.Execute

MyQuery.ActiveConnection = cnOtherConnection
'the cnOtherConnection is over a WAN, so I can increase
'my query timeout to compensate
MYQuery.QueryTimeout = 120
MyQuery.Execute

Choosing the right SQL Syntax

When coding the SQL property of an rdoQuery object, 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 SQL argument of the OpenResultset method or the rdoQuery object's SQL property. In this case, a 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. In this case you can execute your own query or stored procedure, and pass in parameters by concatenation, or using placeholders, or both. The parameters marked with placeholders are managed by RDO as rdoParameter objects. A 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. In this case, a placeholder can be defined for each input, output, or input/output parameter which is automatically mapped to rdoParameter objects. You can also mix in concatenated operators as needed. In this case, a parameter query might look like this:
```
sSQL = "{call ParameterTest (?,?,?) }"
```
– Or –

   sSQL = "{? = call ParameterTest (?,?,?) }"

– Or –

sSQL = "{? = call CountAnimals (?, ?, 14, 'Pig')}

The rdoQuery object is managed by setting the following properties and methods.

Use the SQL property to specify a parameterized SQL statement to execute. The name argument of the CreateQuery method can also be used to provide the SQL query string.
Set query parameters by using the rdoQuery object's rdoParameters collection.
Use the Prepared property to indicate if the rdoQuery object should be prepared by the ODBC SQLPrepare function. If False, the query is executed using the SQLExecDirect function.
Use the Type property to determine whether the query selects rows from an existing table (select query), performs an action (an action query), contains both action and select operations, or represents a stored procedure.
Use the RowsetSize property setting to determine how many rows are buffered internally when building a cursor and locked when using pessimistic locking.
Use the KeysetSize property to indicate the size of the keyset buffer when creating cursors.
Use the MaxRows property to indicate the maximum number of rows to be returned by a query.
Use the RowsAffected property to indicate how many rows are affected by an action query.
Use the QueryTimeout property to indicate how long the driver manager waits before pausing a query and firing the QueryTimeout event.
Use the BindThreshold property to indicate the largest column to be automatically bound.
Use the ErrorThreshold property to indicate the error level that constitutes a trappable error.
Use the Updatable property to see if the result set generated by an rdoQuery can be updated.
Use the OpenResultset method to create an rdoResultset based on the OpenResultset arguments and properties of the rdoQuery.
Use the Execute method to run an action query using SQL and other rdoQuery properties, including any values specified in the rdoParameters collection.
Use the LogMessages property to activate ODBC tracing.

rdoQuery Object Events

The following events are fired as the rdoQuery object is manipulated. These can be used to micro-manage queries associated with the rdoQuery or coordinate other processes in your application.

Event Name	Description
QueryComplete	Fired when a query has completed.
QueryTimeout	Fired when the QueryTimeout period has elapsed and the query has not begun to return rows.
WillExecute	Fired before the query is executed permitting last-minute changes to the SQL, or to prevent the query from executing.

Closing the rdoQuery Object

Use the Close method to close an rdoQuery object, set its ActiveConnection property to Nothing, and remove it from the rdoQueries collection. However, you can still re-associate the rdoQuery object with another rdoConnection object by setting its ActiveConnection property to another rdoConnection object. Using the Execute method or OpenResultset method against an rdoQuery object that has its ActiveConnection property set to Nothing or an invalid rdoConnection causes a trappable error.

Addressing rdoQuery Objects

rdoQuery objects are the preferred way to submit parameter queries to the external server. For example, you can create a parameterized Transact SQL query (as used on Microsoft SQL Server) and store it in an rdoQuery object.

You refer to an rdoQuery object by its Name property setting using the following syntax. Since the rdoQuery object's default collection is the rdoParameters collection, all unqualified references to the rdoQuery object refer to the rdoParameters collection. In these examples, assume we have created an rdoQuery object named rdoQo using the syntax Dim rdoQo as rdoQueries. The first two examples refer to the rdoQuery object named "MyQuery".

rdoQo("MyQuery")

– Or –

rdoQo!MyQuery

You can also refer to rdoQuery objects (and the rdoPreparedStatements collection) by their position in the rdoQueries collection using this syntax (where n is the nth member of the zero-based rdoQueries collection):

rdoQo(n)