Populating SQL Server connection info

Snoopstah · ‎Nov 18, 2007

I am using an InstallScript MSI project and prompt users to enter their SQL connection details by using OnSQLLogin function.

What I want to do is populate the authentication, username and password if a user selects one specific SQL Server instance. This instance has been deployed by us and users will not know the sa password, which is required to run a certain set of scripts. If a user selects our existing SQL Server instance, I want to disable the authentication and login details on the dialog box, and populate the values with our set of information.

How can I do this?

hidenori · ‎Nov 21, 2007

The following is a sample code to manage the SQLServerSelectLogin dialog. You need to copy it to your InstallScript code, and change the OnSQLLogin() event to call the SQLServerSelectLoginCustom() function instead of SQLServerSelectLogin2(). In order to accomplish your requirement, you need to add your code to the "case COMBO_SERVERS" statement so that it will enable/disable other controls based on a server name provided by an end user.

Hope that helps.

#include "DialogsPriv.h"
#include "strpriv.h"

// private constants
#define DLG_SQLSELECTLOGIN          "SQLServerSelectLogin"
#define DLG_SQLSELECTLOGIN_ID       10109
#define EDIT_SQLUSERNAME              301
#define EDIT_SQLPASSWORD               305
#define RADIO_WINDOWS               17813
#define RADIO_SQL                   17814   
#define COMBO_SERVERS				17815  
#define BUTTON_BROWSE				31
#define STATIC_CONNECTION_LABEL     17819
#define STATIC_CONNECTION           17820
#define STATIC_DATABASE_LABEL		17816
#define EDIT_DATABASE				17817
#define BUTTON_DATABASE_BROWSE		17818
#define LINE_DATABASE_SEPARATOR		1303
NUMBER nDlgSQLSELECTLOGIN;

prototype SQLServerSelectLoginCustom( BYVAL STRING, BYREF STRING, BYREF STRING , BYREF STRING, BYREF BOOL, BYREF STRING, BYVAL BOOL, BYVAL BOOL);
prototype SQLSelectLoginEnableButton( INT, INT, BYREF STRING, BYREF STRING, BOOL );

function SQLServerSelectLoginCustom( szConnection, svServer, svUser, svPassword, bvWindowsLogin, svDB, bShowConnectionName, bShowCatalog)
    NUMBER nId, nLen, nStyle, nNil, nMessage;
    STRING szAppKey, szNil, szCaption, szTitle, szServer; 
    LIST listServers;
    BOOL bDone, bEnterLogin;
    HWND hDlg, hEditName, hEditPassword, hWnd, hEditDB, hLableDB, hButtonDB, hLineDB;
