- Revenera Community
- :
- InstallShield
- :
- InstallShield Forum
- :
- Re: Need to check if database catalog exists in basic MSI installation thru installscript
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
Need to check if database catalog exists in basic MSI installation thru installscript
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
Chad
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
Chad
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
Chad
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
export prototype Check_IF_DataBaseExists(HWND);
prototype BOOL DoesDatabaseExist(string , string , string , string , string );
Chad
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
Chad
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
Chad
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
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.
![](/skins/images/8C9D13A71C7F7695F3262372C0172D68/responsive_peak/images/icon_anonymous_message.png)