I have two classes, PaymentTxn and Customer. PaymentTxn contains a foreign
key which points to Customer. When I create a new PaymentTxn, I set the
foreign key,
but the generated SQL gets the wrong value. Perhaps I am doing something
wrong in my sequence of code?
Here is the declaration for PaymentTxn :
----------------------------------------------------------------------------
-
public class PaymentTxn implements PaymentTxnInterface {
private int paymentTxnID;
// some other fields
private int customerID;
private CustomerInterface issuingCustomer;
private int bankID;
private BankInterface bank;
public void setIssuingCustomer(CustomerInterface customer) {
this.issuingCustomer = customer;
}
--------------------------------------
Here is the code which creates the paymentTxn and attempts to
insert it
--------------------------------------
PaymentTxn paymentTxn = new PaymentTxn();
...
CustomerInterface customer = payer.getCustomer();
System.out.println("customer id is " + customer.getID());
paymentTxn.setIssuingCustomer(customer);
---------------------------------------
and the printout when this segment of code is run :
----------------------------------------
customer id is 1
801 ERROR [ExecuteThread: '13' for queue: 'default']
accesslayer.JdbcAccessIm
l - SQLException during the execution of the insert (for a
com.mobius.activebil
.persistentobjects.PaymentTxn): INSERT statement conflicted with
COLUMN FOREIGN
KEY constraint 'FK_PaymentTxn_Customer'. The conflict occurred in
database 'Act
veBill', table 'Customer', column 'customerID'.
com.jnetdirect.jsql.w: INSERT statement conflicted with COLUMN
FOREIGN KEY cons
raint 'FK_PaymentTxn_Customer'. The conflict occurred in database
'ActiveBill',
table 'Customer', column 'customerID'.
-------------------------------------------
When I look at the generated SQL, I can see the error immediately - the
INSERT statement contains a value of 0 for customerID, even though the
correct customerID (as verified by the printout) is 1.
-------------------------------------------
INSERT INTO PaymentTxn
(paymentTxnID,transactionNumber,amount,status,dateStatusChanged,payDate,conf
irmationNumber,bankVoidCode,bankVoidDesc,isPostedToICMS,customerID,bankID,pa
yerID)
VALUES ( '1', '55555', '300.35', 'completed', '', '2003-08-12 11:33:00.0',
'4566', 'xx', 'xxxx', 'true', '0', '1', '1' )
--------------------------------------------
(The customerID value is third from the end)
What is going on here? Has anyone else encountered this kind of problem?
For reference, here are the entries from database_user.xml for the two
classes.
----------------------------------------------------------------------------
-----------
<!-- Definitions for PaymentTxn table -->
<class-descriptor
class="com.mobius.activebill.persistentobjects.PaymentTxn"
table="PaymentTxn"
>
<field-descriptor
name="paymentTxnID"
column="paymentTxnID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor
name="transactionNumber"
column="transactionNumber"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="amount"
column="amount"
jdbc-type="DOUBLE"
/>
<field-descriptor
name="status"
column="status"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="dateStatusChanged"
column="dateStatusChanged"
jdbc-type="TIMESTAMP"
/>
<field-descriptor
name="payDate"
column="payDate"
jdbc-type="TIMESTAMP"
/>
<field-descriptor
name="confirmationNumber"
column="confirmationNumber"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="bankVoidCode"
column="bankVoidCode"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="bankVoidDesc"
column="bankVoidDesc"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="isPostedToICMS"
column="isPostedToICMS"
jdbc-type="BIT"
/>
<field-descriptor
name="customerID"
column="customerID"
jdbc-type="INTEGER"
/>
<reference-descriptor
name="issuingCustomer"
class-ref="com.mobius.activebill.persistentobjects.Customer"
proxy="true"
>
<foreignkey field-ref="customerID"/>
</reference-descriptor>
<field-descriptor
name="bankID"
column="bankID"
jdbc-type="INTEGER"
/>
<reference-descriptor
name="bank"
class-ref="com.mobius.activebill.persistentobjects.Bank"
proxy="true"
>
<foreignkey field-ref="bankID"/>
</reference-descriptor>
<field-descriptor
name="payerID"
column="payerID"
jdbc-type="INTEGER"
/>
<reference-descriptor
name="payer"
class-ref="com.mobius.activebill.persistentobjects.User"
proxy="true"
>
<foreignkey field-ref="payerID"/>
</reference-descriptor>
<collection-descriptor
name="suggestedPayments"
element-class-ref="com.mobius.activebill.persistentobjects.SuggestedPayment"
auto-retrieve="true"
auto-update="false"
indirection-table="CompletedBy"
>
<fk-pointing-to-this-class column="paymentTxnID" />
<fk-pointing-to-element-class column="suggestedPaymentID" />
</collection-descriptor>
</class-descriptor>
<!-- Definitions for Customer table -->
<class-descriptor
class="com.mobius.activebill.persistentobjects.Customer"
table="Customer"
>
<field-descriptor
name="customerID"
column="customerID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor
name="customerNumber"
column="customerNumber"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="name"
column="name"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="title"
column="title"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="type"
column="type"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="subtype"
column="subtype"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="countryCode"
column="countryCode"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="apartmentTypeCode"
column="apartmentTypeCode"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="apartment"
column="apartment"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="floorDescrip"
column="floorDescrip"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="buildingName"
column="buildingName"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="streetNumberFrom"
column="streetNumberFrom"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="streetSubdescription"
column="streetSubdescription"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="streetName"
column="streetName"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="streetSubtitle"
column="streetSubtitle"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="toStreet"
column="toStreet"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="areaCode"
column="areaCode"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="areaDescrip"
column="areaDescrip"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="estate"
column="estate"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="cityAbbrev"
column="cityAbbrev"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="cityDescrip"
column="cityDescrip"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="city"
column="city"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="districtCode"
column="districtCode"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="districtDescrip"
column="districtDescrip"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="postalCode"
column="postalCode"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="status"
column="status"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="isDeleted"
column="isDeleted"
jdbc-type="BIT"
/>
<field-descriptor
name="dateDeleted"
column="dateDeleted"
jdbc-type="TIMESTAMP"
/>
<field-descriptor
name="dateEnrolled"
column="dateEnrolled"
jdbc-type="TIMESTAMP"
/>
<collection-descriptor
name="accounts"
element-class-ref="com.mobius.activebill.persistentobjects.Account"
proxy="true"
>
<inverse-foreignkey field-ref="customerID"/>
</collection-descriptor>
<collection-descriptor
name="users"
element-class-ref="com.mobius.activebill.persistentobjects.User"
proxy="true"
>
<inverse-foreignkey field-ref="customerID"/>
</collection-descriptor>
<collection-descriptor
name="enrolledBanks"
element-class-ref="com.mobius.activebill.persistentobjects.Bank"
auto-retrieve="true"
auto-update="false"
indirection-table="EnrolledForPayment"
>
<fk-pointing-to-this-class column="customerID" />
<fk-pointing-to-element-class column="bankID" />
</collection-descriptor>
</class-descriptor>
