Comment On The Path to WTF is Paved with Good Intentions

'intenet' = what you plan to do on the internet

ok ... ?

// adds some params to his query here
cmdText = this.ParseQuery(obj2.ToString(), ref xnParameters);

I especially like the fact that instead of using standard parameterized queries, he's invented his own templating language!

i don't understand why this is all fine... until we see the table.

?

This is obviously the more robust choice. Twenty years ago you'd be laughed at for suggesting we might add new genders to our species. We now have male, female, trans-gender, non-actualized, multiple, and "none."

We did something similar (table of queries) but in shell script (there wasn't enough time to finish the app and this allowed us to short circuit a lot of the as yet uncoded processing). The understanding was that the code still HAD to be written and this hack HAD to go away.

Naturally, nobody considered it urgent after delivery so it lived on... until the auditors came along. Heads rolled, butts were kicked, and suddenly there was time to finish the work.

Every once in a while you win one...

Where's the WTF?

select code, description from referencedata where codetype = 'YESNO' order by sortorder

Wha-

Nevermind, probably better not to ask.

It's in the queries themselves.

All those selects from the table "referencedata" with a code mean that not only the queries are softcoded, but the schema as well.

Hey, wait a second. A bunch of these aren't reports AT ALL!

So? You can update the codebase for something like that.

select employee_phone from employee_table where employee_title = 'IRISH GIRL'

The real WTF came before the "Well, before firing back in the comments "Where's the WTF?""

INSERT INTO datacommand (CommandName, Query, QueryDescription) VALUES ('CandyUnicornsAndOMGKittens', 'drop table referencedata;', 'Execute this and wonderful things will happen :)')

Reinventing the Stored Procedures, eh? ;)

How about adding a query to show all queries from the query table (or would that be too self-referential)?

rofl

Seems like he could have just used stored procedures... and not reinvent wheels.

I actually worked on a system once that was worse than this. It used XML files to define the stored procs in the system. It worked like this:

Each stored procedure had an XML file that described it. So if I was writing uspGetCustomerOrders, I had to create an xml file GetCustomerOrders.xml that enumerated all the parameters to that proc and their types.

Then each COM component had an XML file that mapped function name to stored procedure name. So if I was writing the getCustomerOrders method in the blOrders component, I had to open blOrders.xml and add an entry to map getCustomerOrders -> GetCustomerOrders.xml.

Really.

So the process of calling a stored proc at runtime from a method was:

1. Open the XML doc for the component you're in
2. Lookup the name of your method, find the SP definition
3. Open the XML doc for the proc definition
4. Fill in the proc definition with parameter values
5. Pass the proc definition to a generic routine to run it and, optionally, retrieve a rowset.

That's right, twice to the drive and twice into the XML DOM for every single data call. Did I mention this was a web app?

Of course the description column should not be nullable. Major WTF!

The proper colloquialism is "the code to hell is paved with good intentions." - Pete Goodliffe

Well, it DOES beat recompiling for every query change...

Or do admins here revoke accounts based on saying such things?

Haven't any of you worked with demon DBAs that will make you fill out forms in triplicate for any and all stored procedure changes, index creations, field additions or modifications or even, horrors, a new table?

I have written whole modules just to get around the stifling bureaucracy. Selecting SQL from a table is small potatoes.

This is obviously an ingenious solution. By routing every db operation through the same method you decrease the application memory footprint by improving code reuse and assure flexibility to provide future updates and extensions with a minimum of code modifications...

I just recently did a table of queries. I got sick of running exports for BHB's all the time for ad-hoc queries. These are things that are common enough to bother me, but not used enough to write a report page. So I have a page that displays all the query choices and lets them export the results as html or csv...

Yeah, I know it's probably not the best way to do it, but it's saved me a lot of time.

@Kempeth: you should apply to that 50000$/yr management positions. how good are you at powerpointing?

Laugh all you want, but I actually have had to add more genders to a system! It was an appointment system & a veterinary clinic decided to use it. In addition to Unknown, Male & Female, they also needed Neutered Male & Spayed Female.

However in this case, rather than make our hardcoded gender fields enterprisey and extendible, I just misused a different extensible field for this customer.

