Many applications require a database backend. If you wish to install one of these applications using NSIS, you will need a way to run a .SQL file as part of the install. This article will explain how to configure your NSIS script to run a .SQL file against a SQL Server and Database.
Following the steps in this article, you will add a page to your installer that looks like this:
First, before you start modifying your script, you will need to place the .sql file in the NSIS Project folder. I usually name it Install.sql. Then go to your SQL Server, find OSQL.EXE and osql.rll, and copy them into your NSIS Project folder.
Second, you will need to add the following two includes at the beginning of your script. Try to put them next to your other includes if you have any to keep them together.
!include nsDialogs.nsh
!include LogicLib.nsh
Third, add the following page definition between your Welcome and Install Files pages (or between the pages that make the most sense for your installer).
!insertmacro MUI_PAGE_WELCOME
Page custom SqlPage SqlPageLeave
!insertmacro MUI_PAGE_INSTFILES
The syntax for the Page command
Fourth, you will need to declare the following variables (towards the beginning, outside any function).
;SQLAuth Dialog Variables
Var Dialog
Var lblLabel
Var lblServer
Var lblDatabase
Var lblUsername
Var lblPassword
Var txtServer
Var txtDatabase
Var txtUsername
Var pwdPassword
Fifth, inside the .onInit function, place the following commands.
;Initialize the plugins directory
InitPluginsDir
SetOutPath "$PLUGINSDIR"
File "Install.sql"
File "OSQL.EXE"
File "osql.rll"
Sixth, you can add your SqlPage and SqlPageLeave Functions. Paste the following into your script. I will explain what all the commands do later so that you can customize it to fit your needs.
Function SqlPage
nsDialogs::Create /NOUNLOAD 1018
Pop $Dialog
${If} $Dialog == error
Abort
${EndIf}
${NSD_CreateLabel} 0 0 100% 24u "Please specify the Server, Database, Username, and Password for your SQL Database Authentication."
Pop $lblLabel
${NSD_CreateLabel} 0 24u 36u 12u "Server"
Pop $lblServer
${NSD_CreateLabel} 0 36u 36u 12u "Database"
Pop $lblDatabase
${NSD_CreateLabel} 0 48u 36u 12u "Username"
Pop $lblUsername
${NSD_CreateLabel} 0 60u 36u 12u "Password"
Pop $lblPassword
${NSD_CreateText} 36u 24u 100% 12u ""
Pop $txtServer
${NSD_CreateText} 36u 36u 100% 12u ""
Pop $txtDatabase
${NSD_CreateText} 36u 48u 100% 12u ""
Pop $txtUsername
${NSD_CreatePassword} 36u 60u 100% 12u ""
Pop $pwdPassword
nsDialogs::Show
FunctionEnd
Function SqlPageLeave
${NSD_GetText} $txtServer $0
${NSD_GetText} $txtDatabase $1
${NSD_GetText} $txtUsername $2
${NSD_GetText} $pwdPassword $3
;Run the eXpress SQL update
nsExec::Exec '"$PLUGINSDIR\OSQL.EXE" -U $2 -P $3 -S $0 -d $1 -i "Install.sql"'
Pop $0 ;return value
IntCmp $0 0 SqlDone
GetDlgItem $0 $HWNDPARENT 1 ; Next button
SendMessage $0 ${WM_SETTEXT} 0 "STR:Retry"
MessageBox MB_OK "SQL Authentication failed, please try again."
Abort ;Do not leave the page until SQL completes successfully
SqlDone:
FunctionEnd
I will now explain all of the commands in the function.
nsDialogs::Create /NOUNLOAD 1018
Pop $Dialog
${If} $Dialog == error
Abort
${EndIf}
nsDialogs::Create /NOUNLOAD 1018
Pop $Dialog
This command creates a new dialog, if you have multiple custom dialogs in your installer, you will want to change the $Dialog variable to something unique, otherwise you won't touch this.
${NSD_CreateLabel} 0 0 100% 24u "Label text."
Pop $lblVariable
This will create a label that will appear in the coordinates specified as the 1st through 4th parameters. The label text is specified as the 5th parameter. $lblVariable is the variable name you will use to reference the label. The variable needs to be unique for each label.
${NSD_CreateText} 36u 24u 100% 12u ""
Pop $txtVariable
This will create a text box for user input. Just like the CreateLabel command, the 1st through 4th parameters are the coordinates. You can optionally supply a default value by entering some text as the 5th parameter. Just like CreateLabel, the varuable needs to be unique for each text box.
${NSD_CreatePassword} 36u 60u 100% 12u ""
Pop $pwdPassword
CreatePassword is identical to CreateText except the text box is masked so whatever is typed in it is not visible.
nsDialogs::Show
This shows the dialog you just created.
${NSD_GetText} $txtServer $0
This gets the text that the user entered for the text box represented by the first variable specified ($txtServer). It stores it in the second variable specified ($0) so that you can use it in your script.
nsExec::Exec '"$PLUGINSDIR\OSQL.EXE" -U $2 -P $3 -S $0 -d $1 -i "Install.sql"'
This runs OSQL with the server name, database name, username, and password specified. It will connect to SQL using the specified credentials and run Install.sql.
Pop $0 ;return value
IntCmp $0 0 SqlDone
This gets the exit code that was returned from OSQL.EXE. The next line will check to see if it is 0 (success). If it is, it will skip to "SqlDone:" otherwise it will execute the following commands:
GetDlgItem $0 $HWNDPARENT 1 ; Next button
SendMessage $0 ${WM_SETTEXT} 0 "STR:Retry"
MessageBox MB_OK "SQL Authentication failed, please try again."
Abort ;Do not leave the page until SQL completes successfully
GetDlgItem gets a handle to the Next button. SendMessage renames the Next button to "Retry". MessageBox displays a warning message that the SQL Authentication failed. Abort tells the installer to stay at this page instead of moving on to the next one.
If you want to have a SQL Command run during the uninstaller, place an Uninstall.sql file in the NSIS Project folder, add the following page definition (just after the custom page definition for your installer):
UninstPage custom un.SqlPage un.SqlPageLeave
And then add the following functions:
Function un.SqlPage
nsDialogs::Create /NOUNLOAD 1018
Pop $Dialog
${If} $Dialog == error
Abort
${EndIf}
${NSD_CreateLabel} 0 0 100% 24u " Please specify the Server, Database, Username, and Password for your SQL Database Authentication."
Pop $lblLabel
${NSD_CreateLabel} 0 24u 36u 12u "Server"
Pop $lblServer
${NSD_CreateLabel} 0 36u 36u 12u "Database"
Pop $lblDatabase
${NSD_CreateLabel} 0 48u 36u 12u "Username"
Pop $lblUsername
${NSD_CreateLabel} 0 60u 36u 12u "Password"
Pop $lblPassword
${NSD_CreateText} 36u 24u 100% 12u ""
Pop $txtServer
${NSD_CreateText} 36u 36u 100% 12u ""
Pop $txtDatabase
${NSD_CreateText} 36u 48u 100% 12u ""
Pop $txtUsername
${NSD_CreatePassword} 36u 60u 100% 12u ""
Pop $pwdPassword
nsDialogs::Show
FunctionEnd
Function un.SqlPageLeave
${NSD_GetText} $txtServer $0
${NSD_GetText} $txtDatabase $1
${NSD_GetText} $txtUsername $2
${NSD_GetText} $pwdPassword $3
;Run the eXpress SQL update
nsExec::Exec '"$PLUGINSDIR\OSQL.EXE" -U $2 -P $3 -S $0 -d $1 -i "Uninstall.sql"'
Pop $0 ;return value
IntCmp $0 0 SqlDone
GetDlgItem $0 $HWNDPARENT 1 ; Next button
SendMessage $0 ${WM_SETTEXT} 0 "STR:Retry"
MessageBox MB_OK "SQL Authentication failed, please try again."
Abort ;Do not leave the page until SQL completes successfully
SqlDone:
FunctionEnd
In my situation, I do not need to allow for using Trusted Authentication, so all of these samples use SQL Authentication only. If you want to allow for Trusted Authentication, you could add a CheckBox and then if the checkbox is enabled use the following command to run the SQL file:
nsExec::Exec '"$PLUGINSDIR\OSQL.EXE" -E -S $0 -d $1 -i "Install.sql"'
The -E command tells OSQL to use the Windows Credentials of whoever is running the installer to authenticate to SQL.