Need to check if database catalog exists in basic MSI installation thru installscript

[duddelar]

Hello All,

I have a requirement to check if database exists before going to sql login dialog.
I have added sql express as a prerequisite and added a sql connection from SQL scripts view to connect to catalog and execute the scripts.
But requirement is before going to sql login dialog.they want to connect to sql and check if database exists if it exists user shud have 2 options if he wants to connect to existing database and they want to continue
without sql dialog.if user selects he wants to create new and drop the existing then the sql dialog takes care about it.
I want to add a check for connection to local sql server and check for database thru installscript.
I have added credentials to fetch the windows credentials and added a below script to check for database.

function Check_IF_DataBaseExists(hMSI)
// To Do: Declare local variables.
BOOL db_exists;
STRING svUserName,svPassword,szServer;
NUMBER nusernameBuf,nPasswordBuf,nServerBuf;

begin
nusernameBuf=256;
nPasswordBuf = 256;
nServerBuf = 256;
MsiGetProperty(hMSI,"USERNAME_P",svUserName,nusernameBuf);
MsiGetProperty(hMSI,"PASSWORD_P",svPassword,nPasswordBuf);
MsiGetProperty(hMSI,"IS_SQLSERVER_SERVER",szServer,nServerBuf);


if (DoesDatabaseExist( "szServer","MyDataBasename","SQL Server",svUserName,svPassword) = FALSE ) then
MessageBox("Local DataBase Does not Exist",MB_OK);
else
MessageBox("Local DataBase Exists", MB_OK);
endif;
end;


//usage if (DoesDatabaseExist("SQLServer", "MyDatabase", "SQL Server", "sa", "saPassword") = FALSE) then

function BOOL DoesDatabaseExist(svServerName, svDatabaseName, svDriver, svUserName, svUserPassword)
OBJECT pADOConnObj, pADORecordSetObj;
STRING szADOConnObjID, szADORecordSetObjID, szConnString, szSQL;
BOOL bExists;
begin
bExists = FALSE;

// Create ADO Connection Object to connect to the SQL server
szADOConnObjID = "ADODB.Connection";
set pADOConnObj = CreateObject(szADOConnObjID);

// Create the SQL string to complete the connection
szConnString = "driver={" + svDriver + "};";
szConnString = szConnString + "server=" + svServerName + ";";
szConnString = szConnString + "uid=" + svUserName + ";";
szConnString = szConnString + "pwd=" + svUserPassword + ";";
szConnString = szConnString + "database=master";

// Open the ADO Connection
pADOConnObj.Open(szConnString);

// Create ADO Recordset object for the return
szADORecordSetObjID = "ADODB.Recordset";
set pADORecordSetObj = CreateObject(szADORecordSetObjID);

// Set some ADO Recordset properties
pADORecordSetObj.CursorType = 3;
pADORecordSetObj.ActiveConnection = pADOConnObj;

// Create the SQL string to retrieve the database if it exists
szSQL = "Select name from sysdatabases where name='" + svDatabaseName + "'";

// Use the recordset to see if the database exists
pADORecordSetObj.Open(szSQL);
if (pADORecordSetObj.RecordCount = 1) then
bExists = TRUE;
endif;

return bExists;
end;

When I debug or install the control is going into the function and its crashing.Not able to figure out the issue.Can anybody help me to check if database exists by connecting to local sql express.
Please check if there any issue with the above script or suggest me if any other code or options to check for database existance.

Thanks in advace.
Ravi.

[chad_petersen]

What are you initializing "MyDataBasename" to before calling the DoesDatabaseExist function? Seems like it might not be getting a value assigned to it.

Chad

[chad_petersen]

I also see you are using the IS_SQLSERVER_SERVER property for your szServer variable, but I don't believe it would exist until after the SQLLogin dialog has been executed (Next button pressed)

One of the difficulties, too, is that once someone clicks the Next button on the SQLLogin dialog and it runs ISSQLServerValidate it will create the database if it doesn't exist - if on of your Connections has the "Create Catalog If Absent" checkbox checked.

