|
GuyThiebaut wrote: passing a view into a control
But that's not a good idea. It doesn't lead to a properly layered application.
GuyThiebaut wrote: aggregate function
Or a UNION or a CASE , probably if there's CAST or CONVERT ... I suppose it requires that there be a primary key as well.
|
|
|
|
|
"But that's not a good idea. It doesn't lead to a properly layered application." - I don't understand why using a view in a control would do this, could you let me know why?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
GuyThiebaut wrote: I don't understand why using a view in a control would do this, could you let me know why?
Because if you use the view in the GUI then there is no database layer. Nor presumably a business layer for that matter.
|
|
|
|
|
There is a database layer - the control is populated via a query from this layer.
Any updates to this view are then passed through this layer via update commands.
So we can lock down particular columns and perform verification on the values before update so that we both have a business and database layer.
So I am still not getting what is wrong with using the results of a view in a GUI.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
GuyThiebaut wrote: Any updates to this view are then passed through this layer via update commands.
Either you are expressing it incorrectly or there is no layer.
You said you used the "view" in the "control".
If instead you are using a DTO in the "control" then that is not the same as what you said.
If however you are using a language specific layer to access the a view in the control then there is no database layer.
GuyThiebaut wrote: So I am still not getting what is wrong with using the results of a view in a GUI.
First, the question, per the OP, is why someone would insist that only views be used. It isn't whether views might or might not be used.
Second, as I already said your terminology/phrasing is not precise. If you have a database layer then you are not using the view in the GUI. What you are using is the results of the database layer or even business layer in the GUI. The fact that they originated from a view in another layer is irrelevant and expressing it that way should be avoided because data model entity might not originate in a one to one mapping with the database. And the user (GUI) should not concern itself with how it did originate from he database.
|
|
|
|
|
GuyThiebaut wrote: since if one is passing a view into a control then it is easier to perform an updat
Control? As in a GUI control?
For small systems with stand along apps that might be appropriate. For anything larger one should have a database layer.
And of course it won't work at all for a web app.
|
|
|
|
|
Since a view can become out of sync with the underlying table, it is not a good idea to insert through a view, [sarcasm]unless you are using MS Access[/sarcasm].
Another problem is, if there are multiple tables in the view things go wrong quickly.
Now if he would have said you should use a stored procedure to do the insert then I would say he has a point. However it is not wrong to insert directly into a table.
|
|
|
|
|
Eric Whitmore wrote: if I do "inserts" into "tables" I am doing it "wrong
You should ask why. There could be an architectural reason such as an intent to move to another schema or legacy reasons.
Could just be an odd way to provide a database layer though. For that though I use stored procs.
|
|
|
|
|
jschell wrote: I use stored procs
Right, I don't (willingly), but I know many do and, of the two techniques, procedures make more sense.
|
|
|
|
|
I ran across this attitude in the 90s (and not since) I believe it was based on security, there was a difference between table and view security back then. I would be very interested in your DBAs reasoning behind the statement.
PieBald
You can insert into views if there is 1 table in the view (how dumb is that) or the relationships are not too complex. I have never explored this as I also think it is a horror waiting to happen.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: in the 90s
At that time I was using Oracle (6) and I remember asking the DBAs why the views (there weren't many) had an _v suffix and the response was "to remind the developers that they are views and therefore readonly".
|
|
|
|
|
I think I was working with MSSQL 6.5!
Try creating a view of only 1 table, no joins, and inserting/editing a record. Now add an inner join and repeat.
Even though I have SSMS open I'll not bother as it is a complete WOTAM.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You can insert into views if there is 1 table in the view (how dumb is that)
It's quite useful if you want to filter the contents of a table for a certain group of users.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Jörgen Andersson wrote: It's quite useful if you want to filter the contents of a table for a certain group of users.
Why would you do that in the database versus the application layer?
And of course in general when you say 'user' if you mean an actual database user then there can be negative impacts such as licensing.
|
|
|
|
|
Because some companies are quite divided.
The right place to do it is in the application, but I've been working for companies where the database department were responsible for that one department couldn't see the same columns as another department.
It's a lot easier to keep track on this with a view than with column permissions.
When it comes to licensing it doesn't matter. It's the number of actual users that counts, not the number of users you setup in the database. It's a common misconception.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Jörgen Andersson wrote: Because some companies are quite divided.
Certainly. And some companies need to create their own database.
But in the general case neither of those are true.
Jörgen Andersson wrote: It's the number of actual users that counts, not the number of users you setup in the database. It's a common misconception.
I know how it works.
The fact that user licensing exists at all specifically indicates that support in the database per user is something that is in fact significant.
Unless you are claiming that only a single user connects at a time, then my comment about user licensing stands. And if you are claiming that then it is far from what any normal business would use.
|
|
|
|
|
Jörgen Andersson wrote: f you want to filter the contents of a table for a certain group of users
Sure but that is one of the uses of a view - a very old fashioned use, but still valid. Who puts authorisation logic at that level these days. Apps no longer have to rely on the database for authorisation, very 80s
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I know, see my answer to jschell.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Yeah I read that and thought poor bastard, I just hope you are not working with one of those old EIS systems as well!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm at a different place now, with a different set of pains.
Mycroft Holmes wrote: I just hope you are not working with one of those old EIS systems as well!
Depends on what EIS stands for. If it is Executive Information System, then yes kind of.
It's my own design though. It's the users that's the main pain now.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Hi Srs.,
I am searching in a lot of sites a way to justify my textboxes on the sql server reports, but I don't found yet.
In a site they suggest I to use html/css fields, but the SSRS don't recognize the tag "justify".
Someone here have some kind of workaround for my issue?
Thanks for your attention!
|
|
|
|
|
|
The db I am testing is locked ATM doing some data-loads so I can't get various potential methods of doing this so I figured I would ask. I have a database with each table having its own file group and each file group has one file. Is there a simple method (other than Import Data and Bulk insert type operations) to detach the file groups (ok, maybe not detach they will be read-only) and copy the table, file group, and file into another database on a completely different server.
My thought would be to just copy the physical files and then through some magical T-SQL have the files appear as the new table. I believe in My-SQL copying db files is all that is required but SQL Server is a special lady. Any thoughts? I am only dealing with about 60 million records an hour so not that much data.
|
|
|
|
|
Ennis Ray Lynch, Jr. wrote: Is there a simple method (other than Import Data and Bulk insert type operations) to detach the file groups (ok, maybe not detach they will be read-only) and copy the table, file group, and file into another database on a completely different server.
Yes. Detach, xcopy, attach.
sp_attach_db [ @dbname= ] 'dbname'
, [ @filename1= ] 'filename_n' [ ,...16 ] (From MSDN[^])
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks for your reply, unfortunately, your method requires that the other database have the tables and files and file groups already created and requires the DB to be taken offline in order to add the new files and file groups which contain the table data.
What I am looking for is some black magic to take files from a separate database and bring then into a different database without bringing it offline. Assuming both databases have the exact same configuration, this is technically possible (otherwise you couldn't add new files and file groups while SQL Server is running) but it is likely a dark-art. Bringing the database offline is not a valid solution for me needs.
|
|
|
|