JDBC & views

[Anonymous]

Hi;

Is there a way in JDBC to create a view on the fly, perform some
selects against it, then delete it? This has to be done at run-time
because I di not know the view that will be needed until run-time.

thanks - dave

Windward Reports -- http://www.WindwardReports.com
DefendTek -- http://www.DefendTek.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
Hillary Clinton -- http://www.HillaryIn2004.org
(yes I have lots of links)

 

[Sudsy]

Hi;

Is there a way in JDBC to create a view on the fly, perform some
selects against it, then delete it? This has to be done at run-time
because I di not know the view that will be needed until run-time.

thanks - dave

Untested, but what about:
- Statement#executeUpdate( "CREATE VIEW ..." )
- Statement#executeQuery( "SELECT ... FROM ..." )
- Statement#executeUpdate( "DROP VIEW ... " )
?

Of course this assumes you have the permissions and that the name
doesn't conflict with other users...
Other than that, the javadocs indicate that DDL SQL is permitted.

 

[Anonymous]

That works great - thanks!!

So question # 2 now is:

Is there a way to have a select applied to a view?

I know how to do it if I am writing the select statement. But what I
want is a little different. The user can write and select statement
they wish and I will then apply that statement to either the entire
database or to a view depending on a setting I have. So the select
will have nothing about the view in it. (In fact, the user does not
know the view exists.)

Any ideas?

thanks - dave

Untested, but what about:
- Statement#executeUpdate( "CREATE VIEW ..." )
- Statement#executeQuery( "SELECT ... FROM ..." )
- Statement#executeUpdate( "DROP VIEW ... " )
?

Of course this assumes you have the permissions and that the name
doesn't conflict with other users...
Other than that, the javadocs indicate that DDL SQL is permitted.

Windward Reports -- http://www.WindwardReports.com
DefendTek -- http://www.DefendTek.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
Hillary Clinton -- http://www.HillaryIn2004.org
(yes I have lots of links)

 

[John C. Bollinger]

That works great - thanks!!

So question # 2 now is:

Is there a way to have a select applied to a view?

I know how to do it if I am writing the select statement. But what I
want is a little different. The user can write and select statement
they wish and I will then apply that statement to either the entire
database or to a view depending on a setting I have. So the select
will have nothing about the view in it. (In fact, the user does not
know the view exists.)

Any ideas?

It seems to me that if the user writes the SELECT statement, then he
determines which table / view he is selecting from. If you want to run
the statement against a programmatically chosen view instead of against
the user specified table(s) then you will have to modify the user's
statements appropriately before submitting them to the DB. You might be
able to (carefully) change the appropriate table names to the
corresponding view names in the statement's FROM clause.

JDBC is just the vehicle for delivering the statements to the DB and
getting the results; it has little to do with how you would implement
this kind of functionality.


John Bollinger
(e-mail address removed)