File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes views: conflict might occur ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "views: conflict might occur ?" Watch "views: conflict might occur ?" New topic
Author

views: conflict might occur ?

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Views:

I�m newbie to views and wanted to consult about the following issue:

Say I have 2 users: A & B, and both are asking for the same report at the same time. The report is a table constructed with some arguments that in order to facilitate I had to use views.

Example: User A asks for the production between date_W and date_X. The query will generate a view with the date arguments and will return the result to user A.
At the same time user B is asking for the same report but this time with *date_Y* and *date_Z* (mind that the view is constructed with those dates)

The problem is that the view has been constructed already with date_W and date_X and user B might get the wrong result (based on dates he didn't ask for)

How can I avoid this and make sure that the result asked by the users is correct without conflicting with other users?

* Of course I can drop the table but my fear is that it might happen concurrently (meaning 2 users are asking for the same report with diff dates)
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Huh?

Are you trying to say that, when generating the report, you are creating a view - with a "CREATE VIEW ...." SQL statement? If so, that's horribly horribly wrong.

Or are you saying you are querying against a view? Such as "SELECT a, b, c FROM my_view"? No problems then, the database constructs the result dynamically, just like it does from a table.
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
I guess it�s a combination of both. I am �CREAT VIEW� for the report in order to facilitate my query and than I do �SELECT a,b,c FROM the VIEW�

Does that present a problem?
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Peter Primrose:
I guess it�s a combination of both. I am �CREAT VIEW� for the report in order to facilitate my query and than I do �SELECT a,b,c FROM the VIEW�

Does that present a problem?


Yes. Views are intended to be "permanent" objects, and the problem you feared in your original post is real and unavoidable; you're misusing views.

There's probably a way to either use a permanent view or rewrite your query that gets around the problem and will keep you from redefining the view.
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Ok...so I'll rewrite my query. So what is the strength of views? I thought they are meant primarily for this type of queries where it gets too complicated and views can facilitate.

Thanks
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Peter Primrose:
Ok...so I'll rewrite my query. So what is the strength of views? I thought they are meant primarily for this type of queries where it gets too complicated and views can facilitate.

Thanks


They are for that as well as other things. They're not for dynamically helping your query (some people use temporary tables for that, but they work very differently and involve temporary copies of data, not always efficient).

For example, consider an application that has 5 tables that are commonly joined together in the same way most of the time; you write a view that encapsulates that 4 joins and your SQL queries the view instead of having to repeat the joins all the time. For example, SELECT ... FROM FULL_ORDER, instead of SELECT ... FROM ORDER, SALESMAN, ORDER_ITEM, INVENTORY_ITEM, PURCHASER WHERE ....

Or say you have a an existing table and you decide you need to split it in two, but maybe there's some read only reporting that goes against the table and you can't rewrite that: create your 2 tables with new names and replace the old table with a view (of the same name and columns). The unchanged read only programs won't know the difference.

Or in some cases, you can use views for "security". Some databases have privilege systems where you can grant a user SELECT privileges on a view and withhold it on the underlying table. In those DBs, you can then hide some columns from some users, by omitting them from the view.
 
Don't get me started about those stupid light bulbs.
 
subject: views: conflict might occur ?
 
Similar Threads
Question about MVC in Swing application
connection pool: difference between tables and views?
Jasper report taking long time to generate a report
More on MVC
GUIContoller