PDA

View Full Version : Custom Edit Field on SQL Dialog



gknierim
10-26-2010, 04:25 PM
I have looked all over and still can't figure this out.

I am using the OnSQLLogin script to display the dialog SQLServerLogin2 to which I have added an Edit Field that will allow the user to specify the database name. The name of the edit field is Edit1 (default name) and the Control Id is 12091.

The reason I can't use the standard database catelog field is that I have a createdatabase SQL Script file that I run that will create the database if it doesn't exist. I have the Create Catalog if absent unchecked.

My question is how can I retrieve the value the user puts in the newly added Edit field and use that in my text replacement on my SQL Script? I know how to do the text replacement - I just can't figure out how to get the value the user entered into that field?

I am using an InstallScript MSI project.

Thanks,
Greg

hidenori
10-26-2010, 04:39 PM
You basically need to copy the code for the SQLServerSelectLogin2 function from C:\Program Files\InstallShield\2011\Script\Isrt\src\SQLServerSelectLoginDlg.rul into your InstallScript code, and then tweak it to support the new control that you have added.

Hope that helps.

gknierim
10-26-2010, 05:22 PM
OK. So I copied the script and inserted into my project. Now what part of this do I need to tweak?

hidenori
10-26-2010, 05:42 PM
My assumption is that you want to add something similar to the 'Name of database catalog' edit control. I would recommend that you search for EDIT_DATABASE in the code to learn what needs to be done for your custom control.

gknierim
10-26-2010, 06:51 PM
OK. I think I understand but where is EDIT_DATABASE defined? and do I just replace EDIT_DATABASE with what???? My Control Id, my edit box name? Do I replace all lines since I'm not using EDIT_DATABASE or just certain lines? I just want to return what is in the Edit field I added.

On another note, I don't see how this is any different than using the default edit field to specify the database name. I was told by IS Support to do this. I can't use the standard catalog name field because it requires that the database name exists so this is why I am here.

hidenori
10-27-2010, 08:58 AM
Actually, you should be able to accomplish your requirement without using a custom edit control. You can simply add your SQL script that will create a database catalog to the connection, and schedule it to run during login. It should work.

gknierim
10-27-2010, 09:58 AM
Not sure if I have made myself clear so I will try again.

During install, the user specifies the server name, the SQL login account used to create the database and the database name. I have my create database script in the SQL Scripts view under a Connection. I have the Create Catalog if Absent UNCHECKED.

My requirements are:
Create the database during the install - not at login which I am assumming is before the user actually completes going through the dialogs. I need the database created in the installation directory and not the default Data directory that they have setup for their database server.

The problem is that if I use the standard dialog SQLServerSelectLogin2 and specify the database name, it tells me it can't connect to the database because it doesn't exist. Well, duh. So I was told by IS Support to edit the dialog, hide the database catalog field and browse button and add my own edit field on the dialog. I have done that and after several hours of figuring out how to add the SQLServerSelectLogin2 function to my scripts and modifying that, I am getting the same result. So apparently that doesn't work either.

So, in summary, I need to create the database during the install (not at login) and I need to be able to create it in the installatioin directory that the program is being installed to. The database will not exist on installation. My create database script checks for that and creates it if neccessary.

So, how can I resolve this? This can't be that hard.

Thanks,
Greg

hidenori
10-27-2010, 12:04 PM
In that case, you need to have two connections; one for the script that will create a database catalog, and another one for other scripts that will modify the database catalog. The two connections should share the same properties for the connections settings, except for the target database catalog. You also need to modify the OnSQLLogin InstallScript event so that only one SQLLogin dialog will be displayed and test the credentials without attempting to connect the database catalog that you want to create. Try the steps below and see if it works:


Create a new InstallScript MSI project.
Open the Tools | Options dialog.
Make sure that the "Generate unique Windows Installer properties for new connections" checkbox under the SQL Scripts tab is unchecked.
Go to the Property Manager.
Create a new property named IS_SQLSERVER_DATABASE_EMPTY with an empty value.
Go to the SQL Scripts view.
Create a connection and name it "CreateDatabaseScript"
Select IS_SQLSERVER_DATABASE_EMPTY for the Target Catalog Property Name setting in the Advanced tab.
Add the SQL script that will create a database catalog.
Create another connection and name it "OtherScripts"
Add the other SQL scrtips.
Go to the InstallScript view.
Select the Before Move Data | OnSQLLogin event from the dropdown lists above the editor, and modify the OnSQLLogin function as highlighted in red:


function number OnSQLLogin( nBtn )
string sMessage;
string szConnection, szServer, szUser, szPassword, szDB, sTemp[MAX_PATH];
number nResult, nSize, nCount;
BOOL bWinLogin, bNext;
LIST listConnections;
begin

//First initialize SQL Server runtime
SQLRTInitialize2();

// Suppress ISSQLSRV.DLL to show a connection error message.
MsiSetProperty( ISMSI_HANDLE, "IS_SQLSERVER_CA_SILENT", "1" );

//Get the names of all the necessary connections
listConnections = SQLRTGetConnections();
ListGetFirstString (listConnections, szConnection);

nCount = 0;

//determine if NEXT or BACK will be returned
//if there are no connections to make
if( nBtn != BACK ) then
bNext = TRUE;
else
bNext = FALSE;
//start at end if going BACK
while (ISERR_SUCCESS = ListGetNextString( listConnections, szConnection ) );
nCount++;
endwhile;
endif;

