NSIS SQL connection

Question

While installing with my NSIS installer script I need to run some script on SQL Server: select, update, create and insert.

How can I do that without having a SQL Server engine on the computer running the NSIS installer?

I thought about packing a SQL Server Compact Edition to my installer in order to use it to connect to the SQL Server. Is that the way I should go for?

Answer 1

You don't need the SQL Server engine to perform queries on a remote machine, you need a driver.

One approach is to use the command line client, which also requires the native driver. You probably want to bundle or search for the driver your application uses in your installer.

In SQL Server 2005 or later the command line client is called sqlcmd. It can be downloaded from the feature pack download pages (2005|2008|2008R2|2012).

So its simply a matter of bundling a SQL script with the installer and executing the script by calling sqlcmd with ExecWait.

You can run a script using a trusted connection via:

sqlcmd -S _SERVER\_INSTANCE_ -d _DBNAME_ -i _SCRIPT_FILE_

Or with a SQL Login:

sqlcmd -S _SERVER\_INSTANCE_ -d _DBNAME_ -U _USERNAME_ -P _PASSWORD_ -i _SCRIPT_FILE_

A SQL 2000 version of this approach can be found here on the nsis wiki.

Answer 2

I found a Plugin for NSIS that can connect to a MS SQL database: MSSQL_OLEDB_plug-in

It can be used like that:

${OLEDB}::SQL_Logon    "$SQLSERVER" "$SQLUSER" "$SQLPASSWORD"
${OLEDB}::SQL_Execute  "$SQLQUERY"
${OLEDB}::SQL_GetError
${OLEDB}::SQL_GetRow
Pop $0
DetailPrint $0
Pop $0
DetailPrint $0
${OLEDB}::SQL_Logout