select code, description from referencedata where codetype = 'YESNO' order by sortorder

you knew someone would say it:

====================
|code |description |
====================
|0 |Yes |
|1 |No |
|2 |FileNotFound|
|3 |true |
|4 |false |
|5 |ON |
|6 |OFF |
|7 |<undefined> |
====================

I hate database WTFs...

For an English Website I had to add the title options Mr, Mrs, Ms and Other.

Megalonychidae:

For an English Website I had to add the title options Mr, Mrs, Ms and Other.

Miss, Dr, Professor, Reverend... :/

What's the problem?

bothwell:

Megalonychidae:

For an English Website I had to add the title options Mr, Mrs, Ms and Other.

Miss, Dr, Professor, Reverend... :/

What's the problem?

Yeah, right. The selection 'other' makes the person obviously a professor.

Megalonychidae:

For an English Website I had to add the title options Mr, Mrs, Ms and Other.

I've seen signup pages that had over four screens worth of titles available. I barely resisted the temptation to list myself as a Rear Admiral or Pope.

Megalonychidae:

bothwell:

Megalonychidae:

For an English Website I had to add the title options Mr, Mrs, Ms and Other.

Miss, Dr, Professor, Reverend... :/

What's the problem?

Yeah, right. The selection 'other' makes the person obviously a professor.

Actually, it does annoy me when websites don't have an option for Dr. I earn the title and I'd like to use it once in awhile.

Anon:

Megalonychidae:

bothwell:

Megalonychidae:

For an English Website I had to add the title options Mr, Mrs, Ms and Other.

Miss, Dr, Professor, Reverend... :/

What's the problem?

Yeah, right. The selection 'other' makes the person obviously a professor.

Actually, it does annoy me when websites don't have an option for Dr. I earn the title and I'd like to use it once in awhile.

My place wouldn't give me business cards, so I made up some joke business cards with the title: Grand Imperial Wizard of All Things Great and Small, but it's never in those on-line lists.

Where is the Internet Complaint Department?

Jay:

I actually worked on a system once that was worse than this. It used XML files to define the stored procs in the system. It worked like this:

Each stored procedure had an XML file that described it. So if I was writing uspGetCustomerOrders, I had to create an xml file GetCustomerOrders.xml that enumerated all the parameters to that proc and their types.

Then each COM component had an XML file that mapped function name to stored procedure name. So if I was writing the getCustomerOrders method in the blOrders component, I had to open blOrders.xml and add an entry to map getCustomerOrders -> GetCustomerOrders.xml.

Really.

So the process of calling a stored proc at runtime from a method was:

1. Open the XML doc for the component you're in
2. Lookup the name of your method, find the SP definition
3. Open the XML doc for the proc definition
4. Fill in the proc definition with parameter values
5. Pass the proc definition to a generic routine to run it and, optionally, retrieve a rowset.

That's right, twice to the drive and twice into the XML DOM for every single data call. Did I mention this was a web app?

I feel your pain - I work with something very similar. Everything is defined in XML (and displayed with XSL) for all core DB access. It means you can add a field to the system by simply adding it to the DB, then to the XML (to read/write it) and the XSL (to display it). Now, I'm a programmer... adding a field in code should be too taxing, but whatever. It was fine until I wanted to add a WHERE clause conditionally on something else. There was just no way to do it.

valerion:

Jay:

So the process of calling a stored proc at runtime from a method was:

1. Open the XML doc for the component you're in
2. Lookup the name of your method, find the SP definition
3. Open the XML doc for the proc definition
4. Fill in the proc definition with parameter values
5. Pass the proc definition to a generic routine to run it and, optionally, retrieve a rowset.

That's right, twice to the drive and twice into the XML DOM for every single data call. Did I mention this was a web app?

I feel your pain - I work with something very similar. Everything is defined in XML (and displayed with XSL) for all core DB access. It means you can add a field to the system by simply adding it to the DB, then to the XML (to read/write it) and the XSL (to display it). Now, I'm a programmer... adding a field in code should be too taxing, but whatever. It was fine until I wanted to add a WHERE clause conditionally on something else. There was just no way to do it.

Clearly this should have been implemented as a table with one column, of datatype LOB, containing XML. That would be totally extensible and enterprisey.

