DEV Community

Cover image for Connect LibreOffice Calc to SingleStoreDB using ODBC
Akmal Chaudhri for SingleStore

Posted on • Edited on

Connect LibreOffice Calc to SingleStoreDB using ODBC

Abstract

There are many methods available to connect to SingleStoreDB, and some of these methods we have discussed in previous articles. This article will show how to install and use the SingleStore ODBC Driver. We'll use LibreOffice Calc as an example application that uses an ODBC connection to SingleStoreDB.

The SQL code used in this article is available in a GitHub Gist.

Introduction

This article used a Virtual Machine running Ubuntu 22.04.2. If you are using Apple macOS or Microsoft Windows, the documentation contains installation instructions for these other platforms.

Create a SingleStoreDB Cloud account

A previous article showed the steps required to create a free SingleStoreDB Cloud account. We'll use ODBC Demo Group as our Workspace Group Name and odbc-demo as our Workspace Name. We'll make a note of our password and host name.

Create the database and table

We can use the SQL code in a GitHub Gist to create the database, table and populate the table. We'll use the Iris flower data set.

Install LibreOffice Base

Database connections are possible using LibreOffice Base. Base can be installed, as follows:

sudo apt update

sudo apt install libreoffice-base
Enter fullscreen mode Exit fullscreen mode

Install and Configure SingleStore ODBC Driver

To the file /etc/apt/sources.list we'll add:

deb http://security.ubuntu.com/ubuntu focal-security main
Enter fullscreen mode Exit fullscreen mode

First, we need to install UnixODBC and OpenSSL, as follows:

sudo apt update

sudo apt install unixodbc odbcinst libssl1.1
Enter fullscreen mode Exit fullscreen mode

Next, we'll download and unpack the latest version of the SingleStore ODBC Driver that, at the time of writing this article, was singlestore-connector-odbc-1.1.6-debian10-amd64.tar.gz:

tar xzvf singlestore-connector-odbc-1.1.6-debian10-amd64.tar.gz
Enter fullscreen mode Exit fullscreen mode

In the directory created by this command, we'll see three files:

libssodbca.so  libssodbcw.so  README.md
Enter fullscreen mode Exit fullscreen mode

The two different drivers are identified as follows:

  • libssodbca.so: ANSI driver
  • libssodbcw.so: Unicode driver

We'll use the Unicode driver in this article.

We'll create a template file, SingleStore_odbc_driver_template.ini, as follows:

[SingleStore ODBC Unicode Driver]
Description=SingleStore ODBC Unicode Driver
Driver=/path/to/libssodbcw.so
Enter fullscreen mode Exit fullscreen mode

We'll replace /path/to/ with the actual path to the driver file.

Next, we'll install the template file to /etc/odbcinst.ini, as follows:

sudo odbcinst -i -d -f SingleStore_odbc_driver_template.ini
Enter fullscreen mode Exit fullscreen mode

In another template file, SingleStore_odbc_data_source_template.ini, we'll configure a Data Source Name (DSN), as follows:

[SingleStore-server]
Description=SingleStore server
Driver=SingleStore ODBC Unicode Driver
SERVER=<host>
USER=admin
PASSWORD=<password>
DATABASE=iris_db
PORT=3306
Enter fullscreen mode Exit fullscreen mode

We'll replace the <host> and <password> with the values from our SingleStoreDB Cloud account.

Next, we'll install the template file to /etc/odbc.ini, as follows:

sudo odbcinst -i -s -l -f SingleStore_odbc_data_source_template.ini
Enter fullscreen mode Exit fullscreen mode

We can test the connection, as follows:

isql SingleStore-server
Enter fullscreen mode Exit fullscreen mode

The output should be:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
Enter fullscreen mode Exit fullscreen mode

Configure LibreOffice Base

We'll launch LibreOffice Base and select Connect to an existing database > ODBC, as shown in Figure 1.

Figure 1. Connect to an existing database.

Figure 1. Connect to an existing database.

We'll click Next >.

We'll choose Browse..., select SingleStore-server as the data source, as shown in Figure 2.

Figure 2. Choose a data source.

Figure 2. Choose a data source.

We'll click OK, and then Next >.

We'll enter admin as the User name and check (✔) Password required and click Test Connection. In the Authentication Required box, we'll enter our password, as shown in Figure 3.

Figure 3. Authentication Required.

Figure 3. Authentication Required.

We'll click OK and the connection should be successful. We'll click OK and then Next >.

We'll take the defaults on the next page, as shown in Figure 4, and then click Finish.

Figure 4. Save and Proceed.

Figure 4. Save and Proceed.

On the next page, we'll enter iris_db into the Name field, as shown in Figure 5, and click Save.

Figure 5. Save.

Figure 5. Save.

The database will open and we'll see iris_db, as shown in Figure 6.

Figure 6. iris_db.

Figure 6. iris_db.

From the left-hand navigation pane, we'll select Queries and then Create Query in SQL View..., as shown in Figure 7.

Figure 7. Queries.

Figure 7. Queries.

In the query text box, we'll enter:

SELECT * FROM iris;
Enter fullscreen mode Exit fullscreen mode

and click the Run Query (F5) icon, as shown in Figure 8.

Figure 8. Run Query.

Figure 8. Run Query.

The output should be as shown in Figure 9.

Figure 9. Run Query Output.

Figure 9. Run Query Output.

We'll now launch LibreOffice Calc, and from the Run Query Output window shown in Figure 9, we'll click the top left corner to select all the output, as shown in Figure 10.

Figure 10. Select All.

Figure 10. Select All.

We'll then drag the top left corner from Figure 10 to a cell in LibreOffice Calc, as shown in Figure 11.

Figure 11. Output copied to LibreOffice Calc.

Figure 11. Output copied to LibreOffice Calc.

In LibreOffice Calc, we can now perform analysis of the data, create charts, and so on. If there are changes to the iris table in SingleStoreDB, we can update the spreadsheet using Data > Refresh Range from the menu bar.

Summary

In this article, we have seen how to install, configure and use the SingleStore ODBC Driver. We have also seen how to connect a popular open-source spreadsheet to a SingleStoreDB database.

Top comments (0)