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
Well ... JDBC can be used to pass arbitrary SQL calls into your database. Assuming you give your JDBC app DBA rights, it will be able to do things like create new tables and views (as well as delete them). Just use the appropriate SQL logic for your database.
I got it creating views - turns out to be a piece of cake.
Now, 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.)
Here is an example:
create the view:
create view v1 as select * from orders where (paymentCode = - 1)
User enters the following:
select * from orders where (varId = 0)
But I want this select to go against the view v1 instead of against the entire database. I am assuming there must be a way to do this - but every select I have tried doesn't work.
What does work is:
select * from v1 where (varId = 0)
But that requires the user to be aware of the view.
thanks - dave
Essentially that is how it is intended to work. A view defines a a specialized perspective to the relational data being accessed. If you intened to access this information you need to use the View and not the table.
The major advantage of using views is the case stated above and also that the queries which back the views are precompiled by the database engine. This provides basic performance benefits on running your queries.
I guess to make a long story short you have two options -
1) Pass the knowledge on the views to the users
2) Provide an internall mapping engine which maps user queries to defined views internally.
Also note that Views have restrictions on their usage. They limit data manipulation operations on them (Please refer to any RDBMS book for information on this).
Hope this helps.