SQL - Module for working with SQL databases

[GhostZ]

Name: SQL
Version: 1.3
Data: SQL.zip, CommonCode.zip - utility module required for work.
Source: -.
Do you agree to include your module in BAS: The module has been added to BAS.
Description: Module for working with SQL databases (Postgres, MySQL, MariaDB, SQLite, MSSQL).
Changelog:

spoiler

Screenshots:

---

For the module to work on BAS below version 24.1.0, you need to install the utility module CommonCode.zip

---

The module is well tested, but if you experience problems while working, you can describe them in this thread.

[morpheus93]

Use it with MySQL an it shows "SequelizeDatabaseError: Invalid default value for 'profile_folder'" on "Execute query" with INSERT OR SELECT. Not always but sometimes nevertheless the values are always inserted correctly into the DB table.

Debug shows: [22:47:33] Thread #1 : Executing (default): SELECT 1+1 AS result
[206186932] [22:47:33] Thread #1 : Executing (default): SHOW FULL COLUMNS FROM profiles;
[206186932] [22:47:33] Thread #1 : Result: {"id":{"type":"INT(11)","allowNull":false,"defaultValue":null,"primaryKey":true,"autoIncrement":true,"comment":null},"profile_folder":{"type":"VARCHAR(120)","allowNull":false,"defaultValue":null,"primaryKey":false,"autoIncrement":false,"comment":"folder to local profile"},"email":{"type":"VARCHAR(60)","allowNull":true,"defaultValue":null,"primaryKey":false,"autoIncrement":false,"comment":"email address linked to profile"},"website":{"type":"VARCHAR(30)","allowNull":true,"defaultValue":null,"primaryKey":false,"autoIncrement":false,"comment":"website where profile was used"},"date":{"type":"DATETIME","allowNull":false,"defaultValue":null,"primaryKey":false,"autoIncrement":false,"comment":"date when profile was added/used"}}
[206186932] [22:47:33] Thread #1 : Executing (default): CREATE TABLE IF NOT EXISTS profiles (id INT(11) NOT NULL auto_increment DEFAULT NULL , profile_folder VARCHAR(120) NOT NULL DEFAULT NULL COMMENT 'folder to local profile', email VARCHAR(60) DEFAULT NULL COMMENT 'email address linked to profile', website VARCHAR(30) DEFAULT NULL COMMENT 'website where profile was used', date DATETIME NOT NULL DEFAULT NULL COMMENT 'date when profile was added/used', PRIMARY KEY (id)) ENGINE=InnoDB;
[206186932] [22:47:33] Thread #1 : SequelizeDatabaseError: Invalid default value for 'profile_folder'"

So I assume it has something to do with the " CREATE TABLE IF NOT EXISTS" and the "Default Null" despite the column is set to "NOT NULL".

[GhostZ]

@morpheus93, this is a standard MySQL error associated with Data Type Default Values

[morpheus93]

@GhostZ Okay, the problem is that BAS ends the current project/thread with error (failed) even on simple SELECT queries and nevertheless the queries against the MySQL database are completely correct and work as it should regarding the database. So the only solution I can currently think of is to set every db action within a BAS project in "Ignore errors" but that's quite inconvenient.

Any other idea?

[GhostZ]

@morpheus93, correct the structure of your database or use only the "Execute query" action as it does not use the Sequelize Model.

[morpheus93]

@GhostZ Thank you for your fast response, I will try it that way.

[tecnewb]

well, i guess i found your error here.
i run an external mysql db and have NOT granted rights to create or alter tables to the user, only table entries

so i noticed that it ALWAYS FAILS on the first query to an existing table after bot restart.
so for example:

• Bot restart
• write to table log -> FAIL
• write again to table log -> success
• write to table data -> FAIL
• write again to table data -> success

100% reproducable for me all the time. so for every Table in the script one would have to run it twice

turn on debug, what do i see:
on first run, it fetches the table data, then makes "create if not exist" query and then fails without executing the insert/update query.
it seems like it is not correctly executing BOTH queries behind each other, as i always get errors for "xyz can not be empty" on the first run, but not the second

on second run it has the table data (in RAM ?) and then executes the correct insert or update query, maybe it is just a missing comma or semicolon somewhere in your code?

can you please fix this? i don't want to screw around with your plugin :)

[GhostZ]

@tecnewb said in SQL:

xyz can not be empty

It means that when adding a record, you did not specify a value that cannot be empty (see your database settings).

[tecnewb]

@GhostZ

i see what you mean, but i mean something different!
i have specified the Value in the query.
The query is sent without the value i set.
it does not read the value from BAS-variable fast enough before sending

[GhostZ]

@tecnewb, the module executes the request as soon as you call its action, if the connection has already been completed, if the connection has not yet been completed, then the connection is performed first, and then the request. If an error occurs while connecting to the database, then most likely the problem is in your database.

[tecnewb]

problem is NOT the DB. promised

3 examples:

1. bot with nodejs mysql2 function
can run 2000 writes per second no problem. no errors
all values are corrrectly inserted
insert into log (id,timestamp,botname,comment) values ([[ID]],CURRENT_TIMESTAMP,[[BOTNAME]],[[COMMENT]]);
--> insert into log (id,timestamp,botname,comment) values (123,CURRENT_TIMESTAMP,'test','test');

2. Bot with your SQL and "Execute query" function
can run 2000 writes per second no problem. no errors
all values are corrrectly inserted
insert into log (id,timestamp,botname,comment) values ([[ID]],CURRENT_TIMESTAMP,[[BOTNAME]],[[COMMENT]]);
--> insert into log (id,timestamp,botname,comment) values (123,CURRENT_TIMESTAMP,'test','test');

