InstallScript Function to return a value from a SQL statement

[mikewiz]

Does anyone have an example of how to run a SQL statement through an InstallScript function and return the value to a MSI Property?

Here is the code I've been trying to use, but it throws an error and rolls back. Nothing is showing up in the log file indicating what the error is.


function ExecuteSQLScript(szServer, szUser, szPassword, szDBName)

STRING szConnString, szSQLCommand, szTemp;
OBJECT oSQLServer, oADOConnection, oADOCommand, sResult;
NUMBER nFuncResult, nFileHandle, nLength;

begin
try
set oSQLServer = CreateObject("SQLDMO.SQLServer");
catch
return -1;
endcatch;

try
oSQLServer.Start( TRUE, szServer, szUser, szPassword);
catch
oSQLServer.Connect(szServer, szUser, szPassword);
endcatch;

szConnString = "driver={SQL Server};";
szConnString = szConnString + "server=" + szServer + ";";
szConnString = szConnString + "Uid=" + szUser + ";";
szConnString = szConnString + "pwd=" + szPassword + ";";
szConnString = szConnString + "database=" + szDBName;

set oADOConnection = CreateObject("ADODB.Connection");
oADOConnection.Open(szConnString);

set oADOCommand = CreateObject("ADODB.Command");
oADOCommand.ActiveConnection = oADOConnection;

szSQLCommand = "SELECT COUNT(1) FROM Metadata.tblUser";

oADOCommand.CommandText = szSQLCommand;
set sResult = oADOCommand.Execute;

MessageBox(sResult, INFORMATION);
end;

[hidenori]

I recommend that you debug your InstallScript code and find out which line is causing the exception.

[mikewiz]

If the debugger took me into the InstallScript function I would.
It takes me into other functions. Any ideas why that would happen?

[hidenori]

Try to call your function from the OnFirstUIBefore() event before the SdWelcome dialog is displayed.

[mikewiz]

I'm using a Basic MSI project, so I don't have that function in my script.

[hidenori]

I suggest that you use an InstallScript project to debug your code, and then you can fix it in your Basic MSI project once you figure out what is causing the problem.

[mikewiz]

Here is the error message I'm getting in the MSI Log file.

MSI (s) (2C:D8) [09:53:28:409]: NOTE: custom action GetLicenseFile unexpectedly closed the hInstall handle (type MSIHANDLE) provided to it. The custom action should be fixed to not close that handle.
Action ended 9:53:28: InstallFinalize. Return value 3.

[mikewiz]

It is erroring out on line

oSQLServer.Start( TRUE, sCFBServer, sUser, sPassword);

I checked to make sure the variables are coming in correctly. I also checked to see if the database and user existed before the function is called.

Is what I'm using correct? Or do you have any examples of an installscript function that connects to a SQL server database?

[hidenori]

There is a sample code that retrieves a list of databases from a Microsoft SQL Server. If it does not help, I suggest that you write a test code outside InstallShield using a preferred language such as C++ or VB in order to make sure that SQLDMO is working on your system.