Jonathan Lalou's blog

Posts Tagged ‘JDBC’

I have learnt Groovy since the beginning of the week.
I had to import some classes from Oracle JDBC driver. As a newbie, I wrote the following annotation:

@Grab(group = 'com.oracle', module = 'ojdbc14', version = '10.2.0.4.0')

I got the following error:

Error grabbing Grapes -- [unresolved dependency: com.oracle#ojdbc14;10.2.0.4.0: not found]

To sum up, adding a dependency to Oracle driver raises two issues:

• adding any external dependency
• adding a dependency to any JDBC driver

Indeed, Grab requires a kind of Maven repository, and you can catch the link between the @Grab annotation below and classic Maven 2 <dependency> tag. As I understood, Grab instructions are closer to Ivy than to Maven system. In my situation, having no “Grab repo” (I don’t know how to call that), I had to download the jars from a Maven repo. To fix it, two operations are needed: hint at Maven (or Ivy) repo, and allow Groovy to access the network.

Regarding the second issue, it is answered by added a @GrabConfig annotation.
Therefore, to fix the original issue:

• replace

  @Grab(group = 'com.oracle', module = 'ojdbc14', version = '10.2.0.4.0')

  with

  @GrabConfig(systemClassLoader=true)
  @GrabResolver(name='nameOfYourMavenRepo, root='http://url-of-your-maven-repo:port')
  @Grab(group = 'com.oracle', module = 'ojdbc14', version = '10.2.0.4.0')
  

• at first run, if needed, hint at the proxy to allow Grab to download the Jars, eg:

  groovy -Dhttp.proxyHost=myProxyHost -Dhttp.proxyPort=8080

You may notice the needed JAR will be written in the $USER_HOME/.groovy/grapes folder.

Case

In a JDBC DAO, I execute a query to retrieve an object. I get this error:

java.sql.SQLException: Wrong data type: NUMBER in statement [CREATE TABLE Jonathan_Table (TableColumn NUMBER]

Stacktrace

java.sql.SQLException: Wrong data type: NUMBER in statement [CREATE TABLE Jonathan_Table (TableColumn NUMBER]
 at org.hsqldb.jdbc.Util.throwError(Unknown Source)
 at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)
 at com.bnpp.pb.risklayer.services.dao.jdbc.JdbcPrimeRiskServerDaoUnitTest.getDataSet(JdbcPrimeRiskServerDaoUnitTest.java:57)
 at org.dbunit.DatabaseTestCase.setUp(DatabaseTestCase.java:154)

Explanation and fix

My original DB is under Oracle, but my DBUnit tests works on HSQL. Yet, types NUMBER and VARCHAR2 are not available under HSQL, this is why the exception is raised.
To fixe the issue, rewrite your scripts, replacing NUMBER and VARCHAR2 with NUMERIC and VARCHAR.

Case

On a unit test with JDBC / DBUnit, extending org.dbunit.DBTestCase, I get this error:

Table 'Jonathan_Lalou_Table' not found in tableMap=org.dbunit.dataset.OrderedTableNameMap[_tableNames=[], _tableMap={}, _caseSensitiveTableNames=false]

Explanation and fix

Indeed, even when you provide a dataset through a flat XML file, DBUnit does not create the tables, but only fills them in. I know, this is paradoxal and most developpers would like to create implicitly the tables prior to filling them…

To fix the issue, add a block like this one, for instance when overriding the method getDataSet():

final PreparedStatement preparedStatement;
preparedStatement = getDatabaseTester().getConnection().getConnection().prepareStatement("CREATE TABLE Jonathan_Lalou_Table ... ");
preparedStatement.executeUpdate();

Case

Your application is connected to a database. The configuration of the DB connexion is set in a Spring file. You would like the connexion to be set in WebLogic, so that no URL/login/password lays in your source code.

Steps

WebLogic

Create a new data source:

• go the WebLogic console >
  • JDBC >
    • DataSource >
      • New >
        • set the name, JNDI name (eg: database.jndi.name), DB type  (Oracle, Sybase, …) >
          • Next >
            • Select the driver >
              • Next >
                • Select the targeted server(s)

This will

1. create a new file <yourDomain>/config/jdbc/<datasourceName>-XXXX-jdbc.xml.
2. add a <jdbc-system-resource> block in <yourDomain>/config/config.xml

Spring

Replace the block:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${database.driver}" />
<property name="url" value="${database.url}" />
<property name="username" value="${database.username}" />
<property name="password" value="${database.password}" />
 </bean>

with:

<jee:jndi-lookup id="dataSource" jndi-name="${database.jndi.name}" />

Error:

MyStrutsAction     - Error on uploading: MyPersonnalClass@971720[primaryId=<null>,type=XXX,myDate=20080702,entityId=<null>,optimisticTimestamp=<null>] org.springframework.remoting.RemoteAccessException: Could not access remote service [my-service-bean]; nested exception is javax.transaction.TransactionRolledbackException: BEA1-0F416BB49A8BE6353E83: javax.transaction.xa.XAException: JDBC driver does not support XA, hence cannot be a participant in two-phase commit. To force this participation, set the GlobalTransactionsProtocol attribute to LoggingLastResource (recommended) or EmulateTwoPhaseCommit for the Data Source = myDatasource

Fix:

• Go on WebLogic console
• Then follow the pages: Home > Summary of JDBC Data Sources > myDatasource > Transactions
• Uncheck:One-Phase Commit
• Check: Emulate Two-Phase Commit