Issue with MySQL script not executing - always returns error
Posted: Thu Aug 16, 2018 10:01 am
I am trying to run a simple MySQL script as part of the install process, to register the user in a table, on my local machine where the MySQL server is installed and running. Eventually, this will be a remote connection, but for test purposes it is all on the local machine.
My test script looks like this:
insert into smartcelluser.smartcell_user_registration(registration_date, user_name,
user_company, user_email, requires_updates)
values (NOW(), "A User Name", "A User Company", "a.user@someaddress.com", FALSE)
Settings for the My SQL Server dialog are:
Server: localhost
User: SmartCellUser1
Port: 3306
Password: freddy01
Abort execution on script error is true and the return result is the (predefined) variable SQLRESULT.
The SQL script is called thus:
My SQL Script : Connect to server localhost at port 3306 as user SmartCellUser1 (get result into variable SQLRESULT)
The SQLRESULT value is set to ERROR after the script runs, no data gets written to the table.
I have tested the script at the command line as follows:
mysql -uSmartCellUser1-pfreddy01 -P3306 < "add_user.sql"
where add_user.sql is exactly as given above. Running the command line command works and the data is inserted as expected. This I believe confirms that the db settings and permissions for this user are valid, and should therefore just work when running the MySQL script from within IA. I'm using version 18.
In due course, once I can get this to work (it should be simple right, it's not "Rocket Science" is it?), the next step will be to supplant the fixed data fields with variables from the IA script that the user captures during registration. This I presume will be something like $USERNAME$, $USERCOMPANY$....etc.
So what else do I need to add to the script to make it work? Apparently no client files are required since it just uses TCP/IP, and clearly the server is locatable otherwise the command line script wouldn't work, right?
My test script looks like this:
insert into smartcelluser.smartcell_user_registration(registration_date, user_name,
user_company, user_email, requires_updates)
values (NOW(), "A User Name", "A User Company", "a.user@someaddress.com", FALSE)
Settings for the My SQL Server dialog are:
Server: localhost
User: SmartCellUser1
Port: 3306
Password: freddy01
Abort execution on script error is true and the return result is the (predefined) variable SQLRESULT.
The SQL script is called thus:
My SQL Script : Connect to server localhost at port 3306 as user SmartCellUser1 (get result into variable SQLRESULT)
The SQLRESULT value is set to ERROR after the script runs, no data gets written to the table.
I have tested the script at the command line as follows:
mysql -uSmartCellUser1-pfreddy01 -P3306 < "add_user.sql"
where add_user.sql is exactly as given above. Running the command line command works and the data is inserted as expected. This I believe confirms that the db settings and permissions for this user are valid, and should therefore just work when running the MySQL script from within IA. I'm using version 18.
In due course, once I can get this to work (it should be simple right, it's not "Rocket Science" is it?), the next step will be to supplant the fixed data fields with variables from the IA script that the user captures during registration. This I presume will be something like $USERNAME$, $USERCOMPANY$....etc.
So what else do I need to add to the script to make it work? Apparently no client files are required since it just uses TCP/IP, and clearly the server is locatable otherwise the command line script wouldn't work, right?