Remote Server Connectivity

• Remove case-sensitivity from the SQL Server
• Make your table names all lower-case
• Use server-side cursors

If the above options are not available, you can issue update SQL statements to the server using the Execute method.

This does not affect Oracle servers.

Submitting a Query

When you use the OpenResultset method against an rdoConnection or rdoPreparedStatement, and assign the result to an existing rdoResultset object, the existing object is maintained and a new rdoResultset object is appended to the rdoResultsets collection. When performing similar operations using the Microsoft Jet database engine, existing recordsets are automatically closed when the variable is assigned, and no two Recordsets collection members can have the same name. For example, using RDO:

Dim rs as rdoResultset
Dim cn as rdoConnection

Set cn = OpenConnection....
Set rs = cn.OpenResultset("Select * from Authors", rdOpenStatic)
Set rs = cn.OpenResultset("Select * from Titles", rdOpenDynamic)

This behavior permits you to maintain existing rdoResultset objects, which are maintained in the rdoResultsets collection, or close them as needed. In other words, you must explicitly close any rdoResultset objects that are no longer needed. Simply assigning another rdoResultset to a rdoResultset-type variable has no affect on the existing rdoResultset formerly referenced by the variable. Note that the procedures and other temporary objects created to manage the rdoResultset are maintained on the remote server as long as the rdoResultset remains open.

If you write an application that does not close each rdoResultset before opening additional rdoResultset objects, the number of procedures maintained in TempDB or elsewhere on the server increase each time another rdoResultset object is opened. Over time, this behavior can overflow the capacity of the server or workstation resources.

The OpenResultset method accepts the following arguments when you use it with an rdoConnection object:

• The source argument specifies an SQL query, the name of an rdoPreparedStatement or rdoTable object, or a stored procedure name preceded by "EXEC".
• The type argument specifies the type of cursor. If you do not indicate a cursor type, a forward-only - type result set is created. You can also specify keyset, dynamic, or static cursor types.
• The locktype argument specifies the type of locking used to support concurrency. The default is rdReadOnly.
• The options argument was enhanced at the last minute. It either specifies if the query should be executed asynchronously rdAsyncEnable or if the ODBC driver manager should create a procedure before executing the query (rdExecDirect). The default is no options enabled. In this case, the query is executed synchronously and the driver manager creates a stored procedure to execute the query.

" Select * from authors where name '%' (?) '%'  "

Choosing a Cursor Type

Forward-Only Result Sets

Static Cursors

Keyset Cursors

On some servers, modifications or additions made directly to keyset cursors using the AddNew and Edit methods are included in the result set, but additions or modifications made using Execute do not affect the cursor. Consult your server manual for details.

To build a keyset-driven cursor, you must provide sufficient resources on the client or server to hold the keys and a block of buffered data rows.

Dynamic Cursors

When a keyset-type or static-type rdoResultset is first created, the RowCount property is set to the number of rows in the result set if rows are returned, 0 if no rows are returned by the query, or -1 if RowCount is not available. RowCount is not applicable to dynamic cursors where the number of rows can change, or to forward-only result sets that only expose one row.

Sequencing Operations

For More Information

Positioning the Current Row Pointer

When using forward-only - type rdoResultset objects, you can only use the MoveNext method -- not the MovePrevious method, MoveFirst method, or the Move method with a negative argument.

Note  This is a change from the documentation and help file which says you can use any of the forward-moving methods on a Forward-Only type cursor. The ODBC driver does not support anything but the MoveNext method - no, not even MoveLast.

When positioning the current row pointer, it is possible to position the pointer past either end of the result set or to a row that has been deleted. RDO may also leave the current row pointer positioned over an invalid row -- as when a row is deleted. Be sure to check the EOF and BOF properties to determine if the current row is positioned beyond the end or beginning of the result set. When using the Bookmark property to reposition the current row, it is possible to reposition to a row that has been deleted by another user. If this occurs, a trappable error results.

For More Information

Changing Data

To change columns in a specific data row in the rdoResultset