So, by the time you run anything after ISSQLServerValidate then the database might exist - and prior to ISSQLServerValidate then IS_SQLSERVER_SERVER doesn't exist yet - tricky one to crack.

Chad

[chad_petersen]

For some reason I can't edit my own posts - should have said IS_SQLSERVER_SERVER does exist, but not in a validated form - i.e. the name may not be a valid SQL Server name yet at that point in time. ISSQLServerValidate needs to run before you can be sure that IS_SQLSERVER_SERVER is valid or not.

Chad

[duddelar]

Hi Chad,

The MyDatabsename is the database itself.Iam passing directly.
And the login credentials are put before sql login dialog.
When I debut all the values are coming exactly.But when it goes to function it crashes.

Thanks in advance,
Ravi.

[chad_petersen]

I don't understand what you mean by you are passing it directly - you should let the computer do the work and try not to pass any parameters directly yourself - I don't even know how you connect yourself to the computer to accomplish that.

Chad

[chad_petersen]

I pasted your script into a new project here and added a dialog to capture some of the needed information and hard-coded my database name since it existed and I am able to step completely through the DoesDatabaseExist function without any crashing. I did have to remove the quotes around szServer in the calling function.

if (DoesDatabaseExist(szServer,"DCP_PROD","SQL Server",svUserName,svPassword) = FALSE ) then

I'm running InstallShield 2016 SP2 in my case. If you have not tried SP2 it might be worth a shot.

Do you get any messages when it crashes? Is it a blue screen of death (BSOD), an Unrecoverable Application Error (UAE) or something else?

Chad

[chad_petersen]

Here was the prototypes I used.

export prototype Check_IF_DataBaseExists(HWND);
prototype BOOL DoesDatabaseExist(string , string , string , string , string );


Chad

[duddelar]

Hi Chad,

Thanks for your reply,
I will try your sugestion and let you know.presently iam struck with other problem.
Yes Iam also using 2016 SP2.
The current problem is my installation doesnt upgrade the files when upgraded.
We had one release and the next release should upgrade the installation.
But it asks for upgrade but the files are not replaced and other new files and folders added also not installed.
Also if i try to uninstall the application the files and folders related to one feature still exists.
They are not cleaned up and we have release in couple of days. Could you please help and suggest the possible solution.

Thanks in advance
Ravi.

[chad_petersen]

It shouldn't ask to upgrade unless that is your intention - I always code an installer to automatically replace a prior version if it is there - no prompting for the user to click on anything. But, it can go either way there as that is a choice. You can

1. Upgrade automatically
2. Prompt for an upgrade
3. Force them to go uninstall from Add/Remove programs before they can install the newer

This is all done using a combination of Product Version, and Product, Upgrade and Package GUIDs.

FindRelatedProducts and MigrateFeatureStates are huge parts of an upgrade. Here's some info on the Microsoft web site. Two links so you can read about both actions. These are built-in Standard Actions that it can and does run by default.

https://msdn.microsoft.com/en-us/library/windows/desktop/aa368600(v=vs.85).aspx

https://msdn.microsoft.com/en-us/library/windows/desktop/aa370034(v=vs.85).aspx

Note though how it says "The method is only useful when the new feature tree has not greatly changed from the original."

It uses the Feature names to determine if a Feature was previously installed so it can select it during the Upgrade. If your Feature names have changed this can cause a problem for MigrateFeatureStates as you can probably imagine.

Chad

[duddelar]

chad.petersen wrote:
I pasted your script into a new project here and added a dialog to capture some of the needed information and hard-coded my database name since it existed and I am able to step completely through the DoesDatabaseExist function without any crashing. I did have to remove the quotes around szServer in the calling function.

if (DoesDatabaseExist(szServer,"DCP_PROD","SQL Server",svUserName,svPassword) = FALSE ) then

I'm running InstallShield 2016 SP2 in my case. If you have not tried SP2 it might be worth a shot.

Do you get any messages when it crashes? Is it a blue screen of death (BSOD), an Unrecoverable Application Error (UAE) or something else?

Chad

Hi Chad,

