Page 1 of 1

SQL commands in database connection settings

PostPosted: Sat Dec 11, 2010 1:08 pm
by ROCLASI
In the Servoy training I gave at PgDay Europe someone came up with a good question/feature request.

They use PostgreSQL with several schemas in one database. Depending on the user logging in they set the search_path right after they connect to the database. In other words they make specific schemas (plural!) available depending on the user.
I know you can create specific JDBC connections to a single schema each but what he suggested was to be able to do this at the connection time.
I heard a rumor there is a dynamic connection coming in 6. Would it be possible to have an onConnect even to allow such search_path commands to be send?
Are there a bunch of gotchas with this because of JDBC or the connection pool or is it doable ? What about the meta data (.dbi) that Servoy needs, etc.
Of course another way is to let the database handle this and use database users that have specific access to certain schemas, which would be doable with dynamic connections I suppose.

Perhaps Johan, JB, Paul or Rob can comment on this?

Re: SQL commands in database connection settings

PostPosted: Sat Dec 11, 2010 4:23 pm
by Jan Blok
The rumor is not true. Its not planned either and would withheld running Servoy as part of an Java EE server in the future. (if we introduce such a feature)
Connections based on users are from the 2 tier architecture's, in 3 tier architecture's (alike Servoy) this is very uncommon.

Re: SQL commands in database connection settings

PostPosted: Sat Dec 11, 2010 5:17 pm
by ROCLASI
That makes sense.
Thank you for the explanation.