begin

    if (!g_bSQLRTInitialized) then
        return ISERR_GEN_FAILURE;
    endif;

    // record data produced by this dialog
    if (MODE = SILENTMODE) then
        SdMakeName(szAppKey, DLG_SQLSELECTLOGIN, "", nDlgSQLSELECTLOGIN);
        SilentReadData(szAppKey, "Result", DATA_NUMBER, szNil, nId);
        if ((nId != BACK) && (nId != CANCEL)) then
            SilentReadData( szAppKey, "szUser", DATA_STRING, svUser, nNil );
            SilentReadData( szAppKey, "szPass", DATA_STRING, svPassword, nNil );
            SilentReadData( szAppKey, "szServer", DATA_STRING, svServer, nNil );
            SilentReadData( szAppKey, "szDB", DATA_STRING, svDB, nNil );
            SilentReadData( szAppKey, "szAuthen", DATA_NUMBER, szNil, bvWindowsLogin );
        endif;
        return nId;
    endif;

    // ensure general initialization is complete
    if (!bSdInit) then
      SdInit();
    endif;

    if (EzDefineDialog(DLG_SQLSELECTLOGIN, "", "", DLG_SQLSELECTLOGIN_ID) = DLG_ERR) then
        return ISERR_GEN_FAILURE;
    endif;

    // Loop in dialog until the user selects a standard button
    bDone = FALSE;

    while (!bDone)
        nId = WaitOnDialog(DLG_SQLSELECTLOGIN);
        switch (nId)
        case DLG_INIT:
				
            // Set connection name
            if( bShowConnectionName = TRUE ) then
                CtrlSetText( DLG_SQLSELECTLOGIN, STATIC_CONNECTION, szConnection );    
            else
                // Hide control
                CtrlSetText( DLG_SQLSELECTLOGIN, STATIC_CONNECTION_LABEL, "" );
                CtrlSetText( DLG_SQLSELECTLOGIN, STATIC_CONNECTION, "" );    
            endif;
                 
        	//populate servers list
        	listServers = SQLRTGetServers2( szConnection, TRUE );
        	CtrlSetList( DLG_SQLSELECTLOGIN, COMBO_SERVERS, listServers ); 
            if( svServer = "" ) then
                ListGetFirstString( listServers, szServer );
            else
                szServer = svServer;
            endif;

        	ListDestroy( listServers );
        	CtrlSetText( DLG_SQLSELECTLOGIN, COMBO_SERVERS, szServer );

            nLen = StrLengthChars(svUser);
            if (nLen = 0) then
                nLen = Resize(svUser, 256);
            endif;
            
            nLen = StrLengthChars(svPassword);
            if (nLen = 0) then
                nLen = Resize(svPassword, 256);
            endif;


            hDlg = CmdGetHwndDlg(DLG_SQLSELECTLOGIN);
            SdGeneralInit(DLG_SQLSELECTLOGIN, hDlg, 0, szSdProduct);

            szTitle = GetDialogTitle(DLG_ASK_TEXT);
            SdSetDlgTitle(DLG_SQLSELECTLOGIN, hDlg, szTitle);
            
            hEditName = GetDlgItem(hDlg, EDIT_SQLUSERNAME);
            if (nLen < 255) then
                nLen = 255;
            endif;
            SendMessage(hEditName, EM_LIMITTEXT, nLen-1, 0);

            hEditPassword = GetDlgItem(hDlg, EDIT_SQLPASSWORD);
            if (nLen < 255) then
                nLen = 255;
            endif;
            SendMessage(hEditPassword, EM_LIMITTEXT, nLen-1, 0);

            nStyle = GetWindowLong(hEditPassword, GWL_STYLE);
            nStyle = (nStyle & ~ES_OEMCONVERT);
            SetWindowLong(hEditPassword, GWL_STYLE, nStyle);
            
            if( bvWindowsLogin ) then

                CtrlSetState (DLG_SQLSELECTLOGIN, RADIO_WINDOWS, BUTTON_CHECKED);
                CtrlSetState (DLG_SQLSELECTLOGIN, RADIO_SQL, BUTTON_UNCHECKED);
                EnableWindow( hEditPassword, FALSE );
                EnableWindow( hEditName, FALSE );    
            else

                CtrlSetState (DLG_SQLSELECTLOGIN, RADIO_WINDOWS, BUTTON_UNCHECKED);
                CtrlSetState (DLG_SQLSELECTLOGIN, RADIO_SQL, BUTTON_CHECKED);
                EnableWindow( hEditPassword, TRUE );
                EnableWindow( hEditName, TRUE ); 
                CtrlSetText( DLG_SQLSELECTLOGIN, EDIT_SQLUSERNAME, szServer );
                CtrlSetText( DLG_SQLSELECTLOGIN, COMBO_SERVERS, szServer );
            endif;   
            
            bEnterLogin = (CtrlGetState( DLG_SQLSELECTLOGIN, RADIO_SQL ) = BUTTON_CHECKED);
            CtrlSetText(DLG_SQLSELECTLOGIN, EDIT_SQLUSERNAME, svUser);
            CtrlSetText(DLG_SQLSELECTLOGIN, EDIT_SQLPASSWORD, svPassword); 
            SQLSelectLoginEnableButton( hDlg, SD_PBUT_CONTINUE, svUser, svServer, bEnterLogin );
            SQLSelectLoginEnableButton( hDlg, BUTTON_DATABASE_BROWSE, svUser, svServer, bEnterLogin );
            
            // Set catalog name
            if( bShowCatalog = TRUE ) then
                CtrlSetText( DLG_SQLSELECTLOGIN, EDIT_DATABASE, svDB );    
            else
                // Hide control
                hLableDB = GetDlgItem(hDlg, STATIC_DATABASE_LABEL);
                ShowWindow( hLableDB, FALSE );
                hEditDB = GetDlgItem(hDlg, EDIT_DATABASE);
                ShowWindow( hEditDB, FALSE );
                hButtonDB = GetDlgItem(hDlg, BUTTON_DATABASE_BROWSE);
                ShowWindow( hButtonDB, FALSE );
                hLineDB = GetDlgItem(hDlg, LINE_DATABASE_SEPARATOR);
                ShowWindow( hLineDB, FALSE );
            endif;
 		
		case RADIO_WINDOWS:
            if( CtrlGetState( DLG_SQLSELECTLOGIN, RADIO_WINDOWS ) = BUTTON_CHECKED ) then
                CtrlSetState (DLG_SQLSELECTLOGIN, RADIO_SQL, BUTTON_UNCHECKED);
                EnableWindow( hEditPassword, FALSE );
                EnableWindow( hEditName, FALSE );
            endif;   
            bEnterLogin = (CtrlGetState( DLG_SQLSELECTLOGIN, RADIO_SQL ) = BUTTON_CHECKED);
            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLUSERNAME, svUser);
            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLPASSWORD, svPassword); 
            CtrlGetText(DLG_SQLSELECTLOGIN, COMBO_SERVERS, svServer); 
            SQLSelectLoginEnableButton( hDlg, SD_PBUT_CONTINUE, svUser, svServer, bEnterLogin );
            SQLSelectLoginEnableButton( hDlg, BUTTON_DATABASE_BROWSE, svUser, svServer, bEnterLogin );

        case RADIO_SQL:
            if( CtrlGetState( DLG_SQLSELECTLOGIN, RADIO_SQL ) = BUTTON_CHECKED ) then
                CtrlSetState (DLG_SQLSELECTLOGIN, RADIO_WINDOWS, BUTTON_UNCHECKED);
                EnableWindow( hEditPassword, TRUE );
                EnableWindow( hEditName, TRUE );
            endif;  
            bEnterLogin = (CtrlGetState( DLG_SQLSELECTLOGIN, RADIO_SQL ) = BUTTON_CHECKED);
            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLUSERNAME, svUser);
            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLPASSWORD, svPassword); 
            CtrlGetText(DLG_SQLSELECTLOGIN, COMBO_SERVERS, svServer); 
            SQLSelectLoginEnableButton( hDlg, SD_PBUT_CONTINUE, svUser, svServer, bEnterLogin );
            SQLSelectLoginEnableButton( hDlg, BUTTON_DATABASE_BROWSE, svUser, svServer, bEnterLogin );

        case BUTTON_BROWSE:
        	if( SQLBrowse2(szConnection, svServer) = NEXT ) then
        	    CtrlSetText( DLG_SQLSELECTLOGIN, COMBO_SERVERS, svServer );
        	endif;  
        	bEnterLogin = (CtrlGetState( DLG_SQLSELECTLOGIN, RADIO_SQL ) = BUTTON_CHECKED);
            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLUSERNAME, svUser);
            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLPASSWORD, svPassword); 
            CtrlGetText(DLG_SQLSELECTLOGIN, COMBO_SERVERS, svServer); 
            SQLSelectLoginEnableButton( hDlg, SD_PBUT_CONTINUE, svUser, svServer, bEnterLogin );
            SQLSelectLoginEnableButton( hDlg, BUTTON_DATABASE_BROWSE, svUser, svServer, bEnterLogin );
            
        case EDIT_SQLUSERNAME:
		   	nMessage = CtrlGetSubCommand(DLG_SQLSELECTLOGIN);
		   	if(nMessage = EDITBOX_CHANGE) then	
	        	bEnterLogin = (CtrlGetState( DLG_SQLSELECTLOGIN, RADIO_SQL ) = BUTTON_CHECKED);
	            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLUSERNAME, svUser);
	            CtrlGetText(DLG_SQLSELECTLOGIN, COMBO_SERVERS, svServer); 
	            SQLSelectLoginEnableButton( hDlg, SD_PBUT_CONTINUE, svUser, svServer, bEnterLogin );
	            SQLSelectLoginEnableButton( hDlg, BUTTON_DATABASE_BROWSE, svUser, svServer, bEnterLogin );
	        endif;  
	        
        case COMBO_SERVERS:
        	bEnterLogin = (CtrlGetState( DLG_SQLSELECTLOGIN, RADIO_SQL ) = BUTTON_CHECKED);
            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLUSERNAME, svUser);
            CtrlGetText(DLG_SQLSELECTLOGIN, COMBO_SERVERS, svServer); 
            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLPASSWORD, svPassword); 
            SQLSelectLoginEnableButton( hDlg, SD_PBUT_CONTINUE, svUser, svServer, bEnterLogin );
            SQLSelectLoginEnableButton( hDlg, BUTTON_DATABASE_BROWSE, svUser, svServer, bEnterLogin );
        	endif;

        case SD_PBUT_CONTINUE: 
            CtrlGetText(DLG_SQLSELECTLOGIN, COMBO_SERVERS, svServer); 
            if( CtrlGetState( DLG_SQLSELECTLOGIN, RADIO_SQL ) = BUTTON_CHECKED ) then
                bvWindowsLogin = FALSE;
                CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLUSERNAME, svUser);
                CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLPASSWORD, svPassword);
            else
                bvWindowsLogin = TRUE;
                svUser = "";
                svPassword = "";
            endif;
            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_DATABASE, svDB); 
            
			//read server

            nId   = NEXT;
            bDone = TRUE;

        case SD_PBUT_BACK:
            nId    = BACK;
            bDone  = TRUE;

        case DLG_ERR:
            nId   = ISERR_GEN_FAILURE;
            SdError(nId, DLG_SQLSELECTLOGIN);
            bDone = TRUE;

        case DLG_CLOSE:
            SdCloseDlg(hDlg, nId, bDone);

        case BUTTON_DATABASE_BROWSE:
        	if( SQLDatabaseBrowse(szConnection, svServer, !bEnterLogin, svUser, svPassword, svDB) = NEXT ) then
        	    CtrlSetText( DLG_SQLSELECTLOGIN, EDIT_DATABASE, svDB );
        	endif;  

        case EDIT_DATABASE:
		   	nMessage = CtrlGetSubCommand(DLG_SQLSELECTLOGIN);
		   	if(nMessage = EDITBOX_CHANGE) then	
        	    CtrlGetText( DLG_SQLSELECTLOGIN, EDIT_DATABASE, svDB );
	        endif;  

        default:
            // check standard handling
            if (SdIsStdButton(nId) && SdDoStdButton(nId)) then
                if ((nId != EDIT_SQLUSERNAME) && (nId != EDIT_SQLPASSWORD)) then
                    bDone = TRUE;
                endif;
            endif;
            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLUSERNAME, svUser);
            CtrlGetText(DLG_SQLSELECTLOGIN, EDIT_SQLPASSWORD, svPassword);
      	    CtrlGetText( DLG_SQLSELECTLOGIN, EDIT_DATABASE, svDB );
        endswitch;
    endwhile;

    EndDialog(DLG_SQLSELECTLOGIN);
    ReleaseDialog(DLG_SQLSELECTLOGIN);

    SdUnInit();

    // record data produced by this dialog
    if (MODE = RECORDMODE) then
        SdMakeName(szAppKey, DLG_SQLSELECTLOGIN, "", nDlgSQLSELECTLOGIN);
        SilentWriteData( szAppKey, "szUser", DATA_STRING, svUser, nNil );
        SilentWriteData( szAppKey, "szPass", DATA_STRING, svPassword, nNil );
        SilentWriteData( szAppKey, "szServer", DATA_STRING, svServer, nNil );
        SilentWriteData( szAppKey, "szDB", DATA_STRING, svDB, nNil );
        SilentWriteData( szAppKey, "szAuthen", DATA_NUMBER, szNil, bvWindowsLogin );
        SilentWriteData( szAppKey, "Result", DATA_NUMBER, szNil, nId );
    endif;

    return nId;
end;

function  SQLSelectLoginEnableButton( hwndDlg, nControlID, svName, svServer, bLogin )
	HWND  hwndItem;
begin

	hwndItem = GetDlgItem( hwndDlg, nControlID );
	if (!IsWindow( hwndItem)) then 
		return FALSE; 
	endif;
     
    StrRemoveSpaces( svServer ); 
	StrRemoveSpaces( svName );
	
	if( svServer = "" ) then
	    EnableWindow( hwndItem, FALSE );
	else 
	    if( bLogin ) then
		    if( svName = "" ) then
	            EnableWindow( hwndItem, FALSE);
	        else
	            EnableWindow( hwndItem, TRUE);
	        endif;
	    else
	        EnableWindow( hwndItem, TRUE );
	    endif;
	endif;
end;

Snoopstah · ‎Nov 21, 2007

That's fabulous. Thankyou so very much!

Populating SQL Server connection info

InstallShield 2008