Easily Integrate SingleStore Data in Lazarus Pascal IDE



Learn how to easily integrate live SingleStore data in Lazarus Pascal IDE using the CData ODBC Driver for real-time insights.

Lazarus Pascal IDE is a powerful, free, open-source development environment for building cross-platform applications. With the CData ODBC Driver for SingleStore, you can seamlessly integrate and query live SingleStore data, bringing real-time insights within your Lazarus Pascal IDE applications to enhance reporting, dashboards, and workflows.

This guide provides a comprehensive walkthrough, from setting up the connection to leveraging live SingleStore in Lazarus Pascal IDE. Whether you're building reports, dashboards, or complex workflows, you'll gain the tools to unlock real-time insights and functionality within your applications.

Overview

Here is an overview of the steps:

  1. Configure the DSN for SingleStore data in the CData ODBC Driver for SingleStore with the necessary connection properties.
  2. Set up the ODBC connection in Lazarus by configuring the TSQLConnector, TSQLQuery, TDataSource, and TDBGrid components with the required details.
  3. Test the connection to SingleStore data by writing sample code on the Main form.
  4. Compile and run the application to verify the successful integration and display of live SingleStore data.

Prerequisites

Before starting, ensure you have the following:

  • Lazarus IDE (version 3.4 recommended). Download from here.
  • CData ODBC Driver for SingleStore. Download and try the latest version from here.


Configure the SingleStore DSN Using the CData ODBC Driver

To start, configure the DSN (Data Source Name) for SingleStore data in your system using the CData ODBC Driver for SingleStore. Download and install a 30-day free trial with all the features from here.

Once installed, launch the ODBC Data Source Administrator:

  • On Windows: Search for ODBC Data Source Administrator in the Start menu and open the application.
  • On Mac: Open Applications, go to Utilities, and select ODBC Manager.
  • On Linux: Use the command line to launch ODBC Data Source Administrator or use unixODBC if installed.

Once launched, double-click on the CData SingleStore data Source and enter the required values to establish a connection:

The following connection properties are required in order to connect to data.

  • Server: The host name or IP of the server hosting the SingleStore database.
  • Port: The port of the server hosting the SingleStore database.
  • Database (Optional): The default database to connect to when connecting to the SingleStore Server. If this is not set, tables from all databases will be returned.

Connect Using Standard Authentication

To authenticate using standard authentication, set the following:

  • User: The user which will be used to authenticate with the SingleStore server.
  • Password: The password which will be used to authenticate with the SingleStore server.

Connect Using Integrated Security

As an alternative to providing the standard username and password, you can set IntegratedSecurity to True to authenticate trusted users to the server via Windows Authentication.

Connect Using SSL Authentication

You can leverage SSL authentication to connect to SingleStore data via a secure session. Configure the following connection properties to connect to data:

  • SSLClientCert: Set this to the name of the certificate store for the client certificate. Used in the case of 2-way SSL, where truststore and keystore are kept on both the client and server machines.
  • SSLClientCertPassword: If a client certificate store is password-protected, set this value to the store's password.
  • SSLClientCertSubject: The subject of the TLS/SSL client certificate. Used to locate the certificate in the store.
  • SSLClientCertType: The certificate type of the client store.
  • SSLServerCert: The certificate to be accepted from the server.

Connect Using SSH Authentication

Using SSH, you can securely login to a remote machine. To access SingleStore data via SSH, configure the following connection properties:

  • SSHClientCert: Set this to the name of the certificate store for the client certificate.
  • SSHClientCertPassword: If a client certificate store is password-protected, set this value to the store's password.
  • SSHClientCertSubject: The subject of the TLS/SSL client certificate. Used to locate the certificate in the store.
  • SSHClientCertType: The certificate type of the client store.
  • SSHPassword: The password that you use to authenticate with the SSH server.
  • SSHPort: The port used for SSH operations.
  • SSHServer: The SSH authentication server you are trying to authenticate against.
  • SSHServerFingerPrint: The SSH Server fingerprint used for verification of the host you are connecting to.
  • SSHUser: Set this to the username that you use to authenticate with the SSH server.

Create a New GUI Project

  1. Launch the Lazarus IDE.
  2. Navigate to File > New > Application to create a new GUI project with a Main form.

Add Required Connection Components to the Form

  1. Go to View > Component > Palette and check the Keep open at the bottom-left to keep the Components window persistently open.
  2. Search for the following components and add them to the Main form Form1 by clicking on Use at the bottom right.
    1. TSQLConnector
    2. TSQLTransaction
    3. TSQLQuery
    4. TDataSource
    5. TDBGrid


Configure the TSQLConnector Component

