Insert statement with database update tool

I am working with joget community and MySQL Version 8.0.31.

I want to enable that users without admin rights can enter new users via a form. For this I have created a form and store the data in the database table "app_fd_neueUser".

So that the users are created in the official table "dir_user" and get the necessary rights, ReportTo and groups (other dir tables), I used the Database Update Tool and saved the following query:

 INSERT INTO dir_user (id,username,password,firstName,lastName,email,active,timeZone,locale) VALUES (select c_fd_username,c_fd_username,c_fd_password,c_fd_firstName,c_fd_lastName,c_fd_email,c_fd_active,'1','NULL' from app_fd_newUser order by datecreated desc limit 1);
 INSERT INTO dir_employment (id,userId,startDate,endDate,employeeCode,role,gradeId,departmentId,organizationId) VALUES (select c_fd_username,'NULL','NULL','',c_fd_organization from app_fd_newUser order by datecreated desc limit 1);
 INSERT INTO dir_employment_report_to (employmentId,reportToId,id) VALUES ((select e.id from dir_employment e join app_fd_neueUser u on e.userId = u.c_fd_username limit 1)(select e.id from dir_employment e join app_fd_neueUser u on e.userId = u.c_fd_reportTo limit 1);
 INSERT INTO dir_user_role (roleId,userId) VALUES (select c_fd_userRole,c_fd_username from app_fd_neueUser order by datecreated desc limit 1);
 INSERT INTO dir_user_group (groupId, userId) VALUES (select c_fd_group,c_fd_username from app_fd_neueUser order by datecreated desc limit 1);

After submitting the user creation form, I receive the following error message:

ERROR 18 Nov 2022 16:23:39 org.joget.apps.app.lib.DatabaseUpdateTool - Error executing plugin
2989
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select c_fd_username,c_fd_username,c_fd_passwort,c_fd_vorname,c_fd_nachname,c_fd' at line 1

If I execute the query directly in the database, I need to do this in two steps as follows for this to work:

How can I make the queries now in the database update tool in one step so that it works? The queries should be basically correct, I have tested them directly on the database.

Insert statement with database update tool

3 answers