cancel
Showing results for 
Search instead for 
Did you mean: 
duddelar
Pilgrim

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

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.
Labels (1)
0 Kudos
16 Replies
chad_petersen
Flexera beginner

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

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

Chad
0 Kudos
chad_petersen
Flexera beginner

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

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
0 Kudos
chad_petersen
Flexera beginner

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

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
0 Kudos
duddelar
Pilgrim

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

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.
0 Kudos
chad_petersen
Flexera beginner

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

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
0 Kudos
chad_petersen
Flexera beginner

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

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
0 Kudos
chad_petersen
Flexera beginner

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

Here was the prototypes I used.

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


Chad
0 Kudos
duddelar
Pilgrim

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

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.
0 Kudos
chad_petersen
Flexera beginner

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

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

0 Kudos