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
> * [EMAIL PROTECTED] #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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
