This one blew about 10 hours of my time trying to figure out what the problem was. And will take a lot more of my time in the future to work around if not fixed!
I am not sure if this is just related to modules or if this is a general problem – relationships between different servers don’t update consistently.
In my case I have different modules using different servers. Scenario:
Module A:
database server A
a form based on table named “task”
an ID field called “id_employee” on the form
Module B:
database server B
a form based on table name “employee”
primary key of this table is “id_employee”
Relationship in module B that is the problem:
left side: server B, table “employee”, match field “id_employee”
right side: server A, table “task”, match field “id_employee”
object: show a list of tasks that an employee is assigned to
Problem: create a task in module A, assign an employee id, and the task list in module B doesn’t update consistently.
If both modules use the same database server – no problems at all.
This, in general, is a tricky situation. Imagine, your server A is an ORACLE database and your server B is a mySql database on a completely different machine. Have you ever thought about how Servoy can “join” between the two? From a Servoy developer point of view, there is not much about it. It’s just two server connections and a “relation” between two of their tables. But behind the scenes there is more to it.
I don’t know about your special case, but in general this setup can lead to great trouble. Usually, a simple “show the tasks of an employee” should work OK, because how many tasks will an employee have? If it is less than 200, it should go OK. But what if you want to ask: give me all tasks, where the employees last name starts with an “A” and you have 500.000 tasks and 10.000 employees?
If you have trouble with this, a look at the stack trace (where you see the actual SQL statement) might help to figure out if you have reached a conceptual limit.
This might not be the answer to your problem, but maybe well worth a thought when using multiple database connections. Even when just using schemas inside one DB system that supports schemas this can be an issue.
Some new territory for me – makes a lot of sense Patrick. I should mention that both of the connection names in question point to the same database (in Sybase). One would think that this wouldn’t present any problems.
With some more experimentation I have found that if id_employee is set in table A on a form that is being shown through a related tab where the relationship needs a global to be set to show the form – that is when table B does not see the new value in id_employee. I am pretty sure this is the crux of the problem. Will test the anomaly more when my brain clears a bit.
(Seems like we were posting about this “global in a relationship issue” a couple of months back in regards to creating related records through such a relationship [and sometimes they wouldn’t show up]. Can’t find the post though.)
patrick:
This, in general, is a tricky situation. Imagine, your server A is an ORACLE database and your server B is a mySql database on a completely different machine. Have you ever thought about how Servoy can “join” between the two? From a Servoy developer point of view, there is not much about it. It’s just two server connections and a “relation” between two of their tables. But behind the scenes there is more to it.
I don’t know about your special case, but in general this setup can lead to great trouble. Usually, a simple “show the tasks of an employee” should work OK, because how many tasks will an employee have? If it is less than 200, it should go OK. But what if you want to ask: give me all tasks, where the employees last name starts with an “A” and you have 500.000 tasks and 10.000 employees?
If you have trouble with this, a look at the stack trace (where you see the actual SQL statement) might help to figure out if you have reached a conceptual limit.
This might not be the answer to your problem, but maybe well worth a thought when using multiple database connections. Even when just using schemas inside one DB system that supports schemas this can be an issue.