Generating relation into SQL databases

Questions and answers regarding general SQL and backend databases

Generating relation into SQL databases

Postby gianni » Wed Jul 05, 2006 5:44 pm

Servoy is enabling to define relationship at logical level; there are three types of relation definable:
1) Real 1-N relation
2) Usage N-1 of relation defined at #1
3) Logical relations involving variables

I've found no way to generate from Servoy repository directly into SQL backends "real 1-N relations" (#1) to enforce referential integrity.

Is there any way?

Tnks,
Gianni
Last edited by gianni on Thu Jul 06, 2006 11:16 am, edited 1 time in total.
gianni
 
Posts: 64
Joined: Sat Feb 12, 2005 2:26 pm
Location: Vercelli, Italy

Postby swingman » Wed Jul 05, 2006 8:19 pm

Ciao Gianni,

if I understand you right, you want to add a constraint, such as a foreign key constraint, on the SQL database from Servoy. There is no way of doing this in version 2.2, but you can add a constraint directly in your SQL database using some SQL tool.

The only problem is that when the user attempts to violate the constraint, you have no way of trapping the error message from the database in Servoy -- it just displays in a dialog window. Two ways around this,
1) you manually check that the contraint will not be violated in your Servoy methods (I know, extra coding :-( )
2) you write a trigger which raises a better error message when the user attempts to violate constraint. (This works with PostgreSQL at least, haven't tested other databases)
Christian Batchelor
Certified Servoy Developer
FullStackDesign.it
Venice, Italy | London, UK
http://www.fullstackdesign.it

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1483
Joined: Wed Oct 01, 2003 10:20 am
Location: Venice / London

Postby admin » Wed Jul 05, 2006 8:39 pm

Note that as of the next 3b4 build you will have some interesting trapping options in servoy too. More on that later
admin
Site Admin
 
Posts: 72
Joined: Wed Apr 23, 2003 4:29 pm

Postby gianni » Thu Jul 06, 2006 11:14 am

Tnks Christian and admin for your replies!

The best solution for now IMHO is to write coding in Servoy app to force referential integrity.

However it would be nice if Servoy automate it; the functionality is quite simple:

A) Servoy developer knows from relations type#1 defined into the repository which is the costraint to implement.
B) If target DBMS is supporting costraint Servoy generate SQL to drop such costraint into DBMS engine.
C1) At runtime if BOTH TABLES are on the same data provider AND the underlyng DBMS is supporting costraints Servoy trap the error from DBMS and return an error signal to the application
C2) At runtime if TABLES are on different data providers or the underlying DBMS is not supporting constraint Servoy raise a standard SQL where PrimaryKeyIWantToDrop is used in ForeignKeyReferred. If it is used return to the application the same error as in C1

Point A and B are obviously depending from the types of costraints Servoy decide to support; but the most valueable ones are DELETE CONSTRAINTS with RESTRICTED, CASCADE and NULLIFY scopes.

This implementation could save MANY hours of development in every application!

Tnks again for your replies!

Ciao,
Gianni
gianni
 
Posts: 64
Joined: Sat Feb 12, 2005 2:26 pm
Location: Vercelli, Italy


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 2 guests