Issue with MySQL script not executing - always returns error

[colinwpa]

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?

[colinwpa]

So, under advisement from one of the admins here, I changed my IA script to use

Run Program MySQL -uSmartCellUser1 -pfreddy01 -P3306 < "add_user.sql" where that script is as above.

This worked just fine, does the job.... but more importantly proves that my parameters are correct and the settings in my db are correct for this user to have insert rights on the table.

However, the big downside is I need to deploy the MySQL.exe executable to the target machine so that it is there to be able to run my script. This might not be such an issue, if MySQL.exe is freely deployable....bearing in mind the application I am installing is a commercial application, that may very well break the GNU licence conditions of the "free" MySQL database application.

So my preference will be to be able to run my insert script without needing any 3rd party file deployed to the target, as is what IA18 purports to be able to do....

Has anyone present on this forum been able to get IA18 to do what it says, or is there a bug here that needs to be attended to?

[colinwpa]

Ok, this is not going well for me..... Now tried to use the IA variables in the sql script and it comes as little surprise to me that when the "run program" command executes the variables in the add-user script are NOT replaced with the actual values, so of course the sql command fails. I suspect that what I want to do, as simple as it may seem to be, just isn't going to do what I want.... So I'm open to suggestions including abandoning IA and reverting to any other tool that might do what I need.

[FrancescoT]

Let me check it and let me see if I may release a sample.

[FrancescoT]

Dear Colin,

I checked what you reported and for now, I can only confirm the issue. Unfortunately, the problem dependents on version 8 of My Sql version and currently, this is not supported by “My SQL Server” command.

It'll will be available an update, but at the moment, I cannot anticipate its actual release times. However, I don't think there will be an update for InstallAware 18.

Due of this, currently the only possible approach is with calling “MySQL.exe” as you did previously. Its use should not broke any license term and this because, you are not deploying the tool. For what I see, the tool is part of any “MySQL” installation and this independently of the “MySQL” edition.

Concerning IA script variables replacement, from your script you may use the following approach:

