Contents

  1. What is Village?
  2. How can I configure Torque to use a JNDI connection?
  3. How can I execute a stored procedure
  4. Can I run a stored procedure (or a complex query), but use Torque's mapping to return the results as objects?
  5. How to add P6Spy for printing SQL
  6. doDelete() is throwing a !TorqueException. What's up?
  7. Is there a way to use one installation of torque for different projects without having a copy of torque-gen for each project (e.g. copy the build-torque.xml and build.properties and set a parameter in build.properties to the torque basedir)?
  8. Can I use my own ID-Generator with torque? How?
  9. We have an existing database where some of the table names have spaces as part of the name. Is there a way in which Torque could replace spaces by, say, underscores? What about table columns with spaces in the names?
  10. The save methods internally use Transaction.start(), what if I want save of multiple entities to be wrapped in single transaction?
  11. How can I count datasets in a table ?
  12. Why are the log4j properties in Torque.properties being ignored in the example bookstore application?
  13. Why are large BLOBs and CLOBs not working in Oracle? How do I store BLOBs or CLOBs > 4000 (or sometimes 2000) bytes?
  14. Why is there a problem with Torque runtime properties?
  15. Why does Torque generate invalid SQL for PostgreSQL BOOL columns?
  16. Why does the generated SQL fail with PostgreSQL?
  17. Why addAscendingOrderByColumn launch a NullPointerException ?
  18. What is the correct DTD to use with the Schema files and how can I avoid loading the DTD file from the internet every time ?
  19. How do you set up the maven 2 plugin?

What is Village?

Answer: See the http://www.softwareforge.de/projects/village homepage for that information.

How can I configure Torque to use a JNDI connection?

Answer: Read the Torque Pool-config Howto for indepth information. However, here is the short howto!