Did you run sql login before u call this function or u just added dialog to capture credentails.
I still has the same issue.It doent crash but its coming out of the loop.I added try to catch an exception but then it alwasy shows database not prsent.

[chad_petersen]

I called it on the Next button click on the SQLLogin dialog before the ISSQLServerValidate action runs.

Chad

[duddelar]

chad.petersen wrote:
I called it on the Next button click on the SQLLogin dialog before the ISSQLServerValidate action runs.

Chad

Oh Ok .So you called it after sql dialog.But can we use the ISSQLServerValidate action in our custom dialog to validate and check database and later go to sql login dialog.

Thanks,
Ravi

[duddelar]

duddelar wrote:
Oh Ok .So you called it after sql dialog.But can we use the ISSQLServerValidate action in our custom dialog to validate and check database and later go to sql login dialog.

Thanks,
Ravi

Hi Chad

I have done the same thing as you did but unable to create a connection.
Do we need any of the prerequisite like MDAC , ADO Data Control and windows latest installer to be added .
Please go thru ur ecxample and let me know if you have added any.

Thanks
Ravi.

[chad_petersen]

I copied your example and pasted into a setup.rul and called it on the Next button click. Nothing special. I assume you can call ISSQLServerValidate at an earlier time in the process. I have not tried that myself.

Chad

[duddelar]

chad.petersen wrote:
I copied your example and pasted into a setup.rul and called it on the Next button click. Nothing special. I assume you can call ISSQLServerValidate at an earlier time in the process. I have not tried that myself.

Chad

Hi Chad,

Presently Iam running this script
function BOOL DoesDatabaseExist(svServerName, svDatabaseName, svDriver, svUserName, svUserPassword )
OBJECT pADOConnObj, pADORecordSetObj;
STRING szADOConnObjID, szADORecordSetObjID, szConnString, szSQL, szError;
BOOL bExists;
begin
bExists = FALSE;

// Create ADO Connection Object to connect to the SQL server
szADOConnObjID = "ADODB.Connection";
set pADOConnObj = CreateObject(szADOConnObjID);
MessageBox(pADOConnObj,MB_OK);
// Create the SQL string to complete the connection
szConnString = "driver={" + svDriver + "};";
szConnString = szConnString + "server=" + svServerName + ";";
szConnString = szConnString + "uid=" + svUserName + ";";
szConnString = szConnString + "pwd=" + svUserPassword + ";";
szConnString = szConnString + "database=master";
/*if (bWindowsLogin) then
szConnString = szConnString + "Trusted_Connection=yes;";
else
szConnString = szConnString + "uid=" + svUserName + ";";
szConnString = szConnString + "pwd=" + svUserPassword + ";";
endif;*/
try
// Open the ADO Connection
MessageBox(szConnString,MB_OK);
pADOConnObj.Open(szConnString);
catch
//LoadStringFromStringTable("ID_UNABLE_CONNECT_DB", szError);
szError = "Error Message Here...";
MessageBox(szError, SEVERE);
return FALSE;
endcatch;
// Create ADO Recordset object for the return
szADORecordSetObjID = "ADODB.Recordset";
set pADORecordSetObj = CreateObject(szADORecordSetObjID);

// Set some ADO Recordset properties
pADORecordSetObj.CursorType = 3;
pADORecordSetObj.ActiveConnection = pADOConnObj;

// Create the SQL string to retrieve the database if it exists
szSQL = "Select name from sysdatabases where name='" + svDatabaseName + "'";

// Use the recordset to see if the database exists
pADORecordSetObj.Open(szSQL);
if (pADORecordSetObj.RecordCount = 1) then
bExists = TRUE;
endif;

return bExists;
end;

In the message box after creating object its showing empty.
And in the message box after try its showing szconnection string.
After that its going to catch..I guess its not creating object and not establishing connection.Can you please check the above script and do the needful

Thanks,
Ravi.

[duddelar]

Hi Chad,

What is the sql server you are using?
Iam using sql server express 2014.Is it may be the issue.coz connecting may differ.

Thanks,
Ravi.