SQL commands in database connection settings
Posted: Sat Dec 11, 2010 1:08 pm
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?
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?