Using RDO to Submit Queries

The primary reason for creating most database front-ends is to retrieve data, manipulate it, display it, and submit changes. Most of the RDO code you write involves building queries and processing the result sets to perform these basic operations.

When you need data from the remote database, you submit a query that specifically calls for a set of data rows. When it comes time to update your data, you submit more queries that pass along new data or changes to the remote database. If your application needs to perform administrative functions, you submit queries that contain high-level instructions to run server-side utilities to establish new users, or run database repair or configuration procedures. In essence, all of the interactive aspects of RDO involve submitting queries. You are basically asking questions and working with the answers that are returned in the form of result sets.

Generally, RDO query processing can be broken down into two parts:

• Submitting a query composed of an SQL statement that describes a result set, or references a stored procedure.
  
  
• Working with the resulting rows, parameters and return values — the result sets.

Query submission involves building an SQL statement, including any needed parameters, and sending the query to the remote server for processing. When deciding how to submit your query, you must decide what form it is to take when it is returned. Depending on the needs of your application, you should consider the following questions:

• Does your application need to scroll or browse through the data? If you must scroll, do you need to scroll in both directions?
  
  
• Will you need to update the data? If so, can you use action queries to make the changes? Does your user have permission to change data?
  
  
• How many other users will be attempting to work on the same data at the same time? Do you have control over these other applications? Will they cooperate with your data sharing scheme?
  
  
• How many rows will return from the query? Does your system have the capacity to store these rows? Does the network have the bandwidth to transmit these rows?
  
  
• How are the rows returned — as bookmarks or as static data?
  
  
• Must you pass parameters to the query?
  
  
• Does the query return rows, output parameters, or return values, or does it simply perform an action?
  
  
• Is the query likely to be repeated several times over the course of the application or executed only a few times?

The following topics address these questions to help you choose the right strategy to fetch your data in a variety of situations.

Managing RDO Queries

Once you have opened a connection, you can submit queries to the remote server for execution. No matter which technique you use, RDO will execute your queries and retrieve the result sets with one of two ODBC API function sets as described below:

• If you use queries that are run only once, use the OpenResultset or Execute method to execute fixed SQL queries against the rdoConnection object to create rdoResultset objects or execute action queries. This option routes your queries to the ODBC API SQLExecDirect function. You can also force the use of the SQLExecDirect function by using the rdExecDirect option with the Execute or OpenResultset methods.
  
  
• If you use queries that are run more than once and might require parameters, use the CreateQuery method to instantiate an rdoQuery object that can be reused as needed and that allows changes to one or more parameters each time the query is executed. Once the rdoQuery is created, use the OpenResultset or Execute method against the rdoQuery to create an rdoResultset or execute an action query. To change the query parameters, change the rdoParameter object settings. This option routes your queries to the ODBC API SQLPrepare and SQLExecute functions.
  
  
• If you use stored procedures to access data or perform action queries, you can use the User Connection Designer to link specific stored procedures and create customized rdoQuery objects to access them. This can also be done in code. This option routes your queries to the ODBC API SQLPrepare and SQLExecute functions. Unlike DAO queries that are stored in the database, these rdoQuery objects are re-created each time your program executes.

Note   The outdated Visual Basic version 4.0 rdoPreparedStatement object is still supported in Visual Basic version 6.0 for backward compatibility, but is not recommended for future development. You are encouraged to replace all references to rdoPreparedStatement objects with rdoQuery objects.