- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
I have actually seen this type of code because there was a CheckStyle rule that checked on string literals.
A manager would run the tool and tell the developers to 'fix the code'. Resulting in the mess you see in the original post.
Edwin
Admin
You cannot write a generic data access layer that works on all database systems because, dispite many similarities, they all do things very differently. The best you can do is separate your request from data from your implementation of the request for data, such as through an interface:
You then implement that interface for each type of database you are using, and have each implementation instantiate the Person class for you. Sorry, but that's the only way you can do a "generic" data access layer.
Admin
Uhhh, no. It would be:
public const string SELECT = " GIMMIE ";
Admin
While I don't think it outweighs the negative sides of writing code like this, there is one thing it helps you with: compile-time spell checking.
I know I've gotten syntax errors from the BD because of spelling errors a few times when writing dynamic SQL, and if the time it takes to build and redeply is anything like what it is in the system I'm stuck with at the moment, this style of writing SQL could probably actually save development time. It's still a loss as it's not as readable as doing it the sane way, so maintainability suffers (but then again, if maintainance is a different account, it may yet be a win for monopoly money reasons...)
Admin
No, you're not thinking enterprisey. It's:
Admin
Does anyone else notice that:
contains no SqlStupid.WHERE_CLAUSE?Admin
Well, you go ahead and have a look-see at the table structures and table names and just the sheer amount of closely duplicated tables in those enterprisey application databases that disallow procedures triggers and the like. They are totally unmanageable. I have yet to deal with one that wasn't chalk full of hacks to get around the issue.
Admin
How about persistence tools such as Hibernate (http://www.hibernate.org/)? On the web site it says that Hibernate supports any JDBC-compliant database (more than 20 is mentioned). Does anybody here have experience on it or similar programs?
Admin
1) Stored procedures tie you to a particular vendor
No argument there.
2) Application logic in the database has to duplicate logic in the application
Not if it's designed correctly. You're assuming the code is bad.
3) Horrible source code control (in every case I've witnessed)
No argument here.
4) Lack of OO design, hence the term "stored PROCEDURE"
Java in oracle, .net in sql 2005, you can do oo in the database, but the bigger the gun to shoot yourself in the foot with. Not a valid argument.
5) Difficulty migrating to a different database vendor/version
No argument here. I just don't think it should be done.
6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record
As opposed to putting more load on the app server? I've never had trouble clustering database servers.
Admin
Who cares?
Wrong, the logic would be in the db INSTEAD.
True, though can be mitigated w/VSS.
Largely pointless...I don't think data design should be dependent on OO app issues.
Again, who cares?
Bingo!
I agree with this for the most part, given #6. Stored procedures are important for several reasons beyond encapsulating business logic (which isn't the best idea). Compiled execution plans and managing permissions are the biggest. There are many places where a good stored proc can save the database from suffering a developer writing code that iterates through a recordset and does lookup queries in a way that the db engine could do much better.
I'd say avoiding SPs totally is pretty dumb, which is apparently what the poster was saying. Sounds like the DBA was trying to cover for lack of expertise in that area.
Dylan (a DBA)
Admin
That's because Hibernate can't do SQL, or complex queries at all. It has a limited query language of it's own - probably to ensure that it is vendor independant
Admin
This reminds me of some truly moronic code I had to work with earlier this year, something like:
query.Append("select").Append("*").Append("from")..etc
It's more readable dontchaknow. Of course, that was the least of the problems with that project, which was eventually canned.
Admin
VSS is horrible. Said as a person who uses it every day, and who went through a great deal of effort to make set up versioning of MS-SQL stored procedures through VSS.
(Just in case you decide to do it, the server component needs msvcr70.dll... they don't tell you this when you install it, of course...)
Admin
I actually uttered a choked scream upon reading that. The first WTF that causes me physical discomfort. :|
Admin
check out my avatar!
Admin
Next on the menu:
public class RegExSymbols { pubilc const string CHAR_A = "A"; public const string CHAR_a = "a"; public const string KLEENE_STAR = "*"; ... }
--silly-- while(!stomach.empty()) { for(hamburger = GIMMIE food FROM table WHERE meat = 'cow'; hamburger.stillTasty() || hamburger.notFinished(); hamburger.takeBite()); }
Admin
This code makes baby Jesus cry :'(
Seriously, the application I'm working on has a lot of constants, some of which seem needless, but this is so far above and beyond anything from my darkest nightmares that it hurts. How is it that people get away this this crap?
Admin
Cool, type safe SQL, now we don't need to wait for LINQ from Microsoft, instead we can use enterprise SQL and have type safe SQL today.
See here for example using ADO.NET:
Will compile but will result in runtime error, which is a big no no for an enterprise application. So instead we use enterprise SQL.
Will not compile, so the bug will detected and fixed before being even run. I'm pretty sure that's why they did it. Still sucks though.
Admin
Helpful for devs with no SQL knowledge or knowledge of the schema. They just type
string queryString = sqlWords.
and IntelliSense gives them a list of options.
Admin
Wuddafukk, a mistake in my code, the Dataset object should be named products or so and not customers, copy and paste error. Sorry didn't code review it enough before hitting the Post button.
Admin
I have to agree. Logic embedded in the database can't possibly be OO. SQL is a decent query language, but a HORRIBLE programming language.
I was at a job where not only was some logic in the database, but all of the Java data objects were the result of GENERATED CODE which was automatically generated from each and every database table. And did I mention that due to legacy requirements, each column's name was limited to sixteen characters?
This nightmare is the result of two factors:
1. Inexperienced DBAs sometimes fancy themselves application coders. "A little knowledge," as they say.
2. Some DBA did it back in 1990, when hardware limitations might have justified it.
Admin
Yet another example of premature optimization. SELECT * may be faster, but it will never be a lot faster except on queries so trivially small that performance doesn't matter. On real DBMSs, after the first run it gets stored compiled like a stored procedure and the benefit goes away.
Another issue, it is impossible for an Index to cover a query if you use SELECT *. There goes one of the more powerful tools that can easily cut 80% of the execution time from some long running queries. It also has a lot of the problems of weakly typed languages. Example: a lot of people like to say that doing SELECT *, either a) allows them to select columns that are added in the future or b) prevents errors when columns are migrated off to other tables. Well for "a", if the column didn't exist at the time you developed the application, exactly what kind of productive use can you put it to? And if your application has a mechanism or extensibility, then you can dynamically build the statement. For "b", sure the statement won't bomb if the column isn't available, but you still won't get the data. The call to retrieve the data will bomb. If you don't use the data then why are you selecting it at all?
SELECT * does not belong in production code. "Because it's faster" isn't a good enough reason to outweigh the disadvantages.
Admin
I wouldn't classify this as a WTF at all. It gives you compile-time syntax checking for SQL statements.
Consider this:
object result = ExecuteCode("int a = 2; for( int i = 0; i < 100, i++) b += 3;");Do you like this code? I hope you would answer "no". Especially because there's a syntax error in it that would be a pain to track down. Why should embedded SQL code be any different?
Granted, the syntax was pretty ugly. With some clever class design, you could probably get it to look like:
This would give you a fairly concise syntax without sacrificing SQLs power. And you'd still get compile time error checking.
-- bob
Admin
What on earth are you talking about?
Try this:
CREATE TABLE test (
name text,
postcode integer
);
CREATE INDEX test_postcode_idx ON test(postcode);
Insert a million rows.
Now:
EXPLAIN SELECT * FROM test WHERE postcode = 98001;
I guarantee it will use the test_postcode_idx. There's absolutely no reason it shouldn't. Which columns you select does not in any way affect index usage.
Admin
Admin
Let me start with one confession: during the last 4 years, I made several applications that put (nearly) all logic into the database (in my case, it's PL/SQL in Oracle),
1) So true, but couldn't care less - our customers already paid for Oracle. Using VB ties you to MS, using Java ties you to Sun. In my country, there is a saying: You have to die one death.
2) Wrong, since there is nearly no application logic outside the DB
3) Partialy true, though I found a way to mitigate that
4) True, but I couldn't care less - OO design is a tool, not a goal
5) True, see 1)
6) Don't agree. The effort to run a stored procedure is negligible compare to the overhead of a multi-tier architecture. In many applications, "Logic" is not expensive compared to database access. DB abstraction layers have a tendency to do a lot of useless "SELECT *" through the network. That is a really expensive habit.
Admin
If I wanted compile-time syntax checking, there are much better though less verbose possibilities. E.g. preprocessors that do not only check the syntax, but also the names of tables, columns etc.
Admin
If I wanted compile-time syntax checking, there are much better though less verbose possibilities. E.g. preprocessors that do not only check the syntax, but also the names of tables, columns etc.
Admin
sorry for the double post...
Admin
So never use stored procedures? Not even to get a list of primary key elements to allow a Data Access Layer to load or persist it's objects?
I've not worked on a large "Enterprise" system but having a Factory that allows you to load and save obects for your application sort of makes sense to me, and so it follows that these factories can make good use of views and stored procedures...Besides I'm a developer who doesn't believe developers should write SQL. By this I mean in your developer mindset. When you're writing SQL you should be thinking like a DBA about data, scalability and performance. When you're writing code you should be thinking like a developer and thinking with objects.
Admin
I believe he meant that the index can't satisfy the query, in other words, your select * will need to lookup the row location in the index and visit the table to retrieve the value for 'name', but a 'select postcode' can be satisfied with just the index.
Admin
Lets see you do a join? Also what happens if SqlColumns.PRODUCTS_ISACTIVE is not a column in SqlTables.PRODUCTS? Its still a runtime error. Its a little better but is it worth it?
or what if every day your app dies b/c someone is not closing their cursor, and the only clue you have for who is doing it is a "Select * from products where product_isactive='1'". Lets see you find the culprit in you codebase? Do it!
Admin
I didn't say it won't use the index, I said the index won't cover the query. It's a performance tuning term that means that all the columns being used in the query are contained in the index an the table doesn't even have to be read. Example:
CREATE TABLE Employees
(
EmployeeID int,
LastName varchar(30),
FirstName varchar(30),
DepartmentID int,
HireDate datetime,
SupervisorID int
)
Create a compound Index:
CREATE INDEX in_biteme ON Employees(LastName, DepartmentID)
Issue the statement:
SELECT DepartmentID FROM Employees WHERE LastName LIKE 'M%'
Because of the compound index, it will incur only a very small number of page reads (probably around 100 pages for a 1,000,000 row table). If the index were only on LastName, it would possibly not be selective enough to even be useful (the same select statement would likely read 20 to 50 percent of the table -- about 3000 pages -- and it's sometime more efficient to read all of the pages in order rather than half of them out of order).
In case you don't believe me, here are a bunch of links to other people's opinions on covering indexes (note that they are using MSSQL, MySQL, and DB2 in the examples):
http://www-128.ibm.com/developerworks/db2/library/techarticle/0303kuznetsov/0303kuznetsov.html
http://www.informit.com/articles/article.asp?p=27015&seqNum=6&rl=1
http://db.apache.org/derby/docs/dev/tuning/ctunoptimz30768.html
http://blogs.conchango.com/jamiethomson/archive/2005/09/30/2223.aspx
http://www.mcse.ms/archive81-2004-10-1161421.html
http://peter-zaitsev.livejournal.com/6949.html
Admin
That'll never do. Look how many times you're repeating the literal " "! What happens if it changes? You need to do this:
public const string SPACE = " ";
public const string SELECT_STAR_FROM = SELECT + SPACE + STAR + FROM;
Admin
... or stored procedures.
Admin
SQL is horrible programming language. But it is an excellent *set-based* data processing language. Literally hundreds of times more efficient than using one of them fancy OOP languages to process the rows one by one at the client.
Admin
Oh, brother.
I've seen this format one too many times, and I'm still wondering where these "experts" learnt them from. Students could do better. >.>
Admin
I am assuming this is ADO.NET, and there you can use something like
command.CommandText = "UPDATE table SET FirstName = ? WHERE ...";
command.CreateParameter();
command.Parameters(0).Value = "Bob";
command.ExecuteNonQuery();
command.Parameters(0).Value = "John";
command.ExecuteNonQuery();
or set the parameter value from a loop. You can also do things like
UPDATE table SET FirstName = @param1 WHERE ...
command.Parameters("@param1").Value = "Bob";
Admin
I have to disagree. We make use of a generic data access layer pattern at the software development company I work for when we are concerned that our customers will want to change the database platform. Here's the approach we take:
You have a base DataAccessLayer class. It has virtual (or abstract or whathaveyou) methods like SQLSelect(), SQLUpdate, SQLInsert, SQLDelete, SQLAlter, etc. Each of these takes parameters specifying portions of the query. For example, SQLSelect might have 4 parameters, like: string selectColumns, string joinTables, string where, string orderby, string top. Then you create classes that inherit from this base class, so you might have a SQLServerDataAccessLayer and a MySQLDataAccessLayer. Using the factory pattern, you create an instance of the DataAccessLayer based on some configuration settings and the code calls the SQLSelect method in a standard way. I.e. the call might look like: SQLSelect("*", "Employees", "employeeID > 0", "hireDate", "1"); The SQLServerDataAccessLayer would create and run "SELECT TOP 1 * FROM Employees WHERE employeeID > 0 ORDER BY hireDate" returning a dataset. The MySQLDataAccessLayer would create and run "SELECT * FROM Employees WHERE employeeID > 0 ORDER BY hireDate LIMIT 1". This way we don't have to rewrite every sql statement when changing DB platforms.
I'm interested to know what the WTF community thinks of this approach.
Admin
What RBDMS do you use ?
There are very few instances where select * should be faster then selecting the column names. You really should have better indexes (ever heard of a covering index?) if you find select * gives you better performance.
Admin
Non-sequitur, and OO isn't required for many of these things anyway. And SQL Server 2K5, Oracle, and PostgreSQL can all use several OO languages for stored procedures.
Not IME.
The database is frequently eaiser to scale and cluster, because for many tasks, it has to be capable of doing that anyway.
More importantly, I'd rather have all my scalability problems in one place, that only have to be solved once, then have to deal with it at the web layer, and the business object layer (if there is a formal one i.e., J2EE EJBs) and then the database anyway.
Far more importantly, one frequently doesn't have any choice in the matter, especially if multiple systems in multiple langauges need the same business rules anyway.
Which ensures nothing about business logic.
A better, more sane thing to argue for would be something akin to a rules-based expert system builtin to a relational database and along side it. The only product I've ever worked with that did this worked well for what we used it for, but it wasn't fancy and the rules were very strict.
It's perfectly scalable, if designed correctly.
Not inherently a good thing.
No, you can't. You can get close, but you can't do this.
Admin
Not really.
Which is why no one uses it for that. Every vendor extends it heavily. More importantly, the procedural stuff (or portions thereof) are only valid in the procedures, so it's really fair at that point to consider distinct languages that are partial-supersets of SQL.
Admin
Admin
SELECT * - I take comfort in knowing exactly what data columns are going to be returned. If a column name changes (for whatever reason) I would prefer to have the code break rather than have the error go undetected.
Admin
This isn't the case. A WHERE clause still allows for Index coverage. Oh, you mean including the data records in the index? That's a hack. Yes, it can't be done. It normally shouldn't be anyway.
Admin
But you can! Take a look at the high-level functions of GNU-Script!
(http://www.art.net/~hopkins/Don/text/gnuscript.html)
Admin
This is so true, at the academic institution where I work, I actually watched someone more senior than me adding this "enterprisation" to a PeopleSoft SQR report.
#define SPACE ' '
#define nul ''
#define zero 0
#define TRUE 'TRUE'
#define FALSE 'FALSE'
#define IS_NEW 'NEW'
#define IS_MOD 'MOD'
#define IS_EXP 'EXP'
#define IS_ACT 'ACT'
Admin
Is there any time when the SQL syntax might be localized, such as:
"ESCOJA * DE ..."
If so, this makes a lot more sense than it does without the context.
Admin
A Hack? It's a very well respected tuning strategy recommended by all major database vendors. Please google "covering index" and study the 14,500 results that come back. Just because you don't know it doesn't make it a hack.
Admin
Which doesn't change the fact it's a hack, like many performance optimizations are.
Non-sequitur.
I'm well aware of the technique, it doesn't change what it is. In an ideal world, I wouldn't have to do that.
Frequently, I do. I never said it wasn't useful. But it's certainly not a first-case, nor universal optimization. It's dependent on schema, load, hardware, and a huge number of other factors.