SQL Server - Test connection with ODBC driver
From ISXKB
(Difference between revisions)
(SQL Server - Test connection with ODBC driver) |
Current revision (11:49, 10 January 2007) (view source) m (added image) |
||
| Line 1: | Line 1: | ||
| - | 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. | + | [[Image:TestODBCConn.png|thumb|200px]]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. | 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. | ||
Current revision
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;
