I have just re-read the comments in the original code. The Run block is not running asynchronously. However, all db calls are serialised onto one thread, and there is a wait until done function.
1 Like
Thanks for looking into it!
Testing SimpleSqlite v3
pSqlite_Simple.aia (50.7 KB)
- Screen1
- Sqlite in ASD.
- Screen2
- Using the KIO4_Base64 extension we Store and Get the sqlite in CloudDB.
1 Like
INSERT IF name NOT EXISTS
INSERT INTO contacts(name, location)
SELECT 'Bob', 'London'
WHERE NOT EXISTS (
SELECT 1 FROM contacts WHERE name = 'Bob'
);
1 Like
Hi @TIMAI2,
I'm facing problems when I'm trying to use the tables. Let me explain what my app does:
1 - When the app is started for the very first time, it has to create all tables needed in SQLite.
2 - After that, it goes to another screen and when press a button, it will fill them with remote data (from a MariaDB remote database).
3 - At the end, it goes to the menu screen.
PS: I'm putting the Open Database in the Initialize and the Close Database in the BackPressed methods.
When I try the App, it gives me the error no such table: materiais (is one of the tables), indicating that the table does not exists.. And the message appears for all tables which were already created in the Screen1.
Waiting for any help or ideas.
You do not appear to be doing any checking, after each stage. Are the tables created?Have the data inserts to tables worked?
How to check this, when there is no return from the CREATE DATABASE command?
No. It shows the error I told you before.
Read my guide:
in the DATABASE SQL section
Did you see this ?
Ok thank you
Here is a simple demo
simplesqlitedemo.aia (24.8 KB)
Some of the commands are useful while you are developing only...
Here also is an empty sqlite database file. You can import this to the default database in order to "reset" to an empty database
empty.db.txt (8 KB)
(remove the .txt so that you just have empty.db)
SQLError - is it asynchonous, somehow? Or the 'when simplesq error makes an error, everyone suffers.
By having load of labels on my screen and writing the states of variable to them in sequence as the code works, what it appears to be showing is that the code that runs if an SQL error occurs doesn't run immediately after the SQL command itself, like an interrupt, but appears to run after the end of that block, before control is handed back to the screen.
It definitely is running, but it seems too late for the information to be of any use to the code that runs immediatley after the SQL command, which is to check the content of the SQLerror message for any text that matches particular strings. ie, PRIMARYKEY or CONSTRAINT_UNIQUE. What I'm finding though is that the variable containing the SQL error message is empty despite being in the 'When simplesqlite1.ERROR' block - until afterwards. I'm not an expert on all the blocks, but I've looked at the ones I know how to look at, and I can't yet see a way of inserting the code to run when an SQL error occurs into the main block of code I want to execute so that it picks up the error immediately after I've tried to run the SQL command. Am I missing something obvious?
What is happening here ?
Are you expecting all the work in that procedure to be completed, before moving on to the next block?
(if so, that is not how it works)
With regard to the SQLError event, this will ONLY fire if the runSQL command fails for some reason.
I am not sure that your variable would update in the middle of your "Add" event, in any case. You may need to move some of the blocks to the SQLError event.
You may need to branch your blocks: 1. No error (in Add event) 2. Error (in SQLERROR event)
'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:
- If making a Create/Update/Delete command, you get back "SQL command completed"
- 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)
- 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 
1 Like