SQL Server - Test connection with ODBC driver

From ISXKB

(Difference between revisions)
Jump to: navigation, search

Vincenzo (Talk | contribs)
(SQL Server - Test connection with ODBC driver)
Newer edit →

Revision as of 11:44, 10 January 2007

If SQL Server ODBC driver is installed, it is possible ask connection parameters and test if these params are good to connect to Sql Server.

Same code is good to test MSDE connection also, and changing driver string is possible to test any database, it's only necessary the ODBC driver and connection parameters.

To compile this sample you need ODBC - Library functions, the definition of the additional form is at bottom of the page.

This sample uses also Cursor - Change the mouse cursor of WizardForm.

Sample code

[Setup]
AppName=ODBCtest
AppVerName=ODBCtest
Uninstallable=false
UpdateUninstallLogAppName=false
DisableDirPage=false
DisableProgramGroupPage=true
DefaultDirName={pf}\ODBCtest
DisableStartupPrompt=true
CreateAppDir=false
OutputBaseFilename=TestODBC

[Code]

// include const and function declarations
#include "odbclib.iss"

// include function to change cursor
#include "CursorLib.iss"

var

  // variables

  myserver, myuser, mypwd: String;


//	Parameters for SQL Server
//	DRIVER={SQL Server}
//	SERVER=servername
//	UID=username
//	PWD=password
//	DATABASE=dbname (optional)
//	WSID=workstation id (optional usually computer name)

//	APP=application (optional)
//	NETWORK=Dbmssocn  (optional)
//	ADDRESS=server,port (optional)

{ Protocols
TCP/IP Windows Sockets	Dbmssocn.dll
Named pipes 			Dbnmpntw.dll
Multiprotocol 			Dbmsrpcn.dll
Novell SPX/IPX 			Dbmsspxn.dll
Banyan Vines			Dbmsvinn.dll
DECNet 					Dbmsdecn.dll
AppleTalk 				Dbmsadsn.dll
}

//
// connect to server using usr and pwd
//
function ConnectServer( server, usr, pwd : String ) : Boolean;
var	envH :SQLHANDLE ;	// env handle
    conH :SQLHANDLE;	// connection handle

    sqlret : SQLRETURN;
    hwnd : LongInt;	   // window wizard handle
    constrIN, constrOUT: String;
    sizeout : SQLSMALLINT;
    msg: String;
