DB2 database support in Roo

[Andrew Swan]

So your POM needs an entry like this (sorry if this is teaching you how to suck eggs, but you didn't post your POM):

<project ...
    ...
    <dependencies>
        <dependency>
            <groupId>com.ibm</groupId>
            <artifactId>db2jcc</artifactId>
            <version>3.50.152</version>
        </dependency>
    </dependencies>
    ...
</project>

The other thing you can check is whether the install-file goal actually saved the driver as <user_home>/.m2/repository/com/ibm/db2jcc/3.50.152/db2jcc-3.50.152.jar.

[eltardo]

You are alright, I did not post my pom.xml but I checked that spring roo 1.0.2 put that entry inside pom.xml and already checked whether the file is correctly installed in the .m2 path and everything looked correct. I will post you some details:

.m2 repo:
ll /Users/tobia/.m2/repository/com/ibm/db2jcc/3.50.152/db2jcc-3.50.152.jar
-rw-r--r-- 1 tobia staff 3226598 26 Feb 12:19 /Users/tobia/.m2/repository/com/ibm/db2jcc/3.50.152/db2jcc-3.50.152.jar

pom.xml (extract) :
<dependency>
<groupId>com.ibm</groupId>
<artifactId>db2jcc</artifactId>
<version>3.50.152</version>
</dependency>
There is no repository section that indicates to search inside local repo..Is it needed?
When executing perform eclipse command in roo shell the BUILD is SUCCESSFUL, even though roo tries to download db2jcc from repo (i think it eventually finds it in the local repo and doesn't raise any error)

But when trying to deploy or launch the webapp in tomcat (mvn tomcat:run) maven still says it is impossible to dl the jar
[INFO] Preparing tomcat:run
Downloading: http: //repository.springsource.com/...c-3.50.152.pom
[INFO] Unable to find resource 'com.ibm:db2jcc: pom:3.50.152' in repository com.springsource.repository.bundles.release (http: //repository.springsource.com/...undles/release)
Downloading: http: //repository.springsource.com/...c-3.50.152.pom
[INFO] Unable to find resource 'com.ibm:db2jcc: pom:3.50.152' in repository com.springsource.repository.bundles.external (http: //repository.springsource.com/...ndles/external)
Downloading: http: //repository.springsource.com/...c-3.50.152.pom
[INFO] Unable to find resource 'com.ibm:db2jcc: pom:3.50.152' in repository com.springsource.repository.bundles.milestone (http: //repository.springsource.com/...dles/milestone)
Downloading: http: //repository.springsource.com/...c-3.50.152.pom
[INFO] Unable to find resource 'com.ibm:db2jcc: pom:3.50.152' in repository com.springsource.repository.bundles.snapshot (http: //repository.springsource.com/...ndles/snapshot)
Downloading: http://repo1.maven.org/maven2/com/ib...c-3.50.152.pom
[INFO] Unable to find resource 'com.ibm:db2jcc: pom:3.50.152' in repository central (http: //repo1.maven.org/maven2)

and also when launching site from browser an exception is raised saying that:
GRAVE: Servlet.service() for servlet default threw exception
org.springframework.dao.DataAccessResourceFailureE xception: Could not create JPA EntityManager
.....
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.ibm.db2.jcc.DB2Driver

Hope i did provide enough details and I really thank you for your support.
I didn't sleep tonite to try to solve this issue and I tested it with any persistence provider to see if anything changed.But the db2jcc driver seems to not be available to the application even though I can see it in the referenced libraries of sts 2.3 and such class(DB2Driver) is present inside the jar.

[Andrew Swan]

No, you never need to tell Maven to look in the local repo. Quite the opposite in fact; Maven will always check the local repo (in your case /Users/tobia/.m2/repository) for a given artifact before trying to download it from any remote repositories (which includes both private repos on your LAN as well as repos on the internet). Snapshot artifacts are more complicated but not relevant to your situation.

If you look closely at those five "Unable to find resource" errors, you will see that they relate to the driver's POM, not the driver's JAR file (which is already in your local repo and doesn't need to be downloaded, as per my previous para). Maven always tries to find the POMs for your project's dependencies in order to identify and download any transitive dependencies (in this case, any JARs upon which the driver itself might depend). Because the driver probably wasn't built using Maven (shame on IBM!), there is no POM for it, so the download fails. As you see, Maven doesn't regard not finding a POM as fatal, so your build completes successfully anyway. But because there is no POM, you have to add the driver's own dependencies manually to your project's POM in the same way as if your project used those JARs itself. You can only find out these dependencies (if any) by reading the driver's documentation. I've never used DB2 so I can't tell you, however I've yet to come across a JDBC driver that does have dependencies (not counting the SQL Server 2000 driver, which rather inconveniently ships as three separate JAR files).

So it looks as though the driver is indeed present. You can check this by building your project using "mvn package", going into your project's "target" folder, and looking for the driver inside your WAR file's WEB-INF/lib folder.

As to why commons-dbcp can't load the driver, I'm not sure. How about you post your project's database.properties file? Remember to use the [ CODE ] tags though (without the spaces of course)!

The other thing you could do is inspect the source code for commons-dbcp to find out why it throws that "Cannot load JDBC driver class" exception.

[eltardo]

ok, i see.
I will check the documentation for any dependency and then i'll try to configure my pom accordingly.
I will at last return here and give my feedback on the investigation and a solution if i'll find any.
I'd like to thank you for your precious help.
pls feel free to post any idea if any.
eltardo

[eltardo]

It works!
Finally I got all the stuff working..
the problem was inside the database.properties file.
building the project from the roo toolkit, roo eventually insert a space " " char at the end of the driver property line:
<code>database.driverClassName=com.ibm.db2.jcc.DB2 Driver </code>

removing that space (after, of course, installing the driver manually into maven local repository) the application resolves correctly the driver dependency, and has the db2 correctly working.

thank you andrew for your interest and support, your advices were extremely important to identify the problem's nature.

[Andrew Swan]

Glad it all works now. If Roo is adding that trailing space by default, and that prevents the DB2 driver loading, it would be great if you could log it as a bug in the issue tracker so that the Roo team can fix it for everyone else.

[eltardo]

Yes it indeed adds that trailing space by itself preventing the correct loading of the driver class.
I opened this bug in jira:

http://jira.springframework.org/browse/ROO-674

[jbbarquero]

Congratulations to eltardo, the space at the end of the database.driverClassName is very difficult to see.

In fact, it's a classic problem that I have since WAS 3.5 (extra whitespaces at the end of the driver class name in properties files)

So it works for me, at last I can connect to DB2...

...however, now I have another problem (see the next POST)

[jbbarquero]

Now I can connect to DB2, however, all the JUnits have errors:

org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not execute query; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query

[...]

Caused by: com.ibm.db2.jcc.b.SqlException: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO

I'm using DB2 OS/390 8.1.5.

In the database log file (thanks to grg for the detailed JIRA ticket: ROO-603) I can find (please note that I've erased the name of the columns and I've changed the table name and alias):

[ibm][db2][jcc][Time:1268665411311][Thread:main][Connection@1df5f21] prepareStatement (select * from ( select rownumber() over() as rownumber_, [B][I][sepparate commas columns] from TABLE ALIAS) as temp_ where rownumber_ <= ?) called

It seems the second query, the one within the parenthesis, has no FROM clause.

Directly in DB2 I have the next error codes:

SQL0104N
sqlcode : -104
sqlstate : 42601

What I'm doing wrong?

Thank you very much in advance.

[jbbarquero]

At last it works for me.

Thanks to all for the help (replies, JIRA tickets and so on)

The remain problems were related to JPA (@Id, @Version, @GeneratedValue) and I have solved almost all of them.