1. Position to the row using one of the Move methods or the AbsolutePosition or PercentPosition property.
2. Use the Edit method to activate the row to be altered. All data is copied into a temporary row buffer.
3. Provide a new value for each column that you need to modify.
4. Use the Update method to save the row buffer to the data source. The current row is deleted and the buffered row replaces it.

If you use the BeginTrans method, changes made to the database with the Update method are deferred until you use the CommitTrans method to save the changes, or the RollbackTrans method to discard the changes.

In addition to changing columns in a specific row, you can add a row to an rdoResultset.

To add a row to an rdoResultset

1. Use the AddNew method to create a row buffer to hold the new row to be added.
2. Provide a new value for each column you need to modify.
3. Use the Update method to save the row to the data source.

Once you've added rows, you can also delete them from the result set.

To delete a specific row in an rdoResultset

1. Position to the row using one of the Move methods or the AbsolutePosition or PercentPosition property.
2. Use the Delete method to delete the row from the cursor (if any) and the data source.

Managing Concurrency

LockType option	Description
rdConcurLock	Pessimistic concurrency. This technique uses the lowest level of locking sufficient to ensure that the row can be updated. This option locks the row or the data page containing the row(s) as soon as the Edit or AddNew method is executed, and holds it until the Update method has written the changes to the data source.
rdConcurRowver	Optimistic concurrency using row versions. The ODBC cursor library and the data source compare row ID or TIMESTAMP values to determine if the row has changed.
rdConcurValues	Optimistic concurrency using row values. The ODBC cursor library and the data source compare data values.
rdReadOnly	(Default) The cursor is read-only. No updates are permitted.

When values or row versions are compared to test concurrency, the original row as it was prior to the Edit or AddNew method is compared with the row as it exists in the database prior to being changed by the Update method. If the row data changed in this timeframe, a trappable error results. At this point, your temporary row buffer is lost. To carry out the changes, you must refresh the current row using the Move 0 technique, use the Edit or AddNew method, fill in the new values, and repeat the Update method again.

Making Changes with Action Queries

The Execute method is not designed to execute queries that return rows. If you execute a stored procedure that performs a mix of "action" operations and row-returning operations, you must use the OpenResultset method and parse the result sets that are generated.

Using Server-Side Cursors

Option	Description
rdUseIfNeeded	(Default) The ODBC driver chooses the appropriate style of cursor and uses server-side cursors if available.
rdUseODBC	RDO will use the ODBC cursor library. This gives better performance for small result sets, but degrades quickly for larger result sets.
rdUseServer	Server-side cursors are always used if available.

Server-side cursors reduce the amount of memory and disk space required on the client machines, but shift that load to the server. For SQL Server 6.0, cursor keysets are maintained in the TEMPDB database on the server, so it must be sized to meet the needs of additional temporary cursor keysets generated by multiple clients.

Working with BLOB or Chunk Datatypes

The ColumnSize property on the rdoColumn object represents the actual length of the data in a BLOB column. When using the ODBC cursor library, this value will always be -1, indicating that the data length is not available. When using server-side cursors, the ColumnSize property will always return the actual data length of a BLOB column.

To get the data from a BLOB column, the user must use the GetChunk methods, which take a number of bytes to retrieve at a time. When using server-side cursors, the user can pass the value of the ColumnSize property as the number of bytes to retrieve to get all the data at once. Since the ColumnSize property is not available when using the ODBC cursor library, the user should call GetChunk repeatedly until no more data is returned. Below is a code sample that shows how to do this:

Dim s As String
Dim sTemp As String
Dim lColSize As Long

lColSize = MyResultset!MyBLOBColumn.ColumnSize
If lColSize = -1 Then
 ' Column size is not available.
 ' Loop getting chunks until no more data.
 sTemp = MyResultset!MyBLOBColumn.GetChunk(50)
 Do
  s = s & sTemp
  sTemp = MyResultset!MyBLOBColumn.GetChunk(50)
 Loop While Len(sTemp) > 0
Else
' Get all of it.
 If lColSize > 0 Then
  s = MyResultset!MyBLOBColumn.GetChunk(lColSize)
 End If

End If

Setting Limits

