Beta 3.03 Transaction related record oddity

Classic Servoy
1

Servoy Developer
Version 3.0b3-build 364
Java version 1.5.0_07-b03 (Windows XP)
Sybase iAnywhere 9.0.2

My user table has a one to one relation with the contact table using contact_id_to_contact_id

I have a couple of related fields from the contact table on the user form: contact.last_name and contact.first_name.

If I create a new contact record and then a new user record and I set user.contact_id = contact.contact_id, on the user form I can type into contact.last_name and contact.first_name with no problems.

But if I start a transaction before creating those records, I can still type into each of those related fields but as soon as I exit each one its contents is no longer displayed and the input value does not make it into the contact record.

But, if I include a call to databaseManager.SaveData() before data entry begins, the related fields work as expected.

Is this a bug or a feature? If the latter could anyone please explain the rules to be used to program this correctly?

thanks,

Terry

2

Hi Terry,

In short it’s neither a feature nor bug. It’s the way SQL databases work. Here’s what’s going on:

  1. When you use a transactio, the data is not committed to the database before you call commit.

  2. This means that the record ID for the main record (not the child) is not really “there” yet, and COULD be invalid (because someone not using a transaction may have already grabbed it).

  3. When you call “saveData()” the PK is saved to the backend and is “reserved” so it’s a “real” ID. That’s why it works.

BTW: Why do you have a self-join to enter data??

Hope this helps.

3

Bob,

thanks for your response. It makes sense. Based on that I’d say it was a feature :slight_smile:

Its not a self-join I’m using. Its from column contact_id in the catuser table to contact_id in the contacts table. Hopefully that is the right thing to use?

Terry

4

I happen to know from our discussion offline that the way you are creating the two related records is by doing this:

forms.user_form.newRecord();
forms.contact_form.newRecord();
// link contact record to the user
 
contact_id = forms.contact_form.contact_id;

in other words you are creating the two records on two different forms and then linking the records “by hand” by assigning a foreign key to the related record.

Although you may get this approach to work, you are trying to do the long way what relations do for you automatically.

All you need to do is create the related record using

user_to_contacts.newRecord()

and everything will work fine, regardless of whether or not you use transactions. Because you’re using the relation, Servoy will take care of assigning the foreign key for you.

So your complete method would look like this:

forms.user_form.newRecord();
user_to_contacts.newRecord() ;