unable to save data

Classic Servoy
1

I have a table that stores info for a letter mailing called mailings. I have a table that stores information for contacts called contacts. And I also have a table that stores the what contacts have gotten what mailings called mailing_contact_rel.

This is a script that is suppose to create a new mailing and relate the contacts in the current foundset to that mailing. The issue I am having is that calling newRecord on the mailing table is ovewriting old mailing with blank information. aka creating a new record with a primary key that already exists. These other mailing were imported via mysql commands from another database. And the new entries in the mailing_contact_rel table only have null values. I’ve included the msql creation script and the code for the method.

forms.mailing.controller.newRecord();
controller.saveData();
forms.mailing.officers_mailed_to = databaseManager.getFoundSetCount(foundset);

var MailID = forms.mailing.id_mailing;

for (var i = 1 ; i <= forms.contact.foundset.getSize(); i++)
{
	forms.mailing_to_contact.controller.newRecord()
	controller.saveData();
	
	forms.mailing_to_contact.fk_mailing_id = MailID;
	var record = forms.contact.foundset.getRecord(i);
	forms.mailing_to_contact.fk_contacts_id = record.id_contact;

	globals.Tester_01 = MailID;
	globals.Tester_02 = record.id_contact;
	
	forms.mailing_to_contact.controller.saveData();

}

and the msql code

CREATE TABLE `mailing` (
  `id_mailing` int(10) unsigned NOT NULL auto_increment,
  `letter_id_letter` int(10) unsigned default '1',
  `mailing` varchar(255) NOT NULL default '',
  `description` varchar(255) default NULL,
  `date` date default NULL,
  `strategy` varchar(255) default NULL,
  `mailing_type` enum('Marketing','Thoughts') default NULL,
  `officers_mailed_to` int(10) unsigned default NULL,
  `num_responses` int(10) unsigned default NULL,
  `method` enum('Email','Postal') default NULL,
  PRIMARY KEY  (`id_mailing`),
  INDEX (`letter_id_letter`),
  CONSTRAINT `mailing_ibfk_1` FOREIGN KEY (`letter_id_letter`) REFERENCES `letter` (`id_letter`)
) TYPE=InnoDB; 

