Reproducing a relation in SQL

Classic Servoy
1

Hi,

I want to find out, just by its name, all the parts of the a relation.

I’d like to write a method that returns the :
Left Table name, Right Table name
all the key used an their logical correspondance

so if I have a relation called : Product_To_categories

I’d like to have it return :

Left Table: Products (p)
Right Table : Categories (c)
keys : p.Product_ref = c.Product_ref

The purpose of this would be to rewrite the realtion in pure sql

is it possible ?

Thanks

2

Sure this is possible.

You should:

a. have a look at some excellent sites explaining how to do this.
My main source of information in the beginning used to be sql course
b. Scott Butler posted an explanation about a join on the forum.
I think searching for ‘join’ will give you the right post. It is pretty recent.

Hope this helps

3

Vince:
The purpose of this would be to rewrite the realtion in pure sql

Not sure what you mean by this because when defined in Servoy you already have that relationship. You just have to use it and it gets translated into SQL for you.
But if you really want to get this information at runtime it’s not possible at this time.
If you really need this then I suggest you send in a feature request.

But if you just want to know what kinda relationships you have in a solution (lets say for documentation or analyzes purpose) then you maybe should take a look at Analyzer from IT2Be.
Of course you can use Servoy itself too to go over your relationships but Analyzer gives you easier access to it and you can easily compare with other solutions and repositories.

Hope this helps.

4

I think the main question was if it was possible to retrieve all relevant information from a given Servoy relation. If I understand the question right, Vince wants to know from a relation

  1. the two tables involved
  2. the field matching used

As far as I see that, only question 1 can be answered with Servoy 3.5. In 3.5 you should be able to do

some_relation.getServerName()
some_relation.getTableName()

So you can figure out the right side of the relation. But what is missing is something like

some_relation.getFieldMapping()

that could return something like

[leftField][operator][rightField]

I can imagine useful application for this kind of thing. So just make this a feature request (the getServerName() and getTableName() has just been added upon my request).

5

Thanks that’s exactly the missing feature I’d like to.

6

Make a feature request. I am also interested in that feature!

7

Feature request done

8

To get the table on the right side of a relation:
databaseManager.getTable(forms.formName.relationName.foundset);

To get the SQL defining the right side of a relation (including all searches, omits and inverts etc):
databaseManager.getSQL(forms.formName.relationName.foundset);

Both work in 3.1 already.

Paul