Odbc help

[Kentechy]

I have a Firebird 2.5 database on a windows 10 machine.  I am able to use ODBC from other windows machines without any issue using the odbc admin tool in windows, the server:drive:/share/database path, and the login credentials.  Everything works fine.

Currently I have an apache server running on windows 10 that I odbc into the other window 10 machine that has the firebird database and I am able to pull out information that I can then use in php through odbc_connect to serve to our intranet.  I would like to have this all running on an Ubuntu machine.

This works fine so long as its windows, but once I try to do this on linux/ubuntu, the documentation I am finding is very lacking in this case.  I cannot find definite answers on if the odbc.ini and odcbinst.ini should be in the /etc or the /usr/lib directories.  I cannot find an answer on what exactly should be in those configs.  I cannot find an answer on how firebird odbc expects the path to be written...should it be mounted and pointed at the mount?  Should it use the ip/share syntax? Should it use the fqdn?  All the configs I see online use localhost, but this is not local.  

Does anyone know of a more thorough linux odbc to windows firebird 2.5 set of documentation somewhere?  

I have been trying for days and have not been successful in connecting the to database from Ubuntu.  It says the database is not found.

[Mark Rotteveel]

On 01-05-2021 17:25, Kentechy wrote:
> I have a Firebird 2.5 database on a windows 10 machine.  I am able to
> use ODBC from other windows machines without any issue using the odbc
> admin tool in windows, the server:drive:/share/database path, and the
> login credentials.  Everything works fine.

Firebird - at least by default - does not open database on a remote
share. It only opens database files on the same machine. So putting the
database in a location that is accessible as a network share is
generally wrong. Don't do that. It is a great way to accidentally
corrupt a database if the database file is opened or modified remotely,
and a good source of dataleaks because anyone with access to the share
can copy the file.

> Currently I have an apache server running on windows 10 that I odbc into
> the other window 10 machine that has the firebird database and I am able
> to pull out information that I can then use in php through odbc_connect
> to serve to our intranet.  I would like to have this all running on an
> Ubuntu machine.
>
> This works fine so long as its windows, but once I try to do this on
> linux/ubuntu, the documentation I am finding is very lacking in this
> case.  I cannot find definite answers on if the odbc.ini and
> odcbinst.ini should be in the /etc or the /usr/lib directories.  I
> cannot find an answer on what exactly should be in those configs.

The requirements for the location of the ODBC configuration are not
dictated by Firebird, but by the unixODBC library. As far as I
understand, the default location is /etc/odbc.ini and /etc/odbcinst.ini
and ~/.odbc.ini for user-specific DSNs, but the location might be
different depending on the distribution.

See also
https://firebirdsql.org/file/documentation/html/en/refdocs/fbodbc20/firebird-odbc-driver-20-manual.html#fbodbc205-linux-config

and

http://www.unixodbc.org/odbcinst.html

> I cannot find an answer on how firebird odbc expects the path to be
> written...should it be mounted and pointed at the mount?  Should it use
> the ip/share syntax? Should it use the fqdn?  All the configs I see
> online use localhost, but this is not local.

You should not use shares nor mount points for a Firebird database, you
should connect to a remote Firebird server process which opens the
database for you. You need to configure the URL as is correct for the
server you're connecting to.

If the database is on server dbserver.local and the database file on
that server is C:\path\to\database.fdb, then your connection string is

dbserver.local:C:\path\to\database.fdb (or use
dbserver.local:C:/path/to/database.fdb as you may need to escape \ to \\)

From dbserver.local you could also connect (assuming the server is
listening on all IP addresses of the host) using
localhost:C:\path\to\database.fdb)

In general it is advisable not to use file paths in the connection
string to your database, but instead define an alias in aliases.conf (or
for Firebird 3 and higher, databases.conf) and use that. That makes it
easier to move a database to a different location, or even change your
OS (e.g. from Windows to Linux).

For example add to aliases.conf:

mydb = C:\path\to\database.fdb

