True, whilst not inaccurate (the function has completed...), it does not indicate an error. You should get something back from sqlite though, which could be used as a conditional test?
Yes you are right, technically, the function as completed and I can also use this value to filter the result as I never expect to simply get "completed" from my sql. It's just that it's confusing at first.
What do you mean by You should get something back from sqlite though. Do you mean an error message or just the value ["completed"] (which is the value returned by the sql block which I can use a condition to test if the sql "succeeded")
I came across another situation where ['completed'] is misleading: I just tested an INSERT and I get ['completed'] whether there was an error or not, so I have no way of knowing if the query was successfully completed or if it completed with an error (ex: I didn't add single quotes before and after the text). In this case, it would be really nice to retrieve a more informative value.
Call back what you inserted with another query. Is it as expected ?
Yes, that is a possible solution but it's complicated in comparaison with receiving a returned value from the sql that is more informative. If it's too complicated/impossible to retrieve description of the error, just "completed with error" or "completed successfully" would be nice.
Try this:
uk.co.metricrat.simplesqliteV3j.aix (now available from first post as latest release)
You will need to use the SQLError event to return the error/code
The Run block now returns: ['SQL command completed'] instead of just completed if not returning a list of items.
Also, I believe my fork is correctly using the async method for all calls on the Run block.
1 Like
It's awesome! Thanks so much!
I installed it this morning but I didn't get the time to test it, and when I came back this afternoon, I forgot I had installed it and also that I had changed the columns names in my db without updating them in the sql block. Of course, it failed but instead of being in the dark, I got a helpful message! That was simply fantastic! Many thanks! You even did it on a Sunday! You are amazing!
I tested it on an insert and a select query and it's just perfect!
Thanks also for implementing the async sql. I wonder how it works. In the extension from Tab Bennedum, there was an "after sql" block, that looked like a procedure block (with a tag to differentiate the sql) so that we can perform some actions after the sql is done, but I don't see this block in your extension. Let's say, I am running a large sql and then feed the result to the webview. If my blocks are like below, is there a risk that the webviewer won't get the returned sql, if it is really large and it takes some time to run in async?
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?
