On The Periphery

Many people have requested the ability to run an SQL file, which holds multiple commands.  As the developer states, this is not really possible as the plugin cannot really deal with multiple result sets and errors.

This would be extremely useful, especially when doing a fresh installation of an application.  All of my application installations install the databases from script, for a number of reasons:

• Easier to do remote DB setup
• Easier to keep code under version control

The MSSQL_OLEDB plugin makes the above quite easy for the SQL Server scenarios. However, for my larger applications, I have hundreds of stored procedures and I do not really want to put each into a separate sql file.

My solution – get NSIS to open the file and retrieve each separate command, which is then run by MSSQL_OLEDB

Below are two macros for achieving this:

   1:  !macro EXECUTE_SCRIPT SQLFILE

   2:      ClearErrors

   3:      FileOpen $R0 "${SQLFILE}" r

   4:      IfErrors noFile

   5:   

   6:      StrCpy $R2 ""

   7:      loop:

   8:          FileRead $R0 $R1

   9:          IfErrors done

  10:          ${IF} $R1 != "GO$\r$\n"

  11:              StrCpy $R2 "$R2 $R1"

  12:          ${ELSE}

  13:              !insertmacro RUN_SQL "$R2"

  14:              Pop $R3

  15:              ${IF} $R3 != 0

  16:                  Push $R3

  17:                  Goto done

  18:              ${ENDIF}

  19:              StrCpy $R2 ""

  20:          ${ENDIF}

  21:      Goto loop

  22:      

  23:      Push 0

  24:      Goto done

  25:   

  26:  noFile:

  27:      DetailPrint "Unable to open SQL File - ${SQLFILE}"

  28:      MessageBox MB_OK "Unable to open SQL File - ${SQLFILE}"

  29:      Push 1

  30:  done:

  31:      FileClose $R0

  32:  !macroend

  33:   

  34:   

  35:  !macro RUN_SQL SQL

  36:      MSSQL_OLEDB::SQL_Execute "${SQL}"

  37:      Pop $R1

  38:      Pop $R2

  39:      ${If} $R1 != 0

  40:          DetailPrint "$R2"

  41:          MSSQL_OLEDB::SQL_GetError

  42:          Pop $R2

  43:          Pop $R3

  44:          DetailPrint $R2

  45:          MessageBox MB_OK "Unable to run script - ${SQL}! $\n $R2"

  46:      ${EndIf}

  47:      Push $R1

  48:  !macroend

EXECUTE_SCRIPT SQLFILE

Opens the SQL file and reads it line by line. 

The lines are concatenated together, to create a single command script.

When it finds a “GO”, it will try to run the script and then clear the script variable (preparing for the next command).  In this context ‘GO’ is used as the separator between commands (which is not  technically correct) and will not be run.   Thus, each command must be followed by “GO” (on its own line).

RUN_SQL SQL

Uses MSSQL_OLEDB to run the SQL.