and then you can use the connection string dbserver.local:mydb instead
of dbserver.local:C:\path\to\database.fdb

> Does anyone know of a more thorough linux odbc to windows firebird 2.5
> set of documentation somewhere?
>
> I have been trying for days and have not been successful in connecting
> the to database from Ubuntu.  It says the database is not found.

I recommend you read the documentation I linked above, and if you still
have problems, then please share your current setup (where is your
server, what is its hostname, where is the database on that server), and
what you've tried.

Mark
--
Mark Rotteveel

[Kentechy]

Well, I was searching on Ubuntu 20 for libfbclient.so.2.0.5 so I wildcarded it to just libfbclient and found one in the /opt/firebird/lib that was 2.5.9.  I symlinked it to /usr/lib/lbgds.so and its working on Ubuntu 20 now.  

I feel that firebird could definitely do with some better documentation.

Thanks for the help.  Seems the same should work on Ubuntu Server, so I will install that, use the same processes I have done so far, install lampp, move my sql tables and php over, and then move on to another project.

[Kentechy]

I have been using those resources, along with many others.  The path I was using was the server.domain:drive:/path.  Apparently, something was wrong with the driver.  I redownloaded it and everything worked.  My actual path is qmsi-server.localdomain:c:/Ideal Share/Ideal for Wndows/IdealData/ICS00C1.GDB.  I thought since it was not working, something was wrong in the path and maybe I need to mount or create a link.  No problem though, in VM Ubuntu Server 16, this runs without any issues.

But, this was in a test environment on Ubuntu 16, which is now end of life.  I would like to get this working on a current LTS version of Ubuntu, but since the mapopa repo is not updated, 18+ versions of Ubuntu cannot pull from that repo and want me to use fb3.0 instead of 2.5.  

I tried installing the drivers and doing everything the same on an Ubuntu 20 desktop version, and I am getting the error: Unable to connect to data source: library 'libgds.so' failed to load.

I see one article about this saying the libfbclient.so.2 needs to be symlinked to libgds.so and that will make it work. But, it looks like fb3.0 does not have a libfbclient.so file, so I have nothing to link.

So my questions now are:

Can current LTS versions of Ubuntu ODBC into Windows machines running fb2.5?  

If so, how do I make that work when I cannot install anything from the mapopa repo?  

Is there some other file from 3.0 that I can symlink to make the odbc connection work?

Is the only option running an outdated version of Ubuntu?

On Saturday, May 1, 2021 at 1:32:15 PM UTC-5 ma...@lawinegevaar.nl wrote:

[Mark Rotteveel]

On 08-05-2021 19:39, Kentechy wrote:
> I see one article about this saying the libfbclient.so.2 needs to be
> symlinked to libgds.so and that will make it work. But, it looks like
> fb3.0 does not have a libfbclient.so file, so I have nothing to link.

Firebird 3 still has a libfbclient.so, I'm not sure what the layout is
on Ubuntu, but in the Linux build provided on the Firebird website, it's
in /opt/firebird/lib, and creates symlinks in /usr/lib64

Maybe someone with more knowledge of using Firebird on Linux knows more.

Mark
--
Mark Rotteveel

[Virgo Pärna]

On 2021-05-09 11:51, Mark Rotteveel wrote:
>
> Firebird 3 still has a libfbclient.so, I'm not sure what the layout is
> on Ubuntu, but in the Linux build provided on the Firebird website, it's
> in /opt/firebird/lib, and creates symlinks in /usr/lib64
>

In standard Debian packages (Ubuntu is based on Debian) libfbclient.so
is part of dev package. Client package only contains versionded so-s
(libfbclient.so.3.0.5).

--
Virgo Pärna
Gaiasoft OÜ
vi...@gaiasoft.ee

[Kentechy]

At least in Ubuntu Server 20.04 with firebird 3.0, /opt/firebird is not created.  The lib is in /usr/lib/x86_64-linux-gnu/firebird/3.0/

