This design is used to define and run queries in an ODBC database. It is used in conjunction with the odbc_Database design. An ODBC "recordset" underlies each instance of this design. You can extract the actual data from the database using additional methods of this design. You can also get other information, such as the record count. You need a single instance of this design in your model for each query. Each instance will typically refer to a single instance of odbc_Database.
| Name | Type | Description |
|---|---|---|
| SQLstatement | string | The SQL statement used to create the underlying recordset. You can either provide the complete statement in this parameter, or ISL can build it for you using the individual parameters. |
| Name | Type | Description |
|---|---|---|
| database | part | The database to query. This must be an instance of ODBC_Database. This is a required parameter. |
| type | name | The type of recordset to create. Default value is :Dynaset, which provides read and write access to the underlying database table. The only other supported option is :Snapshot, which is read-only. |
| tableName | string | Name of the table or view to use in the query. This parameter is required, unless you specify the complete SQL query using the SQLstatement parameter. For an Excel spreadsheet, this would be a named range in the .xls file. |
| fields | string | Fields (columns) to return in the query. Default value is "*", meaning all columns in the table. |
| where | string | An SQL "where" string, used for filtering the rows to return from the table. Default value is "", meaning no filtering. |
| orderBy | string | An SQL "orderBy" string , used to sort the returned rows in the query. Default value is "", meaning no particular sorting. |
| reuse? | boolean | This flag controls the behavior of the recordset at Update time. If reuse? equals True , the system reuses the same Recordset object after the update. The action that the system takes depends on the value of requeryOnUpdate?. This behavior results in optimal performance, but can potentially be out of sync with the rules controlling the internal SQL statement and/or out of sync with the data in the db (which is always a possibility). If reuse? equals False , then the system closes and frees the old Recordset object, and creates and opens a new one. Default value is False . |
| requeryOnUpdate? | boolean | If True , then automatically performs a Requery operation when this part is first demanded after an Update. If False , reuses the Recordset object in the same state as it was before, regardless of any changes to the database or parameters. This parameter is only relevant if the Reuse? parameter is True . Default value is True for dynasets, False otherwise. |
| updateOnUpdate? | boolean | Controls whether or not unsaved modifications to the recordset (if any) are saved back to the database when the ISL recordset is closed. Default value is False , meaning changes are not saved. Use the Update() method to save changes. |
| maxStringSize | integer | Defines the maximum size of a string , including the terminating null byte. Default value is 1024. |
| Name | Type | Description |
|---|---|---|
| isBOF? | boolean | uncached Determines whether the current record is at the first record (if any). |
| isEOF? | boolean | uncached Determines whether the current record is past the last record (if any). |
| isEmpty? | boolean | uncached Determines whether the recordset has no rows. In this case, isBOF?: and isEOF?: will both be True . |
addNew( Optional ignoreErrors? As Boolean = False ) As BooleanAdds a new record (row) to the recordset, without specifying the values of each field (column). Arguments: * ignoreErrors? - If True , then failure in operation does not result in an Intent exception. Returns: True , if operation succeeded
addNewRecord( data As List,
Optional ignoreErrors? As Boolean = False ) As Boolean
Takes a list containing data that corresponds to each field, adds a new record (row) to the recordset, sets the each field (column) to the corresponding element of the data list , and permanently saves it in the database. There is no need to call Update afterwards. You must call the Requery method to see the new record. Arguments: * data - appropriate-formatted data for each field, in field order. If a value for a field is the Name NoChange, then the field value is not changed - but remains at its default value. This is particularly useful for "key" fields which are auto-numbered. Each field is set sequentially. * ignoreErrors? - If True , then failure in operation does not result in an Intent exception. Returns:
True
, if operation succeeded cancelUpdate( Optional ignoreErrors? As Boolean = False ) As BooleanDiscards all changes made to the recordset, but not yet saved by using the update() method. Arguments: * ignoreErrors? - If True , then failure in operation does not result in an Intent exception. Returns: True , if operation succeeded
delete( Optional ignoreErrors? As Boolean = False ) As BooleanDeletes the current row from database. Arguments: * ignoreErrors? - If True , then failure in operation does not result in an Intent exception. Returns: True , if operation succeeded
edit( Optional ignoreErrors? As Boolean = False ) As Boolean"Opens" the current row for modifications. Arguments: * ignoreErrors? - If True , then failure in operation does not result in an Intent exception. Returns: True , if operation succeeded
editRecord( data As List, _
Optional ignoreErrors? As Boolean = False ) As Boolean
Modifies the current record and saves the changes. This method calls the more primitive edit() method, sets all the fields in the row using the given data, and then calls the update() method to save the changes. Arguments: * data - appropriate-formatted data for each field, in field order. If a value for a field is the Name NoChange, then the field value is not changed - but remains at its default value. icularly useful for "key" fields which are auto-numbered. Each field is set sequentially. * ignoreErrors? - If True , then failure in operation does not result in an Intent exception. Returns: True , if operation succeeded
findFirst( Optional value As Any = 0, _
Optional field As String = "", _
Optional ignoreErrors? As Boolean = False, _
Optional OnErrorReturn As Any = False ) As Boolean
Finds the first record (row) where the contents of field is equal to the given value. If found, the found record is made current and the method returns True. If not found, the current record is undefined, and the method returns False. Arguments: * value - Intent value to search for * filed - Field (column) to scan * ignoreErrors? - If True, then failure in operation does not result in an Intent exception. * OnErrorReturn - if error occurs (and ignoreErrors? is True ), this parameter represents the default value Returns:
True
, if operation succeeded getFieldNames( Optional ignoreErrors? As Boolean = False ) As ListReturns a list of the field (column) names in the recordset Arguments: * ignoreErrors? - If True, then failure in operation does not result in an Intent exception.
getFieldValue( columnName As Any, _
Optional OnNullReturn As Any = Error, _
Optional ignoreErrors? As Boolean = False ) As Any
Returns the value of the specified field (column) in the current record. Arguments: * columnName - Field name or integer index of the filed in the table. * OnNullReturn - If the field is empty (null), the returned value is controlled by the onNullReturn argument. The default value for this argument is :Error, meaning raise an Intent Language exception. * ignoreErrors? - If True, then failure in operation does not result in an Intent exception. getRecordCount( Optional fullCount? As Boolean = True, _
Optional ignoreErrors? As Boolean = False ) As Integer
Returns the number of records in the recordset. Arguments: * fullCount -can be set to False to get the number of records seen "so far" (i.e., the "high-water mark" of the "current record" pointer). * ignoreErrors? - If True, then failure in operation does not result in an Intent exception. getRecord( Optional OnNullReturn As Any = :Error, _
Optional ignoreErrors? As Boolean = False ) As List
Returns the value of all the fields in the current record, as a List. Arguments: * OnNullReturn - If the field is empty (null), the returned value is controlled by the onNullReturn argument. The default value for this argument is :Error, meaning raise an Intent Language exception. This argument may be a list establishing the OnNullReturn values for each field * ignoreErrors? - If True, then failure in operation does not result in an Intent exception. move( n As Integer, _
Optional ignoreErrors? As Boolean = False ) As Boolean
Moves the current record by n records. Arguments: * n - records count. The value can be negative if the underlying recordset type supports that. * ignoreErrors? - If True, then failure in operation does not result in an Intent exception. Returns:
True
, if operation succeeded moveFirst( Optional ignoreErrors? As Boolean = False ) As BooleanMoves the current record to point at the first record (if any). Arguments: * ignoreErrors? - If True, then failure in operation does not result in an Intent exception. Returns: True , if operation succeeded
moveLast( Optional ignoreErrors? As Boolean = False ) As BooleanMoves the current record to point at the last record (if any). Arguments: * ignoreErrors? - If True, then failure in operation does not result in an Intent exception. Returns: True , if operation succeeded
moveNext( Optional ignoreErrors? As Boolean = False ) As BooleanMoves the current record pointer ahead by one record. Arguments: * ignoreErrors? - If True, then failure in operation does not result in an Intent exception. Returns: True , if operation succeeded
movePrev( Optional ignoreErrors? As Boolean = False ) As BooleanMoves the current record pointer back by one record. Arguments: * ignoreErrors? - If True, then failure in operation does not result in an Intent exception. Returns: True , if operation succeeded
requery( Optional ignoreErrors? As Boolean = False ) As BooleanReruns the query on the actual database. Does not unbind any previously-cached values, only affects future method calls. Arguments: * ignoreErrors? - If True, then failure in operation does not result in an Intent exception. Returns: True , if operation succeeded
SetFieldValue( columnName As Any, _
value As Any, _
Optional ignoreErrors? As Boolean = False ) As Boolean
Sets the value in columnName in the current record, to be the given value. You must have previously called AddNew() or Edit(). You must call update() in order to save this change. Arguments: * columnName - Field name or integer index of the filed in the table. * value - Intent value to set * ignoreErrors? - If True, then failure in operation does not result an Intent exception. Returns:
True
, if operation succeeded Update( Optional ignoreErrors? As Boolean = False ) As BooleanSaves any changes back to the database. Arguments: * ignoreErrors? - If True, then failure in operation does not result in an Intent exception. Returns: True , if operation succeeded
<tableName_and_options> Builds the SELECT statement from fields, tableName, where, and orderBy rules
Using Recordsets as parts in the project tree works well if the underlying SQL query has not been changed. However a problem will arise when the number of open recordsets exceeds the physical limit imposed by Access [roughly 250 or so]. To overcome this problem with the Database connections, you may need to use the odbc_Recordset* functions instead of designs. The basic process is the following: open recordset read data close recordset. Closing the recordset is very important, because ODBC drivers will allocate more and more connections until premature failure. To implement this, you could do something similar to the following. Suppose you have a database design named db:. You could access the data with a method similar to the following: Method GetDBData( sql As String) As List Dim recSet As Any = odbc_RecordsetOpen(db, :snapshot, sql) ' access the data ' ... perhaps something similar to the Records: rule formula ' close recordset odbc_recordsetClose(recSet) ' # this is important! ' return valueEnd Method Then all Recordset parts could be replaced with rules that call GetDBData:( ) instead. This will preclude having too many recordsets open simultaneously - or for a prolonged amount of time - which is expensive.