1. Georg Lacher
  2. Valentina Studio
  3. Суббота, Июль 25 2020, 03:40 PM
  4.  Подписаться через email
Hello!
Valentina Studio is able to access my SQL database. I created a diagram with links between the tables. The Data Editor shows the records of the selected tables, but not - as shown in the introduction video - the records in the linked tables. What can be the reason?
Thanks
Georg
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hello Georg,

Do you see created link in the links list for this table?
I attached screenshot
Вложения
Комментарий
There are no comments made yet.
Georg Lacher Ответ принят
Hello Sergey,

no, there aren't any entries in the links column. It seems, I missed a step. Do you know which one?

Thanks
Georg
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Do you use Valentina Studio Pro?
When you create a link on the diagram it is necessary to apply changes (there is Apply Changes button), but it is available in the Valentina Studio Pro.

Also, there is a way to do in the Valentina Studio Free in the Schema Editor - just select the table and click Create->Link to open Create Link dialog.

As a result the similar script will be executed:

ALTER TABLE `film_actor`
ADD CONSTRAINT `fk_film_actor_actor` FOREIGN KEY ( `actor_id` )
REFERENCES `actor`( `actor_id` )
ON DELETE Restrict
ON UPDATE Cascade;
Комментарий
There are no comments made yet.
Georg Lacher Ответ принят
Yes, I use Valentina Studio Pro. You're right, the diagram has not been saved properly.
If I try to apply changes, there is an error message: "Error (1075): 42000: "Incorrect table definition; there can be only one auto column and it must be defined as a key".
But I don't know what an "auto column" is.
Комментарий
There are no comments made yet.
Georg Lacher Ответ принят
Now I understand ... "auto columns" means fields with AUTO INCREMENT.
The error message means that each auto increment field must also be defined as a key.

I checked the tables in the MySQL database, but each auto increment field is also defined als a primary key field. So why do I get this error message?

Then I found an interesting explanation. When creating a 1:many-link in the diagram Valentina Studio adds a new field as primary key field to the many-table. I deleted these new fields because each table already has a primary key field.

The "apply changes" button creates a script. The script begins with lines like this:
DROP INDEX `PRIMARY` ON `Table Name`;

I assume these lines were added because I deleted these fields. Right?

So the tables do not have a primary key field anymore, and this causes the error message.

What do you suggest? Shall I delete the diagram and create a new one and leave the new fields? Or shall I delete the DROP INDEX lines and then apply the changes?

Thank you for your help
Georg
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Yes, maybe start on a new diagram, not necessary to drop an old one.
Комментарий
There are no comments made yet.
Georg Lacher Ответ принят
Unfortunately the new diagram causes the same error message: "Error (1075): 42000: "Incorrect table definition; there can be only one auto column and it must be defined as a key".

I don't know what's the reason for the error message.

Some fields in the diagram are highlightened in orange colour. Perhaps this is a hint? I checked the links, there is no type mismatch. Maybe some of the 1:many relations are not correct?
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Looks like the Unique flag is removed from the PK fields, but why it happens is still the question.

I recommend discarding unnecessary changes.
You can select PK field and use the context menu to discard changes (see screenshot).
The same for `PRIMARY` index and unique.

Also, there are some FK/PK fields that are Double, is that correct?
Вложения
Комментарий
There are no comments made yet.
Georg Lacher Ответ принят
Yes, some of the key fields are Double. Is that a problem?
I'm gonna try to discard unnecessary changes.
Thanks!
Georg
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Double values are approximate, it may produce unexpected results in some cases, so generally not recommended. But allowed.
Комментарий
There are no comments made yet.
Georg Lacher Ответ принят
I discovered the reason for these errors. Some of the 1:many relations were not correct. I created a new diagram, now everything is ok with the primary key fields.

But when I try to apply the changes, there is a new error message.

The FOREIGN KEY SQL command does not work. I don't know why. I removed the NULLABLE flag from the foreign key field in the child table, but the error comes again.
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hello Georg,

