SQL::ResultSet Object

When a SQL query returns one or more rows of data, you can use the SQL::ResultSet object to get the description of the result set and to navigate within the rows of the results to retrieve the data.

dim conn as SQL::Connection

dim rs as SQL::ResultSet

dim connString as C

dim select_exp as C

connString = "{A5API='Access', FileName='c:\program files\a5v8\mdbfiles\alphasports.mdb'}"

select_exp = "select * from customer where bill_state_region = 'ny'"

if .not. conn.open(connString)

ui_msg_box("Error", conn.CallResult.text)

end

end if

if .not. conn.execute(select_exp)

ui_msg_box("Error", conn.CallResult.text)

conn.close()

end

end if

rs = conn.ResultSet

conn.close()

The SQL::ResultSet can only be used once. Once you have reached the end of the SQL::ResultSet, you have to re-execute the query to go through it again. This applies when using the ::ToPropertyArray() and ::ToString() methods.

Note : Once you have handed off a SQL::ResultSet via assignment, do not use the original source. Always work with the assigned object.

' after executing this code

rs = conn.ResultSet

' do not do this:

Txt = conn.ResultSet.ToString()

' instead, do this:

Txt = rs.ToString()

Properties

The SQL::ResultSet object has the following variables.

Variable	Type	Mode	Description
CallResult	SQL::CallResult	R	Detailed status of the result of the call.
ColumnCount	N	R	Default = 0. The number of columns in the result set.
ColumnInfo	P	R	An array of SQL::DataTypeInfo column descriptions.
CurrentRow	SQL::Row	R	Returns the current row as an object.
CurrentRowIndex	N	R	Default = 0. The number of the current row.
DBFRowSyntax	C	R	Default = "". The syntax required to create an Alpha Five .DBF table with the same columns.
RowCount	N	R	Default = 0. The number of rows in the result set. -1 If not known.
TableInfo	P	R	Not always available. An array of SQL::TableInfo table descriptions returned by the query.
TableInfoCount	N	R	Default = 0. The number of elements in .TableInfo.

Methods

The SQL::ResultSet object has the following methods.

Method	Description
::ColumnNumber()	Returns the number of the named column.
::Data()	Returns the data for the specified column index.
::DataIsNull()	Determines whether a given element is Null.
::FetchPage()	Fetches a block of one or more rows into a local cache table.
::FormatData()	Returns a string representation of a data value in a result set row.
::GoToRow()	Positions to the requested row. Backward movement is not supported by all databases.
::NextResult()	For databases that support returning more than one result set from a single SQL statement (or batch), this function positions to the next result set.
::NextRow()	Positions to the next row in the result set.
::ToGridComponentDataObject()	Fetches data from a result set to a property array usable by the Grid Component Builder.
::ToJSON()	Converts the data in one or more rows to an implementation of JavaScript Object Notation format specific to the Alpha Five AJAX implementation.
::ToOpenTable()	Fetches rows from a table to an open .DBF table.
::ToPropertyArray()	Fetches data from a result set to a property array.
::ToString()	Copies the result set to a character string.
::ToTableInfo()	Creates and returns an unnamed SQL::TableInfo object with the columns from the current query.
::ToXML()	Copies the result set to an XML formatted string.