Note  When the SQL_MAX_ROWS ODBC statement option is set to a nonzero value, the maximum number of rows processed by Microsoft SQL Server is limited to n rows. This means that only n rows are returned by a query, or only n rows are inserted, updated, or deleted by an action query. SQL_MAX_ROWS is set indirectly by using the rdoPreparedStatement object's MaxRows property. If you share the hStmt created for an rdoPreparedStatement that has MaxRows set, the operations executed against the hStmt are also affected by the limitation imposed by SQL_MAX_ROWS on both the number of rows returned from a query and the number of rows processed in an action query.

In addition, if you reuse an rdoPreparedStatement that has MaxRows set, the number of rows affected by any update, delete, or insert action query will be limited to n rows.

Processing Multiple Result Sets

Note  When executing Remote Data Object (RDO) queries that return more than one set of results, you can use only the ODBC cursor drivers. The Microsoft SQL Server server-side cursors do not support result sets that return more than a single set of results. To enable the ODBC cursor driver, set the rdoEnvironment object's CursorDriver property to rdUseODBC before creating the cursor.

Action queries also generate row-less result sets that must also be processed. For example, if you submit a query that includes four SELECT queries to populate four local ListBox controls, and a stored procedure that updates a table, your code must deal with at least five result sets. Because you might not know how many result sets can be generated by a stored procedure, your code must be prepared to process n sets of results.

There are two approaches to executing queries with multiple result sets:

• Execute the OpenResultset directly against a connection.
• Use the OpenResultset against an rdoPreparedStatement.

Multiple Result Sets: An Example

1. Create your SQL statement and place it in a string variable -- for instance, MySQL. For SQL Server, multiple statements must be separated by semi-colons.

   
   Dim MySQL As String
   MySQL = "Select Name from Authors; "
   MySQL = MySQL & " Select City from Publishers; "
   MySQL = MySQL & " Update MyTable Set Age = 16 Where Name = 'Fred' "
   
   

2. Use an existing rdoPreparedStatement from the rdoPreparedStatements collection, or create a new rdoPreparedStatement and Set a variable declared as rdoPreparedStatement and multiple result sets to this object -- in this case, MyPs. The example assumes an rdoConnection object (Cn) already exists. While it is possible to pass the SQL statement to the CreatePreparedStatement method, this example sets the SQL property after creating an instance of the object. This technique is also used if the rdoPreparedStatement is already created and referenced by its name or ordinal number in the rdoPreparedStatements collection.

   
   Dim MyPs As rdoPreparedStatement
   Set MyPs = Cn.CreatePreparedStatement("MyPS1", "")
   MyPs.SQL = MySQL
   
   

3. Execute the query by using the OpenResultset method against the rdoPreparedStatement object. If you do not need the extra properties and the ability to pass parameters to the query, you can use the OpenResultset method directly against the rdoConnection object. The arguments you use here affect all result sets fetched from this query. For example, if you need to use a cursor on the second result set, you must specify a cursor type when the first result set is opened.

   
   Dim MyRs As rdoResultset
   Set MyRs = MyPs.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)
   
   

4. You are now ready to process the first result set. Note that the rdAsyncEnable options argument was not set. Because of this, control is not returned to the application until the first row of the first result set is ready for processing. If the current rdoResultset contains rows, the RowCount property is set to a value >0, and the EOF and BOF properties are both False. Depending on the driver and data source, the RowCount property returns either -1 to indicate that the number of rows is not available, or 0 to indicate that no rows were returned by the rdoResultset.

   The following example fills a ListBox control called NameList1 with the results of the query.
   

   
   While Not MyRs.EOF     ' Loop through all rows.
   NameList1.AddItem = MyRs(0) ' Use the first column.
   MyRs.MoveNext    ' Position to the next row 
   ' in the result set.
   Wend
   
   

5. The first result set is now at the end-of-file (EOF) position. Use the MoreResults method to activate the next result set. Once you execute MoreResults, the first set of rows is no longer available -- even if you used one of the cursor options to create it.

   
   ' Activate the next set of results.
   If (MyRs.MoreResults) Then ...
   
   

