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
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
First, we need to install UnixODBC and OpenSSL, as follows:
sudo apt update
sudo apt install unixodbc odbcinst libssl1.1
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
In the directory created by this command, we'll see three files:
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 Unicode Driver]
Description=SingleStore ODBC Unicode 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:
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 Unicode 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:
sudo odbcinst -i -s -l -f SingleStore_odbc_data_source_template.ini
We can test the connection, as follows:
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.
We'll click Next >.
We'll choose Browse..., select SingleStore-server as the data source, as shown in Figure 2.
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.
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.
On the next page, we'll enter iris_db into the Name field, as shown in Figure 5, and click Save.
The database will open and we'll see iris_db, as shown in Figure 6.
From the left-hand navigation pane, we'll select Queries and then Create Query in SQL View..., as shown in Figure 7.
In the query text box, we'll enter:
SELECT * FROM iris;
and click the Run Query (F5) icon, as shown in Figure 8.
The output should be as shown in Figure 9.
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.
We'll then drag the top left corner from Figure 10 to a cell in LibreOffice Calc, as shown in Figure 11.
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)