CREATE TABLE `contacts` (
  `id_contact` int(10) unsigned NOT NULL auto_increment,
  `company_name` varchar(100) NOT NULL default '',
  `company_id_company` int(10) unsigned NOT NULL default '0',
  `first_name` varchar(100) NOT NULL default '',
  `last_name` varchar(100) NOT NULL default '',
  `middle_name` varchar(45) default NULL,
  `title` varchar(255) default NULL,
  `address_1` varchar(100) default NULL,
  `address_2` varchar(100) default NULL,
  `address_3` varchar(100) default NULL,
  `city` varchar(100) default NULL,
  `state` varchar(100) default NULL,
  `country` varchar(100) default NULL,
  `zip` int(10) unsigned default NULL,
  `phone_number` varchar(45) default NULL,
  `phone_ext` varchar(45) default NULL,
  `fax_number` varchar(45) default NULL,
  `fax_ext` varchar(45) default NULL,
  `alt_phone` varchar(45) default NULL,
  `alt_ext` varchar(45) default NULL,
  `assistant` varchar(100) default NULL,
  `assistant_phone` varchar(45) default NULL,
  `assistant_ext` varchar(45) default NULL,
  `email_address` varchar(100) default NULL,
  `male_female` enum('Male','Female') default NULL,
  `age` int(10) unsigned default NULL,
  `id_status` enum('Active with Anett','Client','Client/Contact','Contact','Dead','Do Not Mail','Remail','Responder','Thoughts') default NULL,
  `referred_by` varchar(100) NOT NULL default '0',
  `receive_thought_email` tinyint(1) default NULL,
  `receive_thought_post` tinyint(1) default NULL,
  `last_mail_date` date default NULL,
  `last_email_date` date default '0000-00-00',
  `responder_name` varchar(255) default '',
  `last_mailing_response` varchar(100) default '',
  `response_type` varchar(100) default '',
  `response_date` date default '0000-00-00',
  `who_responded` varchar(100) default '',
  `referrals_given_to` varchar(100) default '',
  `discount` tinyint(9) default '0',
  `price_quoted` decimal(10,0) default '0',
  `responder_notes` varchar(255) default '',
  `interests` varchar(255) default '',
  `last_mailing_response_id` int(10) unsigned default NULL,
  `private_coaching_notes` varchar(255) default NULL,
  `contact_for_notes` varchar(255) default NULL,
  `strategy` varchar(100) default NULL,
  `last_update` timestamp,
  PRIMARY KEY  (`id_contact`),
  INDEX (`last_mailing_response_id`),
  INDEX (`company_id_company`),
  CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`last_mailing_response_id`) REFERENCES `mailing` (`id_mailing`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `contacts_ibfk_2` FOREIGN KEY (`company_id_company`) REFERENCES `company` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB; 


CREATE TABLE `mailing_contact_rel` (
  `id_rel` int(12) unsigned NOT NULL auto_increment,
  `fk_contacts_id` int(10) unsigned default '0',
  `fk_mailing_id` int(10) unsigned default '0',
  PRIMARY KEY  (`id_rel`),
  INDEX (`fk_contacts_id`),
  INDEX (`fk_mailing_id`),
  CONSTRAINT `mailing_contact_rel_ibfk_1` FOREIGN KEY (`fk_contacts_id`) REFERENCES `contacts` (`id_contact`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `mailing_contact_rel_ibfk_2` FOREIGN KEY (`fk_mailing_id`) REFERENCES `mailing` (`id_mailing`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
2

You’re “saving” the data in the current controller - not in the one that you’re creating the record in. Your code:

forms.mailing.controller.newRecord();
controller.saveData();

SHOULD BE:

forms.mailing.controller.newRecord();
forms.mailing.controller.saveData();

Do the SAME THING in your loop - reference the FORM as well as the controller - so Servoy knows which table you’re talking about in your method.

Hope this helps,

Bob Cusick

3

That is how I had it written originally. I had changed it because I was just trying a few different things to see if I could get it to work.

I changed it back to that. Still have the same problem. Also it appears that servoy isn’t actually writting over the data in the mailing table. It just shows up that way in servoy. But if you exit and go back into servoy the information is there again. And a new blank entry is added with an id one more then the previous one, just like it should have been to start with.

So it appears that I can create new records, but I’m having trouble writting any information to them. my guess would be that the problem is in this bit of code.

   forms.mailing_to_contact.fk_mailing_id = MailID; 
   var record = forms.contact.foundset.getRecord(i); 
   forms.mailing_to_contact.fk_contacts_id = record.id_contact; 

   forms.mailing_to_contact.controller.saveData();

I’ve set global variables to forms.mailing_to_contact.fk_contacts_id and the value is always NULL, and I’ve set global variables to record.id_contact and it shows up as the right contact id, so for some reason the assignment forms.mailing_to_contact.fk_contacts_id = record.id_contact; is not working. Is there maybe some sort of typecasting I need to be doing?

4

In your relations have you checked the checkbox “Allow creation of related records” ???

:D

Try it.

Bob

5

first:

controller.saveDate() will save ALL pending changes. It doesn’t do only the form you are calling it on.

second:

this seems more of a sequence out of synch problem! You have imported data in the tables behind servoy. So the question is what kind of sequence do you use in servoy? I see that youre tables are auto_increment so are you sure that you use db_indent sequences for youre table/pk columns?
And not servoy sequences?

6

I’m working with chad on this project.
I changed the sequences to db. This fixed the problem with overwriting entries. But I still have the problem where the new entries in the mailing_contact_rel table are NULL.

7

I seemed to have figured out what has been causing all of our problems. All our keys were defined as unsigned ints. I noticed in servoy while changing the keys sequence that all the unsigned int columns showed up as datatype. I made them regular ints and now everything works just fine. It looks like that since servoy didn’t know what the datatype was, that it just couldn’t write data to the field, or follow a relation based on that field.

Is servoy suppose to be able to handle unsigned ints, or were we doing something wrong?

8

That is a problem with the driver of MySQL
They are reporting wrong things back when using unsiged ints

It should be fixed with the next version of the driver (or revert to 3.0.11)