begin

  hwnd := StrToInt(ExpandConstant('{wizardhwnd}'));
  envH := SQL_NULL_HENV;
  conH := SQL_NULL_HDBC;
  Result := true;

  sqlret := SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, envH );
  if sqlret = SQL_SUCCESS then
  begin

    sqlret := SQLSetEnvAttr( envH, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0 );
  	if sqlret = SQL_SUCCESS then
  	begin

  	  sqlret := SQLAllocHandle( SQL_HANDLE_DBC, envH, conH );
  	  if sqlret = SQL_SUCCESS then
  	  begin
        constrIN := 'DRIVER={SQL Server};SERVER='+server
			+';UID='+usr+';PWD='+pwd+';APP=Inno Setup Test;';
			
	    // if it's necessary connect via tcp/ip
        // constrIN := constrIN + 'NETWORK=Dbmssocn;ADDRESS='+server+',1433';
        constrOUT := StringOfChar( ' ', BUFSIZE );

        // connessione
        sqlret := SQLDriverConnect( conH,
                  hwnd,
                  constrIN,
                  Length(constrIN),
                  constrOUT,
                  BUFSIZE - 1,
                  sizeout,
                  SQL_DRIVER_NOPROMPT  );

        constrOUT := CastIntegerToString( CastStringToInteger(constrOUT) );

	    msg := #13#10 + #13#10 + GetODBCMsg( SQL_HANDLE_DBC, conH );

        if IsSQLRetOk( sqlret ) then
        begin

          MsgBox( 'Connection string is: ' + #13#10 + #13#10 + constrOUT + msg,
			mbInformation ,MB_OK );
          sqlret := SQLDisconnect( conH );
        end
        else begin
          MsgBox( 'Connection failed!' + #13#10 + #13#10 + constrIN + msg, mbError , MB_OK );
    	  sqlret := SQLFreeHandle( SQL_HANDLE_DBC, conH );
	      Result := false;
  	    end;
  	  end
	  else begin
		msg := GetODBCMsg( SQL_HANDLE_DBC, conH );
    	MsgBox( 'Error creating CON handle:' + #13#10 + #13#10 + msg, mbError , MB_OK );
    	Result := false;
  	  end;

    end
    else begin
	  msg := GetODBCMsg( SQL_HANDLE_ENV, envH );
      MsgBox( 'Error setting ODBC version:' + #13#10 + #13#10 + msg, mbError , MB_OK );
      Result := false;
    end

	// anyway release ENV handle
    sqlret := SQLFreeHandle( SQL_HANDLE_ENV, envH );

  end
  else begin
	msg := GetODBCMsg( SQL_HANDLE_ENV, envH );
    MsgBox( 'Error creating ENV handle:' + #13#10 + #13#10 + msg, mbError , MB_OK );
    Result := false;
  end;

end;

// include code to create the form
#include "SrvUsrPwd.isf"

procedure InitializeWizard();
begin
	// insert the page after the welcome page
	LoginODBC_CreatePage(wpWelcome);
end;

form SrvUsrPwd.isf




[CustomMessages]
  LoginODBCCaption=Login ODBC Caption
  LoginODBCDescription=Login ODBC description

  [Code]
  var
    lbUsername: TLabel;
    lbServer: TLabel;
    lbPassword: TLabel;
    edUsername: TEdit;
    edServer: TEdit;
    edPassword: TEdit;
    butTest: TButton;


	procedure TestOnClick(Sender: TObject);	
	begin
		WizardForm.Enabled := false;

		myserver := edServer.Text;
		myuser := edUsername.Text;
		mypwd := edPassword.Text;
		
		SetControlCursor(WizardForm, crHourGlass);
		ConnectServer( myserver, myuser , mypwd  );
		SetControlCursor(WizardForm, crDefault);
		
		WizardForm.Enabled := true;
	end;

  procedure LoginODBC_Activate(Page: TWizardPage);
  begin
  end;

  function LoginODBC_ShouldSkipPage(Page: TWizardPage): Boolean;
  begin
    Result := False;
  end;

  function LoginODBC_BackButtonClick(Page: TWizardPage): Boolean;
  begin
    Result := True;
  end;

  function LoginODBC_NextButtonClick(Page: TWizardPage): Boolean;
  begin
    Result := True;
  end;

  procedure LoginODBC_CancelButtonClick(Page: TWizardPage; var Cancel, Confirm: Boolean);
  begin
  end;

  function LoginODBC_CreatePage(PreviousPageId: Integer): Integer;
  var
    Page: TWizardPage;
  begin
    Page := CreateCustomPage(
      PreviousPageId,
      ExpandConstant('{cm:LoginODBCCaption}'),
      ExpandConstant('{cm:LoginODBCDescription}')
    );

    { lbUsername }
    lbUsername := TLabel.Create(Page);
    with lbUsername do
    begin
      Parent := Page.Surface;
      Left := ScaleX(32);
      Top := ScaleY(80);
      Width := ScaleX(48);
      Height := ScaleY(13);
      Caption := 'Username';
      FocusControl := edUsername;
    end;

    { lbServer }
    lbServer := TLabel.Create(Page);
    with lbServer do
    begin
      Parent := Page.Surface;
      Left := ScaleX(32);
      Top := ScaleY(16);
      Width := ScaleX(32);
      Height := ScaleY(13);
      Caption := 'Server';
      FocusControl := edServer;
    end;

    { lbPassword }
    lbPassword := TLabel.Create(Page);
    with lbPassword do
    begin
      Parent := Page.Surface;
      Left := ScaleX(32);
      Top := ScaleY(144);
      Width := ScaleX(46);
      Height := ScaleY(13);
      Caption := 'Password';
      FocusControl := edPassword;
    end;

    { edUsername }
    edUsername := TEdit.Create(Page);
    with edUsername do
    begin
      Parent := Page.Surface;
      Left := ScaleX(32);
      Top := ScaleY(96);
      Width := ScaleX(325);
      Height := ScaleY(21);
      AutoSize := False;
      TabOrder := 1;
      Text := 'sa';
    end;

    { edServer }
    edServer := TEdit.Create(Page);
    with edServer do
    begin
      Parent := Page.Surface;
      Left := ScaleX(32);
      Top := ScaleY(32);
      Width := ScaleX(329);
      Height := ScaleY(21);
      AutoSize := False;
      TabOrder := 0;
      Text := 'localhost';
    end;

    { edPassword }
    edPassword := TEdit.Create(Page);
    with edPassword do
    begin
      Parent := Page.Surface;
      Left := ScaleX(32);
      Top := ScaleY(160);
      Width := ScaleX(325);
      Height := ScaleY(21);
      AutoSize := False;
      PasswordChar := '*';
      TabOrder := 2;
    end;

	// butTest
	butTest := TButton.Create(Page);
    with butTest do
    begin
      Parent := Page.Surface;
      Left := ScaleX(288);
      Top := ScaleY(192);
      Width := ScaleX(73);
      Height := ScaleY(25);
      Caption := 'Test';
      TabOrder := 3;
	  OnClick := @TestOnClick;
    end;


    with Page do
    begin
      OnActivate := @LoginODBC_Activate;
      OnShouldSkipPage := @LoginODBC_ShouldSkipPage;
      OnBackButtonClick := @LoginODBC_BackButtonClick;
      OnNextButtonClick := @LoginODBC_NextButtonClick;
      OnCancelButtonClick := @LoginODBC_CancelButtonClick;
    end;

    Result := Page.ID;
  end;
Personal tools
Ads: