Two SQL Dialogs

[sureshkottakki]

Hi ,
We have created an Installscript MSI project , in that we have one SQl connection which has few SQL scripts in it. From OnSQLLogin function we are calling SQLServerSelectLogin dialog.
Now we had a new task to Show two SQL Dialogs in some cases only , according to one Property(or can be a string) value we need to show the second SQL dialog otherwise no need to show the second SQL dialog.

Can anybody help us how to work on two SQL Dialogs ?

Thanks in advance.

[rguggisberg]

Behavior and Logic
InstallScript
Select Setup.rul
Find you dialog(s) in OnFirstUIBefore
Insert IF statement(s) to control flow

[PlinyElder]

What rguggisberg said

[sureshkottakki]

Hi rguggisberg,

We are having two SQL connections with names SQLConnection and PerfSQLConnection, we need to show PerfSQLConnection in only some conditions (say if string xyz="true"), can you suggest whether to write if condition in this case or to create another function similar to
OnSQLLogin function?

To be more clear we have 3 scenarios:
1)Need to show only SQLConnection related SQL dialog
2)Need to show both SQLConnection and PerfSQLConnection related SQL Dialogs,
3)Need to show only PerfSQLConnection related SQL Dialog.

Below is the Installscript code ,can you suggest where to make the changes if we go with IF condition as you mentioned in earlier comment?

//---------------------------------------------------------------------------
// OnSQLLogin
//---------------------------------------------------------------------------
function number OnSQLLogin( nBtn )
string sMessage;
string szConnection, szServer, szUser, szPassword, szDB, sTemp[MAX_PATH];
string szAuthentication,szNASVer,szSQLServerName,szInitialCatalog,szAuthenticationType,szSQLUserName,szSQLPassword;
number nResult, nSize, nCount;
BOOL bWinLogin, bNext, bPromptForSQLLoginInfo;
LIST listConnections;
number nvSize;
begin

nvSize = MAX_PATH;
MsiGetProperty(ISMSI_HANDLE, "SQLSERVERADDRESS", szServer, nvSize);

nvSize = MAX_PATH;
MsiGetProperty(ISMSI_HANDLE, "SQLSERVERLOGIN", szUser, nvSize);

nvSize = MAX_PATH;
MsiGetProperty(ISMSI_HANDLE, "SQLSERVERPASSWORD", szPassword, nvSize);

nvSize = MAX_PATH;
MsiGetProperty(ISMSI_HANDLE, "SQLAUTHENTICATION", szAuthentication, nvSize);

if (szAuthentication = "WINDOWS") then
bWinLogin = TRUE;
else
bWinLogin = FALSE;
endif;

//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)

//Get Default values for connection
SQLRTGetConnectionInfo( szConnection, szServer, szDB, szUser, szPassword );

// Use our Default, not SQL Servers
//bWinLogin = SQLRTGetConnectionAuthentication( szConnection );

// Display login dialog (without connection name)
// COMMENT OUT TO SWAP DIALOGS
//nResult = 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 );

// nResult = SQLServerSelectLogin(szServer, szUser, szPassword, bWinLogin);

nvSize = MAX_PATH;
MsiGetProperty(ISMSI_HANDLE, "NASVER", szNASVer, nvSize);

if(szNASVer!="") then

nvSize = MAX_PATH;
MsiGetProperty(ISMSI_HANDLE, "SQLSERVERADDRESS", szServer, nvSize);
nvSize = MAX_PATH;
MsiGetProperty(ISMSI_HANDLE, "IS_SQLSERVER_DATABASE", szInitialCatalog, nvSize);
nvSize = MAX_PATH;
MsiGetProperty(ISMSI_HANDLE, "SQLAUTHENTICATION", szAuthentication, nvSize);
nvSize = MAX_PATH;
MsiGetProperty(ISMSI_HANDLE, "SQLSERVERLOGIN", szUser, nvSize);
nvSize = MAX_PATH;
MsiGetProperty(ISMSI_HANDLE, "SQLSERVERPASSWORD", szPassword, nvSize);
if (szAuthentication = "WINDOWS") then
bWinLogin = TRUE;
else
bWinLogin = FALSE;
endif;


nResult=NEXT;
else
nResult = SQLServerSelectLogin(szServer, szUser, szPassword, bWinLogin);
endif;



if( nResult = 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, szDB, 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;

endwhile;

MsiSetProperty(ISMSI_HANDLE, "SQLSERVERADDRESS", szServer);
MsiSetProperty(ISMSI_HANDLE, "SQLSERVERLOGIN", szUser);
MsiSetProperty(ISMSI_HANDLE, "SQLSERVERPASSWORD", szPassword);
if (bWinLogin = TRUE)then
MsiSetProperty(ISMSI_HANDLE, "SQLAUTHENTICATION", "WINDOWS");
else
MsiSetProperty(ISMSI_HANDLE, "SQLAUTHENTICATION", "SQL");
endif;

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

[rguggisberg]

I suppose there are a number of ways to do what you need. I have a couple packages that have 2 database connections. I put my deterministic logic in OnFirstUIBefore (may need to import that into your Setup.rul). In there you will find your call(s) to SQL stuff. I used SQLServerSelectLogin2 for each database. I also added code to test the SQL connection.

[sureshkottakki]

Hi rguggisberg,

Can you please send the logic?

[rguggisberg]

I have a lot of other things in mine. Lets start by you sending me your OnFirstUIBefore code in your Setup.rul. Go to:

Behavior and Logic
InstallScript
Select your Setup.rul (typical)

If you don't find OnFirstUIBefore anywhere in the window on the right, go to tool bar on right window and select the drop down in the left tab (probably has Initialization selected). Click on 'Before Move Data'. Now click on the right drop down and click on OnFirstUIBefore. If you look in there you will find the call to OnSQLLogin. So we need to set up another one of those. Post your OnFirstUIBefore.

[sureshkottakki]

Hi rguggisberg,

I could able to show two SQL Dialogs and could able to control the flow by creating similar function to OnSQLLogin. But one issue with this is not able to differentiate between the two dialogs , from both the functions we are calling SQLServerSelectLogin(szServer, szUser, szPassword, bWinLogin) dialog. Is there any way to differentiate the Dialog wording to tell the user which connection string they were entering the values?

[rguggisberg]

I use SQLServerSelectLogin2 (see the help for it). Then specify a connection name among parameters like this.
nResult = SQLServerSelectLogin2( szConnection, szServer, szUser, szPassword, bWinLogin, szDB, FALSE, TRUE );

[sureshkottakki]

Hi rguggisberg ,

After changing to SQLServerSelectLogin2( szConnection, szServer, szUser, szPassword, bWinLogin, szDB, TRUE , FALSE); , we are getting "CREATE TABLE permission denied in database 'master' " error message while installing in Azure machines.

[sureshkottakki]

Hi rguggisberg ,

We changed to SQLServerSelectLoginEx dialog which solved our problem, thank you for your inputs.