How to Execute Views from VB
-
Is there a method to execute a view from VB?
OR...
Do I execute it directly from a stored procedure?
thx for clarification.
kurt
Sometimes, the obvious isn't...
-
Try to deal with a view in the same manner you deal with a table either in stored procedure or in VB (ADO). It does not matter (especially if the view is not a join or complex query).
By the way, why usage of a view instead of going directly to tables is essential? Is it permissions?
Michael
-
In this particular instance use of the View is for reporting only. For integrity purposes, many of my tables columns store identity keys to actual data records in other tables. This works quite well but the actual view of the data row doesn't translate well without the proper references to the other tables.
Ultimately, I want to embed a 3rd party report designer into my app to allow an end user to create their own reports based on views that I have provided which resolve most of the mucky key resolutions mentioned above.
This may be ugly, but I'm open to input...
thx
kurt
-
This is not ugly. One recommendation I would make is to use stored procedures instead of views. Views do not support parameters while stored procedures do. Therefore I do not see any reason to use views. Put your joins in SP, make your desired SELECT the last statement that retrieves a data set.
-
I think I understand your reasoning. By using an SP I can also allow for parameters to be passed giving the user more flexibilty. In addition, the SP gives me server side executes reducing the number of records returned (which is a good thing.)
But I think this leaves me with a problem. In a report designer the user is ultimately presented with the DB tables and can select multile columns from any number of tables, which in my case would be quite ugly to user.
I have found that instead of offering up the Tables to the user, I have been able to supply only views instead, which give a more concise set of fields to the user.
In this instance, I not sure the SP approach would work. For my own internal canned reports it definitely will work.
Let me know if I'm missing something...
thx
kurt
-
One reason I use views is for security purposes. I typically never grant any rights to the tables themselves, but create views for querying, and stored procedures for action querys. Then I grant roles in the database the rights to those objects as I see fit.
Tim C.
//Will write code for food
Tim C //Will code for food -
To answer your question from ADODB use either :
1) Command.Execute
2) Recordset.Open
3) Set Recordset = Connection.Execute
My RDO and DAO knowledge is rusty, but if you want I can yank those painful memories out of my brain pan.
Take a look at this : http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28001860
Tim C.
//Will write code for food
Tim C //Will code for food -
I usually fight (well, almost) to receive precise requirements on what kinds of reports users will need to generate. "Report on anything in any combination, shape and form" cannot work. If it did, an average user would have to have rights to generate SQL queries. How can you guarantee then that those queries won't bring your servber to death? The only way to give users ability to query on anything is to use Analysis Services with precalculated cubes.
-
Mromm, are you meaning that you do not normally like to use views for the data retrieval? Just curious as to your reasons, plus I love to debate. (Some people say argue, but I say they are wrong
)Tim C.
//Will write code for food
Tim C //Will code for food -
Tim, I do avoid views because there is not a reason to use those in most cases. The downside of views is that optimizer is not always capable of creating good execution plans for such queries. Views do not allow parameters (WHERE column_id = @id) so in cases of joins you might be joining 1M rows with 100K rows (that's your view), only then applying the WHERE clause that limits the resultset. It should be quite opposite: limit the set first, then join. Views are not suitable for it.
Michael
-
Michael, are you saying the query optimizer would join ALL rows together before
applying the where clause? Or are you just implying that the developer may do a
select * from table with no where, and no top predicate either thereby dragging
the server to its knees? If you let them select out of the tables, then the same
danger applies, plus you have no choice on how to reformat the output of what
they pull. Not disagreeing with you, just think it is an interesting approach to
the problem. How do you do this :
quote:
It should be quite opposite: limit the set first, then join. Views arenot suitable for it.
Even in a stored procedure, would not the join clause come before the where
clause? So in effect you are forcing the developers to use a where clause, and
this is your whole reason for not liking views? Interesting. I am of the opinion
that there is no ONE true right way, so hearing other peoples design patterns
does nothing more than feed my need to learn. Please do not take offense at my
challenging you.
Tim C.
//Will write code for food
Tim C //Will code for food -
Tim, you DO put WHERE clause before the JOIN (in a way):
SELECT ...
FROM t1
JOIN t2
ON t1.c1 = @value
AND t1.c2 = t2.c2
As far as letting your developers to select "everything" from a table - that is why you do code reviews and have certain PROCESS in place. You also educate your developers and enforce standards.
Michael
-
I tend to use the DataEnvironment object to execute any views, stored procedures and commands within my app.
Intellisense lets you reference your database objects, and in the case of views and recordset returning stored procs you will gain an extra object for each that is proceeded by rs meaning recordset.
IE vw_Batchheader will gain a recordset called rsvw_BatchHeader.
Makes life a lot simpler.
If users want a "report on anything in any combination or order" program I simply set up a data dump facility that exports data so they can use a localised tool, such as a desktop database.
Unless there is a specific marketing database server I don't allow users to compose free queries against my databases because a badly done query has a big impact on other non-marketing users.
-
view is acting like a table
so you can use it in any select statement
-
Excuse me for also wanting to put my feet in the hot tub 😉
Michael,
In SQL 2000, in exactly what way does you feel that the query optimizer separates these two statements?:
quote:
SELECT ...FROM t1
JOIN t2
ON t1.c1 = @value
AND t1.c2 = t2.c2
And:
SELECT ...
FROM t1
JOIN t2
ON t1.c2 = t2.c2
WHERE t1.c1 = @value
If you are doing a inner join, fine. Outer joins could give differant results of course.
Cheers, Hans!
)Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply