Run SQL Script on Upgrade

[John_Farkas]

I am using an Installscript project and want some sql scripts to run when the Installer runs in upgrade mode as well as when running a new install.

Does anyone know how to accomplish this. I have read many threads on this but have found no solutions.

Any help would be much appreciated.

[Snoopstah]

there are a few ways to go about this depending on whether you are executing your sql scripts from the IDE SQL Scripts view or calling them from within your installscript.

I personally use the Installscript as I feel I have more control. In that situation you can call the sql script from within OnFristUIBefore/After or in OnResumeUIBefore/After.

Create and ADO connection:

	
//-------------------------------------------------------------------------
// OpenSqlConnection
//-------------------------------------------------------------------------
function VOID OpenSqlConnection(svSqlInstanceName, svSqlDatabaseName, svSqlUserName, svSqlPassword, objConnection)
	STRING svConnection, svError;
	OBJECT objConnectionError;
	NUMBER i;
begin

// Build the connection string
svConnection = "Provider=sqloledb;server=" +svSqlInstanceName + ";";
if svSqlUserName = "" then  
    	//Use windows authentication
    	svConnection = svConnection + "Integrated Security=SSPI;database=" + svSqlDatabaseName + ";"; 
else 
   	//Use SQL Authentication
   	svConnection = svConnection + "uid=" + svSqlUserName + ";pwd=" + svSqlPassword + ";database=" + svSqlDatabaseName + ";";
endif;

try
        // Create ADO Connection Object
        set objConnection = CreateObject("ADODB.Connection");
        set objConnectionError = CreateObject("ADODB.Error");

        objConnection.ConnectionString = svConnection;
        objConnection.Open;        

catch
        if (objConnection.Errors.Count > 0) then
	for i = 0 to objConnection.Errors.Count
	set objConnectionError = objConnection.Errors.Item(i);
		SprintfBox(SEVERE, "SQL Connection", 
			"An error has occurred opening a SQL Server Connection.\nErorr %d: '%s'" ,
		objConnectionError.Number, objConnectionError.Description);
	endfor;
        endif;
 	set objConnection = NOTHING;
	set objConnectionError = NOTHING;
endcatch;
end;

Execute a script:

	
function STRING GetSqlVersion(svSqlInstanceName, svSqlDatabaseName, svSqlUserName, svSqlPassword)
    OBJECT objConnection, objRecordSet;
    STRING svQry;
    STRING svConnection;
    STRING svSqlVersion, svError;
    NUMBER nConnectionOpen;
begin

OpenSqlConnection(svSqlInstanceName, "master", svSqlUserName, svSqlPassword, objConnection);
 	
//SELECT SERVERPROPERTY('productversion')
svQry = "SELECT SUBSTRING(@@VERSION,23,4) AS SQLVersion";
 	set objRecordSet = objConnection.Execute(svQry);
  	svSqlVersion = objRecordSet("SQLVersion");
set objRecordSet  = NOTHING;

CloseSqlConnection(objConnection);
 
return svSqlVersion;
end;

[John_Farkas]

Thanks for the reply and for the code samples. Your help is much appreciated.