The TSQLConnector component in Lazarus Pascal IDE facilitates connections between your application and various databases or database APIs. Here's how to configure it:

  1. Select the TSQLConnector component on the Main form.
  2. Open the Object Inspector if it's not already open by going to View > Object Inspector
  3. In the Object Inspector on the left, set the following properties under the Properties section:
    Property Value
    Connected True
    Driver ODBC
    DatabaseName Your DSN name (e.g., CData SingleStore Source)
    UserName Your database username
    Password Your database password
    HostName SingleStore URL or localhost for local databases
    LoginPrompt False
    Transaction Select your TSQLTransaction component

If your credentials require a security token, follow these steps:

  1. Double-click the Main Form in the Lazarus Pascal IDE to open the code editor.
  2. Locate the procedure TForm1.FormCreate(Sender: TObject); section.
  3. Paste the following code below the begin keyword. Replace 'your_security_token' with your actual security token: SQLConnector1.Params.Values ['Password'] := SQLConnector1.Params.Values ['Password'] + 'your_security_token';



Configure the TSQLQuery Component

The TSQLQuery component in Lazarus Pascal IDE receives SQL code for queries and retrieves data from a database, simplifying data manipulation. Configure using the following info:

  1. Select the TSQLQuery component in the Main form.
  2. Set the following properties in the Object Inspector under the Properties section:
    Property Value
    Active True
    DataBase Select the name of your TSQLConnector component
    SQL (Click 3 dots) Your query (e.g., SELECT * FROM Orders)
    Transaction Your TSQLTransaction component




Configure the TDataSource, TDBGrid, TSQLTransaction Components

The TDataSource component in Lazarus Pascal IDE bridges a dataset (e.g., TSQLQuery) with data-aware controls like TDBGrid, allowing data to be displayed and manipulated. The TDBGrid presents the data in a tabular format, while the TSQLTransaction ensures database operations maintain data integrity by executing within a transaction. Here's how to configure all three components:

  1. Select the TDataSource component and set its DataSet property to the name of your TSQLQuery component.
  2. Select the TDBGrid component and set its DataSource property to your TDataSource component.
  3. Select the TSQLTransaction component. Set the following properties:
    1. Active: True
    2. Database: The name of your TSQLConnector component


Add Code to Test the Connection

To successfully test the connection, use the following code:

  1. Double-click on the Main Form to open up the Source Editor.
  2. Add the following code under the begin keyword and before the end. keyword.
procedure TForm1.FormCreate(Sender: TObject); begin // Code for your Security Token SQLConnector1.Params.Values ['Password'] := SQLConnector1.Params.Values ['Password'] + 'your_security_token'; try SQLConnector1.Open; ShowMessage('Connection successful!'); except on E: Exception do ShowMessage('Error: ' + E.Message); end; try SQLQuery1.Open; if SQLQuery1.IsEmpty then ShowMessage('No data found.') else ShowMessage('Data loaded successfully.'); except on E: Exception do ShowMessage('Error: ' + E.Message); end; end; end.

Compile and Run the Application

Save your project. Go to Run > Compile, then Run, or use the shortcut F9 to execute your application.



Next Steps: Play with the Data

Now that your data is successfully imported into Lazarus, here are some ideas to explore its full potential:

  • Enhance Your TDBGrid: Customize your TDBGrid to enable sorting, editing, or highlighting specific rows.
  • Visualize Your Data: Use components like TChart to create graphs and charts for intuitive data representation.
  • Filter and Search: Use components like TDBFilter or implement custom filtering logic to allow users to quickly focus on relevant data. Add a TEdit component for user input and link it to your filtering functionality.
  • Export Your Data: Utilize TSVExport or TFileStream components to save your data in formats like CSV or Excel for sharing or integration into other systems.
  • Perform Calculations: Use TFPCustomDataSet or your dataset's built-in features to calculate totals, averages, or other metrics. Display results in a TLabel or add them to your TDBGrid.
  • Create Forms and Reports: Design interactive forms using TForm and TDBEdit for user interaction. For reports, integrate components like RLReport or FastReport to generate and print professional-quality reports.

Take your data-driven application to the next level with these enhancements!



Simplify Data Connectivity in Lazarus with CData

Unlock the full potential of live SingleStore data directly within Lazarus Pascal. Streamline your workflows, enhance productivity, and experience seamless integration like never before.

Start your free trial today and transform the way you connect and work with your data!

Ready to get started?

Download a free trial of the SingleStore ODBC Driver to get started:

 Download Now

Learn more:

SingleStore Icon SingleStore ODBC Driver

The SingleStore ODBC Driver is a powerful tool that allows you to connect with live data from SingleStore, directly from any applications that support ODBC connectivity.

Access SingleStore data like you would a database - read, write, and update SingleStore. through a standard ODBC Driver interface.