But this is getting so confusing now...I am at a loss why this is so made so difficult.

I can make the firebird isql connect, without renaming anything.  So in Ubuntu Server 20.04, I added the ppa:mapopa repo, installed the firebird3.0 server, and reconfigured.  The dev apparently was installed also along with the server (confirmed by apt list --installed | grep firebird).  I followed this guide: https://help.ubuntu.com/community/Firebird3.0

Then I installed unixodbc and unixodbc-dev and followed this guide for setting up the .ini files: https://www.firebirdsql.org/file/documentation/html/en/refdocs/fbodbc20/firebird-odbc-driver-20-manual.html

I did not do the build though, I just installed unixodbc through the repos.

odbc.ini and odbcinst.ini are both in /etc

odbc.ini (removed user and password for posting)

[FirebirdDSNtoUPS]

Description = Firebird

Driver = Firebird

Dbname = qmsi-server.localdomain:c:/Ideal Share/Ideal for Windows/IdealData/ICS100C1.GDB

User = xxxxxx

Password = xxxxxx

Role =

CharacterSet =

ReadOnly = Yes

NoWait = No

odbcinst.ini

[Firebird]

Description = Interbase/Firebird ODBC Driver

Driver = /usr/local/lib64/libOdbcFb.so

Setup = /usr/local/lib64/libOdbcFb.so

Threading = 1

FileUsage = 1

CPTimeout =

CPReuse =

I downloaded the linux driver from here: https://firebirdsql.org/en/odbc-driver/  (the x64 version) and placed it in /usr/local/lib64 

With this, isql -v FirebirdDSNtoUPS connects and works fine.  I am able to do queries and everything seems good.

But then, after installing apache2 and php (7.4), I am seeing a new problem that I have spent hours and hours on with no relief.

Any odbc queries I try running through php fail and give an error that they have vastly exceed the memory available...and its an outrageous amount...like 50 gigabytes for a query that should return 3 or four lines.

So I read this forum that says the issue in this is that the php version is made against a newer version of unixodbc while the driver for the database is made using an older one and the sqllen is wrong in the older one as even though it says 64, its using the wrong lengths: https://stackoverflow.com/questions/42132558/odbc-prepare-gives-fatal-error-allowed-memory-size-exhausted

That lead me to this exchange where a user received help from a firebird support group that said he should download the firebird odbc source and build / make /install it: https://www.mail-archive.com/firebird...@yahoogroups.com/msg20295.html, which also validates that the 64 bit firebird driver may have an issue.  His issue was a little different, but if the issue was stemming for the sqllen issue, then I figured it may solve my issue too.

I downloaded the source from https://github.com/FirebirdSQL/firebird-odbc-driver  and I had to go into the make file to change my include and lib directory to the /usr/lib/x86_64-linux-gnu/firebird/3.0 to get the build to compile, otherwise it would tell me that FBLIBDIR was unknown and stop.  So I changed my odbcinst.ini to reflect the path this build made of /usr/lib64/libOdbcFb.so and libOdbcFb.so.2 that were links to the x86_64-linux-gnu folder.  

isql still works connecting to FirebirdDSNtoUPS, but the php problem still exists and I cannot run any odbc_connects without the returns claiming to need massive amounts of memory.  That does lead me to believe the connection is being made, but something in the driver (likely still the sqllen) is not matching.

Everything I am using is the 64 versions and in most cases, like unixodbc, it installs based on the architecture anyway.

At this point, I am going to wipe the server again and try one more time.  Any help would be appreciated as I have spent....about 3 weeks on this...which is insane.

[Mark Rotteveel]

Please try asking on the firebird-odbc-devel list (subscribe at
https://sourceforge.net/p/firebird/mailman/) and filing a bug on
https://github.com/FirebirdSQL/firebird-odbc-driver.

Be aware, that list doesn't see a lot of traffic, and I haven't seen the
maintainer of the firebird-odbc driver lately.

Mark

--
Mark Rotteveel