| Version |
Adjustments |
| 1.0 (2024-06-16) |
Initial version |
URS AI2 SQLite
This is a fork/rework/remix of Tom Bennedum's open-sourced aix-SQLite extension:
https://github.com/frdfsnlght/aix-SQLite
with some additional functions and error corrections.
Download
The ZIP archive UrsAI2SQLite for
download. The archive contains the source code and the compiled binary for uploading to App
Inventor.
Reference
Properties
- DBName
- Specifies the name of the database.
- DBVersion
- Specified the version of the database.
- DebugDialog
- Specifies whether debug messages should be displayed in dialogs.
- DebugToast
- Specifies whether debug messages should be displayed as Toast messages.
- ReturnColumnNames
- Specifies whether lists of results will contain column names. See the query blocks for more information.
- Version
- Returns the component's version name.
- VersionSDK
- Returns running Android SDK version.
Methods
- BeginTransaction ()
- Begins a transaction on an open database. Nested transactions are supported.
- CloseDatabase ()
- Closes the database. If the database is already closed, nothing happens. Any uncommited transactions will be rolled back.
- CommitTransaction ()
- Commits the last open transaction.
- DatabaseExists ()
- Returns true if the database file exists, false otherwise.
- DatabasePath ()
- Returns the full path to the database, even if it doesn't exist yet.
- Delete (table, whereClause, bindParams)
- Executes a SQL DELETE statement. There whereClause is optional. All rows in the table will be deleted if no whereClause is specified. Each bind parameter replaces the corresponding '?' in whereClause. Returns the number of rows affected if a whereClause is passed in, 0 otherwise. If an error occurs or the database is not open, -1 is returned.
- DeleteAsync (tag, table, whereClause, bindParams)
- Executes a SQL DELETE statement. The tag identifies the result of this call in the AfterDelete event. See Delete for more information.
- DeleteDatabase ()
- Deletes a closed database. This deletes the database file permanently.
- ExecSelect (sql)
- Execute a single SQL SELECT statement and returns a list of records. If selecting only a single column, each element in the returned list is the value of that column for each result row. If selecting multiple columns, each element of the returned list is itself a list of values for each selected column. If the ReturnColumnNames option is true, each column value will be a two element list where the first element is the column name and the second element is the column value. If the database is not open, an empty list is returned.
- ExecSQL (sql)
- Execute a single SQL statement that is NOT a SELECT. If the database is not open, false is returned.
- Execute (sql, bindParams)
- Execute a single, parameterized SQL statement that is NOT a SELECT and returns whether or not it succeeded. Each bind parameter replaces the corresponding '?' in WHERE clause in the query. If the database is not open, false is returned.
- ExecuteAsync (tag, sql, bindParams)
- Execute a single, parameterized SQL statement that is NOT a SELECT and returns whether or not it succeeded. The tag identifies the result of this call in the AfterExecute event. See ExecParamSQL for more information.
- ExecuteFile (fileName)
- Executes multiple SQL statements from a file and returns the count of statements successfully executed. Each line of the file should be a complete non-SELECT SQL statement, optionally followed by a semicolon. Single line (--) and multiline (/*...*/) comments are ignored. Line continuation using '\\' is supported. '\\n' are replaced with actual newlines. Execution stops at the first error. If the database is not open, -1 is returned. Prefix the filename with / to read from a specific file on the SD card. To read assets packaged with an application (also works for the Companion) start the filename with // (two slashes). If a filename does not start with a slash, it will be read from the applications private storage (for packaged apps) and from /sdcard/AppInventor/data for the Companion.
- ExecuteFileAsync (tag, fileName)
- Executes multiple SQL statements from a file, asynchronously. The tag identifies the result of this call in the AfterExecuteFile event. See ExecFile for more information.
- ExportDatabase (fileName)
- Exports the currently closed database to the specified file. The resulting file is a complete SQLite database. Returns true if the import was successful, false otherwise.
- ImportDatabase (fileName)
- Imports a SQLite database completely replacing the currently closed database. Returns true if the import was successful, false otherwise.
- Insert (table, columns, values)
- Executes a SQL INSERT statement. columns contains a list of column names. values contains a list of column values. Returns the row ID of the newly inserted row. If the error occurs or the database is not open, -1 is returned.
- InsertAsync (tag, table, columns, values)
- Executes a SQL INSERT statement, asynchronously. The tag identifies the result of this call in the AfterInsert event. See Insert for more information.
- InsertBulk (Table, Data, Separator)
- Inserts multiple rows of data from a multi line string into a table. The first line of the string should be a CSV list of the column names. Each of the remaining lines should be a CSV list of values for each new row. Empty lines are ignored. Line continuation using '\\' is supported. '\\n' are replaced with actual newlines. Insertion stops at the first error. If the database is not open, -1 is returned.
- InsertFile (table, fileName, Separator)
- Inserts multiple rows of data from a file into a table. The first line of the file should be a CSV list of the column names. Each of the remaining lines should be a CSV list of values for each new row. Empty lines are ignored. Line continuation using '\\' is supported. '\\n' are replaced with actual newlines. Insertion stops at the first error. If the database is not open, -1 is returned. Prefix the filename with / to read from a specific file on the SD card. To read assets packaged with an application (also works for the Companion) start the filename with // (two slashes). If a filename does not start with a slash, it will be read from the applications private storage (for packaged apps) and from /sdcard/AppInventor/data for the Companion.
- InsertFileAsync (tag, table, fileName, Separator)
- Inserts multiple rows of data from a file into a table, asynchronously. The tag identifies the result of this call in the AfterInsertFile event. See InsertFile for more information.
- IsDatabaseOpen ()
- Returns true if the database is open, false otherwise.
- OpenDatabase ()
- Opens the database. If the database is already open, nothing happens.
- Replace (table, columns, values)
- Executes a SQL REPLACE statement. columns contains a list of column names. values contains a list of column values. Returns the row ID of the newly inserted or updated row. If the error occurs or the database is not open, -1 is returned.
- ReplaceAsync (tag, table, columns, values)
- Executes a SQL REPLACE statement, asynchronously. The tag identifies the result of this call in the AfterReplace event. See Replace for more information.
- RollbackTransaction ()
- Rolls back the last open transaction.
- Select (table, distinct, columns, whereClause, bindParams, groupBy, having, orderBy, limit)
- Executes a SQL SELECT statement. There whereClause is optional. All rows in the table will be returned if no whereClause is specified. Each bind parameter replaces the corresponding '?' in whereClause. groupBy is optional. If not specified, no grouping will be performed. having is optional. If not specified, all row groups will be returned. orderBy is optional. If not specified, rows will be returned unordered. limit is optional. If not specified, all matching rows will be returned. If selecting only a single column, each element in the returned list is the value of that column for each result row. If selecting multiple columns, each element of the returned list is itself a list of values for each selected column. If the ReturnColumnNames option is true, each column value will be a two element list where the first element is the column name and the second element is the column value. See SelectRawSQL for more information on the returned list.
- SelectAsync (tag, table, distinct, columns, whereClause, bindParams, groupBy, having, orderBy, limit)
- Executes a SQL SELECT statement, asynchronously. The tag identifies the result of this call in the AfterSelect event. See Select for more information.
- SelectSQL (sql, bindParams)
- Execute a single, parameterized SQL SELECT statement and returns a list of records. Each bind parameter replaces the corresponding '?' in WHERE clause in the query. If selecting only a single column, each element in the returned list is the value of that column for each result row. If selecting multiple columns, each element of the returned list is itself a list of values for each selected column. If the ReturnColumnNames option is true, each column value will be a two element list where the first element is the column name and the second element is the column value. If the database is not open, an empty list is returned.
- SelectSQLAsync (tag, sql, bindParams)
- Execute a single, parameterized SQL SELECT statement, asynchronously. The tag identifies the result of this call in the AfterSelect event. See SelectSQL for more information.
- TableCount ()
- Returns the number of tables in the database, or -1 if an error occurs or the database is not open.
- TableExists (table)
- Returns true if the table exists in the database, or false if the table does not exist or an error occurs or the database is not open.
- TableNames ()
- Returns a list of names of the tables in the database, or an empty list if an error occurs or the database is not open.
- TableRowCount (table)
- Returns the number of rows in a table, or -1 if an error occurs or the database is not open.
- Update (table, columns, values, whereClause, bindParams)
- Executes a SQL UPDATE statement. columns contains a list of column names. values contains a list of column values. There whereClause is optional. All rows in the table will be updated if no whereClause is specified. Each bind parameter replaces the corresponding '?' in whereClause. Returns the number of rows affected. If an error occurs or the database is not open, -1 is returned.
- UpdateAsync (tag, table, columns, values, whereClause, bindParams)
- Executes a SQL UPDATE statement, asynchronously. The tag identifies the result of this call in the AfterUpdate event. See Update for more information.
Events
- AfterDelete (tag, rowCount)
- This event fires after an asynchronous Delete call. The tag specified in the original call and the number of deleted rows are provided.
- AfterExecute (tag, result)
- This event fires after an ExecuteSQLAsync call. The tag specified in the original call and the result of the execution are provided.
- AfterExecuteFile (tag, execCount)
- This event fires after an ExecuteFileAsync. The tag specified in the original call and the result of the execution are provided.
- AfterInsert (tag, rowId)
- This event fires after an asynchronous Insert call. The tag specified in the original call and the row ID of the new row are provided.
- AfterInsertFile (tag, rowCount)
- This event fires after an asynchronous InsertFile call. The tag specified in the original call and the count of inserted rows are provided.
- AfterReplace (tag, rowId)
- This event fires after an asynchronous Replace call. The tag specified in the original call and the row ID of the new or updated row are provided.
- AfterSelect (tag, rowCount, rows)
- This event fires after an asynchronous Select call. The tag specified in the original call, the number of returned rows, and the result rows are provided.
- AfterUpdate (tag, rowCount)
- This event fires after an asynchronous Update call. The tag specified in the original call and the number of changed rows are provided.
- DatabaseClosed ()
- This event fires when the database is closed.
- DatabaseCreated ()
- This event fires when the database is created.
- DatabaseDowngrade (oldVersion, newVersion)
- This event fires when the database is downgraded. The previous and new version numbers are provided. Use this event to modify the database as required by the version change.
- DatabaseOpened ()
- This event fires when the database is opened.
- DatabaseUpgrade (oldVersion, newVersion)
- This event fires when the database is upgraded. The previous and new version numbers are provided. Use this event to modify the database as required by the version change.
- SQLError (message)
- This event fires when a SQL error occurs. The error message is provided.
For
developing own extensions I
gathered some tips:
AI2 FAQ: Develop Extensions.