Connect LibreOffice Calc to SingleStoreDB using ODBC
Tags: singlestoredb, libreoffice, libreofficecalc, odbc
Published: 2022-11-12 18:34:12

## Abstract
There are many methods available to connect to SingleStoreDB, and some of these methods we have discussed in [previous articles](https://dev.to/veryfatboy). 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](https://gist.github.com/VeryFatBoy/587479244e6f44c6578161c0e5db3e8d).

## Introduction
This article used a Virtual Machine running Ubuntu 20.04.3. If you are using Apple macOS or Microsoft Windows, the [documentation](https://docs.singlestore.com/managed-service/en/developer-resources/connect-with-application-development-tools/connect-with-odbc/the-singlestore-odbc-driver.html) contains installation instructions for these other platforms.

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/veryfatboy/quick-tip-using-dbt-with-singlestoredb-161g) 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](https://gist.github.com/VeryFatBoy/587479244e6f44c6578161c0e5db3e8d) to create the database, table and populate the table. We'll use the [Iris flower data set](https://en.wikipedia.org/wiki/Iris_flower_data_set).

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

```shell
sudo apt update

sudo apt install libreoffice-base
```

## Install and Configure SingleStore ODBC Driver
First, we need to install UnixODBC and OpenSSL, as follows:

```shell
sudo apt install unixodbc

sudo apt install libssl-dev
```

Next, we'll [download](https://github.com/memsql/singlestore-odbc-connector/releases) and unpack the latest version of the SingleStore ODBC Driver that, at the time of writing this article, was `singlestore-connector-odbc-1.1.1-debian10-amd64.tar.gz`:

```shell
tar xzvf singlestore-connector-odbc-1.1.1-debian10-amd64.tar.gz
```

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

```shell
libssodbca.so  libssodbcw.so  README.md
```

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 Driver]
Description=SingleStore ODBC Driver
Driver=/path/to/libssodbcw.so
```

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:

```shell
sudo odbcinst -i -d -f SingleStore_odbc_driver_template.ini
```

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 Driver
SERVER=<host>
USER=admin
PASSWORD=<password>
DATABASE=iris_db
PORT=3306
```

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:

```shell
sudo odbcinst -i -s -l -f SingleStore_odbc_data_source_template.ini
```

We can test the connection, as follows:

```shell
isql SingleStore-server
```

The output should be:

```
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
```

## 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.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ybp9m6mgmrkhoqq7fcbj.png)

<center>Figure 1. Connect to an existing database.</center>

We'll click **Next &gt;**.

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

![Figure 2. Choose a data source.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/okpvqj89n278eky5wmrq.png)

<center>Figure 2. Choose a data source.</center>

We'll click **OK**, and then **Next &gt;**.

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.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/owbjs2e9oal1g6ewnrgy.png)

<center>Figure 3. Authentication Required.</center>

We'll click **OK** and the connection should be successful. We'll click **OK** and then **Next &gt;**.

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

![Figure 4. Save and Proceed.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f649iq6wildt5oldh4l2.png)

<center>Figure 4. Save and Proceed.</center>

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

![Figure 5. Save.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hsk5q0qx86ynqgvtg4oh.png)

<center>Figure 5. Save.</center>

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

![Figure 6. iris_db.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hs0ehs1x8wlbg9lpvnn9.png)

<center>Figure 6. iris_db.</center>

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.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/78dqzc34v9zmiyo8z1rs.png)

<center>Figure 7. Queries.</center>

In the query text box, we'll enter:

```sql
SELECT * FROM iris;
```

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

![Figure 8. Run Query.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1isar6nnz9pu9srqym84.png)

<center>Figure 8. Run Query.</center>

The output should be as shown in Figure 9.

![Figure 9. Run Query Output.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qq5ez44lkpy7jr0nrvow.png)

<center>Figure 9. Run Query Output.</center>

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.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qyantuwhz7dhna9ruqlr.png)

<center>Figure 10. Select All.</center>

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.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h3ufvruud2xoeicd3558.png)

<center>Figure 11. Output copied to LibreOffice Calc.</center>

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 &gt; 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.