Execute a SQL file

Hi,

In my setup, In install Apache and MySQL.
Is it possible to execute a .sql file to create my database and all its content directly in my NSIS setup?

Thanks & Best regards

Recenlty I've got similar problem with Oracle DB.
I finally did it in line that looks

 nsExec::Exec '$OracleExe\$SqlCli $User/$Passwd@$Db @$script '

where
nsExec::Exec - function from nsExec plugin that execute program and wait till it finish
$OracleExe - directory of Oracle executables
$SqlCli - name of SQL client that will run sql script
$User/$Passwd@$Db - name, passwd and connect string to make a valid connection to database
$Script - full path to the string

This line start the client in silient mode without any window on the screen.

If you want to do this with MySQL check options for MySQL client because they will be probably different ones.


Good luck 🙂
Kobus

For mySQL I use a line like this:
nsExec::ExecToLog 'cmd /C $mySQLEXE -u$dbUser -h$dbHost -P$dbPort < "$INSTDIR\tmp\dbinit\create_user.sql"'

$mySQLEXE is usually C:\mysql\bin\mysql.exe, but I detect the correct path earlier in the scripts. The rest should be self explanitory.

Thank you very much.
That's exactly what I was looking for.

How do you do when you have a long path to mysql?
I tried to put the mysql path between double quotes but the command doesn't work in this case.

Did you put it between single and double quotes?

'"file"'

I tried to put the path between double quotes.
The whole command line is between single quotes

In fact, the command:

nsExec::ExecToLog 'cmd /C c:\mysql\mysql.exe -uroot -hlocalhost -P3306 < "$INSTDIR\install.sql"'

works fine but the command:

nsExec::ExecToLog 'cmd /C "c:\mysql\mysql.exe" -uroot -hlocalhost -P3306 < "$INSTDIR\install.sql"'

doesn't work (I get nothing)

not to be picky about it, but I want to be sure you have it right, mysql.exe lives in the bin directory, so do you mean that the path to your mysql.exe file is C:\mysql\bin\mysql.exe or is it C:\mysql\mysql.exe?

I'm trying what you have here, I've always put the path in a variable like mySQLEXE

StrCpy $mySQLEXE "C:\mysql\bin\mysql.exe"

and then used that with quotes around it like this:
nsExec::ExecToStack 'cmd /C "$mySQLEXE" -u$dbUser -p$dbPassword -h$dbHost -P$dbPort < "$INSTDIR\install.sql"'

So I tried it like this and it doesn't have a problem working (the path is the longest I could come up with on short notice):

nsExec::ExecToStack 'cmd /C "C:\Documents and Settings\temp1\Local Settings\Temporary Internet Files\Content.IE5\Q5IVCDQ1\mysql.exe" -uroot -prootpass -hlocalhost -P3306 < "$INSTDIR\install.sql"'

A short version is:

nsExec::ExecToStack 'cmd /C "C:\mysql\bin\mysql.exe" -uroot -prootpass -hlocalhost -P3306 < "$INSTDIR\install.sql"'

As Joost pointed out to me in a different post, cmd will only work on an NT system (NT, 2000, XP, 2003) not on 9x.

For Win9x use command instead of cmd.

is it necessary to use the command 'command' or 'cmd'?

Cannot I type directly the instruction:
nsExec::ExecToStack '"C:\mysql\bin\mysql.exe" -uroot -prootpass -hlocalhost -P3306 < "$INSTDIR\install.sql"'

You are using Windows console features so you have to use command or cmd.

And isn't it better to always use 'command' instead of 'cmd'?

Are there cases where 'command' cannot be used or where 'cmd' doesn't work the same way than 'command'?

As Joost pointed out, cmd isn't a recognized program on Windows 9x. I use it in my installers because our software is only supported on Windows 2000, XP, and 2003. If you need compatibility with Windows 9x you should use command.

command on Windows NT is not the same as cmd.

command is the 16-bit MS-DOS version that will be emulated. It does not support long files names.

cmd is a 32-bit console with long file name support.

I am using this code that you guys mentioned earlier for the sql files that I need executed (9 of them)

nsExec::ExecToLog 'cmd /C $mySQLEXE -u$user -h$host -P$port < "$INSTDIR\tmp\db\"

I have the user enter these mysql values in a custom page I created. (Value - user variable)

• Username - $user
  Password - $pass
  SQL Host - $host
  Port MySQL is running on - $port
  SQL Database - $base

I am very new at mysql, and I was wondering if in the code, how I should deal with the database password, and what actual schema to put it in.

For this would -b (schema to put it in) -p (for password), would these work. Should I just add them into the code?

Thanks in advance everybody! 🙂

Arfy

*bump*

Hi,

Have you guys encountered hang on your installer when trying to execute below script?

nsExec::ExecToLog 'cmd /C $MySQLEXE -u $dbUser -p $dbPwd -h $dbHost -P $dbPort < $MySQLScript'


where:
MySQLScript -> contains the sql file (Check the syntax alread, which is correct 🙂)

usrname, pwd, host, port are correct also.


Please help.


thanks

Hi mutantbc!

nsExec::ExecToLog 'cmd /C $MySQLEXE -u $dbUser -p$dbPwd -h $dbHost -P $dbPort < $MySQLScript'

I think the installer 'hangs' because mysql.exe is waiting for the password. Remove the space between -p and $dbPwd and try again.

Have a nice day!

Cheers

Bruno

Edit: Erm, there's something else to consider: cmd is Windows NT and higher only. The %COMSPEC% environment variable contains the correct value. (Forget this if you are sure, that your installer is not getting in touch with Windows 9x versions.)

after installing MySQL, i try to execute MySQLInstanceConfig.exe...this will configure the username/pwd and install mysql as service. After the configuration, I try to connect to mysql server and execute the sql script...if I remove the -p $dbPwd, an error will occured "Access denied..."

How can i proceed? it seems mysql will ask a password if -p is supplied.

Hi!

Of course, you have to provide a password! But not in the format
-p MyPassWOrD

You have to use it like this:
-pMyPassWOrD

(without space between -p and password)

Cheers

Bruno

I think this can help you,
It works perfectly, 🙂

   GetTempFileName $0

   StrCpy $0 "temp.bat"

   FileOpen $1 $0 "w"

   FileWrite $1 "@echo off$\r$\n"

   StrCpy $2 $TEMP 2     ##C:

   FileWrite $1 "$2$\r$\n"

   FileWrite $1 "mysql.exe database --user=root --password=$passroot < c:\test.sql$\r$\n"

   FileClose $1

   nsExec::exec "$0"

However, I think you should check Environment Variable in Computer property. In Path,you must add "C:\MySQL\MySQL server..\bin"
It also can recognize mysql.exe when you run it on command line.
🙂

the parameter without space works...strange but i works without prompting a password. 🙂

-pwithoutspacepassword


thanks

Hi mutantbc!

Actually you'll find this in the manual:



--password[=password], -p[password]

Cheers

Bruno

you are right, maybe I didnt read the manual thourougly