RayMarron:

Laugh all you want, but I actually have had to add more genders to a system! It was an appointment system & a veterinary clinic decided to use it. In addition to Unknown, Male & Female, they also needed Neutered Male & Spayed Female.

However in this case, rather than make our hardcoded gender fields enterprisey and extendible, I just misused a different extensible field for this customer.

(Clamps legs together)

Heh.. On my previous work I had an idea, that should help us (developers) to avoid system administrators when we visited the production site.
My genius solution was to create a loader, which then connected to the DB, fetches the latest dll for this project, then places it in the memory and after that executes dllmain.
I don't know was it good or bad, but i never got time to finish this part. :)

But we implemented a lot of other "cool" stuff in it. I feel sorry for the guy, who is maintaining this project now.

Chris B:

The real WTF came before the "Well, before firing back in the comments "Where's the WTF?""

I agree. TRFTW is that the DBAs even let anyone run queries against a production database. I thought it was pretty much standard practice in any enterprise to restrict applications to only stored procedures, and to disable sp_execsql.

I can do one better.

OleDbConnection connection = new OleDbConnection(gobjConfig.GetConnectionString());
connection.Open();
program = "select code from program where function = " + szFuncName;
OleDbCommand command = new OleDbCommand(cmdText, connection);
object obj2 = command.ExecuteScalar();

eval(obj2.ToString());

connection.Close();
connection.Dispose();
command.Dispose();

There. Never need to update the program again. Just change the queries in the database.

Patrick:

select code, description from referencedata where codetype = 'YESNO' order by sortorder

you knew someone would say it:

====================
|code |description |
====================
|0 |Yes |
|1 |No |
|2 |FileNotFound|
|3 |true |
|4 |false |
|5 |ON |
|6 |OFF |
|7 |<undefined> |
====================

lolwut

Anon:

Megalonychidae:

bothwell:

Megalonychidae:

For an English Website I had to add the title options Mr, Mrs, Ms and Other.

Miss, Dr, Professor, Reverend... :/

What's the problem?

Yeah, right. The selection 'other' makes the person obviously a professor.

Actually, it does annoy me when websites don't have an option for Dr. I earn the title and I'd like to use it once in awhile.

I didn't go to 7 years of evil medical school to be called Mr.!

cmdText = "select query from datacommand where commandname = " + szText

Is this really the right way to do it on Windows?
Constructing query strings by hand is in my world dangerous crap from the 70s.

This sort of describes my current project ... only it's the harmless version.
SQL snippets are stored in Excel templates, then concatenated by the software to one massive statement (7 pages in Word) only to retrieve 1 line of data (with > 100 columns) which is written back to the Excel template and then sent to the client as an Excel sheet - with the original SQL snippets still existing in "hidden" cells. And this is just one major WTF in the whole thing!

Luckily, my task is a total rewrite to get rid of such madness.

Tilman Baumann:

cmdText = "select query from datacommand where commandname = " + szText

Is this really the right way to do it on Windows?
Constructing query strings by hand is in my world dangerous crap from the 70s.

no, it is not the right way, OLE DB / ADO have always supported a parameters collection. Even RDO (the predecessor to ADO) supported some parameterization, and that's going back to the VB 5 days.

ShatteredArm:

Chris B:

The real WTF came before the "Well, before firing back in the comments "Where's the WTF?""

I agree. TRFTW is that the DBAs even let anyone run queries against a production database. I thought it was pretty much standard practice in any enterprise to restrict applications to only stored procedures, and to disable sp_execsql.

The exact opposite happens in my company. For some reason the DBA's are afraid of Stored Procs, but not afraid of rouge queries in our code.

Corporate Cog:

The proper colloquialism is "the code to hell is paved with good intentions." - Pete Goodliffe

s/good\ intentions/cool\ inventions

$0.02 please

Corporate Cog:

The proper colloquialism is "the code to hell is paved with good intentions." - Pete Goodliffe

Not to mention the code from hell.

Anon:

Actually, it does annoy me when websites don't have an option for Dr. I earn the title and I'd like to use it once in awhile.

Poor baby. Sounds like you "earn the title" AMF as well (A = arrogant).