6. You are now ready to process the second result set. This example uses only the first few names and discards the remaining rows.

   
   ' Loop through some rows.
   Do While Not MyRs.EOF and MyRs(0) < "B"
   ' Use the first column.
   NameList1.AddItem = MyRs(0)
   MyRs.MoveNext
   Loop
   ' Activate the next set of results and discard remaining rows.
   If (MyRs.MoreResults) Then ...
   
   

7. You are now ready to process the last set of results. Because this is an UPDATE statement, there are no rows to be returned, but you can determine the number of rows affected by using the RowsAffected property. The MoreResults method is used for the last time to release all resources connected with this query.

   
   If MyPs.RowsAffected = 0 Then
     MsgBox "No rows were updated"
   End If
   ' Activate the next set of results.
   If (MyRs.MoreResults) Then ...
   
   

Creating Parameter Queries

You can use a parameter as either an input, output, or both input and output parameter. For example, to execute the sp_password procedure (which can be used in SQL Server systems to change a user's password), use the TSQL statement as follows:

execute sp_password clyde, framis

Dim CPw As rdoPreparedStatement, QSQL As String
QSQL$ = "{ ? = call sp_password (?, ?) }"

The next line of code creates the rdoPreparedStatement and names it SetPassword. The SQL property is set with the ODBC syntax SQL query. You only need to execute this line once. The new rdoPreparedStatement object is automatically appended to the rdoPreparedStatements collection where it can be recalled later.

Set CPw = cn.CreatePreparedStatement("SetPassword",QSQL$)

Set rs = cn.OpenResultset("Select * from Authors", rdOpenStatic, _
rdConcurValues, rdExecDirect)

Cpw.rdoParameters(0).Direction = rdParamReturnValue
Cpw.rdoParameters(2).Direction = rdParamInput

Cpw.rdoParameters(1) = "clyde"  ' Set the first input parameter.
Cpw.rdoParameters(2) = "framis"  ' Set the second input parameter.

Cpw.Execute()

If Cpw.rdoParameters(0) <> 0 Then
 Msgbox "Could not change password"
End If

SELECT * FROM Authors WHERE Au_Lname = ? 

QSQL$ = "SELECT * FROM Authors WHERE Au_Lname = ?"}
Set PSAuthors = cn.CreatePreparedStatement("",QSQL$)
PSAuthors.rdoParameters(0) = Text1.Text
Set MyRs = PSAuthors.OpenResultset()

PSAuthors.rdoParameters(0) = Text1.Text
MyRs.Requery

Dim SQL As String, MyOutputVal1 As Variant
Dim MyOutputVal2 As Variant, MyRetVal As Variant
Dim cn As rdoConnection, rs As rdoResultset

rdoEnvironments(0).CursorDriver = rdUseOdbc
' To permit execution on SQL Server 6.0 Set 
' cn=rdoEnvironments(0).OpenConnection(dsname:="MyDSN",
' Prompt:=rdDriverNoPrompt)

' Use ODBC parameter argument syntax.
SQL = "{ ? = call MyProcName (?, ?,?,?) }"

Dim Ps As rdoPreparedStatement   
' Create reusable rdoPreparedStatement.

Set Ps = cn.CreatePreparedStatement("PsTest",SQL)

' Set Parameter "direction" types for each parameter,
' both input and output.
Ps(0).Direction = rdParamReturnValue
Ps(1).Direction = rdParamInput
Ps(2).Direction = rdParamInput
Ps(3).Direction = rdParamOutput
Ps(4).Direction = rdParamOutput

' Set the input argument values.
Ps.rdoParameters(1) = "Test%"   
Ps.rdoParameters(2) = 1    


' Create the result set and populate the Ps values.
Set rs = Ps.OpenResultset(rdOpenStatic)

MyRetVal = Ps(0)   ' Contains the return value argument.
MyOutputVal1 = Ps(3)  ' Contains the first output parameter.
MyOutputVal2 = Ps(4)  ' Contains the second output parameter.

Dim tb As rdoTable
For Each tb in cn.rdoTables
 List1.AddItem tb.Name
Next

RemoteData1.rdoResultset.MoveNext