3. Bot with your SQL and "Insert Row" function
First run after new start:
insert into log (id,timestamp,botname,comment) values ([[ID]],CURRENT_TIMESTAMP,[[BOTNAME]],[[COMMENT]]);
--> insert into log (id,timestamp,botname,comment) values (NULL,NULL,NULL,NULL);
NO VALUES !!!!!

4. Bot with your SQL and "Insert Row" function
second run, can run 2000 writes per second no problem. no errors
insert into log (id,timestamp,botname,comment) values ([[ID]],CURRENT_TIMESTAMP,[[BOTNAME]],[[COMMENT]]);
--> insert into log (id,timestamp,botname,comment) values (123,CURRENT_TIMESTAMP,'test','test');
all values are corrrectly inserted

it is always ONLY on 1. run

is there something i can check locally? DB connection is fine and everything else works fine as well, just not sequelize

[GhostZ]

@tecnewb, attach a screenshot of your settings in the "Insert Row" action

[tecnewb]

@GhostZ

this is execute query

and this is insert query

[tecnewb]

Please see this video for screen recording

https://streamable.com/11eo16

[GhostZ]

@tecnewb, attach a project, a file with test data and a table schema, I will try to repeat the error.

[tecnewb]

@GhostZ

Table:

CREATE TABLE `z_log` (
  `action_id` int NOT NULL AUTO_INCREMENT,
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `error` tinyint(1) NOT NULL DEFAULT '0',
  `vm_id` int NOT NULL,
  `run_id` varchar(45) NOT NULL,
  `botname` varchar(45) NOT NULL,
  `message` varchar(500) NOT NULL,
  PRIMARY KEY (`action_id`)
) ENGINE=InnoDB AUTO_INCREMENT=467 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

This file has to be put in "C:/Sync/vm.csv" it contains the settings for the bot: VM_ID, DB Host IP ([[DB_IP]]), DB User, DB Password
Variable "ACC_ID" is not set in the file but also not used in the script
Please note this file is TAB Seperated and that the DB Name is hardcoded in the "DB Setup" Function in "start"
--> vm.csv

here is a sample project file from the video
--> AAA_new_bot_template.xml

Test Data to be inserted to DB is generated live by the script

Funny sidenote:
when the "Insert records" is in the "Main" function, then "execute query" fails with the error of empty VM_ID
when you delete "insert records" everything works
this happens only AFTER "Insert records" is triggered
--> https://streamable.com/k77vu6

[GhostZ]

@tecnewb, Checked I do not have this error

But I saw this in the settings of your project, perhaps these settings will cause a conflict, try disabling all modules and Node.js itself

[tecnewb]

@GhostZ okay i disabled the modules and nodejs, but it is still the same
but when it is not there for you, do you have any additional runtimes/libraries installed on your PC?
On my VM i only installed Visual C++ redist 2019 x64

did you use mysql or sqlite?
maybe sequelize mysql implementation is just broken

[GhostZ]

@tecnewb, Sqlite, but it doesn't matter which dialect is used, because the module itself works with all dialects in the same way, and if there was an error in the module, it would be with any dialect. If the error is repeated with Node.js 8.6.0 disabled, then most likely the error is really in the sequelize implementation. But I previously checked all actions with mysql and there were no errors. Check if you have an error with this project SQLtester.xml.

[tecnewb]

@GhostZ

[424058906] [22:28:30] Thread #1 : Executing (default): SELECT 1+1 AS result
[424058906] [22:28:30] Thread #1 : Executing (default): SELECT 2+3 AS result
[424058906] [22:28:30] Thread #1 : Result: [{"result":5}]
[424058906] [22:28:30] Thread #1 : Executing (default): SELECT 1+1 AS result
[989962290] [22:28:31] Thread #1 : Query | Function completed successfully
[66240405] [22:28:31] Thread #1 : Executing (default): DROP TABLE IF EXISTS CountRecords
[953171962] [22:28:31] Thread #1 : CountRecords | An error occured (66240405): SequelizeDatabaseError: DROP command denied to user 'bot'@'10.0.0.143' for table 'CountRecords'
[66240405] [22:28:32] Thread #1 : Executing (default): DROP TABLE IF EXISTS InsertRecords_SelectRecords
[953171962] [22:28:32] Thread #1 : InsertRecords_SelectRecords | An error occured (66240405): SequelizeDatabaseError: DROP command denied to user 'bot'@'10.0.0.143' for table 'InsertRecords_SelectRecords'
[66240405] [22:28:33] Thread #1 : Executing (default): DROP TABLE IF EXISTS DeleteRecords
[953171962] [22:28:33] Thread #1 : DeleteRecords | An error occured (66240405): SequelizeDatabaseError: DROP command denied to user 'bot'@'10.0.0.143' for table 'DeleteRecords'
[66240405] [22:28:34] Thread #1 : Executing (default): DROP TABLE IF EXISTS UpdateRecords
[953171962] [22:28:34] Thread #1 : UpdateRecords | An error occured (66240405): SequelizeDatabaseError: DROP command denied to user 'bot'@'10.0.0.143' for table 'UpdateRecords'
[989962290] [22:28:35] Thread #1 : Close | Function completed successfully

Messages:
       1 - SequelizeDatabaseError: DROP command denied to user 'bot'@'10.0.0.143' for table 'InsertRecords_SelectRecords'
       1 - SequelizeDatabaseError: DROP command denied to user 'bot'@'10.0.0.143' for table 'DeleteRecords'
       1 - SequelizeDatabaseError: DROP command denied to user 'bot'@'10.0.0.143' for table 'UpdateRecords'
       1 - SequelizeDatabaseError: DROP command denied to user 'bot'@'10.0.0.143' for table 'CountRecords'