Connecting to Firebird Database

Darth · December 29, 2020, 10:23pm

I’m trying to connect to an existing Firebird database with Lucee 5.3.7.47 on Windows 10.
I’ve tried creating a datasource and defining it in the Application.cfc with zero luck.
I can connect to MS SQL and My SQL fine. I’ve put the JAR files in Lucee for Firebird.

Here’s the connection string (it works in several other apps):
jdbc:firebirdsql://hub:3050/D:\SiteWatch\DB\SiteWatch.FDB

And the code in Application.cfc:

this.datasources["drb"] = {
	  class: 'org.firebirdsql.jdbc.FBDriver'
	, connectionString: 'jdbc:firebirdsql://hub:3050/D$\SiteWatch\DB'
	, username: 'REPORTS'
	, password: "******"
	
	// optional settings
	, connectionLimit:100 // default:-1
	, validate:false // default: false
}

If anyone can help I would greatly appreciate it. I’ve lost days trying to connect to this db with Lucee.

Zackster · December 30, 2020, 1:48pm

there’s was built in support for Firebird and Lucee claims to support it

github.com

lucee/Lucee/blob/5.3/core/src/main/java/lucee/runtime/db/DBUtil.java#L52


		// TODO extract data from JDBC config
		if ("db2".equals(type)) {
			if (DB2 == null) {
				DB2 = new DataSourceDefintion("com.ddtek.jdbc.db2.DB2Driver", "jdbc:datadirect:db2://{host}:{port};DatabaseName={database}", 50000);
			}
			return DB2;
		}
		if ("firebird".equals(type)) {
			if (FIREBIRD == null) {
				FIREBIRD = new DataSourceDefintion("org.firebirdsql.jdbc.FBDriver", "jdbc:firebirdsql://{host}:{port}/{path}{database}", 3050);
			}
			return FIREBIRD;
		}
		if ("h2".equals(type)) {
			if (H2 == null) {
				JDBCDriver jdbc = getJDBCDriver(config, "h2", "org.h2.Driver", "org.h2", "1.3.172", "jdbc:h2:{path}{database};MODE={mode}");
				H2 = new DataSourceDefintion(jdbc.cd, jdbc.connStr, -1);
			}
			return H2;
		}

I filed a bug https://luceeserver.atlassian.net/browse/LDEV-3205

cfmitrah · December 31, 2020, 10:26am

@Darth could you please share what kind of issue you faced? in which folder you put the fireBird jar files?.

cfmitrah · December 31, 2020, 11:55am

@Darth Could you please check your connectionString? I can able to connect the fireBird database

This is what I use,

this.datasources["drb"] = {
        class: 'org.firebirdsql.jdbc.FBDriver'
        , connectionString: 'jdbc:firebirdsql://localhost:3050/D:/test.fdp'
        , username: 'USER'
        , password: '*********'
        // optional settings
        , connectionLimit:100 // default:-1
        , validate:false // default: false
    }

Darth · January 1, 2021, 2:32pm

I believe it’s a problem with where to put the JAR Files. I spent two days checking the connection string. The other possibility is the user account running Lucee doesn’t have access to the network path. (Windows 10 default install). Other tools like DBeaver connect with that connection string.
Thanks!

Darth · January 1, 2021, 2:33pm

The documentation definitely claims Firebird support, but it’s not supported.

Darth · January 1, 2021, 2:36pm

I’ll post the exact error, but it seems to be finding the JAR files but not a decency or a class.
Where should the Firebird JAR Files be placed?

Darth · January 3, 2021, 10:50pm

Darth:

this.datasources["drb"] = {
	  class: 'org.firebirdsql.jdbc.FBDriver'
	, connectionString: 'jdbc:firebirdsql://hub:3050/D$\SiteWatch\DB'
	, username: 'REPORTS'
	, password: "******"
	
	// optional settings
	, connectionLimit:100 // default:-1
	, validate:false // default: false
}

