Comment On SQL Sentences

Soooo, hard coded magic numbers, double the amout of code, an indirect requiring multiple DB reads just to figure out what to do, and presuming arguments needed to be passed to the query's where-clause, the whole thing was hard-wired anyway.

Nice!

I hope they included a SQL sentence history table as well for version control!

Can you patent ideas? I would like to patent this concept. Then I will never let anyone use it ever again.

How can one become "lead" developer by relying on hearsay? Probably because the superiors "heard" this person "is fit for the job". UnbeWTFinglievable!

JonC:

I hope they included a SQL sentence history table as well for version control!

Why stop there? I would suggest adding all code to an SQL library, with an identifier column, a language column and then a column with the code itself. Heck, you might even add a comment column.

Then, instead of calling a hard-wired procedure, you retrieve the correct id/language from the library, and eval() it. Much more flexible!

Drum D.:

How can one become "lead" developer by relying on hearsay? Probably because the superiors "heard" this person "is fit for the job". UnbeWTFinglievable!

Where have you been? These days, "lead developer" doesn't mean you have experience, it means that you were the second programmer the company ever hired (the first would be Director/VP of IT). You won't believe how many so-called "leads" I've met who were terrible, terrible coders, yet were Leads based only on seniority, not talent. God forbid companies actually promote people based on WHAT THEY CAN DO, and not HOW LONG THEY'VE BEEN THERE.

Addendum (2008-02-20 08:41):
Same thing with the majority of IT managers I've met - most of them had no real skill, but got their position through being with the company for 10 years.

I love places that proudly claim "We promote Leads from within", yet the guy who is the current lead has been there for 7 years, and was the second developer they hired.

BTW the real WTF is this "You can only edit the post for x amount of time before it forces you to append" nonsense.

of course, the where clauses were also in the SQLSentences

Though David wasn't around when these changes were implemented, when he came onboard and heard the story he asked the Lead Developer if he'd ever heard of a Stored Procedure before.

Misread that last part as Stoned Procedure. That could have been, too.

you had me at "So, to address this problem, they thoroughly analyzed the development process and carefully tweaked it for efficiency. I’m just kidding." :)

