Tutorial: Using TSQLMonitor with an ODBC Connection

From RAD Studio
Jump to: navigation, search

Go Up to Database and LiveBindings Tutorials


TSQLMonitor offers support for ODBC connections.

The following tutorial shows how TSQLMonitor can be used with an ODBC connection. The ODBC establishes a connection to an Interbase database. The example is a VCL Form Applications that contains 3 controls: TSQLConnection, TSQLMonitor, and TButton. When the button is clicked, a table is created and populated with data, and then a simple select query is executed. Finally, the TraceList of the TSQLMonitor is saved to a file on disk.

Steps

Create the connection to the ODBC datasource

  1. Create an ODBC datasource. For more information, see: Microsoft documentation.
  2. From the Data Explorer, right-click Odbc, and select Add New Connection.
AddNewConnection.png
3. Select a name for the new connection and click OK. In this example, we will use ODBCINTERBASECONNECTION.
4. Right-click the newly created connection, and select Modify. Set the Database field to the name of the Datasource created with ODBC Data Source Administrator, and the credentials needed to connect to the database.
ModifyConn.png
5. Click Test Connection. If the connections has been properly set, a dialog box stating Test connection succeeded should be displayed.

The application

  1. Select File > New > VCL Forms Application - Delphi.
  2. Add the following controls the form: a TSQLConnection, a TSQLMonitor, and a TButton.
  3. Add the following code to the OnClick event handler of the TButton control:
procedure TForm2.Button1Click(Sender: TObject);
begin
  try
    Connect;
    SQLMonitor1.SQLConnection := SQLConnection1;
    // Activate the Monitor
    SQLMonitor1.Active := True;
    ExecuteQueries;
    // Write the trace list of the TSQLMonitor to a file on disk
    SQLMonitor1.SaveToFile('D:\\Log.txt');
  except
    on E: Exception do
      ShowMessage('Exception raised with message: ' + E.Message);
  end;
end;

Here is the code for the functions called in the OnClick event handler of the TButton control:

// establishes the connection to the ODBC datasource
procedure TForm2.Connect;
begin
  SQLConnection1 := TSQLConnection.Create(nil);
  // the name of the connection created in the data explorer
  SQLConnection1.ConnectionName := 'odbcinterbaseconnection';
  SQLConnection1.LoginPrompt := False;
  SQLConnection1.LoadParamsOnConnect := True;
  SQLConnection1.Connected := True;
end;

// Executes several queries on the database.
procedure TForm2.ExecuteQueries;
var
  Query: String;
begin
  try
    if SQLConnection1.Connected then
    begin
      Query := 'CREATE TABLE ExampleTable(id INTEGER, name VARCHAR(50))';
      SQLConnection1.Execute(Query, nil);
      Query := 'INSERT INTO ExampleTable VALUES(1,''test1'')';
      SQLConnection1.Execute(Query, nil);
      Query := 'INSERT INTO ExampleTable VALUES(2,''test2'')';
      SQLConnection1.Execute(Query, nil);
      Query := 'INSERT INTO ExampleTable VALUES(3,''test3'')';
      SQLConnection1.Execute(Query, nil);
      Query := 'SELECT * FROM ExampleTable';
      SQLConnection1.Execute(Query, nil);
    end;
  except
    on E: Exception do
      ShowMessage('Exception raised with message: ' + E.Message);
  end;
end;

See Also