{{{ torque.database.default.adapter=mssql

<resource-ref>
  <description>
    Resource reference to a factory for java.sql.Connection
    instances that may be used for talking to a particular
    database that is configured in the server.xml file.
  </description>
  <res-ref-name>
    jdbc/fortius
  </res-ref-name>
  <res-type>
    org.apache.torque.pool.TorqueClassicDataSource
  </res-type>
  <res-auth>
    Container
  </res-auth>
 </resource-ref>

 <Resource name="jdbc/fortius" scope="Shareable" type="javax.sql.DataSource"/>
 <ResourceParams name="jdbc/fortius">
  <parameter>
    <name>validationQuery</name>
    <value>SELECT 1</value>
  </parameter>
  <parameter>
    <name>maxWait</name>
    <value>5000</value>
  </parameter>
  <parameter>
    <name>maxActive</name>
    <value>4</value>
  </parameter>
  <parameter>
    <name>password</name>
    <value>mypassword</value>
  </parameter>
  <parameter>
    <name>url</name>
    <value>jdbc:microsoft:sqlserver://cuzco:1433;DatabaseName=fortius;SelectMethod=cursor</value>
  </parameter>
  <parameter>
    <name>driverClassName</name>
    <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
  </parameter>
  <parameter>
    <name>maxIdle</name>
    <value>2</value>
  </parameter>
  <parameter>
    <name>username</name>
    <value>SilverUserLogin</value>
  </parameter>
 </ResourceParams> 

If all else fails, first try and verify that you are able to directly create the datasource. I wrote a little action called DB.java that I could call from torque with a single action:

{{{ public void doPerform(RunData data, Context context)

-- EricPugh

How can I execute a stored procedure

Answer: You can use some of the functionality exposed by the Village library to execute and deal with the result set of your stored procedure:

{{{ import com.workingdogs.village.Record;

What else you may notice is that if you do something similar with a complex sql query: {{{String SQL = "select top 50 distinct kinase_id,atp_conc from reaction where workorder_id = "

}}}

-- EricPugh

Can I run a stored procedure (or a complex query), but use Torque's mapping to return the results as objects?

Answer: Yes, you can! Here's an example.

{{{String sql = "select * from jps_containers c where not exists (select * from jps_apps a where a.container_id =c.container_id)"; List records = ContainerConfigPeer.executeQuery(sql); List listOfContainerConfigs = ContainerConfigPeer.populateObjects(records); }}}

This is very similar to the previous answer (How can I execute a stored procedure), but you don't have to worry after you add a column to the underlying table.

-- MichalKwiatek

How to add P6Spy for printing SQL

Answer:

  1. add the p6spy.jar to your application classpath
  2. copy the "spy.properties" into your classpath (e.g. same location as log4j.properties)
  3. set the "realdriver" in spy.properties, e.g. realdriver=oracle.jdbc.driver.OracleDriver for Oracle

  4. change the Torque.properties, e.g. "torque.dsfactory.XXX.connection.driver = com.p6spy.engine.spy.P6SpyDriver" to use the proxy JDBC drivers

  5. run your application
  6. P6Spy creates a spy.log in your current directory

-- Siegfried Goeschl

doDelete() is throwing a !TorqueException. What's up?

Answer: doDelete() throws a TorqueException with message "You must specify KeyDef attributes for this TableDataSet in order to delete a Record" when the schema for the table does not contain a primary key. If the table has no primary key, doDelete() may not be used to delete from it.

-- Gary Shea

Is there a way to use one installation of torque for different projects without having a copy of torque-gen for each project (e.g. copy the build-torque.xml and build.properties and set a parameter in build.properties to the torque basedir)?

Answer: Yes. Torque is mavenized, so you can install and use torque as a plugin for Maven. http://db.apache.org/torque/releases/torque-3.2/maven-plugin/index.html

After successfull instalation of the plugin you will be able to run the common torque tasks and plus this you will be able to run pomre specific tasks configurations as well. For example the command line: maven -D absolute_folder_location torque:om which will look inside the given path and will run torque's task OM using the build.properties that are located there. and the xml defined schema will be in a relative folder, (according to: plugin.properties in your MAVEN_HOME/plugins/maven-torque-plugin directory)

-- bogdan

Can I use my own ID-Generator with torque? How?

Answer: ????

-- Michael Wyraz

We have an existing database where some of the table names have spaces as part of the name. Is there a way in which Torque could replace spaces by, say, underscores? What about table columns with spaces in the names?

-- Daniel Canas

Answer: Quick way -> No. And we hope that in the next release there will be a way for plugging external Name Genrator. But for now you can still change the genrator to fit your needs. For example I have extended JavaNameGenerator this way:

1) added a single line to interface org.apache.torque.engine.database.model.NameGenerator;

String CONV_METHOD_UNDERSCORE_IGNORE_DOTS = "underscore_ignore_dots";

2) added a single conversion method to class org.apache.torque.engine.database.model.JavaNameGenerator;

        protected String underscoreIgnoreDotsMethod(String schemaName) {
                schemaName = schemaName.replaceAll("\\.", "_");

                StringBuffer name = new StringBuffer();
                StringTokenizer tok =
                        new StringTokenizer(schemaName, String.valueOf(STD_SEPARATOR_CHAR));
                while (tok.hasMoreTokens()) {
                        String namePart = ((String) tok.nextElement()).toLowerCase();
                        name.append(StringUtils.capitalize(namePart));
                }
                return name.toString();
        }

3) added a single condition to class org.apache.torque.engine.database.model.JavaNameGenerator; method: String generateName(List inputs);

                } else if (CONV_METHOD_UNDERSCORE_IGNORE_DOTS.equals(method)) {
                        javaName = underscoreIgnoreDotsMethod(schemaName);

4) rebuilt the torque-generator maven plugin (src/generator$ maven jar:install)

5) now I can use the attribute value in the database tags of my schemas

<database defaultJavaNamingMethod="underscore_ignore_dots" .....

My code ignores gots inside the name. You can ignore spaces for your case ..or make whatever conversions you need. (this is the way in short to add new naming method)

-- Bogdan Vatkov

The save methods internally use Transaction.start(), what if I want save of multiple entities to be wrapped in single transaction?

Answer: Use Transaction yourself:

{
  java.sql.Connection connection = null;
  try {
    connection = org.apache.torque.util.Transaction.begin("torques_name_of_db");
    someObject.save(connection);
    someOtherObject.save(connection);
    org.apache.torque.util.Transaction.commit(connection);
    connection = null;
  }
  catch (Exception e) {
    // error-handling code goes here
  }
  finally {
    // no rollback if the transaction succeeded,
    // because in this case connection is null here
    if (connection != null) {
      org.apache.torque.util.Transaction.safeRollback(connection);
    }
  }
}

torques_name_of_db must be replaced with the database's name from torque's configuration files. The save(connection) methods are implemented in Torque's autogenerated base classes (where also the "usual" save() - Methods are), so you don't have to implement these by yourself.

One important point in using the Transaction class is to make sure that the Database connection is released at the end of the transaction, even if the transaction failed. If database connections are not released, you will run out of available connections sooner or later, leading to errors which are not easy to find. Releasing the connection is ensured in the above code because either Transaction.commit() or Transaction.safeRollback() are called (or both if the commit fails). Both methods release the database connection. Note that connection.commit() does NOT release the database connection, so connection.commit() should not be used unless you know what you are doing.

-- Thomas Fischer

How can I count datasets in a table ?

Suppose one wants to execute a SQL-statement like "select count(*) from employee where name='james'".

Answer: This query can be executed using the following code (assuming the employee Table has at least two Columns: "NAME" and "ID", where ID is the primary key, and the Peer class to the employee Table is EmployeePeer):

int numberOfRecords;
{
  org.apache.torque.util.Criteria criteria = new org.apache.torque.util.Criteria();
  criteria.add(EmployeePeer.NAME, "james");
  criteria.addSelectColumn("COUNT(" + EmployeePeer.ID + ")");
  java.util.List result = EmployeePeer.doSelectVillageRecords(criteria);
  com.workingdogs.village.Record record = (com.workingdogs.village.Record) result.get(0);
  numberOfRecords = record.getValue(1).asInt();
}

I don't know which columns should be used in addSelectColumn (and if it makes a difference which one is used), but I presume using the primary key is fine. Note that addSelectColumn("count(*)") does not work.

The answer was put together from articles by Scott Eade and Tulsi Das on the Torque Users Mailing List.

-- Thomas Fischer

Why are the log4j properties in Torque.properties being ignored in the example bookstore application?

The torque-gen-3.1 jar file includes configuration files for Log4j and commons-logging SimpleLog - if this jar is in your classpath at runtime it may adversly impact your logging implementation.

The best thing to do is to ensure the torque-gen jar file is not in the classpath of your application (it is only required when generating your object model classes (and the other generator tasks). See commons-logging configuration for how to configure the logging for your application.

Why are large BLOBs and CLOBs not working in Oracle? How do I store BLOBs or CLOBs > 4000 (or sometimes 2000) bytes?

The problem is with the Oracle JDBC Driver. It doesn't follow the JDBC standards properly. To fix the problem, use the patched version of Village available here. The Village patch for Oracle was last updated on 10-Oct-2004.

-- Sarav

The documentation of Oracle 10g mentions a connection property parameter that can be set to allow bigger BLOBs/CLOBs.

"In summary, PreparedStatement.setString() comes handy for processing the CLOB data, by just setting the Connection property SetBigStringTryClob. However, handling very large amounts of data this way may not be a wise; streaming the data is a better alternative."

from http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/clob10g/handlingclobsinoraclejdbc10g.html

-- tv

Why is there a problem with Torque runtime properties?

Some of the properties have been renamed, so the torque properties file is partly outdated, e.g.

#torque.defaults.pool.connectionWaitTimeout = 10
#has been renamed to:
torque.defaults.pool.maxWait=10

Read the Jakarta Commons DBCP docs about DBCP configuration and adapt your torque properties file accordingly.

-- Tarlika Elisabeth Schmitz

Why does Torque generate invalid SQL for PostgreSQL BOOL columns?

Torque generates invalid SQL for bool comparisons: generated "WHERE xyz=0", but it should be WHERE xyz=FALSE or "WHERE xyz='0'".

The fault is in the adapter class \org\apache\torque\adapter\DBPostgres.class:

public String getBooleanString(Boolean b)
{
    //was : return (b == null) ? "0" : (Boolean.TRUE.equals(b) ? "1" : "0");
    return (b == null) ? "NULL" : (Boolean.TRUE.equals(b) ? "TRUE" : "FALSE");
}

-- Tarlika Elisabeth Schmitz

Why does the generated SQL fail with PostgreSQL?

The SQL generated for PostgreSQL using defaultIdMethod "native" causes a problem with PostGreSQL 7.2 or higher:

For each autoIncrement primary key a CREATE SEQUENCE statement is generated followed by the CREATE TABLE statement. The latter fails because this version of PostgreSQL automatically creates a SEQUENCE for each serial column. The table will not be created if the SEQUENCE already exists.

Remove line 7 #if ($sequence.length()>0)$sequence#end from the template sql\base\postgresql\table.vm

-- Tarlika Elisabeth Schmitz

Why addAscendingOrderByColumn launch a NullPointerException ?

Answer: Torque has not been correctly initialized for the database. The properties file need the key torque.database.db.adapter=mysql where db is the database name and mysql the database adapter used by torque for accessing the base.

-- Alexandre Gouze

Answer: Another error may be at the OM generation. The database name was not specifed in then XML schema file. <database defaultIdMethod="idbroker" name="databaseName">

-- Jorge Ortega

Answer: Passing a non-existing column name via addAscendingOrderByColumn can also get you a NullPointerException when BasePeer.doSelect(Criteria crit) is called

-- HelgeWeissig

What is the correct DTD to use with the Schema files and how can I avoid loading the DTD file from the internet every time ?

Answer For the 3.1 release of Torque, these are the right DTD Definitions:

Torque 3.1 and Torque 3.1.1:          http://db.apache.org/torque/dtd/database_3_1.dtd 
Torque 3.2:                           http://db.apache.org/torque/dtd/database_3_2.dtd

If you use this DTD, then the Torque Generator will use the database.dtd file embedded in the torque generator jar.

You should use the following DTD declaration in your Schema files:

Torque 3.1 and Torque 3.1.1: <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database_3_1.dtd">
Torque 3.2:                  <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database_3_2.dtd">

-- Henning Schmiedehausen

How do you set up the maven 2 plugin?

Add this to your pom.xml

<project>
  ...
  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.db.torque</groupId>
        <artifactId>torque-maven-plugin</artifactId>
        <version>3.3-RC1</version>
      </plugin>
    </plugins>
  </build>
</project>

Then refer to the maven 2 torque plugin documentation for supported goals.

TODO: additional configuration properties are needed for the torque plugin to operate. I believe these plugin properties are just added to the plugin element in the pom.xml.

TODO: add this to the maven 2 plugin site documentation.

-- Scot Hale

FrequentlyAskedQuestions (last edited 2009-09-20 23:04:19 by localhost)