// Login for each connection
while (nResult = ISERR_SUCCESS)

if (szConnection = "OtherScripts") then
//Get Default values for connection
SQLRTGetConnectionInfo( szConnection, szServer, szDB, szUser, szPassword );

bWinLogin = SQLRTGetConnectionAuthentication( szConnection );

// Display login dialog (without connection name)
// COMMENT OUT TO SWAP DIALOGS
nBtn = SQLServerSelectLogin2( szConnection, szServer, szUser, szPassword, bWinLogin, szDB, FALSE, TRUE );

// Display login dialog (with connection name)
// UNCOMMENT TO SWAP DIALOGS
// nResult = SQLServerSelectLogin2( szConnection, szServer, szUser, szPassword, bWinLogin, szDB, TRUE, TRUE );

if( nBtn = NEXT ) then

//store data in case we need it again
SQLRTPutConnectionInfo2( szConnection, szServer, szDB, szUser, szPassword );

SQLRTPutConnectionAuthentication( szConnection, bWinLogin );

//test connection
nResult = SQLRTTestConnection2( szConnection, szServer, "", szUser, szPassword, bWinLogin );

nSize = MAX_PATH;
MsiGetProperty( ISMSI_HANDLE, "IS_SQLSERVER_STATUS", sTemp, nSize );

if( sTemp != "0" ) then

nSize = _MAX_PATH;
MsiGetProperty( ISMSI_HANDLE, "IS_SQLSERVER_STATUS_ERROR", sMessage, nSize );

if( nSize = 0 ) then
Sprintf(sMessage, SdLoadString( IDS_IFX_SQL_ERROR_LOGIN_FAILED ), szConnection, SdLoadString( ISCRIPT_E_UNKNOWN_ERROR_DESC ));
endif;

MessageBox( sMessage, MB_OK );

//Show same login dialog again
nResult = ListCurrentString(listConnections, szConnection);

else //SUCCESS

//Move on to next connection
nCount++;
bNext = TRUE;
nResult = ListGetNextString(listConnections, szConnection);

endif;

else

//BACK
nCount--;
bNext = FALSE;
nResult = ListSetIndex( listConnections, nCount );
ListCurrentString( listConnections, szConnection );

endif;
else

if( nBtn = NEXT ) then
//Move on to next connection
nCount++;
bNext = TRUE;
nResult = ListGetNextString(listConnections, szConnection);
else
//BACK
nCount--;
bNext = FALSE;
nResult = ListSetIndex( listConnections, nCount );
ListCurrentString( listConnections, szConnection );
endif;
endif;
endwhile;

if( bNext ) then
return NEXT;
else
return BACK;
endif;

end;


Build a release.

gknierim
10-27-2010, 08:50 PM
Thanks. Your last post did the trick. I don't understand why it has to be this way with 2 connections but I guess as long as it works.
:cool:

gknierim
10-27-2010, 10:40 PM
Well, I spoke too soon. Before i get to the SQLLogin interface, I am asking whether you want a Complete or Custom setup (SdSetupType2). If I select Custom and click Next, it goes to the Features screen, then the SQLLogin interface and everything works.

However, if I choose Complete and click Next, it never progresses to the SQLLogin interface. I have debugged the OnSQLLogin function and it never loops through both Connections and just returns BACK and so the interface never progresses and is stuck on the Select Setup Type interface.

What could be happening?

hidenori
10-27-2010, 11:28 PM
Try this and see if it solves the issue:


Go to the InstallScript view.
Select the Before Move Data | OnFirstUIBefore event from the dropdown lists above the editor, and modify the Dlg_SetupType block in the OnFirstUIBefore function as follows:


Dlg_SetupType:
szTitle = "";
szMsg = "";
nResult = SetupType2(szTitle, szMsg, "", nSetupType, 0);
if (nResult = BACK) then
goto Dlg_SdRegisterUser;
else
nSetupType = nResult;
if (nSetupType != CUSTOM) then
nvSize = 0;
FeatureCompareSizeRequired(MEDIA, INSTALLDIR, nvSize);
if (nvSize != 0) then
MessageBox(szSdStr_NotEnoughSpace, WARNING);
goto Dlg_SetupType;
endif;
bCustom = FALSE;
nResult = NEXT;
goto Dlg_SQL;
else
bCustom = TRUE;
endif;
endif;

Rebuild the setup.

gknierim
10-27-2010, 11:46 PM
Yep, that did it. Thought I had that in there before but might have removed it.

Everything is working good except for my other issue with the error 27506 located here: http://community.flexerasoftware.com/showthread.php?t=195515

Thanks for your help!

iprasla
01-25-2012, 11:01 PM
Hi Hidenori,

I used your suggested way of creating multiple connections to create db on INSTALL. Along with this, I am also following your other suggestions for creating multiple instances on the same server using semi-colon. It is posted here
http://community.flexerasoftware.com/showthread.php?t=195235&highlight=semi-colon

However, this technique of using semi-colon no longer works. If "DB1; DB2" is entered, then the whole string is taken as a single db name. I think I know where the problem but not the solution.

Problem - I am using text replacement in the first connections as DB to be replaced by IS_SERVER_DATABASE. How should Text Replacement be done for each of this db?