OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED]Trouble linking many wines to many grapes on a form
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
JDHeinzmann
General User
General User


Joined: 04 Mar 2008
Posts: 44
Location: Manchester, NH

PostPosted: Sun Apr 06, 2008 4:32 pm    Post subject: [SOLVED]Trouble linking many wines to many grapes on a form Reply with quote

I have built a database using MySQL 5.0 to track bottles of wine in a wine cellar. One of the tables in this database is the Wine table with a non-null, auto-incrementing, integer primary key called WineID. Another table called Grape tracks the details of many grape varieties with a non-null, auto-incrementing, integer primary key called GrapeID. To link the grapes that went into making each wine and track their percentages, I have created a WineGrapes table as follows:

Code:
CREATE  TABLE IF NOT EXISTS `JDsWine`.`WineGrapes` (
  `WineID` INT UNSIGNED NOT NULL COMMENT 'ID of wine this grape is used in' ,
  `GrapeID` INT NOT NULL COMMENT 'ID of grapes this wine is made from' ,
  `Percent` INT UNSIGNED NULL COMMENT 'Percent of this grape used in this wine' ,
  PRIMARY KEY (`WineID`, `GrapeID`) ,
  INDEX fk_WineGrapes_Wine (`WineID` ASC) ,
  INDEX fk_WineGrapes_Grape (`GrapeID` ASC) ,
  CONSTRAINT `fk_WineGrapes_Wine`
    FOREIGN KEY (`WineID` )
    REFERENCES `JDsWine`.`Wine` (`WineID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_WineGrapes_Grape`
    FOREIGN KEY (`GrapeID` )
    REFERENCES `JDsWine`.`Grape` (`GrapeID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
COMMENT = 'Grapes used to make a particular wine and their percentages';


I have seen this referred to as a cross-reference or pivot table, as it manages the many-to-many relationship that can exist between a wine and its grapes (a variety of grape can be used in many different wines and a wine can be made from a blend of one or more grape varieties).

All this works fine in MySQL, as well as from ooBase via ODBC when accessing the WineGrapes table directly from a main form via a Table Control.

What I need help with is that I have created a form called frmWine for entering and maintaining wine records. It displays the pertinent information about a wine, one record at a time. On that form, I created a sub form, sfrmWineGrape, that I placed a TableControl on, tcWineGrapes, containing numeric columns for WineID, GrapeID and Percent from the WineGrapes table. I linked the Master and Slave fields via WineID.

This form displays data from my database as expected. When I navigate to a wine, the details of the wine display in frmWine and the proper WineID, GrapeIDs and Percents show in tcWineGrapes. I can change the GrapeID or the Percent for one of the grapes and save it just fine. And I can successfully delete rows from tcWineGrapes.

This all falls apart, however, when I try to add a grape for the selected wine in tcWineGrapes. Let’s say I have selected the wine with WineID=102 in the main form, and I have two rows displayed in tcWineGrapes, each showing WineID=102, and the first showing GrapeID=54 at 40% and the second showing GrapeID=104 at 50%. Then I click on the GrapeID in the 3rd row to enter a new grape. At that point, WineID for the new grape conveniently gets automatically set to 102 (as it should). I enter 66 for the new GrapeID and click on the Percent column and get this Base error message:

Quote:
“Error inserting the new record” [MySQL][ODBC 3.51 Driver][mysql-5.0.45-community-nt]Cannot add or update a child row: a foreign key constraint fails (`jdswine/winegrapes`, CONSTRAINT `fk_WineGrapes_Wine` FOREIGN KEY (`WineID` ) REFERENCES `wine` (`WineID`) ON DELETE NO ACTION ON UPDATE NO ACTION


and the WineID in tcWineGrapes automatically changes to 0 for the attempted new record.

If I access the WineGrapes table directly from the Base GUI, I can add this new record just fine with no complaints from Base or MySQL.

Can you tell me what is going on here and what is going wrong? Do you know what I can do to fix this? I have tried some of the macros suggested on other posts about xref tables to no avail. Unfortunately, I am too new at this to understand what those macros are trying to do or how to debug them.

Thanks!
_________________
JD

MySQL 5.0.45-community-nt, OOo 2.3.1, MySQL ODBC 5.1.5, Windows 6.0 SP1.


Last edited by JDHeinzmann on Mon Apr 21, 2008 4:36 am; edited 1 time in total
Back to top
View user's profile Send private message
TessaES
OOo Advocate
OOo Advocate


Joined: 17 Feb 2007
Posts: 228
Location: Solna

PostPosted: Mon Apr 07, 2008 8:48 am    Post subject: Reply with quote

This post explains all about many-to-many relationships and using a form to enter new data for them.
Back to top
View user's profile Send private message
JDHeinzmann
General User
General User


Joined: 04 Mar 2008
Posts: 44
Location: Manchester, NH

PostPosted: Wed Apr 09, 2008 7:28 pm    Post subject: This is the same problem as Drew Jensen's Movie.odt solves Reply with quote

Indeed, the post that TessaES suggested does explain a complicated way of dealing with many-to-many relationships using lots of buttons and macros. Ow, ow, ow, my head! I am certain there is a more elegant way!

So, while I was avoiding dealing with my problem I got to reading this thread: Where is the Enforce Referential Integrity Option? and lo and behold, Drew Jensen did exactly what I am trying to do with his little Movie database suggestion that uses a MovieGenre table to handle the Many-to-Many relationship without using any macros. It works exactly as I want mine to work. And I tried to do mine exactly as he did his. Exactly, exactly, exactly!

Well... not exactly... I am using MySQL through ODBC, and I added a Percent column to my WineGrapes xref table. But I don't see that that should be violating the referential integrity constraint. And I wasn't using a ListBox to pick Grapes and automatically insert their GrapeIDs, I was doing it manually just to get it working, before I switched to using the ListBox.

So I tried setting up my form using only the ListBox in the Table Control just like he did in Movies.odb but I get the same foreign key constraint failure message as listed in my original post.

I know the Percent column will complicate matters on my form, but I want to understand this referential integrity constraint problem first.

Thanks.
_________________
JD

MySQL 5.0.45-community-nt, OOo 2.3.1, MySQL ODBC 5.1.5, Windows 6.0 SP1.
Back to top
View user's profile Send private message
JDHeinzmann
General User
General User


Joined: 04 Mar 2008
Posts: 44
Location: Manchester, NH

PostPosted: Fri Apr 11, 2008 6:42 pm    Post subject: Still mystified Reply with quote

In scrutinizing my WineGrapes table structure to to see if I could find any clues, I realized that the GrapeID was a signed integer type, whereas all my other keys are unsigned integers. It turns out that for some reason I was consistent and used a signed integer for GrapeID in the Grape table too, so that should not be the problem. I went through my database and made sure that all my indexes are unsigned int for consistency. Still no luck though.

I remain mystified. I have no problem inserting records into the WineGrapes table directly, I just can't seem to do it from a Table Control on a subform without being blocked by a referntial integrity constraint.

Is there a way to see what the SQL statement(s) that the form is trying to execute is?
_________________
JD

MySQL 5.0.45-community-nt, OOo 2.3.1, MySQL ODBC 5.1.5, Windows 6.0 SP1.
Back to top
View user's profile Send private message
cscj01
General User
General User


Joined: 12 Apr 2008
Posts: 11

PostPosted: Sat Apr 12, 2008 3:50 pm    Post subject: Reply with quote

I am having a similar problem with Forms connected to a MySQL DB using ODBC. See this link:

http://user.services.openoffice.org/en/forum/viewtopic.php?f=39&t=4175

I finally have gotten to the point that I can add records to the table in the subform.. Here is what I have to do:

(1) Enter the row information;
(2) press the save record icon;
(3) the Form shows a record with a 0 for the key field;
(4) retype the key field using the original value;
(5) press the save record icon.
(6) the record is saved correctly.

I have checked every property in the table control and the table object. This has to be a bug or something so simple I can't think of it.

I should mention I use an associative table to handle the many to many issue.

Maybe if I post here, someone will have an idea. I have gotten no responses on the other forum.
Back to top
View user's profile Send private message
JDHeinzmann
General User
General User


Joined: 04 Mar 2008
Posts: 44
Location: Manchester, NH

PostPosted: Sat Apr 12, 2008 6:40 pm    Post subject: The problem is not that I am using MySQL Reply with quote

When I saw Drew's Movie.odb example I wondered if the difference with mine is that I am using MySQL and he is using the embedded HSQL Database Engine. Well to test this theory out, I created my own Movie database in MySQL replete with the requisite Movie, Genre and MovieGenre tables. I replicated his primary and foriegn keys, connected to it using Base, replicated his Movie form with SubForm for MovieGenre, and populated the Movie and Genre tables with the same 3 movies and Genres.

It works great. I can select the Genres from the ListBox in the SubForm and everything is fine. Smile

So, what is wrong with mine? Grrrr... Mad

My Wine form has a lot of other stuff on it right now. I wonder if that is somehow screwing me up. I will try creating a simpler Wine form with only WineID and a subform with the desired TableControl hooked to the WineGrapes table. I'll let you know how it goes.
_________________
JD

MySQL 5.0.45-community-nt, OOo 2.3.1, MySQL ODBC 5.1.5, Windows 6.0 SP1.
Back to top
View user's profile Send private message
JDHeinzmann
General User
General User


Joined: 04 Mar 2008
Posts: 44
Location: Manchester, NH

PostPosted: Sat Apr 12, 2008 9:04 pm    Post subject: Relations strangeness Reply with quote

Okay, so I tried making a very simple form with one subform. The mainForm is associated with and displays a couple of fields from the Wine table and the subform is associated with the WineGrapes table and contains one TableControl with one Grapes column of type ListBox, implemented the same way as Drew's Genre ListBox in movies.odb. It does not work, I get the exact same referntial integrity constraint message upon trying to insert a new WineGrapes record from the Table Control.

Interestingly though, when I created the MySQL version of movies.odb, and I created the subform, in step 2 of the Form Wizard, and I clicked the Add Subform checkbox, I was allowed to activate the radio button labelled "Subform based on existing relation" and select my MovieGenre table as the relation I wanted to add. This is the first time I have been able to do anything like this. Usually there are no relations listed to choose from.

So I snooped around some more... where is this relation coming from? Now, with MySQL, all these relationships were formed using foreign keys in the schema for the MySQL database using a MySQL script, not Base. In Base, I clicked Tools | Relationships... and got the message "The database does not support relations." Confused I thought that was weird. On the one hand with my MySQL Movies database, the Relation Design dialog won't come up, but the Form Wizard can see the relation and this database will insert MovieGenre records via a subform. On the other hand, my Wine database shows no relations in the Form Wizard, the Relation Design dialog won't come up, and it will not update my WineGrapes table. Is this a clue Question

I started wondering again if the third column (Percent) in my XRef table (WineGrapes) was interfering. Perhaps an XRef table must contain only 2 columns that are each FKs and together form the PK (although I don't know why that should matter). So I created just that (a copy of my WineGrapes table (see my original post) but with the Percent column removed). I built another form using this 2-column XRef table instead but it behaves the same way.

Okay, enough for tonigh. It's late. I must go to bed.
_________________
JD

MySQL 5.0.45-community-nt, OOo 2.3.1, MySQL ODBC 5.1.5, Windows 6.0 SP1.
Back to top
View user's profile Send private message
JDHeinzmann
General User
General User


Joined: 04 Mar 2008
Posts: 44
Location: Manchester, NH

PostPosted: Sun Apr 13, 2008 2:51 pm    Post subject: Tried engine=InnoDB to no avail Reply with quote

I decided to snoop around this forum for threads about relations and MySQL. Woah, there is quite a lot of discussion of the topic. Among other things, there were suggestions to use the InnoDB engine. I thought that was interesting because I did notice that for some reason the script that MySQL workbench generated to create the Movies database contained engine=InnoDB for each table. My Wine database script from the same tool contained no such clause. So, with great hope, I changed all my tables from their default engine (MyISAM?) to InnoDB and tried again. No difference, same constraint complaint.

Another suggestion was to use JDBC. Do I need to be a Java programmer to do that? Do you think that would help? If so, I'll go through that learning curve.

Thanks.
_________________
JD

MySQL 5.0.45-community-nt, OOo 2.3.1, MySQL ODBC 5.1.5, Windows 6.0 SP1.
Back to top
View user's profile Send private message
cscj01
General User
General User


Joined: 12 Apr 2008
Posts: 11

PostPosted: Mon Apr 14, 2008 9:44 am    Post subject: Reply with quote

No, you do not have to be a Java programmer to install and use the JDBC connector. I have tried JDBC, but things did not improve. It's not particularly difficult to set up the JDBC Connector. And given the seeming obtuseness of this issue, it may work for you.

I am also using MyISAM. For the past week or so, I've considered moving to InnoDB. Having used InnoDB in the past, I found it slower than MyISAM for my application, so I moved it to MyISAM. You have to manage referential integrity yourself with MyISAM, because it doesn't support foreign keys. Another reason I have not switched to InnoDB is because of it's unknown status since it is now owned by Oracle.

I have not tried InnoDB with JDBC. I am going to convert my DB to InnoDB, define my foreign keys, and see what happens with OOo Base. It will probably be tomorrow before I can do this as today presents other pressing issues.

I really appreciate your information here. Perhaps between the two of us we can resolve this issue. At least we can try all the configurations suggested.
Back to top
View user's profile Send private message
JDHeinzmann
General User
General User


Joined: 04 Mar 2008
Posts: 44
Location: Manchester, NH

PostPosted: Mon Apr 14, 2008 11:04 am    Post subject: How do I tell what engine I am using? Reply with quote

cscj,

I realize now that before I switched to InnoDB, I didn't know what engine I was using. The schema script that MySQL workbench created (see my original post) included no engine clause so I assume MySQL used its default. But I don't actually know what that is. Do you know how I find that out?

If I was using MyISAM, what was MySQL doing with all the foreign key clauses that my create table statements contained if I have to manage ref. integ. myself? Clearly they were working because I was getting "errors" related to the constraints.

Thanks for your insights as well. I look forward to figuring all this out.
_________________
JD

MySQL 5.0.45-community-nt, OOo 2.3.1, MySQL ODBC 5.1.5, Windows 6.0 SP1.
Back to top
View user's profile Send private message
cscj01
General User
General User


Joined: 12 Apr 2008
Posts: 11

PostPosted: Tue Apr 15, 2008 1:59 pm    Post subject: Reply with quote

The default storage engine is usually MyISAM. If you create a table without specifying an engine, the default is used. However, my guess is, for whatever reason, your install script created a a MySQL Server with InnoDB as the default because you had the foreign key constraints.

If you are using a Linux distribution, check mysql.cnf in /etc/mysql. If you are using Windows, check my.ini in c:\program files\mysql\mysql server 5.x, where x is your version. I am assuming you are using 5.x as your version, probably 5.0.
Back to top
View user's profile Send private message
JDHeinzmann
General User
General User


Joined: 04 Mar 2008
Posts: 44
Location: Manchester, NH

PostPosted: Thu Apr 17, 2008 7:08 pm    Post subject: Doesn't work with JDBC either Reply with quote

Well, I may not need to be a Java programmer to install the JDBC connector, but it wasn't a breeze either. I did finally get it to work. I will share the details of what I had to do on another thread (the one that got me through my install).

With the JDBC connection to my Wine database, I was thrilled to see that all the relations I had defined in MySQL show up when I go to create a subform in the Form Wizard (Subform based on existing relation). Oddly, they still DON'T show up in the Relation Design dialog. Someone in this forum had suggested that using JDBC would fix this and it appeared to be working, somewhat. But when I tried adding a record to my WineGrapes xref table (from a Table Control on a subform for WineGrapes ) I got essentially the same referential integrity message as in my original post but without the ODBC reference (I am using JDBC this time):

Quote:
“Error inserting the new record” Cannot add or update a child row: a foreign key constraint fails (`jdswine/winegrapes`, CONSTRAINT `fk_WineGrapes_Wine` FOREIGN KEY (`WineID` ) REFERENCES `wine` (`WineID`) ON DELETE NO ACTION ON UPDATE NO ACTION


So I made some progress on Base recognizing the foriegn key relationships, but did not solve my underlying problem. And when I click on Relationships..., Base still says: The database does not support relations. Crying or Very sad

What is going on here??!! Do you think I should upgrade to OOo 2.4? The few macros I have are not stored in my forms so that should not be a problem.

I would be grateful for any suggestions.

Thanks.
_________________
JD

MySQL 5.0.45-community-nt, OOo 2.3.1, MySQL ODBC 5.1.5, Windows 6.0 SP1.
Back to top
View user's profile Send private message
Don Rumata
General User
General User


Joined: 18 May 2006
Posts: 15
Location: Sweden

PostPosted: Fri Apr 18, 2008 9:51 am    Post subject: Reply with quote

Hi!

I followed your thread with great interest. I have a similar problem with a 1:many relationship. The main table is patients (id, name, dob,...), the other table is history (id, patient_id, date,....). I would like to have a form with patients, linked via patient.id - history.patient_id to a subform with history. I am using MySQL database connection via JDBC and the form works so far as I can switch from patient.id to the next and the respective number in history.patient_id is updated. However, when I try to enter a new record, the number in history.patient_id is set to 0 when i press the save button (just as described in your thread). This is seriously annoying and I have not found the solution yet. InnoDB did not work out for me. The cooperation between base and MySQL seems quite buggy.

Regards

Thiemo
Back to top
View user's profile Send private message
Don Rumata
General User
General User


Joined: 18 May 2006
Posts: 15
Location: Sweden

PostPosted: Fri Apr 18, 2008 9:53 am    Post subject: Reply with quote

By the way, I am using OO2.4 and it does not make a difference.
Back to top
View user's profile Send private message
cscj01
General User
General User


Joined: 12 Apr 2008
Posts: 11

PostPosted: Sat Apr 19, 2008 2:14 pm    Post subject: Reply with quote

I typed a really long discussion of what I have been through this week, but the system timed me out and threw away my post. Seems as if I am destined to have problems from every direction.

Well, the gist of what I was going to post is that I believe the following is happening:

(1) the problem lies in the table control or the table form or both;
(2) the offending party decides to not allow a foreign key column to be changed;
(3) since the column needs a value, the offender uses a zero;
(4) since zero is not in the parent table, the insert fails.

I have converted my tables to InnoDB, updated to the latest ODBC implementation, defined all my foreign key constraints, checked them in OO Base (they are there and correct), and tried many different settings in the table control and the table form, none of which work. I can insert a row directly in the child table from OO Base from table view. I can insert a row in the child table if I create a form with no subform and the child table as the only entity in the main form.

So the problem is in the table control or table form of the subform or both. That's my story, and I'm sticking to it.

I am intrigued by why the logic dictates that a zero is always inserted into the foreign key column of the child table no matter what you type. It's just that my mind is numb at the moment. Perhaps inspiration will strike. Until then, ...
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group