- load the MySql script into an IA variable (“Read from Text File” with #READALL\ prefix to read in the entire file).

- Use “Replace String” command for IA script variables replacement (you may need to repeat this for each variable substitution).

- Use the “Write to Text File” to update the content of the MySql script file with.

- Finally, execute the “MySQL.exe” tool to apply your MySql script.

--
Set Variable MY_SQL_SCRIPT to
Read from Text File #READALL\$SUPPORTDIR$\myscript.sql into Variable MY_SQL_SCRIPT
Replace $MY_VARIABLE$ with NEWVALUE in variable MY_SQL_SCRIPT
Set Variable NATIVE_ENGINE to TRUE
Delete Files $SUPPORTDIR$\myscript.sql (when installing)
Set Variable NATIVE_ENGINE to FALSE
Write into Text File $SUPPORTDIR$\myscript.sql from Value $MY_SQL_SCRIPT$ (at start of file)
Run Program Path to MySQL.exe -uSmartCellUser1 -pfreddy01 -P3306 < "$SUPPORTDIR$\myscript.sql" (WAIT)
--

Code: Select all

~InstallAware Clipboard Data~
~Run Program~
~{69C92558-2CD1-4003-A39D-1DF8D2B61C08}~
~Path to MySQL.exe~
~TRUE~
~-uSmartCellUser1 -pfreddy01 -P3306 < "$SUPPORTDIR$\myscript.sql"~
~TRUE~
~~
~Write to Text File~
~{53A3B065-762E-4AE5-BD70-0A69E21898F4}~
~$SUPPORTDIR$\myscript.sql~
~$MY_SQL_SCRIPT$~
~TRUE~
~FALSE~
~Set Variable~
~{4F56500E-CBC3-4C22-B6A8-B0DB008E1E7F}~
~NATIVE_ENGINE$MYAH$MYAH$FALSE~
~FALSE~
~Delete Files~
~{2B62EB9C-2CED-4111-A4AC-948F7AC1B94F}~
~$SUPPORTDIR$~
~myscript.sql~
~TRUE~
~FALSE~
~Set Variable~
~{D19D2235-E7D4-429F-BE97-43AC2898991B}~
~NATIVE_ENGINE$MYAH$MYAH$FALSE~
~TRUE~
~Replace String~
~{4308A84E-50FB-49BF-A4BA-E64722249103}~
~MY_SQL_SCRIPT~
~$MY_VARIABLE$~
~NEWVALUE~
~TRUE~
~TRUE~
~Read from Text File~
~{C9ACF905-3A78-466D-8F70-70187637751C}~
~#READALL\$SUPPORTDIR$\myscript.sql~
~MY_SQL_SCRIPT~
~~
~Set Variable~
~{CD3A1253-573D-4669-8A73-FA481E25B59E}~
~MY_SQL_SCRIPT$MYAH$MYAH$FALSE~
~~


*
You can add any file(s) to support files location. InstallAware uses support files at several places throughout the installation. Support files are not actually installed onto the target system, but they are part of the installation package. They are available temporarily while the installation is executing and are cleaned up when the installer finishes execution.

With the link below, it's available a full function code snippet that easily describes how to add a package to SUPPORTDIR and then how to run it at setup runtime. This sample code executes a VBS file ... but this can be any file.

https://www.installaware.com/forums/viewtopic.php?f=2&t=9962

and this one is another sample:

https://www.installaware.com/forums/viewtopic.php?f=2&t=10676

To find out how to use the SUPPORTDIR, please search for "Modifying Support Files" in the IA documentation (just press F1 in IA IDE).

Hope this helps you.

[colinwpa]

Thank you Francesco for your help here. So it seems I have to deploy the MySQL.exe application (and it's support dlls, probably need to do a full install to be sure that everything needed is there....) to get this to work.

I did use a similar approach with the setup of the sql script with the variables from the IA:

Write into Text File $TARGETDIR$\add_user.sql from Value "$USERNAME$", "$USERCOMPANY$", "$EMAILADDRESS$", $REQUIRESUPDATES$) (at end of file)

where add_user.sql initially is deployed with the text:

insert into smartcelluser.smartcell_user_registration(registration_date, user_name, user_company, user_email, requires_updates) values (NOW(),

This works on my dev machine where I have MySQL installed, so perhaps all I really need to do is deploy the MySQL Server 5.5 application runtimes and that should, I hope, get the MySQL.exe installed and usable....

[colinwpa]

Have tried what you suggested but the problem is to deploy just the relevant portions of MySQL so that it can work from within the SUPPORTDIR directory. These seems to the no easy way for the relevant files to be deployed..... MySQL doesn't provide a simple installer and it seems to be a dark secret as to what files MySQL.exe will call upon to run.

But in any case, the script does not run when called from within IA using the RUN PROGRAM command (and one can't see what the error might be as even though the "Hide Window" is NOT checked the output from running the script is just lost (even tried piping it to a file, but that didn't work either) YET, the command line run outside of IA executes correctly and the data is added to the table.

So, can you recommend which version of IA might support this so that we could try to upgrade to, so that we can get beyond the impasse?

Surely this shouldn't be so hard - all we are trying to do is register the user that installs our software, something one should reasonably expect an installation application to be able to do.....

[FrancescoT]

If worked with invoking the tool manually from command line, it must work from IA via “Run Program” as well. Due of this, I believe there is something wrong with the Run Program's parameters.

Most probably, this may be caused by the MySql path. For example, depending on the target OS architecture (x86/x64) or on the version of the installed MySql package, this could be “C:\Program Files” or “C:\Program Files (x86)”.

Consider also that by default, the setup engine runs under 32 bit mode (if not forced to 64 bit explicitly via “Set 64 Bit mode”). Consequently, any script command used to obtain a “know Folder”, it will return the respective x86 system path (this due OS redirection).

At any rate, it will be my concern to contact you once the fix is available. As I said previously, currently I cannot anticipate a date of release. Then successively, you can decide if to upgrade.

Hope this helps you.