Execute SQL script with param
i'm new in nsis and i want to execute sql script with some parameter.
Archive: Execute SQL script with param
Execute SQL script with param
i'm new in nsis and i want to execute sql script with some parameter.
Here's an example of running some SQL Files:
OutFile TestSQL.exe
;Backgound Colors
BGGradient 800080 000000 FFFFFF
BrandingText " "
;Title Of Your Application
Name "SQLRelease"
;Included file(s)
!include servicelib.nsh
!include filefunc.nsh
!include Wordfunc.nsh
!include RecFind.nsh
;Macros
!insertmacro WordFind
;Output File Name
OutFile SQLRelease.exe
;The Default Installation Directory
InstallDir "D:\ReleaseSQL"
;The text to prompt the user to enter a directory
DirText "Please select the folder below"
Section "SQLRelease"
Delete $INSTDIR\install.log ;Delete the leftover install log if it's there
LogSet On
;Install Files
SetOutPath $INSTDIR
SetCompress Auto
SetOverwrite on
;Put the files where we can walk through and run them
File /r ".\SQLStuff\*.*"
;Logon to sql
LogText "Loging on to SQL server"
MSSQL_OLEDB::SQL_Logon /NOUNLOAD "machineName" "" ""
pop $0
IntCmp $0 '0' SQLUp
pop $0 ;Put error text into $R0
Call ReportError ;Report it to the user and abort processing
SQLUp:
;Set database we're going to run against
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "use MySQLDBName"
pop $0
IntCmp $0 '0' Connected
pop $0 ;Put error text into $R0
Call ReportError ;Report it to the user and abort processing
Connected:
;OK, now start running the scripts that are in each folder
Strcpy $2 "$INSTDIR\Tables"
Call RunSQLFiles
Strcpy $2 "$INSTDIR\Stored Procedures"
Call RunSQLFiles
Strcpy $2 "$INSTDIR\Views"
Call RunSQLFiles
;We're done, end connection with DB
MSSQL_OLEDB::SQL_Logout
LogSet Off
SectionEnd
Function ReportError
;***********************************************
;* *
;* Function to report errors encountered *
;* *
;* *
;* Inputs: *
;* $0 - Error *
;* *
;* Returns: *
;* This function aborts *
;***********************************************
LogText "Release Produced the Following Error: $0"
MSSQL_OLEDB::SQL_Logout ;If we're connected to the DB, disconnect
Abort "Threw Error, see Error Log"
FunctionEnd
Function RunSQLFiles
;***********************************************
;* *
;* Function to run sql files against db *
;* *
;* *
;* Assumed: *
;* It is assumed that database is *
;* connected. *
;* Inputs: *
;* $2 - Directory where files are *
;* *
;* Returns: *
;* None *
;***********************************************
;Run all SQL Files in passed directory
${RecFindOpen} $2 $R0 $R1
${RecFindFirst} ;Find first item in directory
${WordFind} $R1 ".sql" "*" $R2 ;Is it a SQL file?
strcmp $R1 $R2 continue ;If WOrdFind returns the string passed, it didn't find any matches, so check next file
LogText "Running Script: '$2$R0\$R1'" ;Log that we're running this
MSSQL_OLEDB::SQL_ExecuteScript /NOUNLOAD "$2$R0\$R1" ;Run this script against file found ($R0 is "\directory" if it's recursed)
pop $0 ;Put return code into $0 to check for errors
IntCmp $0 '0' continue ;Did this guy throw an error, if not, go get next file to run
MSSQL_OLEDB::SQL_GetError /NOUNLOAD ;Get the error from SQL
pop $0 ;Error code from calling geterror
pop $0 ;Error text
Call ReportError ;Report it to the user and abort processing
continue:
${RecFindNext} ;Get next file (auto loops back to after RecFindFirst)
${RecFindClose}
FunctionEnd