🟥 [FREE] SimpleSqlite - an extension for working with SQLite Databases

'Validatecontextfields' is a subroute / procedure that checks the individual fields for their own validity before trying to create a new record (also will be used for checking fields prior to updating an existing record). It just checks fields for length and content validity so it doesn't create a record that has inherently invalid content. It doesn't do any SQL commands or refer to any SQL error message field in it.

I was intending to rely on the attempt to add the record and have the SQL constraints (duplicate key or duplicate description field which are already defined in the database) prevent an invalid record being written because, which does work. It's just that the feedback from that isn't available until the block containing the SQL command that has failed has completed in its entirety. I was wondering if it's a timing issue with an asychronous element to the error message handling, or if I am forced to it a 'proper' way of checking for existing records with identical fields and getting the response back from that before issuing the 'INSERT' command.

what this shows is that the label7.text is set to label1.text,as the last action of the block that includes attempting to insert the new record into the SQL table. This is after it has reloaded the table view file, which for testing purposes also loads the whole of the list into label1.text. However, Label1.text itself, just below the tableview table, is showing the SQL error text, which means it is not updated with the SQL error text until after the end of the whole block of code that executes the SQL instruction from which the error originated. So is there a way of getting access to that error code immediately after the SQL instruction that causes the error?

The paid SQL extension is worse - error trapping only applies to importing and exporting tasks. Looks like I'll have to use it in th way you suggest.

If I remember correctly, and from a quick look at the underlying code, you get one of three responses from the runSQL block:

  1. If making a Create/Update/Delete command, you get back "SQL command completed"
  2. If making a Read command, you get back the data as an array (unless there is no data, in which case you get the above string)
  3. If an error, then you get back nothing.

You can test for this in your Label3 content, and then run more blocks depending on the returned value, branching to the SQL Error if response is blank.

Ensure your set Label3 to blank before running the SQL.

Ah, that's really helpful, thanks. I think that's important information actually in understanding how to utilise SQL commands.

with this particular extension :wink:

1 Like

For an event oriented refresher, see


Hmmm, are you quite sure that the return result is blank or something other than 'SQL command completed' if the command fails? It's looking to me as though I'm getting that result even when it fails to insert a record.

I was worried this might be the case...

This might work:

  • Drag in a clock timer and set interval to 100ms
  • In SQLError set the output to a variable
  • start the clock after the Run method (no other blocks below this)
  • In the Clock Timer event, first stop the clock, then check the variable for an error message
  • If error do one thing, if no error, do what you were going to do in the Add event after a successful response

My logic is that if you break out of the Add event with a clock event, you should then be able to get the output from the SQL Error ?

Hmm, thanks for the suggestion. That feels like an unnecessarily complicated way of getting at it though. Would it take much to fix that so that it doesn't return 'completed' if it didn't carry out the command? Does the Tom Bennedum's version have the same problem I wonder?

Same code as far as I can remember. My fork was really about sorting out file paths and reducing the block methods and properties.

I had a look at the code, and do not really want to mess with it.

The timeout clock technique is also known as a watchdog timer.

This is a well known design pattern.

In case you are talking about my extension at App Inventor Extensions: SQlite | Pura Vida Apps
In case of an error, the error will be returned directly in the Execute method or for the ExecuteAsync method in the Executed event


1 Like

Oh ok, that probably was the one I was referring to. The description I was looking at seemed to be indicating that it would only return an error message if import or export fails.

sqlite list. Where am I going wrong?

You have a list of lists:

What is this ListFixer method about?
You probably get your result after removing that method. .
Alternatively use a sqlite extension, which really is simple to use.. App Inventor Extensions: SQlite | Pura Vida Apps

See the documentation.

yes, list list.

Can you see the difference?

It's like peeling an onion, working from outside in one layer at a time.

The expressions are evaluated from right to left, so the rightmost selection gets you the outermost layer, then proceed from right to left.

1 Like