numerating available SQL Servers. Retrieving databases on a SQL Server (Views: 40)
Problem/Question/Abstract: how to create your own connection dialog for a SQL Server database. Full Delphi source code for getting the list of available MS SQL Servers (on a network) and listing database names on a Server. Answer: If you are doing database development with Delphi and Microsoft's SQL Server is your choice (I'm thinking of using Delphi over ADO and OLE DB), then creating connection strings for a particular database on a particular SQL Server is what you do every day. The ConnectionString property of the TAdoConnection component is pretty easy to construct if you know the name of the SQL Server and the database you are trying to connect to. On the other hand, if your application needs to connect to different databases on various Servers, then having a custom dialog form that enables your users to pick a database to connect to, is much more appropriate. This article and the accompanying Delphi code shows how to enumerate the list of SQL Servers and databases that a particular SQL server has. Open SQL Server Form Here's the idea: mimic the PromptDataSource function (calls the standard OLE DB Data Link Properties dialog box) found in the ADODB unit, but only display the Connection Tab for SQL Server. Our custom dialog should only return the connection string to a specified database on a selected Server. The result of this modal dialog is the simplest form of the connection string for connecting to a database on a MS SQL Server for use with the TAdoConnection component. The string returned by the class function named Execute is valid for a connection either using the integrated Windows security or using the custom login / password information. The result will be the string similar to either Data Source=Home;Initial Catalog=MyDatabase;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; where "Home" is the name of the MS SQL Server instance and "MyDatabase" is the name of the database, or Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=DataServer;Initial Catalog=DelphiWeb;uid=sa;pwd=adminpwd; where "DataServer" is the name of the SQL Server, "DelphiWeb" is the name of the database; "sa" is the login name, and "adminpwd" is the password for the "sa" user. Here's how to call the OpenSQLServerForm form's Execute method (screen capture below displays the result): procedure TCallingForm.btnConnectClick(Sender:TObject); begin Memo1.Text := TOpenSQLServerForm.Execute; //Just testing //PromptDataSource(Handle, Memo1.Text); end; Note: you can download the full source code and alter it to suit your needs. In general, the Execute method creates the dialog (modal form) and starts by populating a drop down list with all available SQL Servers on the network. When the OK button is clicked the Execute method returns the connection string for a specified database. We'll now only explore the two main methods found in the code, notably the code that enumerates network-visible instances of Microsoft SQL Server; and the code that gets all the databases on a particular SQL Server. List Available SQL Servers The ListAvailableSQLServers fills in the TStrings object passed as the only argument. This method retrieves all available SQL Server instances on a network. uses DB, ADODB, Variants, ActiveX, ComObj, AdoInt, OleDB; ... procedure ListAvailableSQLServers(Names : TStrings); var RSCon: ADORecordsetConstruction; Rowset: IRowset; SourcesRowset: ISourcesRowset; SourcesRecordset: _Recordset; SourcesName, SourcesType: TField; function PtCreateADOObject (const ClassID: TGUID): IUnknown; var Status: HResult; FPUControlWord: Word; begin asm FNSTCW FPUControlWord end; Status := CoCreateInstance( CLASS_Recordset, nil, CLSCTX_INPROC_SERVER or CLSCTX_LOCAL_SERVER, IUnknown, Result); asm FNCLEX FLDCW FPUControlWord end; OleCheck(Status); end; begin SourcesRecordset := PtCreateADOObject(CLASS_Recordset) as _Recordset; RSCon := SourcesRecordset as ADORecordsetConstruction; SourcesRowset := CreateComObject(ProgIDToClassID('SQLOLEDB Enumerator')) as ISourcesRowset; OleCheck(SourcesRowset.GetSourcesRowset( nil, IRowset, 0, nil, IUnknown(Rowset))); RSCon.Rowset := RowSet; with TADODataSet.Create(nil) do try Recordset := SourcesRecordset; SourcesName := FieldByName('SOURCES_NAME'); SourcesType := FieldByName('SOURCES_TYPE'); Names.BeginUpdate; try while not EOF do begin if (SourcesType.AsInteger = DBSOURCETYPE_DATASOURCE) and (SourcesName.AsString <> '') then Names.Add(SourcesName.AsString); Next; end; finally Names.EndUpdate; end; finally Free; end; end; List database names on a SQL Server The DatabasesOnServer function populates the Databases string list passed as the only argument. procedure TOpenSQLServerForm. DatabasesOnServer(Databases : TStrings); var rs : _RecordSet; begin Databases.Clear; with TAdoConnection.Create(nil) do try //simple ConnectionString without the DB name ConnectionString := ConnStr; LoginPrompt := False; try Open; rs := ConnectionObject.OpenSchema( adSchemaCatalogs, EmptyParam, EmptyParam); with rs do begin try Databases.BeginUpdate; while not Eof do begin Databases.Add( VarToStr( Fields['CATALOG_NAME'].Value)); MoveNext; end; finally Databases.EndUpdate; end; end; Close; except on e:exception do MessageDlg(e.Message,mtError, [mbOK],0); end; finally Free; end; end; |