Documentation

Added in v3.11

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.

Add comment

Login or register to post comments