this.datasources["drb"] = {
	  class: 'org.firebirdsql.jdbc.FBDriver'
	, connectionString: 'jdbc:firebirdsql://hub:3050/D$\SiteWatch\DB'
	, username: 'REPORTS'
	, password: "******"
	
	// optional settings
	, connectionLimit:100 // default:-1
	, validate:false // default: false
}

Darth · January 3, 2021, 10:58pm

What is the correct folder to place the Jar files for the JDBC Driver?

As for the database path, I’ve tried several variations. Note that I do have several other tools linked to that server via JDBC with the exact connection defined above. (DBBeaver, etc.) The JDBC Driver is “jaybird” as you can see in the error log below.

I’ll be happy to fix this or write an extension if one is needed.

Thanks!

This is on a server and I’ve tried the network path a number of ways

'jdbc:firebirdsql://hub:3050/D:/SiteWatch/DB'
'jdbc:firebirdsql://hub:3050/D$/SiteWatch/DB'
'jdbc:firebirdsql://hub:3050/D:\SiteWatch\DB'
'jdbc:firebirdsql://hub:3050/D$\SiteWatch\DB'
```etc.

**This is the end of the error thread:**

org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.NoClassDefFoundError: javax/resource/ResourceException
… 48 more
Caused by: java.lang.ClassNotFoundException: javax.resource.ResourceException not found by jaybird.4.0.1.java8 [51]
at org.apache.felix.framework.BundleWiringImpl.findClassOrResourceByDelegation(BundleWiringImpl.java:1597)
at org.apache.felix.framework.BundleWiringImpl.access$300(BundleWiringImpl.java:79)
at org.apache.felix.framework.BundleWiringImpl$BundleClassLoader.loadClass(BundleWiringImpl.java:1982)
at java.lang.ClassLoader.loadClass(Unknown Source)
… 48 more

Darth · January 4, 2021, 12:26am

Ok - so I know the jaybird.jar files are being used because they are locked by Windows. It still cannot find something. See the error below. I’m not sure if I’m missing a JAR file or not putting them everywhere they should be. They are in:
…\lucee\tomcat\lucee-server\context\lib

 org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
 	at java.base/java.lang.Thread.run(Thread.java:834)
 Caused by: java.lang.NoClassDefFoundError: javax/resource/ResourceException
 	... 48 more
 Caused by: java.lang.ClassNotFoundException: javax.resource.ResourceException not found by jaybird.4.0.1.java11 [50]
 	at org.apache.felix.framework.BundleWiringImpl.findClassOrResourceByDelegation(BundleWiringImpl.java:1597)
 	at org.apache.felix.framework.BundleWiringImpl.access$300(BundleWiringImpl.java:79)
 	at org.apache.felix.framework.BundleWiringImpl$BundleClassLoader.loadClass(BundleWiringImpl.java:1982)
 	at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)

cfmitrah · January 4, 2021, 2:37pm

@Darth Put the jar file into the lib folder in installer path \lucee\lib\ or \lucee\tomcat\lib\ instead of \lucee\tomcat\lucee-server\context\lib. I use jaybird-full-3.0.9.jar file, get the jar in this zip file (https://github.com/FirebirdSQL/jaybird/releases/download/v3.0.9/Jaybird-3.0.9-JDK_1.8.zip).

Darth · January 5, 2021, 1:06am

Thank you!
That worked perfectly! I was using jaybird-full-4.0.1.java8.jar, not jaybird-full-3.0.9.jar (although I thought I had tried it.)
I put the Jar in \lucee\lib
Is the same/better to put it in Tomcat?

Thanks again, you don’t know how much I appreciate it. I knew it was something easy, just couldn’t get the right combination or Jar, directory, etc.

Darth · January 5, 2021, 1:09am

I also created at CFC for Firebird, if anyone wants it. Firebird shows in “Datasources” in the admin now.