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.
I managed to connect to the database with LibreOffice Base 220.127.116.11 - 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?
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.
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.
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:
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?
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?
As suggested I’ve tried using ODBC Driver for Microsoft SQL Server for Linux.
This one to be precise:
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.
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.