|
|
 |
|
|
sql
Syntax: %sql(name, sql-string)
Related: #SQLDB, %sqldb
Perform a SQL query on a database and return the first row of results. The results are returned as a special "query object" that has several properties and methods you can use:
Properties
- Text : set or retrieve the SQL query text
- RecordCount : The number of records in the query
- Data : the raw JSON data table of the current row
- Position : set or retrieve the current record number of the query
- FieldCount : returns the number of fields (columns) in the row
- Filter : a filter string for databases that allow filtering
- Filtered : set to true (1) to apply the filter string in the Filter property. Set to false (0) to unapply the filter and return to the full result set.
- Item(index) : set or retrieve a specific column data from the current query row. The "index" can be the name of a field, or the numeric field index from 0 to FieldCount-1
Methods
- First : Move to the first row in the query
- Last : Move to the last row in the query
- Next : Move to the next row in the query
- Prior : Move to the prior row in the query
- Moveby(n) : Adds n to the current record number and moves to that row in the query
- Edit : Place the query into Edit mode. Note that setting the Item() property will automatically handle this, but this method is useful for making a large batch of changes
- Post : Post/Commit changes to the row made since Edit was called
- Cancel : Cancel changes to the row made since Edit was called
- Insert : Add a new record to the query. Must call Post after fields of the new record are set.
- Delete : Delete the current query row
- Eof Returns true (1) if at the last row of the query
- Bof Returns true (1) if at the first row of the query
- Refresh Re-executes the SQL query
Example
#SQLDB sessions.db
row = %sql(sessions, "SELECT * FROM chardb")
#WHILE (!@row.Eof()) {#SHOW @row.Item("Title");#CALL @row.Next}
#SQLCLOSE sessions
Loops through the local "sessions.db" SQLite database and displays the Title of each session. |
| User comments |
| hogarius: Sat Sep 03, 2011 7:50 pm |
|
| The List property is not documented in this article. |
|
 |
| MattLofton: Mon Dec 05, 2011 4:15 am |
|
| The Item property has a numeric field index range from 1 to Fieldcount rather than 0 to Fieldcount-1. Which range is the correct one? |
|
|
|
%abs