Introduction
I need to prepare a complex report from a big table database. I’m running Linux Mint 18.3, the database resides on a Microsoft SQL Server. I have success with sqsh - I can run queries and I have even mirrored all tables to local CSV files, but I need to be able to access the database in a more user-friendly environment.
I have to explore table relationships and reverse-engineer the database structure to be able to produce that report.
As I’m learning SQL as I go, I don’t really feel I can get any further using just sqsh for my exploration.
ODBC
I managed to connect to the database with LibreOffice Base 5.1.6.2 - I can list the tables but trying to open them I get SQL syntax errors:
Maybe I should set some flags somewhere?
I have installed LibreOffice 6.0. Whe nI open the same database file I created with LibreOffice 5 - it prompts me for user and password, but then it fails to load the ODBC driver:
Even though it’s present in my system:
$ file '/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so'
/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, BuildID[sha1]=307e2b20ed1be01cab757c3ecd4d6e85f4a18b7b, stripped
I’ve tried creating a new database file to define the connection anew, but it fails the same way when I press “Test Connection” - so that’s not a thing with loading LO 5 file.
I’ve tried preloading that library:
$ LD_PRELOAD="/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so" libreoffice6.0
ERROR: ld.so: object '/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so' from LD_PRELOAD cannot be preloaded (wrong ELF class: ELFCLASS64): ignored.
Maybe I should reinstall the ODBC driver?
JDBC
I tried using JDBC instead of ODBC to avoid this problem. From what I found Microsoft SQL Server has JDBC support since version 2008, so I should be able to use that driver.
However - I have no idea how to configure that. I got the JRE installed and the Microsoft JDBC driver .jre file added to class path. I’ve tried various things and dug on forums but didn’t have any success:
What should I put in these fields? I am really out of ideas for now.
Apache OpenOffice
I’ve tried the ODBC connection with another package, but it has the same behaviour. However, it showned me more detail about the problem:
The SQL command leading to this error is:
SELECT * FROM "DATABASE"."dbo"."A4B_IntTmpE_DBA_View"
I’ve tried this exact query with sqsh and it worked just fine. Not sure why LibreOffice chokes on that.
Bugs?
I have a feeling that what I am struggling with often is cause by some problems in the software, so I have filed two bug reports:
https://bugs.documentfoundation.org/show_bug.cgi?id=117798
https://bugs.documentfoundation.org/show_bug.cgi?id=117800
Finale
I’ve put many hours now into trying to solve this. I really need to make some progress finally. Any help will be greatly appreciated. Maybe I’m just doing something stupid here? Or maybe there are other open-source tools that I can use to explore an MSSQL database?
PS
It’s strange but I realized I can create Queries and Reports - Base lists all the keys in the Tables, but so far wasn’t able to evaluate any query without a syntax error near the database name. Maybe I should not prove the database name in my /etc/odbc.ini configuration file?
PPS
As suggested I’ve tried using ODBC Driver for Microsoft SQL Server for Linux.
This one to be precise:
https://packages.microsoft.com/ubuntu/17.10/prod/pool/main/m/msodbcsql17/msodbcsql17_17.1.0.1-1_amd64.deb
Unfortunately Libre Office 5 fails to read the driver library file. It works with the FreeTDS driver though. LibreOffice 6 fails with both drivers in the same way.
I’ve even tried running LibreOffice as Root, but that’s not making any difference.
In the image above - my ODBC configuration, error message form LibreOffice Base 5 and proof that the library file exists.
PPPS
LibreOffice 6 under Windows 10 works with no problems so far using Windows built-in ODBC configuration wizard, but I’d much prefer to have this work on Linux too.