Friday, September 27, 2024

 ODBC / JDBC Connectivity with LibreOffice Base on Debian 12

Below are some hurdles I had to jump over to get ODBC and JDBC working when connecting to mySQL server on Debian 12:

Download ODBC Drivers directly from dev.mysql.com: https://dev.mysql.com/downloads/connector/odbc/ 

Make sure you choose "Linux - Generic" under "Select Operating System". 

  • copy the file libmyodbc8a.so to /usr/lib/x86_64-linux-gnu/odbc/ 
  • copy the file libmyodbc8w.so to /usr/lib/x86_64-linux-gnu/odbc/ 
*The name/versions of these files may change with updates.

 then, create file  /etc/odbcinst.ini ⇒ sudo nano /etc/odbcinst.ini

[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc9a.so ‡
Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbc9w.so
FileUsage = 1
and create file /etc/odbc.ini ⇒ sudo nano /etc/odbc.ini 

[my-connector]
Description     = MySQL connection to  database
Driver          = MySQL (‡ see note below)
Database        = eik
Server          = localhost
User            = root
Password        = 
Port            = 3306
#Socket          = /opt/lampp/var/mysql/mysql.sock
Socket          = /run/mysqld/mysql.sock

echo "select 1" | isql -v my-connector
  1. Duplicate above for separate databases you wish to connect to e.g.
[anotherdb]
  Description     = MySQL connection to  database
  Driver          = Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc9a.so 
  Database        = testdb
  Server          = localhost
  User            = root
  Password        = 
  Port            = 3306
  Socket          = /run/mysqld/mysql.sock

  echo "select 1" | isql -v anotherdb
  1. Note the location of the mysqld may vary and the commented out string is for xampp
  2. You can find your running mysql daemon location with this command: sudo find / -type s
  3. Edit /etc/odbc.ini to include the correct path.
To connect to ODBC/JDBC in  LibreOffice Base:
  1. "Connect to an existing database": MySQL/MariaDB
  2. "Connect using ODBC (Open Database Connectivity"
  3. "Browse" (button in the next dialog should show the configured ODBC connections in /etc/odbc.ini).
  4. Select User Name: usually root
  5. Select Password required checkbox if password not blank.

If you get an error:

"Could not load the program library libodbc.so or it is corrupted. The ODBC data source selection is not available."

sudo apt-get install odbc-mdbtools

‡ If you get an error:

"[unixODBC][Driver Manager]Data source name not found and no default driver specified at ./connectivity/source/drivers/odbc/OTools.cxx:357"

Look in the file /etc/odbcinst.ini and copy Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc9a.so to Driver = ‡ in /etc/odbc.ini

ODBC doesn't display Japanese characters, but JDBC does!

(ODBCでは日本語の文字は表示されませんが、JDBCでは表示されます)

JDBC connect

  1. sudo apt install default-jdk
  2. sudo apt install libmariadb-java
  3. Download : https://dev.mysql.com/downloads/connector/j/ for Debian Linux
  4. sudo dpkg -i mysql-connector-j_9.0.0-1debian12_all.deb
  • Open LibreOffice Start Center (not Base) and set the path /usr/share/java tools->options->advanced->class path
  • Add : mariadb-java-client.jar as a class 
  •  I moved /usr/share/java/mariadb-java-client.jar to ~/Documents/JDBC folder

    • Database Connection Wizard (in LibreOffice Base):

    1. "Connect to an existing database": MySQL/MariaDB
    2. "Connect using JDBC (Java Database Connectivity"
    3. Enter Database Name:
    4. Server: localhost
    5. Port: 3306
    6. You should see org.mariadb.jdbc.Driver in the Window:
      MySQL/MariaDB JDBC Driver Class

     

    Notes:

    at No comments:
    Subscribe to: Posts (Atom)

     ODBC / JDBC Connectivity with LibreOffice Base on Debian 12

    Below are some hurdles I had to jump over to get ODBC and JDBC working when connecting to mySQL server on Debian 12: Download ODBC Driver...