Recursive foreign key : SQLException

[Nicolas Pottrain <np...@reference.be>]

Hi all,

I have a problem using torque for a table that has a foreign key to itself
(in order to implement tree structures).  This table in question has an
integer primary key.  I'm using oracle 8 as DBMS.

Insertion of a new record at the top level of a tree (with a null value for
the FK field), will throw an sql exception when using the standard .save()
method of the torque table class.  

In the archives I found somebody posted a workaround to this problem by
using the doInsert() method of the object's peer class, using a Criteria
object with the null inserted as FK.

Another thing I did was overloading the table class' save() method, to check
for a null value fk, and if so, use the workaround, otherwise use the
enherited save() method from the baseclass.

I was wondering if there is a more elegant solution to this problem?

Kind regards,

Nicolas

Re: Recursive foreign key : SQLException

[Malte Stien <ma...@stien.de>]

Hi,

I am not sure, if I really understand your problem. I am doing basically the 
same thing and it works fine for me. What does the SQL-Exception say?

Did you set the defaultJavaType to "object"? The problem might be that Torque 
maps Java-primitives (the default) to the columns of the database. That does 
not allow Torque to save "nothing" to a column. Hence, for columns of type 
integer Torque comes up with the value 0 and if that is a foreign-key column 
that references a non-existing primary key in another table you get an 
SQL-excpection saying that the referenced key could not be found. If that is 
the case, switch defaultJavaType to "object" and you will be fine.

<database
  ...
  defaultJavaType="object">

I have the following table that represents a tree as well and it works fine 
for me. If I would like to save a root element in the tree, I just do not set 
the foreign-key at all and just save the object.
...
  <table name="ExpirableDataItem" description="...">
    <column
      name="ExpirableDataItemID"
      required="true"
      primaryKey="true"
      type="INTEGER"
      description="ID of the ExpirableDataItem"/>
    <column
      name="ExpiryTimestamp"
      required="false"
      type="TIMESTAMP"
      description=""/>
    <column
      name="PreviousExpiryDataItemID"
      required="false"
      type="INTEGER"
      description=""/>
    <!-- ModelRelationShip R31 -->
    <foreign-key foreignTable="ExpirableDataItem">
      <reference
        local="PreviousExpiryDataItemID"
        foreign="ExpirableDataItemID"/>
    </foreign-key>
  </table>
...


On Tuesday 17 June 2003 18:28, Nicolas Pottrain wrote:
> Hi all,
>
> I have a problem using torque for a table that has a foreign key to itself
> (in order to implement tree structures).  This table in question has an
> integer primary key.  I'm using oracle 8 as DBMS.
>
> Insertion of a new record at the top level of a tree (with a null value for
> the FK field), will throw an sql exception when using the standard .save()
> method of the torque table class.
>
> In the archives I found somebody posted a workaround to this problem by
> using the doInsert() method of the object's peer class, using a Criteria
> object with the null inserted as FK.
>
> Another thing I did was overloading the table class' save() method, to
> check for a null value fk, and if so, use the workaround, otherwise use the
> enherited save() method from the baseclass.
>
> I was wondering if there is a more elegant solution to this problem?
>
> Kind regards,
>
> Nicolas
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org