The error I see is in the previous version I was able to query the database to save application data, pull data, etc. In the newer version the opening of the database fails so all subsequent queries fail. I am not sure if the implementaion changed. I also see no .db folder in the application like I saw in 2.12.
Thank you for providing the HMI via PM. I was able to load the HMI without issues at first with WebIQ Designer 2.15 - it also contains the .db directory. What I noticed however is that you are using an unsupported combination of WebIQ Designer and WebIQ Server:
"webiq": {
"connect": "2.14.3 (release/2.14.3/31c8a976)",
"designer": "2.13.0 v2.13.0 622196e24012a028988ef0b04111b10d95d1f2d4",
"visuals": "2.14.0 Build 36849"
}
You must not use different versions of WebIQ Designer and server - this can cause issues like the one you described and is not supported. Also, starting with WebIQ Designer 2.14, you will not be able to connect to a non-fitting version of WebIQ Server anymore.
Other than that I could not update the project because I do not have the used custom widget and the custom widget in the HMI contains an error where a variable is used in the LESS files that has not been defined:
I was able to fix this by defining it in less/custom/controls/rexroth-select-widget/sizes/default.json of the custom widget.
When it comes to the database - you are using WebIQ Server 2.14.3 - the changelog for WebIQ 2.14 shows this:
Furthermore, project.sqlite was a system database. You however used the database for creating custom tables inside:
This is unsupported and done at your own risk. Simply creating your own SQLite database and executing your queries on this file would have been the correct way to do this as these will remain untouched.
This is also the solution for your request.
Is there any documentation on working with custom tables? When I add a .db directory to the project and an hmi.sqlite file inside, the queries are unable to open the database file.
The database is always created automatically whenever you create an HMI:
The original post was about an issue where a user used project.sqlite to store data and had issues upgrading as there was a change in project.sqlite specifically.
Since I am hosting the runtime server on the ctrlX CORE, I do not have access to the folder above. I recieve an "unable to open database file" error whenever I try and execute a query on the hmi.sqlite database. I get this error in the designer preview and after publishing to the runtime.
Here is a working example using the recommended prepared statement functions:
1. Publish the HMI (if already existing)
2. Create the desired table in custom.sqlite manually, e.g. using SQLiteStudio:
3. Create prepared statements in the file .db/statements.json of the project:
{
"statements": [
{
"conditions": [],
"deferred": true,
"description": "",
"sql": "INSERT INTO test ([val]) VALUES (:val);",
"statementName": "test_insert",
"targetDatabase": "custom"
},
{
"conditions": [],
"deferred": true,
"description": "",
"sql": "SELECT * FROM test",
"statementName": "test_select_all",
"targetDatabase": "custom"
}
],
"version": 0
}
4. Load the HMI in WebIQ Designer and create a new UI Action:
actions["dbAction"] = function(parameters) {
const value = parameters[0],
qm = shmi.visuals.session.QueryManager;
console.log('[UI:dbAction] ' + value);
qm.executePreparedStatement('test_insert', { 'val' : value }, function(error, data) {
if (error) {
console.error(error);
} else {
console.log(`Inserted with id ${data.last_insert_id}`);
// Dump all
qm.executePreparedStatement('test_select_all', {}, function(error, data) {
if (error) {
console.error(error);
} else {
console.log(data.rows);
}
});
}
});
};
When you now add the UI Action to a button you can insert data into the table:
The UI Action will also show all entries in the database table on the console after insertion.
The method is documented here: https://www.smart-hmi.com/user/download/deliver/docs/documentation-webiq-visuals-reference-2.15-ee64...
I have attached the HMI (2.15.4).