Inno Setup How to execute SQL scripts

As one of the two most popular free Windows Application installation package authoring tools with NSIs, Inno is relatively low on learning difficulties and is ideal for packaging simple desktop applications. But for more complex installation processes, or Web applications, I personally feel that it is not Inno's strength. Of course, since Inno embedded the Pascal language to extend the functionality, it is theoretically not possible to cope with the complex installation process, but it is more complex to implement.

For example, in the installation process to connect the database and execute SQL scripts such requirements, the use of InstallShield should be much simpler, and Inno does not support the direct operation of the database, and the relevant information is less than the NSIs, so much so that I pale no place, In the end, they found ideas in NSIs's data.

The main idea is that during the installation process, the database client is called to connect to the database and execute the SQL script, then output the execution result or error information to the file, and finally analyze the file to determine the result of the command execution. However, since it is called a specific client, the operation of the different databases will naturally differ, as described below.

The required files are first included in the [Files] section of the packaging script:

[Files]
Source: "d:/development/mydemoapp/code/*"; DestDir: "{app}"; Flags:ignoreversion Recursesubdirs Createallsubdirs

; Osql.exe in the SQL Server2000 installation directory
Source: "D:/development/mydemoapp/osql.exe"; Flags:dontcopy
Source: "D:/development/mydemoapp/mysql.exe"; Flags:dontcopy
Source: "D:/development/mydemoapp/script_mssql.sql"; Flags:dontcopy
Source: "D:/development/mydemoapp/script_mysql.sql"; Flags:dontcopy
Source: "D:/development/mydemoapp/script_oracle.sql"; Flags:dontcopy

Code snippet to execute script in SQL Server:

function Execscriptinmssql (dbhost, dblogin, Dbpass, dbname:string): Boolean;
Var
connectexe:string;
connectparam:string;
Begin
{Extract Temporary files}
Extracttemporaryfile (' Osql.exe ');
Extracttemporaryfile (' Script_mssql.sql ');
{Construct database connection string}
Connectexe: = Expandconstant (' {tmp} ') + '/osql.exe ';
Connectparam: = '-S ' + dbhost
+ '-u ' + dblogin
+ '-P ' + Dbpass
+ '-d ' + DBName
+ '-I script_mssql.sql-o '
+ expandconstant (' {tmp} ') + '/dbstatus.txt ';
{Establish database connection and execute script}
If Exec (Connectexe, Connectparam, ", Sw_hide, ewwaituntilterminated, ResultCode) THEN BEGIN
Result: = ResultCode = 0;
Loadstringfromfile (Expandconstant (' {tmp} ') + '/dbstatus.txt ', statusstring);
If statusstring <> ' then BEGIN
MsgBox (statusstring, Mberror, MB_OK);
Result: = False;
End ELSE begin
Result: = True;
End
End ELSE begin
MsgBox (' Database update failed: ' #10 # # + syserrormessage (resultcode), Mberror, MB_OK);
Result: = False;
End
End

Code snippet for executing script in MySQL:

function Execscriptinmysql (dbhost, dblogin, Dbpass, dbname:string): Boolean;
Var
connectexe:string;
connectparam:string;
Begin
{Extract Temporary files}
Extracttemporaryfile (' Mysql.exe ');
Extracttemporaryfile (' Script_mysql.sql ');
{Construct database connection string}
Connectexe: = expandconstant (' cmd ');
Connectparam: = '/C ' + expandconstant (' {tmp} ') + '/mysql.exe '
+ '-h ' + dbhost
+ '-u ' + dblogin
+ '-P ' + Dbpass
+ '-d ' + DBName
+ '-e ' source ' + expandconstant (' {tmp} ') + '/script_mysql.sql ' "" > ' + expandconstant (' {tmp} ') + '/dbstatus.txt 2&GT;&A Mp;1 ';
{Establish database connection and execute script}
If Exec (Connectexe, Connectparam, ", Sw_hide, ewwaituntilterminated, ResultCode) THEN BEGIN
Result: = ResultCode = 0;
Loadstringfromfile (Expandconstant (' {tmp} ') + '/dbstatus.txt ', statusstring);
If statusstring <> ' then BEGIN
MsgBox (statusstring, Mberror, MB_OK);
Result: = False;
End ELSE begin
Result: = True;
End
End ELSE begin
MsgBox (' Database update failed: ' #10 # # + syserrormessage (resultcode), Mberror, MB_OK);
Result: = False;
End
End

Because Mysql.exe does not have output parameters to the file, it is necessary to use Cmd.exe to run the Mysql.exe in order to redirect its output to the file dbstatus.txt. In addition, at the end of the command, add the parameter 2>&1, the standard error output device is also redirected to the file, otherwise the command execution error message will not be output to the file.

Code snippets for executing scripts in Oracle:
function Execscriptinoracle (Clientpath, DBInstance, dblogin, dbpass:string): Boolean;
Begin
{Extract Temporary files}
Extracttemporaryfile (' Script_oracle.sql ');
{Connect to database and execute script}
If Exec (expandconstant (' cmd '), '/C ' + Clientpath + '-l-s ' + dblogin
+ '/' + Dbpass
+ ' @ ' + dbinstance
+ ' @ ' + expandconstant (' {tmp} ') + '/script_oracle.sql> ' + expandconstant (' {tmp} ') + '/dbstatus.txt 2>&1 ',
‘‘,
Sw_hide, ewwaituntilterminated, ResultCode)
Then BEGIN
Result: = ResultCode = 0;
Loadstringfromfile (Expandconstant (' {tmp} ') + '/dbstatus.txt ', statusstring);
If Pos (' Holytail ', statusstring) <> 0 THEN BEGIN
{If there is a substring of "holytail" in the output message, the script executes successfully}
{Prompt user to open log file if error is performed}
If Pos (' ora-', statusstring) <> 0 THEN BEGIN
{Prompt for user script execution error}
If MsgBox (' Database update error, do you want to open the log file? ', mbconfirmation, mb_yesno) = Idyes THEN BEGIN
{Open log}
If not shellexec ("', Expandconstant (' {tmp} ') + '/dbstatus.txt '," ', ' ", Sw_show, Ewnowait, ErrorCode) THEN BEGIN
MsgBox (' Log File open Error! ', Mberror, MB_OK);
End
End
Result: = False;
{If executed correctly, returns true}
End ELSE begin
Result: = True;
End
End else if statusstring <> ' then BEGIN
MsgBox (statusstring, Mberror, MB_OK);
Result: = False;
End ELSE begin
Result: = True;
End
End ELSE begin
MsgBox (' Database update failed: ' #10 # # + syserrormessage (resultcode), Mberror, MB_OK);
Result: = False;
End
End
Oracle's client is too large to integrate into the installation package and should use a Tinputfilewizardpage user-selected Sqlplus.exe installation location. Also, since Sqlplus.exe does not have the output parameters to the file, you must use Cmd.exe to run it and redirect the output to the file. In addition Since Sqlplus.exe executes the script regardless of success or failure, it will output information, it is not as simple as using SQLCMD.exe and Mysql.exe to determine whether the script is successful, you need to output a special string to the file at the end of the script through the SELECT statement, and then by judging the Dbstat Whether the string exists in us.txt to determine the execution of the script, and because Sqlplus.exe does not exit automatically after executing the script, and the exit statement is added to the script at the end, the Script_oracle.sql must be the following:

SELECT ' Holytail ' from dual;
Exit

Inno Setup How to execute SQL scripts