Key problems - Torque 3.1.1

Hi, all,

Torque's driving me a bit batty this morning. Here is the problem: I'm 
trying to use Torque to facilitate transfers from one database to 
another, but it seems like I can't set the primary keys in the 
destination database correctly. Here is an example of code that is 
supposed to do this:

   /**
    * This method will transfer a template set from the source database to
    * {@link #getDestSetId() <i>destSetId</i>} in the destination database.
    */
   private void transferTemplateSet(Connection sourceConnection, 
Connection destConnection) throws TorqueException {
       Criteria dcrit = new Criteria();
       dcrit.add(DataInfoPeer.ID, this.destSetId);
       List destList = DataInfoPeer.doSelect(dcrit, destConnection);
      
       if (destList.isEmpty() || this.updateSet) {
           Criteria crit = new Criteria();
           crit.add(DataInfoPeer.ID, this.sourceSetId);
           List sourceList = DataInfoPeer.doSelect(crit, sourceConnection);
           log.info(sourceList.size() + " entries found in the source 
database for data set " + this.sourceSetId);

           if (!sourceList.isEmpty()) {
               DataInfo source = (DataInfo) sourceList.get(0);
               DataInfo dest =  source.copy();
               dest.setId(this.destSetId);
               if (this.dataSourceOverride > 0) 
dest.setDataSourceId(this.dataSourceOverride);

               log.debug("Saving record " + dest.toString());
               dest.save(destConnection);
           }
       }
   }

To facilitate this, I had to patch Object.vm and Peer.vm to run a query 
for isNew() instead of just using the boolean property, since I'm 
copying the source object and setting the primary keys and I don't know 
in advance whether or not the object exists in the destination database. 
Here are the patches I use:

Object.vm:

***************
*** 1143,1149 ****
             // If this object has been modified, then save it to the 
database.
             if (isModified())
             {
!                 if (isNew())
                 {
                     ${table.JavaName}Peer.doInsert(($table.JavaName) 
this, con);
                     setNew(false);
--- 1143,1149 ----
             // If this object has been modified, then save it to the 
database.
             if (isModified())
             {
!                 if (isNew(con))
                 {
                     ${table.JavaName}Peer.doInsert(($table.JavaName) 
this, con);
                     setNew(false);
***************
*** 1190,1195 ****
--- 1190,1207 ----
             alreadyInSave = false;
         }
   #end
+     }
+
+     /**
+      * Check whether the object already exists in the database.
+      * Used to determine whether to insert or update. This is more
+      * expensive than using the boolean isNew() flag, but allows
+      * much nicer handling for IDB applications. -Kris Nuttycombe
+      */
+     public boolean isNew(Connection con) throws TorqueException {
+         Criteria criteria = 
${table.JavaName}Peer.buildCriteria(this.getPrimaryKey());
+         List list =  ${table.JavaName}Peer.doSelect(criteria, con);
+         return list.isEmpty();
     }
 #end

Peer.vm:

***************
*** 53,62 ****
 #if (!$table.isAlias())

     /** the default database name for this class */
!     public static final String DATABASE_NAME = "$table.Database.Name";

      /** the table name for this class */
!     public static final String TABLE_NAME = "$table.Name";

     /**
      * @return the map builder for this peer
--- 53,62 ----
 #if (!$table.isAlias())

     /** the default database name for this class */
!     public static final String __DATABASE_NAME__ = "$table.Database.Name";

      /** the table name for this class */
!     public static final String __TABLE_NAME__ = "$table.Name";

     /**
      * @return the map builder for this peer
***************
*** 612,618 ****
     public static void doUpdate(Criteria criteria, Connection con)
         throws TorqueException
     {
!         Criteria selectCriteria = new Criteria(DATABASE_NAME, 2);
   #foreach ($col in $table.Columns)
     #set ( $cup=$col.Name.toUpperCase() )
     #if($col.isBooleanInt())
--- 612,618 ----
     public static void doUpdate(Criteria criteria, Connection con)
         throws TorqueException
     {
!         Criteria selectCriteria = new Criteria(__DATABASE_NAME__, 2);
   #foreach ($col in $table.Columns)
     #set ( $cup=$col.Name.toUpperCase() )
     #if($col.isBooleanInt())
***************
*** 748,753 ****
--- 748,764 ----
     }

     /**
+      * Method to do selects
+      *
+      * @throws TorqueException Any exceptions caught during processing 
will be
+      *         rethrown wrapped into a TorqueException.
+      */
+     public static List doSelect($table.JavaName obj, Connection con) 
throws TorqueException
+     {
+         return doSelect(buildCriteria(obj), con);
+     }
+
+     /**
      * Method to do inserts
      *
      * @throws TorqueException Any exceptions caught during processing 
will be
***************
*** 890,896 ****
     /** Build a Criteria object from the data object for this peer */
     public static Criteria buildCriteria( $table.JavaName obj )
     {
!         Criteria criteria = new Criteria(DATABASE_NAME);
   #foreach ($col in $table.Columns)
     #set ( $cfc=$col.JavaName )
     #set ( $cup=$col.Name.toUpperCase() )
--- 901,907 ----
     /** Build a Criteria object from the data object for this peer */
     public static Criteria buildCriteria( $table.JavaName obj )
     {
!         Criteria criteria = new Criteria(__DATABASE_NAME__);
   #foreach ($col in $table.Columns)
     #set ( $cfc=$col.JavaName )
     #set ( $cup=$col.Name.toUpperCase() )
***************
*** 906,912 ****
     /** Build a Criteria object from the data object for this peer, 
skipping all binary columns */
     public static Criteria buildSelectCriteria( $table.JavaName obj )
     {
!         Criteria criteria = new Criteria(DATABASE_NAME);
   #foreach ($col in $table.Columns)
     #set ( $cfc=$col.JavaName )
     #set ( $cup=$col.Name.toUpperCase() )
--- 917,923 ----
     /** Build a Criteria object from the data object for this peer, 
skipping all binary columns */
     public static Criteria buildSelectCriteria( $table.JavaName obj )
     {
!         Criteria criteria = new Criteria(__DATABASE_NAME__);
   #foreach ($col in $table.Columns)
     #set ( $cfc=$col.JavaName )
     #set ( $cup=$col.Name.toUpperCase() )
***************
*** 980,986 ****
         $table.JavaName retVal = null;
         try
         {
!             db = Torque.getConnection(DATABASE_NAME);
             retVal = ${retrieveMethod}(pk, db);
         }
         finally
--- 991,997 ----
         $table.JavaName retVal = null;
         try
         {
!             db = Torque.getConnection(__DATABASE_NAME__);
             retVal = ${retrieveMethod}(pk, db);
         }
         finally
***************
*** 1033,1039 ****
         List retVal = null;
         try
         {
!            db = Torque.getConnection(DATABASE_NAME);
            retVal = ${retrieveMethod}s(pks, db);
         }
         finally
--- 1044,1050 ----
         List retVal = null;
         try
         {
!            db = Torque.getConnection(__DATABASE_NAME__);
            retVal = ${retrieveMethod}s(pks, db);
         }
         finally
***************
*** 1115,1121 ****
         $table.JavaName retVal = null;
         try
         {
!            db = Torque.getConnection(DATABASE_NAME);
            retVal = retrieveByPK(
   #set ( $comma = false )
   #foreach ($col in $table.PrimaryKey)
--- 1126,1132 ----
         $table.JavaName retVal = null;
         try
         {
!            db = Torque.getConnection(__DATABASE_NAME__);
            retVal = retrieveByPK(
   #set ( $comma = false )
   #foreach ($col in $table.PrimaryKey)
***************
*** 1546,1552 ****
     protected static TableMap getTableMap()
         throws TorqueException
     {
!         return Torque.getDatabaseMap(DATABASE_NAME).getTable(TABLE_NAME);
     }
   #end ## ends if (!$table.isAlias())

--- 1557,1563 ----
     protected static TableMap getTableMap()
         throws TorqueException
     {
!         return 
Torque.getDatabaseMap(__DATABASE_NAME__).getTable(__TABLE_NAME__);
     }
   #end ## ends if (!$table.isAlias())

***************
*** 1557,1563 ****
         // another value so == check is okay and faster
         if (crit.getDbName() == Torque.getDefaultDB())
         {
!             crit.setDbName(DATABASE_NAME);
         }
     }
 }
--- 1568,1574 ----
         // another value so == check is okay and faster
         if (crit.getDbName() == Torque.getDefaultDB())
         {
!             crit.setDbName(__DATABASE_NAME__);
         }
     }
 }

The patch of Peer.vm may be more verbose than is really necessary, but I 
needed to change the DATABASE_NAME and TABLE_NAME members to avoid 
conflicts with column names in some of my tables. The important 
difference here is the addition of the metho public static List 
doSelect($table.JavaName obj, Connection con).

Now, here's the problematic part. I'll start with my log output from the 
first code segment above:

http8080-Processor24 INFO  template.TemplateSetTransfer - 1 entries 
found in the source database for data set 400133
http8080-Processor24 DEBUG template.TemplateSetTransfer - Saving record 
DataInfo:
Id = 400133
Title = Template Set Editing Tools
DataSourceId = 100005
FgdcNumber = null
LastUpdate = 2004-08-04 10:58:11.0

According to the log, the destination data_info entry looks right. 
Here's the schema.xml entry for the data_info table:

   <table name="data_info">
       <column name="id" type="INTEGER" primaryKey="true" required="true"/>
       <column name="title" size="100" type="VARCHAR"/>
       <column name="data_source_id" required="true" type="INTEGER"/>
       <column name="fgdc_number" size="24" type="VARCHAR"/>
       <column name="last_update" type="TIMESTAMP"/>
      
       <foreign-key foreignTable="data_source">
           <reference local="data_source_id" foreign="id"/>
       </foreign-key>
   </table>

However, here's what shows up when I query the destination database:

mysql> select * from data_info;
+----+----------------------------+----------------+-------------+----------------+
| id | title                      | data_source_id | fgdc_number | 
last_update    |
+----+----------------------------+----------------+-------------+----------------+
|  0 | Template Set Editing Tools |         100005 | NULL        | 
20040804105811 |
+----+----------------------------+----------------+-------------+----------------+
1 row in set (0.00 sec)

No matter what entry I attempt to transfer, the destination ID always 
ends up as 0. Of course, this causes an exception due to a key conflict 
if I attempt another transfer.

Can anyone please help me figure out why the correct primary key is not 
being set?

Thank you very much,

Kris Nuttycombe

--
=====================================================
Kris Nuttycombe
Associate Scientist
Geospatial Data Services Group
CIRES, National Geophysical Data Center/NOAA
(303) 497-6337
Kris.Nuttycombe@xxxxxxxx
=====================================================