iAnywhere.com - Data retrieval: SELECT

Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » UltraLite - AppForge Programming » Understanding UltraLite Development with AppForge » Working with data using dynamic SQL

Data retrieval: SELECT

When you execute a SELECT statement, the ULPreparedStatement.ExecuteQuery method returns a ULResultSet object.

The ULResultSet class contains methods for navigating within a result set. The values are then accessed using methods of the ULResultSet class.

See ULResultSet class.

Example

In the following code, the results of a SELECT query are accessed through a ULResultSet. When first assigned, the ULResultSet is positioned before the first row. The ULResultSet.MoveFirst method is then called to navigate to the first record in the result set.

For more information about navigating a result set, see Navigation with dynamic SQL.

'MobileVB using VB6
Dim MyResultSet As ULResultSet
Dim PrepStmt As ULPreparedStatement
PrepStmt = Connection.PrepareStatement( _ 
    "SELECT ID, Name FROM customer")
MyResultSet = PrepStmt.ExecuteQuery
MyResultSet.MoveFirst
'Crossfire using vb.net
Dim MyResultSet As UltraLiteAFLib.ULResultSet
Dim PrepStmt As UltraLiteAFLib.ULPreparedStatement
PrepStmt = Connection.PrepareStatement( _ 
    "SELECT ID, Name FROM customer")
MyResultSet = PrepStmt.ExecuteQuery
MyResultSet.MoveFirst

UltraLite for AppForge provides you with methods to get data of particular types from the UltraLite database into a result set. MobileVB does not support the use of Variant data types and, because of this, UltraLite for MobileVB includes functions to handle all types of data. Each of these methods is called using the following template, where Index is the ordinal position of the column name in your SELECT statement:

MyResultSetName.MethodName( Index )

Example

The following code demonstrates how to use the GetString method to obtain the column values for the current row.

The GetString method uses the following syntax, where Index is the ordinal position of the column name in your SELECT statement.

MyResultSetName.GetString(Index)

The MoveRelative(0) method is called to refresh the contents of the current buffer from the result set, so that the effects of any data modification are included.

If MyResultSet.RowCount = 0 Then
  lblID.Caption = ""
  txtName.Text = ""
Else
  lblID.Caption = MyResultSet.GetString(1)
  txtName.Text = MyResultSet.GetString(2)
  MyResultSet.MoveRelative(0)
End If

The following procedure uses a SELECT statement to retrieve information from the database. The results of the query are assigned to a ULResultSet object.

To perform a SELECT statement

Declare a ULPreparedStatement object.

'MobileVB using VB6
Dim PrepStmt As ULPreparedStatement
'Crossfire using vb.net
Dim PrepStmt As UltraLiteAFLib.ULPreparedStatement

Assign a prepared statement to your ULPreparedStatement object. In the following code, TableName and ColumnName are the names of a table and column.
```
Set PrepStmt = Connection.PrepareStatement( _ 
    "SELECT ColumnName FROM TableName")
```

Execute the query.

In the code below, an AFListBox captures the result of the SELECT query.

Dim MyResultSet As ULResultSet
Set MyResultSet = PrepStmt.ExecuteQuery
While MyResultSet.MoveNext
  aflistbox.AddItem MyResultSet.GetString(1)
Wend

After processing the query, free resources by closing the result set.
```
MyResultSet.Close()
```