- Did you hear about spoons?
- Yes, but I also heard that they are a pain in the ass for some reason (I don't know why), so I'm gonna keep eating my soup with the fork.

snoofle:

Soooo, hard coded magic numbers, double the amout of code, an indirect requiring multiple DB reads just to figure out what to do, and presuming arguments needed to be passed to the query's where-clause, the whole thing was hard-wired anyway.

Nice!

Wow, you noticed that too?

Heh, that reminds me of a not-really-WTFey project I once did for a university.

Basically, they had a legacy system with fixed-width text fields. They wanted to convert the whole thing to a different type of legacy system, that was comma-delimited. The problem was, the formats weren't directly translatable, some tables had to be combined and others torn apart.

Additionally, we were doing this work for VB developers who at least had a handle on SQL.

So what did we do?

We wrote a ~100-line program that would read "tbl_queries", which held an entry for each column in the new format. When the program ran, it would import the old format to MySQL, run the query for each output column, and output all the old data to the new format.

It ran beautifully!

Some developers may want to use constants. Others might want functions to write the SQL:

string sql;
// @SQLSentence=0x02f0
if ((bool)(Configuration.GetValue("UseHardcodedSQL")))
{
sql = "SELECT cust_name, cust_phone, cust_addr " +
" FROM customers WHERE cust_type='Active'";
}
else if ((bool)(Configuration.GetValue("UseSQLConstants")))
{
sql = GET_CUSTOMERS;
}
else if ((bool)(Configuration.GetValue("UseSQLFunctions")))
{
sql = GetCustomerSql();
}
else
{
sql = DataConnector.GetSQLSentence(0x02f0);
}

I wish I could say this was new to me, but we use a similar system here. We store statements such as "SELECT * FROM Table WHERE ?1?" in a table, then our homegrown data access object takes a numeric ID and a parameter list, replaces the wild cards, and executes the statement.

The reason for doing this, of course, is to "prevent SQL injection attacks". Never mind that the developers can simply fill in the wild cards with whatever they want, including user input: since it's in the database already, there's no way for an injection attack to succeed.

Fortunately, the management does listen to reason occasionally and we're now free to use stored procs and actual injection prevention for new code. The old stuff doesn't show any sign of going away, though.

But where do they store the SQL code that queries the sql_sentences table?

I wonder if the queries inside DataConnector.GetSQLSentence(0x02f0); are also in the database... if so, is the query to retrieve that query also in the database.

We should rate programmers on how many layers of useless indirection they write before they realize their idea is stupid. +10 bonus for using integers as identifier per layer. We could call that the Enterprisey Programmer Scale.

I've got a much better solution for them:
- Program a Turing machine interpreter in SQL (its possible! i've done this)
- Program your business logic as a Turing machine and store it in the DB

This has major advantages:
- Barely anything needs to be done in the hardcoded program, making maintenance easy
- If SQL ever changes (or if you switch to a different DBMS) you only need to change the Turing machine interpreter - perfect portability

akatherder, that's actually a very good idea. You are a genius. If we can get enough donations, then we could patent all the really bad ideas, and then if people use them, we can charge them a fortune for it (as a kind of fine for being so stupid).

The only problem with this idea is that because some idiot thought of it first, then you can't really patent it as he/she would own the patent.

To work around this problem, we all need to put our collective heads together and come up with the most f*&#ed up and deranged ideas we can think of, and then get enough money together to patent them all. We'll get our money back in the end, from all the patent infringement fees.

WE'LL ALL BE RICH! RICH I SAY! DID YOU HEAR ME? RICH!

Why!? Why were these stores SQL Sentences stored as XML strings? That would add a lot more enterprisey and usability/maintanability!!! Why?!

NO no NO!:

Why!? Why were these stores SQL Sentences stored as XML strings? That would add a lot more enterprisey and usability/maintanability!!! Why?!

That sounds like ibatis.

Saving in the data base is a bad idea, but referencing something by a HEX number is a good one...

They could have just #define GET_ALL_CUSTOMERS 0x00F9 in their header file... I don't see the problem with that.

But reading SQL via SQL doesn't seem like a good idea, if they got injection attacked they whole program could be took over.

All you credit cards and bank info are belong us.

One of my previous workplaces the entire database was just three columns: TABLE_NAME, COLUMN_NAME, DATA_TYPE. I suspect you'll get the idea....

You might call it'a self-aware database...??

OUCH! Views and stored procedures... who needs them, right:P

Chris G:

One of my previous workplaces the entire database was just three columns: TABLE_NAME, COLUMN_NAME, DATA_TYPE. I suspect you'll get the idea....

You might call it'a self-aware database...??

What about the actual value? I recommend a separate (nullable) column for each data type. So, INTEGER_VALUE, DATETIME_VALUE, NVARCHAR_50_VALUE, etc. If you want to abstract this, everything can be stored as a VARCHAR(50) anyway...

vereor:

Saving in the data base is a bad idea, but referencing something by a HEX number is a good one...

They could have just #define GET_ALL_CUSTOMERS 0x00F9 in their header file... I don't see the problem with that...

Uhhh what? If they are given some arbitrary identifier, then how are you supposed to look these things up to see if a query you want already exists? This looks like C# also so no #define statements either.

That said, what these guys are trying to do isn't the craziest idea that I've ever heard of, but it may be the craziest implementation. At my company we do maintain a mapping of key -> statement, but we keep it in a simple properties file and not the database itself. The vast majority of these statements are calls to stored procedures, though.

You have to remember that the whole purpose of this was to facilitate bug fixes. These guys recognized that lots of changes needed to be made to their SQL statements so they needed a way to change these easily without recompiling the code. Except for the fact that they are hardcoding these things anyway in that stupid switch statement, separating your code from your SQL stuff isn't such a bad idea.

Dave:

snoofle:

Soooo, hard coded magic numbers, double the amout of code, an indirect requiring multiple DB reads just to figure out what to do, and presuming arguments needed to be passed to the query's where-clause, the whole thing was hard-wired anyway.

Nice!

Wow, you noticed that too?

Actually, I noticed it first, but yes, I am Master of the Obvious!

vereor:

All you credit cards and bank info are belong us.

Wow! Multiple layers of Engrish!

Cosmin Marginean:

- Did you hear about spoons?
- Yes, but I also heard that they are a pain in the ass for some reason (I don't know why), so I'm gonna keep eating my soup with the fork.

Reminds me of that poem:

I eat my peas with honey;
I've done it all my life.
It makes the peas taste funny,
But it keeps them on my knife.

Drum D.:

How can one become "lead" developer by relying on hearsay? Probably because the superiors "heard" this person "is fit for the job". UnbeWTFinglievable!

Obviously, the superiors heard about him from the secretary, just like any other normal software company.

Though David wasn't around when these changes were implemented, when he came onboard and heard the story he asked the Lead Developer if he'd ever heard of a Stored Procedure before.

“Yeah,” he replied, “but I’ve heard they’re a big pain to maintain.”

Classic. I laughed so hard when I read this.

"Though David wasn't around when these changes were implemented, when he came onboard and heard the story he asked the Lead Developer if he'd ever heard of a Stored Procedure before.

“Yeah,” he replied, “but I’ve heard they’re a big pain to maintain.” "


BWAHAHAHAHAHAHAHA! where's the 2 drum hits and a cymbal?
That sounded just like a punchline to a joke, WHERE punchline = 'sprocs a pain to maintain' and joke = 'sql sentences stored in a database' Although not a very funny one if you were hearing it in person, I'd imagine...

Also kinda funny, that to use the SQL, they jump up network traffic to GET the SQL to go BACK to the database WITH the SQL.. I don't even want to think about concurrency issues..

snoofle:

Soooo, hard coded magic numbers, double the amout of code, an indirect requiring multiple DB reads just to figure out what to do, and presuming arguments needed to be passed to the query's where-clause, the whole thing was hard-wired anyway.

Nice!

...and how does this fix their deployment problem?? <confused>

I've encountered Stored Procedure Fear many times and I don't get it. A quick Google search turns up a few sites explaining why they are evil and unnecessary - the authors of these sites typically claim that they are "old school" and experience has taught them that dynamic SQL is better.

Who do you think commits more "custom solution" WTFs? Young programmers who haven't yet learned that there's a better way to do something or old programmers who insist that their personally developed code is superior to common practice?

The kicker here is the hexadecimal identifiers for each sentence. I've seen half-ass homemade stored procedures like this several times in real applications, so it would be only a mild WTF for me. But I don't think I've ever seen them named with this amount of opacity before. Wow.

savar:

snoofle:

Soooo, hard coded magic numbers, double the amout of code, an indirect requiring multiple DB reads just to figure out what to do, and presuming arguments needed to be passed to the query's where-clause, the whole thing was hard-wired anyway.

Nice!

...and how does this fix their deployment problem?? <confused>

It doesn't; a big part of the wtf!

Another case of someone trying to reinvent an existing technology because they think they can make it better just like HyperParser.

I learned long ago that in corporate IT everything has already been done by smart people. I just have to find the solution, license and implement it. It is a great job. No stress and great hours for decent pay. However, I do have to look elsewhere for any kind of mental challenge, but I don't consider that a drawback because I don't live to work.

DRWTF is buried at the begining of second paragraph:

"Management figured that, (...)"

Everything else is just a downstream effect of business team developing a technical solution. Major no-no.

I wish you were joking, that I didn't understand what you were talking about, and that I wasn't working with people (lead architects) who think this is a good idea.

J:

I've encountered Stored Procedure Fear many times and I don't get it. A quick Google search turns up a few sites explaining why they are evil and unnecessary - the authors of these sites typically claim that they are "old school" and experience has taught them that dynamic SQL is better.

Who do you think commits more "custom solution" WTFs? Young programmers who haven't yet learned that there's a better way to do something or old programmers who insist that their personally developed code is superior to common practice?

sprocs have been around for a while, so I'd say it's not a matter of old vs new. However, what really gets me about the purely dynamic crowd and the purely stored procedure crowd is that neither of them seem to realize how properly managing dynamic sql IN a stored procedure can yield EXTREMELY great results. As long as you understand how sql text is parsed by the engine in determining when a new execution plan is generated, utilizing both (sproc with dynamic sql) in the appropriate amounts will yield excellent results and better maintainability. There are many people who swear by this, and make an excellent case for such use. Check out Erland Sommarskog for example, this guy knows his shit!!!

Barf 4 Eva:

J:

There are many people who swear by this, and make an excellent case for such use. Check out Erland Sommarskog for example, this guy knows his shit!!!

doh... And so that I don't mis-state his position, on his site he gives many different examples of properly maintaining dynamic sql... Obviously via a sproc is just one way.

Re: the "useHardCodedSQL" config setting, yes I've seen that in real life too.

An app started out with hard-coded queries:

<cfquery name="get_products" ds="#datasource#>

    select * from products where customer_id=#customer_id#

</cfquery>

Then someone had the idea that stored procs were the way to go. They used a perl or sed script or something to run through all the code and find all the queries and make them all be like this:

<cfif storedProcs>

  <cfquery name="get_products" ds="#datasource#>

    select * from products where customer_id=#customer_id#

  </cfquery>

<cfelse>

  <cfquery name="get_products" ds="#datasource#>

    select * from products where customer_id=#customer_id#

  </cfquery>

</cfif>

Yes, its the same on both branches. Someone then had to manually go through and change it:

<cfif storedProcs>

  {call sp_get_products(#customer_id#)}

<cfelse>

  <cfquery name="get_products" ds="#datasource#>

    select * from products where customer_id=#customer_id#

  </cfquery>

</cfif>

Well, they never made it through the whole app. Lots and lots of queries remained with the same SQL code repeated twice. Most of them, in fact. Then they abandoned the whole idea of converting everything to stored procs, but left all this code in.

Because the "storedProcs" config setting had already been set to true and left that way permanently, people eventually started only bothering to maintain the SQL on the true side. So for instance say they added a new column "status" and some more logic:

<cfif storedProcs>

  <cfquery name="get_products" ds="#datasource#>

    select * from products where

    customer_id=#customer_id#

    and status = 1

  </cfquery>

<cfelse>

  <cfquery name="get_products" ds="#datasource#>

    select * from products where customer_id=#customer_id#

  </cfquery>

</cfif>

So if you ever turned "storedProcs" off, the app would run the wrong SQL, even through stored procedures weren't being used at all.

Quis query ipsos queries?

Droll Troll on a roll:

Another case of someone trying to reinvent an existing technology because they think they can make it better just like HyperParser.

It's really more another case of someone creating a horrible, unusable, unmaintainable mess in the name of "not hardcoding stuff". They're probably proud because it's so much more flexible and maintainable.

This kind of thing is frighteningly common amongst architects and framework programmers who come up with APIs that they don't have to use themselves.

“but I’ve heard they’re a big pain to maintain.”

What? I'm no DBA but what? Maybe I missed something when I studied SP's but "a big pain" is not the idea I had. Maybe some experienced DBA can explain us in which way SP are a pain to maintain, must be so complex that my IQ doesn't reach there.

Anyway, what a stupid solution for such a simple matter. Thank god they weren't using C/C++ and filled it with DEFINE's.

John:

akatherder, that's actually a very good idea. You are a genius. If we can get enough donations, then we could patent all the really bad ideas, and then if people use them, we can charge them a fortune for it (as a kind of fine for being so stupid).

The only problem with this idea is that because some idiot thought of it first, then you can't really patent it as he/she would own the patent.

To work around this problem, we all need to put our collective heads together and come up with the most f*&#ed up and deranged ideas we can think of, and then get enough money together to patent them all. We'll get our money back in the end, from all the patent infringement fees.

WE'LL ALL BE RICH! RICH I SAY! DID YOU HEAR ME? RICH!

This site alone lists too much prior art! How many patents could we get past review?

FredSaw:

vereor:

All you credit cards and bank info are belong us.

Wow! Multiple layers of Engrish!

Welcome to the Internet. Yes, we know that these owls and cats look funny, we noticed that too.

John:

akatherder, that's actually a very good idea. You are a genius. If we can get enough donations, then we could patent all the really bad ideas, and then if people use them, we can charge them a fortune for it (as a kind of fine for being so stupid).

The only problem with this idea is that because some idiot thought of it first, then you can't really patent it as he/she would own the patent.

To work around this problem, we all need to put our collective heads together and come up with the most f*&#ed up and deranged ideas we can think of, and then get enough money together to patent them all. We'll get our money back in the end, from all the patent infringement fees.

WE'LL ALL BE RICH! RICH I SAY! DID YOU HEAR ME? RICH!

Alex is way ahead of you.

I learned long ago that in corporate IT everything has already been done by smart people. I just have to find the solution, license and implement it.

This is so true in CS in general. I do game development and I'm constantly amazed by the gall that game programmers have when they think they write a better mousetrap.

We (as an industry) still think that it's a good idea to always write a custom memory handling code for every project rather than having the OS handle it like a normal application. This may make sense on PS3 or something (not even sure about this) but on Windows? Mac? or Linux??? Are you crazy?!! Game programmers always like to say games are different than normal apps when it comes to memory management and OS's just can't handle it well. Riiiiight.

Usually it makes sense to tweak things here and there like allocate a huge block of memory when you know you are going to fill it all up right away then throw it all away. But to write a full memory allocation routine for everything in the heap is just madness in my opinion and usually is a cause of painful hard to debug errors when things like "page" handling breaks. Or more commonly, huge unneeded memory usage when your heap becomes all fragmented to hell and it becomes nontrivial to reclaim "freed" memory in an efficient manner. Just leave that to the OS developers! They've worked on that problem for a long time and know their domain way better than we do.

Zonkers:

We (as an industry) still think that it's a good idea to always write a custom memory handling code for every project rather than having the OS handle it like a normal application. This may make sense on PS3 or something (not even sure about this) but on Windows? Mac? or Linux??? Are you crazy?!! Game programmers always like to say games are different than normal apps when it comes to memory management and OS's just can't handle it well. Riiiiight.

Even if what "they" say is true, it still doesn't make sense to solve the problem again and again. Either build a library of code that you can reuse or - even better - find someone else who has already solved the problem better than you ever could, and reuse their code. As you say, these are the types of problems that have already been solved by people much smarter than you and I.

My company did this. Seriously.

Major european contractor of municipial software. The bonus part is guess where parameters to a query is handled? client or server?

You guessed right. Major fucking secority hole. Luckely it is not a public system.