I have discovered a significant problem since Servoy 2.2.2. A relationship that can be manually verified to be valid is becoming invalid. This problem does not occur in 2.2.1. Here are the details:
A quote has one child email record. Clicking a ‘Send Email’ button checks to see if the current quote has an email record and creates a new one if not. The email record’s PK is stored in a global and the email record is displayed in a form in dialog where the user is able to enter basic email information. Additionally, there is a tabpanel that displays quotes that can be included as email attachments by checking a checkbox. Clicking one of the checkboxes runs a method that adds or removes the selected quote’s PK from a concatenated string stored in the current email record through the global-to-email-record relationship.
If the email record is newly created, editing the value of any field causes the global-to-email-record relationship to become invalid. The relationship remains invalid until design mode is toggled. Once the solution has gone in and out of design mode and the ‘Send Email’ button is clicked again, the relationship remains valid and this problem does not occur.
I have included a sample solution. Here are the steps to reproduce the problem:
While viewing the first record in the ‘specs’ form, click the ‘Send Email’ button. The record already has an associated email record, so everything in the resulting form in dialog should be working propery. Check and uncheck the checkboxes to see the value of the ‘attachment_ids’ column change.
Click the ‘Delete This Record’ button. The email record is deleted and the form in dialog closes.
Click the ‘Send Email’ button again. This time, a new email record has been created and is displayed in the form in dialog. Before editing or clicking anything, note the value of the ‘raw_sn’ column and the value of the ‘GLOBAL SN’ column on the records in the tabpanel. They should match. Next to the ‘GLOBAL SN’ column is a ‘RELATED SN’ column that should be showing the same value as ‘raw_sn’.
Edit and click out of any of the editable fields, or click any of the checkboxes in the tabpanel records. As soon as the email record is edited, the value in the ‘RELATED SN’ column disappears and the checkboxes no longer function properly. The relationship is now broken.
Click the ‘Close’ button. Navigate to the next spec record. Click ‘Send Email’. This record already has an email record, so all edits and checkboxes should work properly. Click ‘Close’.
Navigate back to the first record. Click ‘Send Email’ and note that the relationship is still borken and the checkboxes do not work.
Click ‘Close’. Enter and exit design mode. You should still be on the first record. Click ‘Send Email’ and note that now everything works correctly and the relationship remains valid.
I am running MS SQL 2003 SP4, Windows 2000 Server SP4, Servoy 2.2.3 (first appeared in 2.2.2). My sequences are set to db identity and the SQL tables are all set up properly for this to happen. The method that creates the new email record is using controller.newRecord(true) to create the new record on top. Things like controller.saveData() and application.updateUI() do not have any effect. Adding this code
at the end of the method that creates the new email record causes the problem to not happen, but only in developer. Once the solution is shared via server and accessed through Servoy client, the flushAllClientsCache code doesn’t make a difference.
Thanks for bearing with me. I discovered this several weeks ago, but couldn’t spend the time making a sample, so I coded in a different direction. Now I have encountered it again and have no other way out.
a relation uses a String column and a global that contains “null” and points to the messages table. This relation should see the reference value from the i18n messages table for the given String column as message_key.
A method checks if the record exists and creates it.
Once it is created you can edit the field and type something. I have checked in the database and I saw the record but without data. Then you click in the background and expect the text to be saved. But now it disappears. Checking the database tells me it is still there but without data. If you now type again, a second record containing data is created.
I have fixed the issue by doing a refreshFromDatabase right after the creation but something there goes wrong.
you do say that if you use this on sybase it doesn’t happen?
Then i think the only difference is that you use DB_IDENT columns? (Because this is then the Big change between the 2 at the solution level)
Yes, this does not occur under Sybase and yes, switching the sequence in my sample solution under MS SQL from db identity to servoy seq does fix this problem, but I cannot do this in my real-life solution.
This is not an issue under Servoy 2.2.1. With this in mind, will it be possible to fix 2.2.3 so that I may still use db identity?
SteveInLA:
3) Click the ‘Send Email’ button again. This time, a new email record has been created and is displayed in the form in dialog. Before editing or clicking anything, note the value of the ‘raw_sn’ column and the value of the ‘GLOBAL SN’ column on the records in the tabpanel. They should match. Next to the ‘GLOBAL SN’ column is a ‘RELATED SN’ column that should be showing the same value as ‘raw_sn’.
What you are trying todo is strange:
-you have a editable checkbox on a calculation (and try to set a value by clicking)
-you try to lookup a db_ident value by query (without saving first, a db identity is generated when the record is inserted)
-part of the script loadSpecsListRecordsForEmail should read:
controller.saveData();
var query = 'SELECT raw_sn FROM specs WHERE specs.customer_id = ? ‘+ specStages + ’ ORDER BY specs.creation_date desc’;
var data = new Array();
data[0] = customer_id
forms.specs_list.controller.loadRecords(query,data);
BTW many databases will not allow you to update/change the db identity value
Attached is a new sample solution. It contains one table with one self-join relationship through the primary key. The primary key’s sequence is set to db identity and has identity insert turned on in MS SQL. The main form contains a tab panel that displays two forms through the self-join relationship. Follow these steps to reproduce the problem:
From the menu bar, choose Select]New Record. A new record is created. Click anywhere on the main form to force the primary key to be displayed.
Enter any text into any field shown in the tab panel. Do this BEFORE entering text into the fields on the main form.
Enter text into either of the fields on the main form. As soon as you leave the field, the text in the tab panel disappears. Entering text into the tab panel fields causes my computer to beep and the text ‘java.lang.Object@da5dd’ to appear in the field.
Enter and exit design mode. Select the record that was just giving the error and note that data can be entered anywhere now.
From the menu bar, choose Select]New Record. A new record is created.
Enter text into the fields on the main form BEFORE entering text into the tab panel fields. Once text is entered and you exit the field, text can be entered into the tab panel fields with no problems, but only if you have toggled design mode after initially encountering this problem.
This error does not occur if I switch the sequence to servoy seq and turn off identity insert in MS SQL, but in my real solution, that is not possible. This error does not occur in Servoy 2.2.1 even with sequence set to db identity and identity turned on in MS SQL.
In response to Jan’s post:
-you have a editable checkbox on a calculation (and try to set a value by clicking)
I am sorry that this seemed to cause so much confusion. While working with Maarten, we substituted a button for the checkbox. This had no effect on this problem whatsoever. The checkbox is not the problem. The method on the checkbox is not the problem. The fact that the relationship breaks when the displayed form’s data is edited is the problem.
-you try to lookup a db_ident value by query (without saving first, a db identity is generated when the record is inserted)
Note that my query is 'SELECT raw_sn FROM specs WHERE specs.customer_id = ? ‘+ specStages + ’ ORDER BY specs.creation_date desc’. The records I am retrieving are not from the table where I have just created the new record (spec_email). The concept in this case is that a user wants to create a record (spec_email) associated with a particular quote (in this case called specs) that includes a reference to the quotes to be included as pdf email attachments. This query is not gathering records that haven’t already been saved.
BTW many databases will not allow you to update/change the db identity value
As far as I can tell, there is no place where I am manually updating or changing the db identity in this solution. Please show me if this is not the case.
SteveInLA:
…Do this BEFORE entering text into the fields
In Servoy 2.2.x its needed to save before any links are made on db_ident pks (because the pk is generated on insert), in the sample you provided I do not have any problem when using the first button which does a save after newRecord.
In Sevoy 3.0 this behaviour has already bin improved, to work without explicit save by using temporary keys.
In Servoy 2.2.x its needed to save before any links are made on db_ident pks
Then why do I not have this problem in 2.2.1? And why does the sample solution which includes a saveData still produce this problem?
in the sample you provided I do not have any problem when using the first button which does a save after newRecord.
Please be sure you are using MS SQL Server 2000 as your backend database as this does not occur using Sybase. Doing a saveData or an updateUI has had no effect on this problem for me.
I am not making this problem up. It is occurring more frerquently as I continue development.
By the way, this post describes an issue that I think is either identical or very closely related to my issue:
And please have a look at my comment, too. I think this is the same problem and it does not involve DB identities, because in my case it happens on the messages table and that is the only table where I use a Servoy sequence. I have to say, however, that I wasn’t able to reproduce this in a simple sample solution. The problem seems to occur under circumstances, that are not present in a simple setup. I will see if I can isolate those circumstances…
I use the jtds JDBC driver version 1.2, but I have also tried Microsoft’s JDBC driver and the problem occurs as well. I have 100% percent reproducability of this problem with the database running on two different MS SQL servers, with developer running on the same machine as MS SQL Server (Windows Server 2000) and also from a Mac OS X workstation on the same network.