So there is some data in these tables, right?
Is that possible that some of the FNr values in the Zuordnung table don't exist in the Filme table?

The following query should find such values if any:

SELECT * FROM Zuordnung WHERE Zuordnung.FNr NOT IN ( SELECT FNr FROM Filme )
Комментарий
There are no comments made yet.
Georg Lacher Ответ принят
Hello, Sergey.

I found 42 records out of 70.516 records in the Zuordnung table which an unvalid FNr. It's a handmade database, sometimes I discovered double entries in the Filme table and deleted one of them. I tried to adjust the child tables, but obviously sometimes I forgot to do this. So I'm gonna delete these 42 records and try to go on, right?

Thank you!
Georg
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hello Georg,

Yes, delete or change FNr to some existing value from the Filme table (to adjust it later).

As usual, it is recommended to create a backup so if something goes wrong you could return to the working copy.
Комментарий
There are no comments made yet.
Georg Lacher Ответ принят
Hello Sergey,

I deleted the selected records. After that there were some other error messages, and I had to delete other lost records. Finally, the changes were applied successfully.

The next question is: what can I do with this diagram? Now Valentina knows how the tables are connected together. Does this help me to go on, for example to create an input form with subforms?

Thank you for you help?
Georg
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hello Georg,

Yes, created links (not necessarily using the diagram) can be used to show related records in the subform.

I attached the link to the tutorial which covers this topic.
Also, it is possible to show records in subform even without the link.
Ссылки
  1. http://valentina-db.com/docs/dokuwiki/v10/doku.php?id=valentina:products:vstudio:tutorials:forms:lesson2#case_1related_records_using_the_link
Комментарий
There are no comments made yet.
Georg Lacher Ответ принят
Thank you, Sergey,

the link to the tutorial helped me a lot.

I created a new form with a subform which shows related records using the third way, using the map of parameters.

The next problem is that my database needs one step more. It has a table with movies and a table with persons and between these tables there is a third table. It shows which persons are related to which films as directors, authors, actors and so on. The third table contains only indexes. How can I manage this problem? Probably with an additional query, but I don't know exactly how.

And another question: Is it possible to create a form with TWO subforms?

Best wishes
Georg
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hello Georg,

Probably with an additional query


Yes, a query should work in this case, I'll add more details soon.

Is it possible to create a form with TWO subforms?


Yes, it is supported
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Here can be used the same approach with parameters but now the query will be with join.

For example:

1. The list of actors for the movie specified by id:

SELECT * FROM actor
JOIN film_actor ON actor.actor_id = film_actor.actor_id
WHERE film_actor.film_id = $P(pFilmID)


2. The list of movies for the actor specified by id:

SELECT * FROM film
JOIN film_actor ON film.film_id = film_actor.film_id
WHERE film_actor.actor_id = $P(pActorID)
Комментарий
There are no comments made yet.
Georg Lacher Ответ принят
Hello Sergey,

thank you for your help! Now I'm working on the subform which shows directors, screenwriters, cameramen and actors who were involved in the movie (which is shown in the main form).

Now I'm having a special problem with this subform. The columns don't show the names of the people, but only their indexes in the Personen table. I tried to write a query which shows the names of the people, but this is difficult, because there is more than one foreign key column.

I tried to write subqueries like this:

Select Zuordnung.FNr, Concat (Personen.Name, ', ', Personen.Vorname),
(SELECT Concat(Personen.Name, ',', Personen.Vorname)from Zuordnung Join Personen On Zuordnung.Drehbuch = Personen.RNr)
...
from Zuordnung Join Personen ON Zuordnung.Regie = Personen.RNr where Zuordnung.FNr = $P(P_FNr)

I got an error message: "Subquery returns more than one row".

Do you know how to solve this problem?

Thank you very much
Georg
Вложения
Комментарий
There are no comments made yet.
  • Страница :
  • 1
  • 2


There are no replies made for this post yet.
However, you are not allowed to reply to this post.