Firebird, Subreports, multiple connections

[2006 IR Open Dicussion]

By: yolomann - yolomann

Firebird, Subreports, multiple connections

2006-03-06 15:39

I haven't been able to get subreports to work at all using the latest Firebird/Jaybird release. Just before giving up, I put data in Oracle and everything worked fine! It appears the result set is being closed in Firebird when passed to the subreport.

 

How do I get it to work in Firebird? I think if I created two connections it would work. I don't know how to create another $P{REPORT_CONNECTION} using the connectionExpression tag.

 

Is this a lack of JDBC 2.0 compliance on Jaybird's part? Or a lack of Cursor support in Firebird. The requirements for jasper are a bit vague.

 

Thanks

 

Bill

 

 

 

 

 

By: yolomann - yolomann

RE: Firebird, Subreports, multiple connections

2006-03-07 07:41

I even created a connection in a scriptlet derived class and passed it to the subreport.

 

$P{REPORT_SCRIPTLET}.getDatabaseConnection()

 

It gives the error "no transaction for request"

 

Anybody else using Firebird? trying to use oss.

 

Bill

 

 

 

 

 

By: Lucian Chirita - lucianc

RE: Firebird, Subreports, multiple connection

2006-03-09 00:51

I'm not at all familiar with Firebird, these are just some things I noted..

 

I tried to run a subreport with Firebird and JayBird 2.0.1 and indeed I got the first error you posted. It seems that JayBird 2.0.1 does not support multiple open result sets. However, I tried the 1.5.6 drivers and it worked, which is a little strange..

 

But passing a different connection worked for me with the 2.0.1 driver (org.firebirdsql.jdbc.FBDriver). Something must be different in your environment.

 

Maybe you know what that error actually means? If you know what's the cause, we might find a workaround.

 

 

Regards,

Lucian

 

 

By: yolomann - yolomann

RE: Firebird, Subreports, multiple connections

2006-03-10 07:16

Many thanks for the input. Am I passing a connection correctly or what is the standard way?

 

I'll try and post something in Jaybird Forum to see if I can get it fixed.

 

 

As for Firebird, it is a great, easy to use database that was transaction based from the start w/ support for stored procs, triggers, user-defined funcs, etc. There's a free admin tool called IBExpert. I tried to switch to MySql because of popularity (and it getting first third-party support) but I just like Firebird better. MySql just hired the original architect of Firebird (and his wife) to enhance MySql with it's own transaction engine (since Oracle bought Innobase). I'm hoping they convince them the best move is to migrate to Firebird.

 

 

Bill

 

 

 

 

 

By: Lucian Chirita - lucianc

RE: Firebird, Subreports, multiple connection

2006-03-10 07:40

I don't think there is a standard way to pass a separate connection for a subreport, usually the connection of the master report is also used by subreports.

 

You can either do it by using the scriptlet or by creating a new parameter and passing a separate connection in the parameters map. The latter approach has the advantage that reusing the connection accross subreport occurences and closing the connection when the fill is done can be handled in a simpler manner.

 

What puzzled me is that the 1.5.6 driver seems to work while the 2.0.1 doesn't. I didn't go through the release notes/documentation, but this looks like a backward compatibility issue.

 

I also noticed that the list of not (yet) supported JDBC 2.0 functionality is quite long..

 

 

Regards

 

 

By: yolomann - yolomann

RE: Firebird, Subreports, multiple connections

2006-03-10 11:27

Sounds like it's the autocommit default setting (below) and it is set to ON by jdbc spec. Where in jrxml or iReports can I set it it to OFF before the master query is run?...or can I?

 

I could guess $P{REPORT_CONNECTION}.setAutoCommit(false) but wouldn't know where it needs to go.

 

As a workaround, I can temporarily revert to jdk1.4.2 and jaybird 1.5.6 if need be.

 

again thanks for the help.

 

Bill

 

 

*** from jaybird message group ***

<question>

Lately with JayBird 2.0.1 I seem to be getting allot of ResultSet closed

errors....I did not have this problem with older versions of JayBird.

</question>

 

<answer>

We have now JDBC-specs conformant behavior in auto-commit mode. See

release notes and JDBC specs for information when result set is closed.

</answer>

 

 

 

 

 

 

 

 

 

By: Lucian Chirita - lucianc

RE: Firebird, Subreports, multiple connection

2006-03-13 02:23

You could simply put it in your code, somewhere in between the connection creation and the report fill.

 

Or maybe you could use a property when you create the connection so that you get a connection having the auto commit flag unset.