Re: Key problems - Torque 3.1.1

I think the problem is with the id being an auto increment column - if u
set it to be autoIncrement="false" on the target database then you will
be able to put the correct value in there.
Guy
On Mon, 2004-11-15 at 14:53, Kris Nuttycombe wrote:
> 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