Enterprise SQL

« Return to Article
  • BradC 2006-03-21 14:26
    <P>Is this what people mean when they preach against using "dynamic sql" ? [:O]</P>
  • Mig-O 2006-03-21 14:27
    <span id="_ctl0_PostForm_Reply">&lt;cite&gt;...we learned how vital enterprise
    application are for proactive organizations leveraging collective
    synergy to think outside the box and formulate their key objectives
    into a win-win game plan with a quality-driven approach that focuses on
    empowering key players to drive-up their core competencies and increase
    expectations with an all-around initiative to drive up the bottom-line.&lt;/cite&gt;<br><br>LOL<br>buzzword bashing at its best<br><br>PS: First?<br></span>
  • Seltsam 2006-03-21 14:28
    Yes!&nbsp; My company's product has a huge list of constants.&nbsp; Some of which are just as silly as those above.<br>
  • darthCodex 2006-03-21 14:30
    The real WTF is ofcourse that they use SELECT STAR instead of specifying the columns they really need.<br>
  • Colin 2006-03-21 14:33
    SqlMisc.NUMBERS_ONE;<br><br><br>OH THANK GOD! Just in case the number one changes to 'blue' or something.<br><br><br>"GIMME # sourced table conditioned action%blue"
  • Ricky 2006-03-21 14:34
    Thats not really bad.<br>In fact, if you do a test -- you will see that using select * is on average faster that specifying all the columns!<br>-Ricky<br>
  • Ragnaros 2006-03-21 14:36
    <P>IDIOTS!!!</P>
  • beefcake 2006-03-21 14:36
    OH MY GOD  <br>
  • headhigh 2006-03-21 14:36
    This WTF gets (4 * SqlWords.STAR) out of 5 for WTFness.<br>
  • Code Slave 2006-03-21 14:38
    <P>Let's take this to n'th degree</P>
    <P><SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">class</SPAN> SqlWords<BR>{<BR>&nbsp; <SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> SPACE _CHAR= <SPAN style="COLOR: #848284">"&nbsp;"</SPAN>;</P>
    <P>//...<BR>&nbsp; <SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> E_CHAR = <SPAN style="COLOR: #848284">"E"</SPAN>;</P>
    <P>//...<BR>&nbsp; <SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> L_CHAR = <SPAN style="COLOR: #848284">"L"</SPAN>;</P>
    <P>//....<BR>&nbsp; <SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> S_CHAR = <SPAN style="COLOR: #848284">"S"</SPAN>;<BR>}</P>
    <P><SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">class</SPAN> SqlWords<BR>{<BR>&nbsp; <SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> SELECT = <SPAN style="COLOR: #848284">SPACE_CHAR + S_CHAR + E_CHAR + //...</SPAN></P>
    <P><SPAN style="COLOR: #848284"><FONT color=#000000>}</FONT></SPAN></P>
    <P><SPAN style="COLOR: #848284"><FONT color=#000000>Perhaps that's n-2'th, but there's no way I'm going to hex of binary.</FONT></SPAN></P>
  • anonymous 2006-03-21 14:38
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).&nbsp; Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br>
    CAPTCHA: SOLDIER, I mean<br>
    CAPTCHA: salad<br>
  • cconroy 2006-03-21 14:39
    <span style="font-family: verdana;"><font size="2">return Words.WHAT + Words.THE + Words.EXPLETIVE_OF_CHOICE + Punctuation.QUESTION_MARK;<br>
    <br>
    </font></span>
  • Romeo 2006-03-21 14:41
    Alex Papadimoulis:
    <blockquote><pre style="color: rgb(0, 0, 0);">
    <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> INNER = <span style="color: rgb(132, 130, 132);">" INNER "</span>;
    <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> JOIN = <span style="color: rgb(132, 130, 132);">" JOIN "</span>;
    <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> INNER_JOIN = <span style="color: rgb(132, 130, 132);">" INNER JOIN "</span>;
    </pre></blockquote>
    <br><br>Allright.&nbsp; Following this, we may have:<br><pre style="color: rgb(0, 0, 0);"><span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> SELECT_STAR_FROM = <span style="color: rgb(132, 130, 132);">" SELECT * FROM "</span>;<br><br>And so on...<br></pre>
    <br>&nbsp;<br>
  • Destroyer 2006-03-21 14:44
    Romeo:
    Alex Papadimoulis:

    <BLOCKQUOTE><PRE style="COLOR: rgb(0,0,0)"> <SPAN style="COLOR: rgb(0,0,255)">public</SPAN> <SPAN style="COLOR: rgb(0,0,255)">const</SPAN> <SPAN style="COLOR: rgb(0,0,255)">string</SPAN> INNER = <SPAN style="COLOR: rgb(132,130,132)">" INNER "</SPAN>;
    <SPAN style="COLOR: rgb(0,0,255)">public</SPAN> <SPAN style="COLOR: rgb(0,0,255)">const</SPAN> <SPAN style="COLOR: rgb(0,0,255)">string</SPAN> JOIN = <SPAN style="COLOR: rgb(132,130,132)">" JOIN "</SPAN>;
    <SPAN style="COLOR: rgb(0,0,255)">public</SPAN> <SPAN style="COLOR: rgb(0,0,255)">const</SPAN> <SPAN style="COLOR: rgb(0,0,255)">string</SPAN> INNER_JOIN = <SPAN style="COLOR: rgb(132,130,132)">" INNER JOIN "</SPAN>;
    </PRE></BLOCKQUOTE>
    <BR><BR>Allright.&nbsp; Following this, we may have:<BR><PRE style="COLOR: rgb(0,0,0)"><SPAN style="COLOR: rgb(0,0,255)">public</SPAN> <SPAN style="COLOR: rgb(0,0,255)">const</SPAN> <SPAN style="COLOR: rgb(0,0,255)">string</SPAN> SELECT_STAR_FROM = <SPAN style="COLOR: rgb(132,130,132)">" SELECT * FROM "</SPAN>;<BR><BR>And so on...<BR></PRE>
    <P><BR>&nbsp;<BR>
    </P>
    <P>How about:</P>
    <P>public const string SELECT_STAR_FROM = SELECT + " " + STAR + " " + FROM;</P>
  • R.Flowers 2006-03-21 14:45
    <P><FONT face="Courier New">public class Hello<BR>&nbsp;{<BR>&nbsp;&nbsp;&nbsp; public const string HELLO "Hello";<BR>&nbsp;}</FONT></P>
    <P><FONT face="Courier New">public class&nbsp;Space<BR>&nbsp;{<BR>&nbsp;&nbsp;&nbsp; public const string SPACE " ";<BR>&nbsp;}</FONT></P>
    <P><FONT face="Courier New">public class World<BR>&nbsp; {<BR>&nbsp;&nbsp;&nbsp; public const string WORLD "World";</FONT></P>
    <P><FONT face="Courier New">public class&nbsp;ExclamationPoint<BR>&nbsp;{<BR>&nbsp;&nbsp;&nbsp; public const string EXCLAIM "!";<BR>&nbsp;}</FONT></P>
    <P><FONT face="Courier New">public class&nbsp;Output<BR>&nbsp;{<BR>&nbsp;&nbsp;&nbsp; public const string&nbsp;OUTPUT = Hello.HELLO +&nbsp;Space.SPACE + World.WORLD + ExclamationPoint.EXCLAIM;<BR>&nbsp;}</FONT></P>
    <P>I'm going to reapply for that job at my local university, now that I'm all enterprisey.</P>
  • GalacticCowboy 2006-03-21 14:45
    <P>
    Anonymous:
    <SPAN style="COLOR: #848284"><FONT color=#000000>Perhaps that's n-2'th, but there's no way I'm going to hex of binary.</FONT></SPAN>
    </P>
    <P>N minus tooth?</P>
  • Ragnaros 2006-03-21 14:45
    <P>I take it back. I understand why they did this. It's because the SQL needs to be globalized. If it's going to run in Mexico, for example,</P>
    <P><FONT face="Courier New" color=#000000>"TENER Productos.*, Empleados.*&nbsp;DE Productos, Empleados&nbsp;DONDE ProductoID ES " +&nbsp;Constants.TWENTY_TWO + " Y NUMERO_INTENTOS ES MENOR QUE @PrimerConta JUNTOS CON Departmento&nbsp;EN EmpleadoID"</FONT></P><FONT face="Times New Roman" color=#000000>
    <P>It's been a while since I have written SQL in Spanish, but I'm pretty sure this is right. These guys built exactly what I need.</P>
    <P>Except now my spanish SQL statement has nasty English words in it like "Constants" and "TWENTY_TWO"</P></FONT>
  • daBowmore 2006-03-21 14:49
    Gotta love that SqlWords.STAR! I will definitely use that everywhere in my enter- or returnprise piece of applications!
  • Ash 2006-03-21 14:52
    Well... at least they can use code completion now...
  • limelight 2006-03-21 14:53
    Alex Papadimoulis:

    <P>If you're looking at today's code and thinking, <I>gee, shouldn't these be in stored procedures, or views, or something</I>, then you're still thinking in the world of unenterpriseness
    <BLOCKQUOTE>
    <P><PRE style="COLOR: #000000">
    <SPAN style="COLOR: #008200">/* SNIP */</SPAN>

    <SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">class</SPAN> SqlQueries
    {
    <SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> SELECT_ACTIVE_PRODCUTS =
    SqlWords.SELECT +
    SqlWords.STAR +
    SqlWords.FROM +
    SqlTables.PRODUCTS +
    SqlWords.WHERE +
    SqlColumns.PRODUCTS_ISACTIVE +
    SqlWords.EQUALS +
    SqlMisc.NUMBERS_ONE;

    <SPAN style="COLOR: #008200">/* SNIP */</SPAN>

    <SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> UPDATE_LOGON =
    SqlWords.UPDATE +
    SqlTables.CREDENTIALS +
    SqlWords.SET +
    SqlColumns.CREDENTIALS_LOGON_NAME +
    SqlWords.EQUALS +
    SqlMisc.PARAMS_FIRST +
    SqlWords.COMMA +
    SqlColumns.CREDENTIALS_LOGON_PASS +
    SqlWords.EQUALS +
    SqlMisc.PARAMS_SECOND;
    }</PRE></BLOCKQUOTE>
    <P>
    </P>
    <P>&nbsp; Looking at the second SQL string, I&nbsp;can't quite&nbsp;figure out where you would need&nbsp;a constant update statement. Other than some kind of "lets reset something or another", where would such a statement be useful? I'm thinking that perhaps this is part of some kind of setup program and it is used to initially condition the database for usage. Since the update statement contains a reference to "PARAMS_FIRST" and "PARAMS_SECOND", it may be used to actually generate a stored procedure (e.g."create procedure &lt;something or another&gt; &lt;some input params&gt; as " + UPDATE_LOGON).</P>
  • BlackTigerX 2006-03-21 14:54
    Alex Papadimoulis:

    <P>... I should note that this approach also pads your code against changes in the SQL language. I've heard from some good sources that the next version of SQL will use the word "GIMMIE" instead of "SELECT".</P>
    <P>
    </P>
    <P>too bad they used <STRONG>const</STRONG> instead of <STRONG>static readonly</STRONG>, they will have to recompile all the applications that are using those assemblies if SQL makes a change to the key words!!</P>
  • Jeff S 2006-03-21 14:57
    Anonymous:
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).&nbsp; Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br><br>While today's post is a great WTF, if what you are saying about those DBAs is true, then they have taken the top "WTF" prize for the day.&nbsp; That might be one of the stupidest things I have ever heard.&nbsp; Did you really work with *many* of them? That's a scary thought.<br>
  • Corentin 2006-03-21 14:58
    A great design indeed, if you want to write your SQL requests in klingon language.<br>
    <br>
    Truly an Enterprise system! (despite the lack of XML)<br>
  • Matt B 2006-03-21 14:58
    The best part of this is that the compiler is more likely than not inlining all of their constants into<br><br>
    public const string SELECT_ACTIVE_PRODUCTS = SELECT * FROM PRODUCTS WHERE PRODUCTS_IS ACTIVE = 1
    <br>
  • kmerkle 2006-03-21 14:58
    What's most important here, is that the developer is freed from such annoying constraints such as the correct spelling of table names:&nbsp; SELECT_ACTIVE_<FONT color=#ff0000>PRODCUTS</FONT>
  • Matt B 2006-03-21 14:58
    Matt B:
    The best part of this is that the compiler is more likely than not inlining all of their constants into<br><br>
    public const string SELECT_ACTIVE_PRODUCTS = SELECT * FROM PRODUCTS WHERE PRODUCTS_IS ACTIVE = 1
    <br>
    <br><br>forgot the quotes but you get the idea&nbsp;&nbsp; <br>
  • WTFlet 2006-03-21 14:59
    Anonymous:
    <p>Let's take this to n'th degree</p>
    <p><span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">class</span> SqlWords<br>{<br>&nbsp; <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> SPACE _CHAR= <span style="color: rgb(132, 130, 132);">"&nbsp;"</span>;</p>
    <p>//...<br>&nbsp; <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> E_CHAR = <span style="color: rgb(132, 130, 132);">"E"</span>;</p>
    <p>//...<br>&nbsp; <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> L_CHAR = <span style="color: rgb(132, 130, 132);">"L"</span>;</p>
    <p>//....<br>&nbsp; <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> S_CHAR = <span style="color: rgb(132, 130, 132);">"S"</span>;<br>}</p>
    <p><span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">class</span> SqlWords<br>{<br>&nbsp; <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> SELECT = <span style="color: rgb(132, 130, 132);">SPACE_CHAR + S_CHAR + E_CHAR + //...</span></p>
    <p><span style="color: rgb(132, 130, 132);"><font color="#000000">}</font></span></p>
    <p><span style="color: rgb(132, 130, 132);"><font color="#000000">Perhaps that's n-2'th, but there's no way I'm going to hex of binary.</font></span></p>
    <br><br>May your death be long and painful (just like mine after seeing this abomination)<br>
  • Dave 2006-03-21 15:01
    I can see a future programmer confused as to why SqlWords.ASTERISK is not defined...<br><br>Of course if a majority of their intellectual property was in the SQL statements themselves, if I'm not mistaken, their asinine way of doing the SQL statements would be more easily obfuscated by an obfuscator, because we all know how secure .NET code is!<br>
  • mpswaim 2006-03-21 15:02
    <P>
    Anonymous:
    Thats not really bad.<BR>In fact, if you do a test -- you will see that using select * is on average faster that specifying all the columns!<BR>
    </P>
    <P>But what if the number of columns changes? There's some stuff (.net typed datasets, among others) that get really upset when the number of columns changes. I've stored multiple values in a column because it was easier to decode values than to patch all the applications that assumed a certain number of columns. Eventually I went on a rampage and removed every "select *" from all the application code and stored procedures I could find. Life is better now.</P>
  • lamborghini 2006-03-21 15:02
    Alex Papadimoulis:

    <BLOCKQUOTE>
    public const string INNER = " INNER ";
    public const string JOIN = " JOIN ";
    public const string INNER_JOIN = " INNER JOIN ";
    </BLOCKQUOTE>


    [sarcastic-begin]
    THAT'S not how it's done in a real enterprise application. The right way is,

    public const string INNER = " INNER ";
    public const string JOIN = " JOIN ";
    public const string INNER_JOIN = " " + SqlWords.INNER + " " + SqlWords.JOIN + " ";
    [sarcastic-end]

    :-D
  • georgeblunt 2006-03-21 15:03
    This was one of the best WTF's I've read on this page.. not because of the code (which of course IS a truly WTF..no doubt), but because of the way the article was written.<br>Gotta love this kind of sarcasm :D<br>
  • Ralph Wiggum 2006-03-21 15:07
    Anonymous:
    Thats not really bad.<br>In fact, if you do a test -- you will see that using select * is on average faster that specifying all the columns!<br>-Ricky<br>
    <br><br>That's a blanket statement, not always accurate. If your table has 100 columns, and you need 1, selecting the 1 column WILL be faster. If you need 99 columns, selecting * should be faster. Depending on the language, there can be a lot of overhead - JDBC in java for example introduces some horrible overhead for meta columns you're not using anyways.<br>
  • sebmol 2006-03-21 15:07
    So we all agree here that this is a stupid way of doing things. But let me ask you then: how do you write a generic data access layer for a SQL database? Or do you even do that? I can understand that performing direct queries makes a lot of sense in a desktop application, but how does that scale on a multithreaded web or server application? And how do you automate schema changes? Any thoughts?<br>
  • MadCow42 2006-03-21 15:16
    <P>Definately the biggest WTF I've seen here in a while... in most cases you can understand how some newbie, hack, or complete F*&amp;kup might think he's doing something great, but THIS?&nbsp; How can ANYONE think this is a good way to program anything???</P>
    <P>Wow... the only possible excuse I can even think of is they're paid or measured by the line of code.&nbsp; They should be castrated and sent on their way anyhow though.</P>
  • Scott 2006-03-21 15:19
    sebmol:
    So we all agree here that this is a stupid way of doing things. But let me ask you then: how do you write a generic data access layer for a SQL database? Or do you even do that? I can understand that performing direct queries makes a lot of sense in a desktop application, but how does that scale on a multithreaded web or server application? And how do you automate schema changes? Any thoughts?<br>
    <br><br>With alot of code.<br>
  • Willie 2006-03-21 15:19
    " I've heard from some good sources that the next version of SQL will use the word 'GIMMIE' instead of 'SELECT'."

    Am I the only person who totally wants this to happen?
  • Alex Papadimoulis 2006-03-21 15:19
    <P>
    Anonymous:
    For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes).&nbsp; Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.<BR><BR>I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?
    </P>
    <P>I take it then your database structure looks like:<BR>&nbsp;CREATE TABLE [Tables] ( Table_Name VARCHAR )<BR>&nbsp;CREATE TABLE [Columns] (Table_Name VARCHAR, Column_Name VARCHAR)<BR>&nbsp;CREATE TABLE [Rows] (Table_Name VARCHAR, Row_Num IDENTITY)<BR>&nbsp;CREATE TABLE [Values] (Table_Name VARCHAR, Row_Num IDENTITY, Column_name VARCHAR, Value SQLVARIANT)</P>
    <P>Because if it looks anything like that, then you're still embedding some kind of business logi in the database.</P>
  • mikeb 2006-03-21 15:23
    I call bull!

    There's no way this can be for real. Simply no way.

    Please... this really can't be for real?
  • sebmol 2006-03-21 15:26
    Anonymous:
    sebmol:
    So we all agree here that this is a stupid way of doing things. But let me ask you then: how do you write a generic data access layer for a SQL database? Or do you even do that? I can understand that performing direct queries makes a lot of sense in a desktop application, but how does that scale on a multithreaded web or server application? And how do you automate schema changes? Any thoughts?<br>
    <br><br>With alot of code.<br>
    <br><br>So you end up writing basically the same code for every application with slight adjustment to the respective problem domain?<br><br>I'm seriously looking for answers because it just doesn't seem right to me. I've been going through ASP.NET/ADO.NET samples and they always go directly through the query without any abstraction and certainly without any caching.<br>
  • More Stored Procedures Please!!! 2006-03-21 15:30
    Jeff S:
    Anonymous:
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).  Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br><br>While today's post is a great WTF, if what you are saying about those DBAs is true, then they have taken the top "WTF" prize for the day.  That might be one of the stupidest things I have ever heard.  Did you really work with *many* of them? That's a scary thought.<br>
    <br><br>Wow, trying to keep application logic out of a database is the *stupidest* thing you've ever heard?  If there is one property of "enterprise" applications, it is the fact they are often systems that deal with many data sources and services, often performing logic between the disparate sources.  But lets list some reasons to keep the logic out of the database.<br><br>1) Stored procedures tie you to a particular vendor<br>2) Application logic in the database has to duplicate logic in the application<br>3) Horrible source code control (in every case I've witnessed)<br>4) Lack of OO design, hence the term "stored PROCEDURE"<br>5) Difficulty migrating to a different database vendor/version<br>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<br><br>Instead, put the logic in your objects, keep the database as minimal as possible, and certainly use a good persistence tool to ensure the updates and queries are correct.<br><br>I admit, you can instaed take a very data-centric view of things, and many people successfully work under the idea that the database *IS* the application, and everything else is just a GUI/front end.  But this is not very scalable, not Object Oriented at all, and IMHO is only suitable for smaller systems, and certainly not large "enterprise" ;) systems.  All these points can, of course, be debated...<br><br>But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!<br>
  • chazzie 2006-03-21 15:36
    <span id="_ctl0_PostForm_Reply">&gt; And how do you automate schema changes?<br><br>Simple.&nbsp; You DON'T.&nbsp; You can provide compatibility layers in the form of views and triggers, you can make schema changes through tools that record the schema migration, but there simply isn't any way to make it seamless and automatic.<br><br>The rest goes about the same, just less strongly (dialects and types after all don't change as frequently).&nbsp; You can't write a perfect generic-for-everything layer any more than you can write a program that does everything with one button push and one line of code that both read "do what I mean".&nbsp; <br><br>As for scaling, databases are built for that already.&nbsp; Don't second-guess the database with application logic.&nbsp; If you end up having to do that, get a better database.<br><br>My captcha says "register".&nbsp; Is it trying to tell me something?&nbsp; Yes, that it's broken and now wants me to say "china".&nbsp; Proving once again that the forum software is the real WTF<br></span>
  • sebmol 2006-03-21 15:38
    Anonymous:
    <br>Wow, trying to keep application logic out of a database is the *stupidest* thing you've ever heard?&nbsp; If there is one property of "enterprise" applications, it is the fact they are often systems that deal with many data sources and services, often performing logic between the disparate sources.&nbsp; But lets list some reasons to keep the logic out of the database.<br><br>1) Stored procedures tie you to a particular vendor<br>2) Application logic in the database has to duplicate logic in the application<br>3) Horrible source code control (in every case I've witnessed)<br>4) Lack of OO design, hence the term "stored PROCEDURE"<br>5) Difficulty migrating to a different database vendor/version<br>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<br><br>Instead, put the logic in your objects, keep the database as minimal as possible, and certainly use a good persistence tool to ensure the updates and queries are correct.<br><br>I admit, you can instaed take a very data-centric view of things, and many people successfully work under the idea that the database *IS* the application, and everything else is just a GUI/front end.&nbsp; But this is not very scalable, not Object Oriented at all, and IMHO is only suitable for smaller systems, and certainly not large "enterprise" ;) systems.&nbsp; All these points can, of course, be debated...<br><br>But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!<br>
    <br><br>Those have been my thoughts exactly. My first larger DB project, I did everything using stored procedures. Actually, I created a web service that basically published those stored procedures and all outside access was done through the web service. The advantage is that you can have one data source regardless of whether your application runs online or on the desktop. While that makes for a very scalable middle-tier, I was stuck with the particular database (SQL Server) I was using and moving to a different database, much less different vendor, was close to impossible.<br><br>So this time around I have a system that is entirely code-driven and executes dynamic queries in the background. The DB schema is automatically updated whenever I make changes to the corresponding objects in the application. Data access is also cached so that I only have to pull data from the db once.<br><br>But my question is still: is this a good way to do things? All the sample code I see everywhere performs direct queries with no caching and no OO whatsoever. Thoughts?<br><br>
  • DG 2006-03-21 15:39
    Looking at that made me physically nauseous.  I'm actually still a little queazy.  I probably have to go do something else for a while to get rid of the feeling... <br>
  • anonymous 2006-03-21 15:45
    <P>First, just because&nbsp;you have&nbsp;"ASP.NET/ADO.NET samples and they always go directly through the query" doesn't mean there's "no abstraction or caching going on".&nbsp; All professional databases, and even some Microsoft ones, use techniques like cursor sharing when processing queries.</P>
    <P>&nbsp;</P>
    <P>&nbsp;</P>
    <P>&nbsp;</P>
    <P><BR>&nbsp;</P>
  • Mihai 2006-03-21 15:48
    Anonymous:
    <p>Let's take this to n'th degree</p>
    <p><span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">class</span> SqlWords<br>{<br>  <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> SPACE _CHAR= <span style="color: rgb(132, 130, 132);">" "</span>;</p>
    <p>//...<br>  <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> E_CHAR = <span style="color: rgb(132, 130, 132);">"E"</span>;</p>
    <p>//...<br>  <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> L_CHAR = <span style="color: rgb(132, 130, 132);">"L"</span>;</p>
    <p>//....<br>  <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> S_CHAR = <span style="color: rgb(132, 130, 132);">"S"</span>;<br>}</p>
    <p><span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">class</span> SqlWords<br>{<br>  <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> SELECT = <span style="color: rgb(132, 130, 132);">SPACE_CHAR + S_CHAR + E_CHAR + //...</span></p>
    <p><span style="color: rgb(132, 130, 132);"><font color="#000000">}</font></span></p>
    <p><span style="color: rgb(132, 130, 132);"><font color="#000000">Perhaps that's n-2'th, but there's no way I'm going to hex of binary.</font></span></p>
    <br><br>Someties this might have a good reason. This is for real:<br>=============<br>const XMLCh chLatin_A               = 0x41;<br>const XMLCh chLatin_B               = 0x42;<br>...<br>const XMLCh chLatin_Z               = 0x5A;<br><br>const XMLCh chLatin_a               = 0x61;<br>const XMLCh chLatin_b               = 0x62;<br>...<br>const XMLCh chLatin_z               = 0x7A;<br>=============<br>Then used like this:<br>static const XMLCh fgXMLCategory[] =<br>{<br>    chLatin_X, chLatin_M, chLatin_L, chNull<br>};<br>=============<br>Q. Can you tell where is this from, and why is done this way?<br>A. It is from Xerces, the Apache cross-platform C++ XML parser.<br>And it is done this way because it can also run on EBCDIC platforms, where even the 0-128 area does not map directly to Unicode, the way it does in ASCII-based encodings.<br><br>
  • ParkinT 2006-03-21 15:48
    <P>
    </P>
    <P>...quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations...
    </P>
    <P>&nbsp;</P>
    <P>That is a true <STRONG>Leveraged Synergy</STRONG> of <EM>Dilbertisms</EM></P>
  • Jeff S 2006-03-21 15:51
    Anonymous:
    Jeff S:
    Anonymous:
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).&nbsp; Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br><br>While today's post is a great WTF, if what you are saying about those DBAs is true, then they have taken the top "WTF" prize for the day.&nbsp; That might be one of the stupidest things I have ever heard.&nbsp; Did you really work with *many* of them? That's a scary thought.<br>
    <br><br>Wow, trying to keep application logic out of a database is the *stupidest* thing you've ever heard?&nbsp; If there is one property of "enterprise" applications, it is the fact they are often systems that deal with many data sources and services, often performing logic between the disparate sources.&nbsp; But lets list some reasons to keep the logic out of the database.<br><br>1) Stored procedures tie you to a particular vendor<br>2) Application logic in the database has to duplicate logic in the application<br>3) Horrible source code control (in every case I've witnessed)<br>4) Lack of OO design, hence the term "stored PROCEDURE"<br>5) Difficulty migrating to a different database vendor/version<br>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<br><br>Instead, put the logic in your objects, keep the database as minimal as possible, and certainly use a good persistence tool to ensure the updates and queries are correct.<br><br>I admit, you can instaed take a very data-centric view of things, and many people successfully work under the idea that the database *IS* the application, and everything else is just a GUI/front end.&nbsp; But this is not very scalable, not Object Oriented at all, and IMHO is only suitable for smaller systems, and certainly not large "enterprise" ;) systems.&nbsp; All these points can, of course, be debated...<br><br>But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!<br>
    <br><br>Sorry, I should have been more clear:<br><br>&gt;&gt; I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes). <br><br>THAT is the stupidest thing I've ever heard.&nbsp; When something stupid is suggested, even if it has good intentions, it is still pretty stupid.<br><br>Let's try an example for you:<br><br>person #1: "No one should be allowed to use sharpened pencils, because they can be used to kill someone, and killing is bad!"<br><br>person #2: "I disagree with that"<br><br>do you think that this means person #2 thinks killing people isn't bad?<br>
  • Smash 2006-03-21 15:52
    <P>If "GIMMIE" is the new "SELECT" then they will have to change it to:</P><SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> GIMMIE = <SPAN style="COLOR: #848284">"&nbsp;GIMMIE "</SPAN>;
    <P>and</P>
    <P><SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> GIMMIE_ACTIVE_PRODCUTS = <BR>&nbsp;&nbsp;&nbsp; SqlWords.GIMMIE + <BR>&nbsp;&nbsp;&nbsp; SqlWords.STAR + <BR>&nbsp;&nbsp;&nbsp; SqlWords.FROM + <BR>&nbsp;&nbsp;&nbsp; SqlTables.PRODUCTS +<BR>&nbsp;&nbsp;&nbsp; SqlWords.WHERE + <BR>&nbsp;&nbsp;&nbsp; SqlColumns.PRODUCTS_ISACTIVE + <BR>&nbsp;&nbsp;&nbsp; SqlWords.EQUALS + <BR>&nbsp;&nbsp;&nbsp; SqlMisc.NUMBERS_ONE;<BR></P>
    <P>For the poster that said they wanted GIMMIE to be the new select -- perhaps this notation is for you!&nbsp; You could re-name the entire SQL syntax!&nbsp; heh...</P>
    <P>-Smash</P>
  • [Si]dragon 2006-03-21 16:04
    Alex Papadimoulis:
    In yesterday's post (<a href="/forums/64597/ShowPost.aspx">Bitten by the Enterprise Bug</a>),
    we learned how vital enterprise application are for proactive
    organizations leveraging collective synergy to think outside the box
    and formulate their key objectives into a win-win game plan with a
    quality-driven approach that focuses on empowering key players to
    drive-up their core competencies and increase expectations with an
    all-around initiative to drive up the bottom-line. But of course,
    that's all a "high level" overview of things. Today, I'd like to dig
    into the code of enterprise systems a bit.
    <br>
    <br>
    Now <span style="font-style: italic;">that</span> was written by a professional.<br>
  • Scott 2006-03-21 16:06
    sebmol:
    Anonymous:
    sebmol:
    So we all agree here that this is a stupid way of doing things. But let me ask you then: how do you write a generic data access layer for a SQL database? Or do you even do that? I can understand that performing direct queries makes a lot of sense in a desktop application, but how does that scale on a multithreaded web or server application? And how do you automate schema changes? Any thoughts?<br>
    <br><br>With alot of code.<br>
    <br><br>So you end up writing basically the same code for every application with slight adjustment to the respective problem domain?<br><br>I'm seriously looking for answers because it just doesn't seem right to me. I've been going through ASP.NET/ADO.NET samples and they always go directly through the query without any abstraction and certainly without any caching.<br>
    <br><br>Try using Generics in 2.0.&nbsp; You can write a complete Persistence layer that is 100% resusable.<br>
  • Edje 2006-03-21 16:25
    I have actually seen this type of code because there was a CheckStyle rule that checked on string literals.<br>A manager would run the tool and tell the developers to 'fix the code'. Resulting in the mess you see in the original post.<br><br>Edwin<br>
  • Whiskey Tango Foxtrot? Over. 2006-03-21 16:27
    <P>
    sebmol:
    So we all agree here that this is a stupid way of doing things. But let me ask you then: how do you write a generic data access layer for a SQL database? Or do you even do that? I can understand that performing direct queries makes a lot of sense in a desktop application, but how does that scale on a multithreaded web or server application? And how do you automate schema changes? Any thoughts?<BR>
    </P>
    <P>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:</P><PRE>public interface SampleDataAccessAbstraction<BR>{<BR>&nbsp; Person [] getPersonList();<BR> Person getPersonDetails(int personNumber);<BR> Person [] searchForPerson(string request);<BR><BR>}</PRE>
    <P>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. </P>
  • Brian 2006-03-21 16:27
    Anonymous:

    <P>If "GIMMIE" is the new "SELECT" then they will have to change it to:</P><SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> GIMMIE = <SPAN style="COLOR: #848284">"&nbsp;GIMMIE "</SPAN>;
    <P>
    </P>
    <P>Uhhh, no.&nbsp; It would be:</P>
    <P><SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN>&nbsp;SELECT = <SPAN style="COLOR: #848284">"&nbsp;GIMMIE "</SPAN>; </P>
    <P>&nbsp;</P>
  • Magnus 2006-03-21 16:29
    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...)
  • Whiskey Tango Foxtrot? Over. 2006-03-21 16:29
    Anonymous:
    Anonymous:

    <P>If "GIMMIE" is the new "SELECT" then they will have to change it to:</P><SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> GIMMIE = <SPAN style="COLOR: #848284">"&nbsp;GIMMIE "</SPAN>;
    <P>
    </P>
    <P>Uhhh, no.&nbsp; It would be:</P>
    <P><SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN>&nbsp;SELECT = <SPAN style="COLOR: #848284">"&nbsp;GIMMIE "</SPAN>; </P>
    <P>&nbsp;</P>
    <P>
    </P>
    <P>No, you're not thinking enterprisey. It's:</P><PRE>public const string GIMMIE = " GIMMIE ";<BR>public const string SELECT = GIMMIE;</PRE>
  • Anonymous 2006-03-21 16:42
    Does anyone else notice that:<br><br><span id="_ctl0_PostForm_Reply"><pre style="color: rgb(0, 0, 0);"> <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> UPDATE_LOGON = <br> SqlWords.UPDATE +<br> SqlTables.CREDENTIALS +<br> SqlWords.SET +<br> SqlColumns.CREDENTIALS_LOGON_NAME +<br> SqlWords.EQUALS + <br> SqlMisc.PARAMS_FIRST + <br> SqlWords.COMMA +<br> SqlColumns.CREDENTIALS_LOGON_PASS +<br> SqlWords.EQUALS + <br> SqlMisc.PARAMS_SECOND;</pre></span>contains no SqlStupid.WHERE_CLAUSE?<br>
  • beefcake 2006-03-21 16:45
    Anonymous:
    Jeff S:
    Anonymous:
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).  Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br><br>While today's post is a great WTF, if what you are saying about those DBAs is true, then they have taken the top "WTF" prize for the day.  That might be one of the stupidest things I have ever heard.  Did you really work with *many* of them? That's a scary thought.<br>
    <br><br>Wow, trying to keep application logic out of a database is the *stupidest* thing you've ever heard?  If there is one property of "enterprise" applications, it is the fact they are often systems that deal with many data sources and services, often performing logic between the disparate sources.  But lets list some reasons to keep the logic out of the database.<br><br>1) Stored procedures tie you to a particular vendor<br>2) Application logic in the database has to duplicate logic in the application<br>3) Horrible source code control (in every case I've witnessed)<br>4) Lack of OO design, hence the term "stored PROCEDURE"<br>5) Difficulty migrating to a different database vendor/version<br>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<br><br>Instead, put the logic in your objects, keep the database as minimal as possible, and certainly use a good persistence tool to ensure the updates and queries are correct.<br><br>I admit, you can instaed take a very data-centric view of things, and many people successfully work under the idea that the database *IS* the application, and everything else is just a GUI/front end.  But this is not very scalable, not Object Oriented at all, and IMHO is only suitable for smaller systems, and certainly not large "enterprise" ;) systems.  All these points can, of course, be debated...<br><br>But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!<br>
    <br><br>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.<br>
  • Jackal von ÖRF 2006-03-21 16:47
    Whiskey Tango Foxtrot:
    <br>
    sebmol:
    So we all agree here that this is a stupid way of doing things. But let me ask you then: how do you write a generic data access layer for a SQL database? Or do you even do that? I can understand that performing direct queries makes a lot of sense in a desktop application, but how does that scale on a multithreaded web or server application? And how do you automate schema changes? Any thoughts?<br>
    <br><br>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:<br><br>public interface SampleDataAccessAbstraction<br>{<br>&nbsp; Person [] getPersonList();<br>&nbsp; Person getPersonDetails(int personNumber);<br>&nbsp; Person [] searchForPerson(string request);<br><br>}<br><br>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.<br><br>
    <br>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?<br>
  • bleah 2006-03-21 16:54
    Anonymous:
    Jeff S:
    Anonymous:
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).  Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br><br>While today's post is a great WTF, if what you are saying about those DBAs is true, then they have taken the top "WTF" prize for the day.  That might be one of the stupidest things I have ever heard.  Did you really work with *many* of them? That's a scary thought.<br>
    <br><br>Wow, trying to keep application logic out of a database is the *stupidest* thing you've ever heard?  If there is one property of "enterprise" applications, it is the fact they are often systems that deal with many data sources and services, often performing logic between the disparate sources.  But lets list some reasons to keep the logic out of the database.<br><br>1) Stored procedures tie you to a particular vendor<br>2) Application logic in the database has to duplicate logic in the application<br>3) Horrible source code control (in every case I've witnessed)<br>4) Lack of OO design, hence the term "stored PROCEDURE"<br>5) Difficulty migrating to a different database vendor/version<br>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<br><br>Instead, put the logic in your objects, keep the database as minimal as possible, and certainly use a good persistence tool to ensure the updates and queries are correct.<br><br>I admit, you can instaed take a very data-centric view of things, and many people successfully work under the idea that the database *IS* the application, and everything else is just a GUI/front end.  But this is not very scalable, not Object Oriented at all, and IMHO is only suitable for smaller systems, and certainly not large "enterprise" ;) systems.  All these points can, of course, be debated...<br><br>But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!<br>
    <br><br><span id="PostFlatView">1) Stored procedures tie you to a particular vendor<br>    No argument there.<br>2) Application logic in the database has to duplicate logic in the application<br>    Not if it's designed correctly.  You're assuming the code is bad.<br>3) Horrible source code control (in every case I've witnessed)<br>    No argument here.<br>4) Lack of OO design, hence the term "stored PROCEDURE"<br>    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.<br>5) Difficulty migrating to a different database vendor/version<br>    No argument here.  I just don't think it should be done.<br>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<br>    As opposed to putting more load on the app server?  I've never had trouble clustering database servers.  <br></span>
  • Dylan 2006-03-21 16:58
    <P>
    1) Stored procedures tie you to a particular vendor
    </P>
    <P>Who cares?</P>
    <P>
    2) Application logic in the database has to duplicate logic in the application
    </P>
    <P>Wrong, the logic would be in the db INSTEAD.</P>
    <P>
    3) Horrible source code control (in every case I've witnessed)
    </P>
    <P>True, though can be mitigated w/VSS.</P>
    <P>
    4) Lack of OO design, hence the term "stored PROCEDURE"
    </P>
    <P>Largely pointless...I don't think data design should be dependent on OO app issues.</P>
    <P>
    5) Difficulty migrating to a different database vendor/version
    </P>
    <P>Again, who cares?</P>
    <P>
    6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record
    </P>
    <P>Bingo!<BR></P>
    <P>
    Instead, put the logic in your objects, keep the database as minimal as possible, and certainly use a good persistence tool to ensure the updates and queries are correct.<BR><BR>I admit, you can instaed take a very data-centric view of things, and many people successfully work under the idea that the database *IS* the application, and everything else is just a GUI/front end.&nbsp; But this is not very scalable, not Object Oriented at all, and IMHO is only suitable for smaller systems, and certainly not large "enterprise" ;) systems.&nbsp; All these points can, of course, be debated...
    </P>
    <P>I agree with this for the most part, given #6.&nbsp; Stored procedures are important for several reasons beyond encapsulating business logic (which isn't the best idea).&nbsp; Compiled execution plans and managing permissions are the biggest.&nbsp; 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.</P>
    <P>
    But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!
    </P>
    <P>I'd say avoiding SPs totally is pretty dumb, which is apparently what the poster was saying.&nbsp; Sounds like the DBA was trying to cover for lack of expertise in that area.</P>
    <P>Dylan (a DBA)<BR></P>
  • Ass 2006-03-21 17:00
    That's because Hibernate can't do SQL, or complex queries at all.&nbsp; It has a limited query language of it's own - probably to ensure that it is vendor independant<br>
  • bill ..no fred 2006-03-21 17:07
    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.

  • Maurits 2006-03-21 17:10
    Anonymous:
    <br>
    3) Horrible source code control (in every case I've witnessed)
    <br>True, though can be mitigated w/VSS.<br>
    <br><br>VSS is horrible.&nbsp; 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.<br><br>(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...)<br>
  • Arancaytar 2006-03-21 17:11
    &nbsp;I actually uttered a choked scream upon reading that. The first WTF that causes me physical discomfort.<a href=""> :|</a>
  • bobafett 2006-03-21 17:14
    check out my avatar!
  • Willie 2006-03-21 17:23
    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());
    }

  • Pastor_Of_Muppets 2006-03-21 17:34
    This code makes baby Jesus cry&nbsp;<a href="">:'(</a> <br><br>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.&nbsp; How is it that people get away this this crap?<br> <a href=""></a>
  • pjsson 2006-03-21 17:37
    <P>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.</P>
    <P>See here for example using ADO.NET:</P><PRE>// Assumes that connection is a valid SqlConnection object.<BR>string queryString = "<STRONG>SLEECT</STRONG> * FROM <STRONG>Prodcuts</STRONG>";<BR>SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);<BR>DataSet customers = new DataSet();<BR>adapter.Fill(customers, "<STRONG>Prodcuts</STRONG>");</PRE>
    <P>Will compile but will result in runtime error, which is a big no no for an enterprise application. So instead we use enterprise SQL.</P><PRE>// Assumes that connection is a valid SqlConnection object.<BR>string queryString = sqlWords.<STRONG>SLEECT</STRONG> + SqlWords.STAR + SqlWords.FROM + SqlTables.<STRONG>PRODCUTS</STRONG>;<BR>SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);<BR>DataSet customers = new DataSet();<BR>adapter.Fill(customers, SqlTables.<STRONG>PRODCUTS</STRONG>);</PRE>
    <P>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.</P>
  • Maurits 2006-03-21 17:42
    Helpful for devs with no SQL knowledge or knowledge of the schema.&nbsp; They just type<br><br>string queryString = sqlWords.<br><br>and IntelliSense gives them a list of options.<br>
  • pjsson 2006-03-21 17:44
    pjsson:
    <pre>DataSet customers = new DataSet();<br>adapter.Fill(customers, "<strong>Prodcuts</strong>");</pre>
    <br><br>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.<br>
  • VGR 2006-03-21 18:29
    Anonymous:
    <br><br>Wow, trying to keep application logic
    out of a database is the *stupidest* thing you've ever heard?&nbsp; If
    there is one property of "enterprise" applications, it is the fact they
    are often systems that deal with many data sources and services, often
    performing logic between the disparate sources.&nbsp; But lets list
    some reasons to keep the logic out of the database.<br><br>1) Stored procedures tie you to a particular vendor<br>2) Application logic in the database has to duplicate logic in the application<br>3) Horrible source code control (in every case I've witnessed)<br>4) Lack of OO design, hence the term "stored PROCEDURE"<br>5) Difficulty migrating to a different database vendor/version<br>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<br><br>Instead,
    put the logic in your objects, keep the database as minimal as
    possible, and certainly use a good persistence tool to ensure the
    updates and queries are correct.<br><br>I admit, you can instaed take a
    very data-centric view of things, and many people successfully work
    under the idea that the database *IS* the application, and everything
    else is just a GUI/front end.&nbsp; But this is not very scalable, not
    Object Oriented at all, and IMHO is only suitable for smaller systems,
    and certainly not large "enterprise" ;) systems.&nbsp; All these points
    can, of course, be debated...<br><br>But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!<br>
    <br>
    <br>
    I have to agree.&nbsp; Logic embedded in the database can't possibly be
    OO.&nbsp; SQL is a decent query language, but a HORRIBLE programming
    language.<br>
    <br>
    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.&nbsp; And
    did I mention that due to legacy requirements, each column's name was
    limited to sixteen characters?<br>
    <br>
    This nightmare is the result of two factors:<br>
    <br>
    1.&nbsp; Inexperienced DBAs sometimes fancy themselves application coders.&nbsp; "A little knowledge," as they say.<br>
    2.&nbsp; Some DBA did it back in 1990, when hardware limitations might have justified it.<br>
  • jsmith 2006-03-21 18:29
    <P>
    Anonymous:
    Thats not really bad.<BR>In fact, if you do a test -- you will see that using select * is on average faster that specifying all the columns!<BR>-Ricky<BR>
    <BR>Yet another example of premature optimization.&nbsp; SELECT * may be faster, but it will never be <EM>a lot faster</EM> except on queries so trivially small that performance doesn't matter.&nbsp; On real DBMSs, after the first run it gets stored compiled like a stored procedure and the benefit goes away.</P>
    <P>Another issue, it is <EM>impossible</EM> for an Index to cover a query if you use SELECT *.&nbsp; There goes one of the more powerful tools that can easily cut&nbsp;80% of the execution time from some long running&nbsp;queries.&nbsp; It also has a lot of the problems of weakly typed languages.&nbsp; Example:&nbsp; 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.&nbsp; 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?&nbsp; And if your application has a mechanism or extensibility, then you can dynamically build the statement.&nbsp; For "b", sure the statement won't bomb if the column isn't available, but you still won't get the data.&nbsp; The call to retrieve the data will bomb.&nbsp; If you don't&nbsp;use the data then why are you selecting it at all?</P>
    <P>SELECT * does not belong in production code.&nbsp; "Because it's faster" isn't a good enough reason to outweigh the disadvantages.</P>
  • munificent 2006-03-21 18:35
    I wouldn't classify this as a WTF at all. It gives you compile-time syntax checking for SQL statements.<br>
    <br>
    Consider this:<br>
    <pre>object result = ExecuteCode("int a = 2; for( int i = 0; i &lt; 100, i++) b += 3;");</pre>
    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?<br>
    <br>
    Granted, the syntax was pretty ugly. With some clever class design, you could probably get it to look like:<br>
    <pre>SqlQuery selectActiveProducts = Select.AllFrom(SqlTables.PRODUCTS).Where(SqlColumns.PRODUCTS_ISACTIVE, Equals, "1");</pre>
    This would give you a fairly concise syntax without sacrificing SQLs power. And you'd still get compile time error checking.<br>
    <br>
    -- bob<br>
  • Arachnid 2006-03-21 18:40
    jsmith:
    <p>Another issue, it is <em>impossible</em> 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.</p>
    <br><br>What on earth are you talking about?<br><br>Try this:<br>CREATE TABLE test (<br>  name text,<br>  postcode integer<br>);<br><br>CREATE INDEX test_postcode_idx ON test(postcode);<br><br>Insert a million rows.<br><br>Now:<br>EXPLAIN SELECT * FROM test WHERE postcode = 98001;<br><br>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.<br>
  • triso 2006-03-21 18:44
    Pastor_Of_Muppets:
    but this is so far above and beyond
    anything from my darkest nightmares that it hurts.&nbsp; How is it that
    people get away this this crap?
    <font size="5">O</font>ften it
    is because this code is written by the senior project leader who is so
    great that his code does not need to be peer reviewed.<br>
    <br>
    <br>
  • ammoQ 2006-03-21 18:50
    Anonymous:
    <br><br>Wow, trying to keep application logic
    out of a database is the *stupidest* thing you've ever heard?&nbsp; If
    there is one property of "enterprise" applications, it is the fact they
    are often systems that deal with many data sources and services, often
    performing logic between the disparate sources.&nbsp; But lets list
    some reasons to keep the logic out of the database.<br><br>1) Stored procedures tie you to a particular vendor<br>2) Application logic in the database has to duplicate logic in the application<br>3) Horrible source code control (in every case I've witnessed)<br>4) Lack of OO design, hence the term "stored PROCEDURE"<br>5) Difficulty migrating to a different database vendor/version<br>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<br><br>Instead,
    put the logic in your objects, keep the database as minimal as
    possible, and certainly use a good persistence tool to ensure the
    updates and queries are correct.<br><br>
    <br>
    <br>
    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),<br>
    <br>
    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.<br>
    2) Wrong, since there is nearly no application logic outside the DB<br>
    3) Partialy true, though I found a way to mitigate that<br>
    4) True, but I couldn't care less - OO design is a tool, not a goal<br>
    5) True, see 1)<br>
    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.<br>
    <br>
    <br>
  • ammoQ 2006-03-21 19:01
    munificent:
    I wouldn't classify this as a WTF at all. It gives you compile-time syntax checking for SQL statements.<br>
    <br>
    Consider this:<br>
    <pre>object result = ExecuteCode("int a = 2; for( int i = 0; i &lt; 100, i++) b += 3;");</pre>
    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?<br>
    <br>
    Granted, the syntax was pretty ugly. With some clever class design, you could probably get it to look like:<br>
    <pre>SqlQuery selectActiveProducts = Select.AllFrom(SqlTables.PRODUCTS).Where(SqlColumns.PRODUCTS_ISACTIVE, Equals, "1");</pre>
    This would give you a fairly concise syntax without sacrificing SQLs power. And you'd still get compile time error checking.<br>
    <br>
    -- bob<br>
    <br>
    <br>
    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.<br>
  • ammoQ 2006-03-21 19:01
    munificent:
    I wouldn't classify this as a WTF at all. It gives you compile-time syntax checking for SQL statements.<br>
    <br>
    Consider this:<br>
    <pre>object result = ExecuteCode("int a = 2; for( int i = 0; i &lt; 100, i++) b += 3;");</pre>
    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?<br>
    <br>
    Granted, the syntax was pretty ugly. With some clever class design, you could probably get it to look like:<br>
    <pre>SqlQuery selectActiveProducts = Select.AllFrom(SqlTables.PRODUCTS).Where(SqlColumns.PRODUCTS_ISACTIVE, Equals, "1");</pre>
    This would give you a fairly concise syntax without sacrificing SQLs power. And you'd still get compile time error checking.<br>
    <br>
    -- bob<br>
    <br>
    <br>
    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.<br>
  • ammoQ 2006-03-21 19:01
    sorry for the double post...<br>
  • A Developer 2006-03-21 19:14
    Anonymous:
    Jeff S:
    Anonymous:
    Alex Papadimoulis:

    <P>If you're looking at today's code and thinking, <I>gee, shouldn't these be in stored procedures, or views, or something...</I></P>
    <P>
    <BR><BR>For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes).&nbsp; Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.<BR><BR>I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<BR>
    <BR><BR>While today's post is a great WTF, if what you are saying about those DBAs is true, then they have taken the top "WTF" prize for the day.&nbsp; That might be one of the stupidest things I have ever heard.&nbsp; Did you really work with *many* of them? That's a scary thought.<BR>
    <BR><BR>Wow, trying to keep application logic out of a database is the *stupidest* thing you've ever heard?&nbsp; If there is one property of "enterprise" applications, it is the fact they are often systems that deal with many data sources and services, often performing logic between the disparate sources.&nbsp; But lets list some reasons to keep the logic out of the database.<BR><BR>1) Stored procedures tie you to a particular vendor<BR>2) Application logic in the database has to duplicate logic in the application<BR>3) Horrible source code control (in every case I've witnessed)<BR>4) Lack of OO design, hence the term "stored PROCEDURE"<BR>5) Difficulty migrating to a different database vendor/version<BR>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<BR><BR>Instead, put the logic in your objects, keep the database as minimal as possible, and certainly use a good persistence tool to ensure the updates and queries are correct.<BR><BR>I admit, you can instaed take a very data-centric view of things, and many people successfully work under the idea that the database *IS* the application, and everything else is just a GUI/front end.&nbsp; But this is not very scalable, not Object Oriented at all, and IMHO is only suitable for smaller systems, and certainly not large "enterprise" ;) systems.&nbsp; All these points can, of course, be debated...<BR><BR>But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!<BR>
    </P>
    <P>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? </P>
    <P>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&nbsp;I'm a developer&nbsp;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.</P>
  • thornegreb 2006-03-21 19:21
    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.<br>
  • mlathe 2006-03-21 19:23
    munificent:
    I wouldn't classify this as a WTF at all. It gives you compile-time syntax checking for SQL statements.<BR><BR>Consider this:<BR><PRE>object result = ExecuteCode("int a = 2; for( int i = 0; i &lt; 100, i++) b += 3;");</PRE>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?<BR><BR>Granted, the syntax was pretty ugly. With some clever class design, you could probably get it to look like:<BR><PRE>SqlQuery selectActiveProducts = Select.AllFrom(SqlTables.PRODUCTS).Where(SqlColumns.PRODUCTS_ISACTIVE, Equals, "1");</PRE>
    <P>This would give you a fairly concise syntax without sacrificing SQLs power. And you'd still get compile time error checking.<BR><BR>-- bob<BR>
    </P>
    <P>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?</P>
    <P>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!</P>
  • jsmith 2006-03-21 19:24
    Anonymous:
    jsmith:

    <P>Another issue, it is <EM>impossible</EM> for an Index to cover a query if you use SELECT *.&nbsp; There goes one of the more powerful tools that can easily cut&nbsp;80% of the execution time from some long running&nbsp;queries.</P>
    <P>
    <BR><BR>What on earth are you talking about?<BR><BR>Try this:<BR>CREATE TABLE test (<BR>&nbsp; name text,<BR>&nbsp; postcode integer<BR>);<BR><BR>CREATE INDEX test_postcode_idx ON test(postcode);<BR><BR>Insert a million rows.<BR><BR>Now:<BR>EXPLAIN SELECT * FROM test WHERE postcode = 98001;<BR><BR>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.<BR>
    <BR>I didn't say it won't <EM>use</EM> the index, I said the index won't <EM>cover</EM> the query.&nbsp; It's a performance tuning term that means that <EM>all</EM> the columns being used in the query are contained in the index an the table doesn't even have to be read.&nbsp; Example:</P>
    <P>CREATE TABLE Employees<BR>(<BR>&nbsp; EmployeeID int,<BR>&nbsp; LastName varchar(30),<BR>&nbsp; FirstName varchar(30),<BR>&nbsp; DepartmentID int,<BR>&nbsp; HireDate datetime,<BR>&nbsp; SupervisorID int<BR>)</P>
    <P>Create a compound&nbsp;Index:</P>
    <P>CREATE INDEX in_biteme ON Employees(LastName, DepartmentID)</P>
    <P>Issue the statement:</P>
    <P>SELECT DepartmentID FROM Employees WHERE LastName LIKE 'M%'</P>
    <P>Because of the compound index, it will incur only a very small number of page reads (probably around&nbsp;100&nbsp;pages for a 1,000,000 row table).&nbsp; 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&nbsp;of the table -- about 3000 pages --&nbsp;and it's sometime more efficient to read all of the pages in order rather than&nbsp;half&nbsp;of them out of order).</P>
    <P>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):</P>
    <P><A href="http://www-128.ibm.com/developerworks/db2/library/techarticle/0303kuznetsov/0303kuznetsov.html">http://www-128.ibm.com/developerworks/db2/library/techarticle/0303kuznetsov/0303kuznetsov.html</A><BR><A href="http://www.informit.com/articles/article.asp?p=27015&amp;seqNum=6&amp;rl=1">http://www.informit.com/articles/article.asp?p=27015&amp;seqNum=6&amp;rl=1</A><BR><A href="http://db.apache.org/derby/docs/dev/tuning/ctunoptimz30768.html">http://db.apache.org/derby/docs/dev/tuning/ctunoptimz30768.html</A><BR><A href="http://blogs.conchango.com/jamiethomson/archive/2005/09/30/2223.aspx">http://blogs.conchango.com/jamiethomson/archive/2005/09/30/2223.aspx</A><BR><A href="http://www.mcse.ms/archive81-2004-10-1161421.html">http://www.mcse.ms/archive81-2004-10-1161421.html</A><BR><A href="http://peter-zaitsev.livejournal.com/6949.html">http://peter-zaitsev.livejournal.com/6949.html</A></P>
    <P>&nbsp;</P>
  • EvanED 2006-03-21 19:48
    Anonymous:
    Romeo:
    Alex Papadimoulis:

    <blockquote><pre style="color: rgb(0, 0, 0);"> <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> INNER = <span style="color: rgb(132, 130, 132);">" INNER "</span>;
    <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> JOIN = <span style="color: rgb(132, 130, 132);">" JOIN "</span>;
    <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> INNER_JOIN = <span style="color: rgb(132, 130, 132);">" INNER JOIN "</span>;
    </pre></blockquote>
    <br><br>Allright.&nbsp; Following this, we may have:<br><pre style="color: rgb(0, 0, 0);"><span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> SELECT_STAR_FROM = <span style="color: rgb(132, 130, 132);">" SELECT * FROM "</span>;<br><br>And so on...<br></pre>
    <p><br>&nbsp;<br>
    </p>
    <p>How about:</p>
    <p>public const string SELECT_STAR_FROM = SELECT + " " + STAR + " " + FROM;</p>
    <br><br>That'll never do. Look how many times you're repeating the literal " "! What happens if it changes? You need to do this:<br><br>public const string SPACE = " ";<br>public const string SELECT_STAR_FROM = SELECT + SPACE + STAR + FROM;<br>
  • Jeff S 2006-03-21 20:00
    ammoQ:
    munificent:
    I wouldn't classify this as a WTF at all. It gives you compile-time syntax checking for SQL statements.<br>
    <br>
    Consider this:<br>
    <pre>object result = ExecuteCode("int a = 2; for( int i = 0; i &lt; 100, i++) b += 3;");</pre>
    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?<br>
    <br>
    Granted, the syntax was pretty ugly. With some clever class design, you could probably get it to look like:<br>
    <pre>SqlQuery selectActiveProducts = Select.AllFrom(SqlTables.PRODUCTS).Where(SqlColumns.PRODUCTS_ISACTIVE, Equals, "1");</pre>
    This would give you a fairly concise syntax without sacrificing SQLs power. And you'd still get compile time error checking.<br>
    <br>
    -- bob<br>
    <br>
    <br>
    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.<br>
    <br>... or stored procedures.<br>
  • Jeff S 2006-03-21 20:05
    VGR:
    <br>
    I have to agree.&nbsp; Logic embedded in the database can't possibly be
    OO.&nbsp; SQL is a decent query language, but a HORRIBLE programming
    language.<br>
    <br><br>SQL is horrible programming language.&nbsp; But it is an excellent *set-based* data processing language.&nbsp;&nbsp; Literally hundreds of times more efficient than using one of them fancy OOP languages to process the rows one by one at the client.<br>
  • A chicken passeth by 2006-03-21 20:06
    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. >.>
  • John 2006-03-21 20:11
    limelight:
    Alex Papadimoulis:

    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something</i>, then you're still thinking in the world of unenterpriseness
    </p><blockquote>
    <p></p><pre style="color: rgb(0, 0, 0);"> <br> <span style="color: rgb(0, 130, 0);">/* SNIP */</span>

    <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">class</span> SqlQueries<br>{<br> <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> SELECT_ACTIVE_PRODCUTS = <br> SqlWords.SELECT + <br> SqlWords.STAR + <br> SqlWords.FROM + <br> SqlTables.PRODUCTS +<br> SqlWords.WHERE + <br> SqlColumns.PRODUCTS_ISACTIVE + <br> SqlWords.EQUALS + <br> SqlMisc.NUMBERS_ONE;<br><br> <span style="color: rgb(0, 130, 0);">/* SNIP */</span>

    <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> UPDATE_LOGON = <br> SqlWords.UPDATE +<br> SqlTables.CREDENTIALS +<br> SqlWords.SET +<br> SqlColumns.CREDENTIALS_LOGON_NAME +<br> SqlWords.EQUALS + <br> SqlMisc.PARAMS_FIRST + <br> SqlWords.COMMA +<br> SqlColumns.CREDENTIALS_LOGON_PASS +<br> SqlWords.EQUALS + <br> SqlMisc.PARAMS_SECOND;<br>}</pre></blockquote>
    <p>
    </p>
    <p>  Looking at the second SQL string, I can't
    quite figure out where you would need a constant update
    statement. Other than some kind of "lets reset something or another",
    where would such a statement be useful? I'm thinking that perhaps this
    is part of some kind of setup program and it is used to initially
    condition the database for usage. Since the update statement contains a
    reference to "PARAMS_FIRST" and "PARAMS_SECOND", it may be used to
    actually generate a stored procedure (e.g."create procedure <something or="" another=""> <some input="" params=""> as " + UPDATE_LOGON).</some></something></p>
    <br>
    <br>
    I am assuming this is ADO.NET, and there you can use something like<br>
    command.CommandText = "UPDATE table SET FirstName = ? WHERE ...";<br>
    command.CreateParameter();<br>
    command.Parameters(0).Value = "Bob";<br>
    command.ExecuteNonQuery();<br>
    command.Parameters(0).Value = "John";<br>
    command.ExecuteNonQuery();<br>
    <br>
    or set the parameter value from a loop.  You can also do things like<br>
    UPDATE table SET FirstName = @param1 WHERE ...<br>
    <br>
    command.Parameters("@param1").Value = "Bob";<br>
  • mctesty 2006-03-21 20:21
    I have to disagree.&nbsp; 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.&nbsp; Here's the approach we take:<br><br>You have a base DataAccessLayer class.&nbsp; It has virtual (or abstract or whathaveyou) methods like SQLSelect(), SQLUpdate, SQLInsert, SQLDelete, SQLAlter, etc.&nbsp; Each of these takes parameters specifying portions of the query.&nbsp; For example, SQLSelect might have 4 parameters, like: string selectColumns, string joinTables, string where, string orderby, string top.&nbsp; Then you create classes that inherit from this base class, so you might have a SQLServerDataAccessLayer and a MySQLDataAccessLayer.&nbsp; 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.&nbsp; I.e. the call might look like:&nbsp; SQLSelect("*", "Employees", "employeeID &gt; 0", "hireDate", "1");&nbsp; The SQLServerDataAccessLayer would create and run "SELECT TOP 1 * FROM Employees WHERE employeeID &gt; 0 ORDER BY hireDate" returning a dataset.&nbsp; The MySQLDataAccessLayer would create and run "SELECT * FROM Employees WHERE employeeID &gt; 0 ORDER BY hireDate LIMIT 1".&nbsp; This way we don't have to rewrite every sql statement when changing DB platforms.<br><br>I'm interested to know what the WTF community thinks of this approach.<br>
  • J 2006-03-21 20:34
    Anonymous:
    Thats not really bad.<br>In fact, if you do a test -- you will see that using select * is on average faster that specifying all the columns!<br>-Ricky<br>
    <br><br>What RBDMS do you use ?<br><br>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.<br>
  • LordHunter317 2006-03-21 20:59
    Anonymous:
    2) Application logic in the database has to duplicate logic in the application
    Not if the application is designed correctly.<br><br>
    4) Lack of OO design, hence the term "stored PROCEDURE"
    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.<br><br>
    5) Difficulty migrating to a different database vendor/version
    Not IME.<br><br>
    6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record
    The database is frequently eaiser to scale and cluster, because for many tasks,  it has to be capable of doing that anyway.<br><br>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.<br><br>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.<br><br>
    and certainly use a good persistence tool to ensure the updates and queries are[ correct.
    Which ensures nothing about business logic.<br><br>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.<br><br>
    But this is not very scalable,
    It's perfectly scalable, if designed correctly.<br><br>
    not Object Oriented at all,
    Not inherently a good thing.<br><br>
    scott:
    <span id="PostFlatView">Try using Generics in 2.0.  You can write a complete Persistence layer that is 100% resusable.
    No, you can't.   You can get close, but you can't do this.<br></span>
  • LordHunter317 2006-03-21 21:01
    VGR:
    I have to agree.&nbsp; Logic embedded in the database can't possibly be
    OO.
    Yes, it can be.<br><br>
    SQL is a decent query language,
    Not really.<br><br>
    but a HORRIBLE programming
    language.
    Which is why no one uses it for that.&nbsp; Every vendor extends it heavily.&nbsp; 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.<br>
  • Barney 2006-03-21 21:01
    <blockquote><pre>while(!stomach.empty()) {<br> for(hamburger = GIMMIE food FROM table WHERE meat = 'cow';<br> hamburger.stillTasty() || hamburger.notFinished(); hamburger.takeBite())<br>} <br></pre>
    </blockquote>
    Is liable to throw a <tt>BiteOutOfBurgerException</tt>. You need <tt>&amp;&amp;</tt> instead of <tt>||</tt>.</blockquote></blockquote>
  • The Internet 2006-03-21 21:02

    <p class="MsoNormal">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.</p>

  • LordHunter317 2006-03-21 21:03
    jsmith:
    Yet another example of premature optimization.
    No, it's never optimization.<br><br>
    Another issue, it is <em>impossible</em> for an Index to cover a query if you use SELECT *.
    This isn't the case.&nbsp; A WHERE clause still allows for Index coverage.&nbsp; Oh, you mean including the data records in the index?&nbsp; That's a hack.&nbsp; Yes, it can't be done.&nbsp; It normally shouldn't be anyway.<br>
  • martinl 2006-03-21 21:03
    Anonymous:
    <span id="_ctl0_PostForm_Reply">You can't write a perfect generic-for-everything layer any more than you can write a program that does everything with one button push and one line of code that both read "do what I mean".</span>
    <br>But you can! Take a look at the high-level functions of GNU-Script!<br>(http://www.art.net/~hopkins/Don/text/gnuscript.html)<br>
  • Ex-MT Worker 2006-03-21 21:10
    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.<br><br>#define SPACE ' '<br>#define nul ''<br>#define zero 0<br>#define TRUE 'TRUE'<br>#define FALSE 'FALSE'<br>#define IS_NEW 'NEW'<br>#define IS_MOD 'MOD'<br>#define IS_EXP 'EXP'<br>#define IS_ACT 'ACT'<br><br><br>
  • akrotkov 2006-03-21 22:48
    Is there any time when the SQL syntax might be localized, such as:<br><br>"ESCOJA * DE ..."<br><br>If so, this makes a lot more sense than it does without the context.<br>
  • jsmith 2006-03-21 23:01
    Anonymous:
    jsmith:
    Yet another example of premature optimization.
    No, it's never optimization.<BR><BR>
    Another issue, it is <EM>impossible</EM> for an Index to cover a query if you use SELECT *.
    This isn't the case.&nbsp; A WHERE clause still allows for Index coverage.&nbsp; Oh, you mean including the data records in the index?&nbsp; That's a hack.&nbsp; Yes, it can't be done.&nbsp; It normally shouldn't be anyway.<BR>
    <BR>A Hack?&nbsp; It's a very well respected tuning strategy recommended by all major database vendors.&nbsp; Please google "covering index" and study the 14,500 results that come back.&nbsp; Just because you don't know it doesn't make it a hack.
  • LordHunter317 2006-03-21 23:17
    jsmith:
    A Hack?
    Yes, I shouldn't have to include the data I'm interested in in the index, mearly what I'm indexing by.<br><br>
    It's a very well respected tuning strategy recommended by all major database vendors.
    Which doesn't change the fact it's a hack, like many performance optimizations are.<br><br>
    Please google "covering index" and study the 14,500 results that come back.
    Non-sequitur.<br><br>
    &nbsp; Just because you don't know it doesn't make it a hack.
    I'm well aware of the technique, it doesn't change what it is.&nbsp; In an ideal world, I wouldn't have to do that.<br><br>Frequently, I do.&nbsp; I never said it wasn't useful.&nbsp; But it's certainly not a first-case, nor universal optimization.&nbsp; It's dependent on schema, load, hardware, and a huge number of other factors.<br>
  • hjweth 2006-03-21 23:57
    Anonymous:
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).&nbsp; Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br><br>Well, once you start using stored procedures and foreign keys and triggers and other fancy bells and whistles like that, you run a real risk of actually enforcing data integrity, and nobody wants that as far as I can tell. <br><br>
  • jsmith 2006-03-22 00:07
    <P>In order for a performance optimization to be considered a hack, it would have to be implemented in a way that somehow detracts from the intended design.&nbsp; Building an optimized data storage engine for a project would usually be considered a hack unless there are really strange circumstances.</P>
    <P>However, a covering index has no hack elements to it.&nbsp; It performs its job without any adverse effects (there are zero non-performance side effects of implementing covering indexes).&nbsp; It is totally reversible in seconds even while the system is running.&nbsp; It is transparent to the application.&nbsp; It does not introduce any new maintenance issues.&nbsp; It is widely understood.&nbsp; It is how the database vendors intended the solution to be designed (the performance tuning courseware from both Oracle and&nbsp;Microsoft teach covering indexes, I don't know if IBM does, but it is on their website).</P>
    <P>Indexes are performance tools, you include what gives the best performance.&nbsp; There is no concept of "what you should include" other than what works best.&nbsp; A database that tends to be read more will (and should)&nbsp;have more indexes that include more columns.&nbsp; I could understand your point if there were even a single downside other than the incredibly obvious and well-known downside of indexing in general -- it slows down data manipulation.</P>
    <P>Do you consider the fact that you have to turn your steering wheel&nbsp;counterclockwise in order to turn your car left a hack?&nbsp; That is how it was designed, but I feel "I shouldn't have to do it".&nbsp; Therefore, it is a hack.&nbsp; Brillant.</P>
  • LordHunter317 2006-03-22 01:09
    jsmith:
    In order for a performance optimization to be considered a hack, it would have to be implemented in a way that somehow detracts from the intended design.
    And the intended purpose of an index is to index data rows.&nbsp; Not to store the data I'm actually interested in.&nbsp; The moment you include a column that's not in the WHERE clause, you've exceeded the intended purpose of an index (generally).<br><br>
    However, a covering index has no hack elements to it.
    Yes, it does.&nbsp; It, by definition, contains more information than is required to index the table.<br><br>
    It performs its job without any adverse effects (there are zero non-performance side effects of implementing covering indexes).
    First, you're wrong; second, that's irrelevant to deciding whether it's a hack or not.<br><br>
    It is totally reversible in seconds even while the system is running.
    Wrong, situation dependent.&nbsp; Index builds on new tables may take hours.<br><br>
    It does not introduce any new maintenance issues.
    Wrong, depending on the data, adding new indexes may extend maintaince time or cause other issues.&nbsp; PostgreSQL for example, requires periodic reindexing that takes time, especially on large indexes.&nbsp; Most other databases have other such pitfalls.&nbsp; <br><br>
    It is widely understood.
    And?&nbsp; Irrelevant to determining whether it's a hack or not.<br><br>
    It is how the database vendors intended the solution to be designed
    No, they didn't.&nbsp; If that were true, we'd use covering indexes all the time and we don't.&nbsp; It is a solution.<br><br>
    Indexes are performance tools, you include what gives the best performance.
    No, that is not there sole purpose in many RDBMS.&nbsp; They enforce data integrity in several.<br><br>
    I could understand your point if there were even a single downside other than the incredibly obvious and well-known downside of indexing in general -- it slows down data manipulation.
    There are, you seem to be quite igorant of the realities of DB maintance and planning.
    <p>
    Do you consider the fact that you have to turn your steering wheel&nbsp;counterclockwise in order to turn your car left a hack?&nbsp; That is how it was designed, but I feel "I shouldn't have to do it".&nbsp; Therefore, it is a hack.&nbsp; Brillant.
    Except this isn't a comparable scenario.&nbsp; Indexes were meant to index, not store data for retreival.&nbsp; They're mearly lists of pointers to the actual data, ordered in some useful fashion.&nbsp; They're addtionally used for relational integrity in certain situations, though not all DBMS use them for that, and it's not the only way to enforce uniqueness.<br></p>
  • Anonymous coward 2006-03-22 01:16
    Do you two want a ruler?<br>
  • dave 2006-03-22 02:01
    The old "should we have stored procedures debate" misses one important thing out.

    Sometimes it is the only way to get something done with out granting excessive rights to a user. For example, we had a situation where one user needed to be able to truncate the table in another user's schema. To do this the user needs to be able to DROP the table. But, Oracle doesn't allow you to drop another user's objects, so the only way to do this is to grant DROP ANY rights to the object needing to do the truncate.

    Or, you can write a stored procedure which the dropping user calls and only has the rights to run that. Safe, secure, no problem.
  • DiRadical 2006-03-22 02:49
    <P><EM>....</EM><EM>we learned how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven ... </EM></P>
    <P><STRONG>BINGO</STRONG></P>
    <P>That was quick</P>
  • Huibert Gill 2006-03-22 02:54
    Yes, but...<br>
    <br>
    shouldn't these "constants" be read from a config file?<br>
    Just in case some DB starts to use "GET" instead of "SELECT".<br>
    <br>
    just my 2ct.<br>
    <br>
    Happy Hacking!<br>
  • oview 2006-03-22 02:57
    Jeff S:
    <br>SQL is horrible programming language.&nbsp;
    But it is an excellent *set-based* data processing
    language.&nbsp;&nbsp; Literally hundreds of times more efficient than
    using one of them fancy OOP languages to process the rows one by one at
    the client.<br>
    <br>
    <br>
    Well, SQL-implementations (so to speak) are quite good. I'm not sure it's really great as language but that's what we have.<br>
    <br>
    Database (or other) vendors propably don't want to go through pains of standardizing any radically different language. <br>
  • Erwan 2006-03-22 03:07
    Anonymous:
    <p>IDIOTS!!!</p>
    <br><br>Shouldn't this be EnterpriseConstants.IDIOTS ?<br>
  • Arancaytar 2006-03-22 03:15
    Anonymous:
    Anonymous:
    <p>IDIOTS!!!</p>
    <br><br>Shouldn't this be EnterpriseConstants.IDIOTS ?<br>
    <br><br>Just in case "IDIOTS" ever gets redefined as "marketing visionaries"...<br>
  • Abdul 2006-03-22 03:41
    <P>never mind all else..that person can't spell the word "PRODUCTS"</P>
    <P>"<SPAN style="COLOR: #0000ff">public</SPAN> <SPAN style="COLOR: #0000ff">const</SPAN> <SPAN style="COLOR: #0000ff">string</SPAN> SELECT_ACTIVE_PRODCUTS "</P>
    <P>&nbsp;</P>
    <P>&nbsp;</P>
    <P>&nbsp;</P>
  • Drum D. 2006-03-22 04:16
    I'm still a student and surely have produced a lot of WTFs myself, but I can't believe that people who produce such unbelievable crap actually are allowed to stay in their job.<br>
  • Justin 2006-03-22 04:35
    Anonymous:
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).&nbsp; Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    <br><br>No decent DBA would do that, as it is an application developer's decision whether or not to embed.<br><br>My argument (accepted most places) is that there should be an API to the database, and the middle tier should be a client to the database.&nbsp; In fact, it should be impossible to connect as a user with generic 'select' privileges, much less insert or update.<br><br>That way, the database developer can ensure that all the selects which are required are (a) efficient, (b) auditable and (c) EFFICIENT.<br><br>Justin.<br>
  • darjien 2006-03-22 05:40
    Jeff S:
    Anonymous:
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).&nbsp; Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br><br>While today's post is a great WTF, if what you are saying about those DBAs is true, then they have taken the top "WTF" prize for the day.&nbsp; That might be one of the stupidest things I have ever heard.&nbsp; Did you really work with *many* of them? That's a scary thought.<br>
    <br><br>Well, here in the wholly-owned subsidiary of ENGLISH_DEFS.whatAClockMeasures + " " + ENGLISH_DEFS.personWhoIssuesACaution, we have a fun mix of DBAs, often in the same groups, who either demand or will never use STPs.. That one's funky. I've seen apps need to be competely rewritten when the backend support eprson changes because they will no longer allow direct sql, and I've seen the opposite.<br>
  • annoyingmous 2006-03-22 06:24
    Anonymous:
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).&nbsp; Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br>
    CAPTCHA: SOLDIER, I mean<br>
    CAPTCHA: salad<br>
    <br>
    <br>
    You mean DBAs that refuse to do any work?<br>
  • hmemcpy 2006-03-22 06:25
    Well... at least they declared those as 'const' and not 'static readonly'...
  • Mr Goaty 2006-03-22 06:25
    Quality suggestion, which begs the questions:  <br><ol><li>Why on Earth would you ever want to embed SQL statements within your source code?</li><li>Does it really have any place in there at all?</li><li>Surely it gets in the way of any surrounding logic?</li><li>Why is embedding the SQL into source code favourable to reading in from a config file or a resource bundle (or similar)?</li><li>Is it wrong of me to want to invite unholy retribution upon all those that embed SQL within their source code?  <a href="">:D</a></li></ol>Fantastic debate BTW, it's been good reading!<br>Kind Regards.<br>
  • Iago 2006-03-22 06:42
    Anonymous:
    <p><span style="color: rgb(132, 130, 132);"><font color="#000000">Perhaps that's n-2'th, but there's no way I'm going to hex of binary.</font></span></p>
    <br><br>Allow me...<br><br><span style="font-family: Courier New;">public class SqlBits<br>{<br></span><span style="font-family: Courier New;">&nbsp;&nbsp;&nbsp; public const int SqlBitZero = 0;<br>
    </span><span style="font-family: Courier New;">&nbsp;&nbsp;&nbsp; public const int SqlBitOne = 1;<br>&nbsp;&nbsp;&nbsp; public const int SqlBitFileNotFound = 2;<br>}<br><br>public class SqlBytes<br>{<br>&nbsp;&nbsp;&nbsp; public const </span><span style="font-family: Courier New;">byte</span><span style="font-family: Courier New;"> SqlByteZero = (SqlBits.SqlBitZero &lt;&lt; 8) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 7</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 6</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 5</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 4</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 3</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 2</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 1</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 0);<br></span><span style="font-family: Courier New;">&nbsp;&nbsp;&nbsp; public const </span><span style="font-family: Courier New;">byte</span><span style="font-family: Courier New;"> SqlByteOne = (SqlBits.SqlBitZero &lt;&lt; 8) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 7</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 6</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 5</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 4</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 3</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 2</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero &lt;&lt; 1</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitOne &lt;&lt; 0);<br>&nbsp;&nbsp;&nbsp; // ...snip...<br></span><span style="font-family: Courier New;">&nbsp;&nbsp;&nbsp; public const byte SqlByteTwoHundredAndFiftyFive = (SqlBits.SqlBitOne &lt;&lt; 8) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> &lt;&lt; 7</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> &lt;&lt; 6</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> &lt;&lt; 5</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> &lt;&lt; 4</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> &lt;&lt; 3</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> &lt;&lt; 2</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> &lt;&lt; 1</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitOne &lt;&lt; 0);<br>
    </span><span style="font-family: Courier New;"></span><span style="font-family: Courier New;">}<br><br>public class SqlChars<br>{<br></span><span style="font-family: Courier New;">&nbsp;&nbsp;&nbsp; public const char SqlCharAsciiSpace = (char) SqlBytes.SqlByteThirtyTwo;</span><span style="font-family: Courier New;"><br>&nbsp;&nbsp;&nbsp; public const char SqlCharAsciiA = (char) SqlBytes.SqlByteSixtyFive;<br></span><span style="font-family: Courier New;">&nbsp;&nbsp;&nbsp; public const char SqlCharAsciiB = (char) SqlBytes.SqlByteSixtySix;<br>&nbsp;&nbsp;&nbsp; // brain explodes<br></span><span style="font-family: Courier New;">}<br></span>
  • ammoQ 2006-03-22 06:43
    Anonymous:
    Quality suggestion, which begs the questions:&nbsp; <br><ol><li>Why on Earth would you ever want to embed SQL statements within your source code?</li><li>Does it really have any place in there at all?</li><li>Surely it gets in the way of any surrounding logic?</li><li>Why is embedding the SQL into source code favourable to reading in from a config file or a resource bundle (or similar)?</li><li>Is it wrong of me to want to invite unholy retribution upon all those that embed SQL within their source code?&nbsp; <a href="">:D</a></li></ol>Fantastic debate BTW, it's been good reading!<br>Kind Regards.<br>
    <br><br>1. To access the database ;-)<br>There are better alternatives like Hibernate, but sometimes they are an overkill. It's arguably a bad idea to bury SQL deep inside the business logic but it's IMO ok to do it in a source module dedicated to database access. Some people have a tendency to add more and more levels of&nbsp; indirection but this only complicates things and (in many cases) gains nothing.<br>4. a) It's easier to understand the code if you don't have to lookup the config files.<br>b) If a statement needs to access a lot of host variables (like an insert or update statement), putting it into a config file doesn't improve anything.<br>c) If you use a precompiler, it can check the SQL code during compile time.<br>5. Yes<br>
  • Iago 2006-03-22 06:44
    Er, spot the <span style="font-style: italic;">deliberate </span>mistake.&nbsp; Yeah, deliberate.&nbsp; Honest.<br>
  • Dan 2006-03-22 06:47
    How about creating a code generator to generate the data access layer for the database? Although you will spend a lot of time implementing such a generator, you may save a lot of time when the database is big or&nbsp;when its schema&nbsp;changes. And of course you can reuse such a tool for more than one application.
  • boicy 2006-03-22 06:53
    <span id="_ctl0_PostForm_Reply"><span id="PostFlatView">"Java in oracle,"<br><br>hahhahahahahahah...<br><br>you were joking right? oh...<br></span></span>
  • ammoQ 2006-03-22 07:11
    Anonymous:
    <span id="_ctl0_PostForm_Reply"><span id="PostFlatView">"Java in oracle,"<br><br>hahhahahahahahah...<br><br>you were joking right? oh...<br></span></span>
    <br>Java stored procedures (in Oracle) are usefull for ... well, those things you probably wouldn't do in the database anyway. Like file system and network operations. For business logic, PL/SQL is the better choice.<br>
  • jsmith 2006-03-22 07:14
    Anonymous:
    jsmith:
    In order for a performance optimization to be considered a hack, it would have to be implemented in a way that somehow detracts from the intended design.
    And the intended purpose of an index is to index data rows.&nbsp; Not to store the data I'm actually interested in.&nbsp; The moment you include a column that's not in the WHERE clause, you've exceeded the intended purpose of an index (generally).
    <BR>I like how you state the any use outside of "indexing rows" is a hack, but down below you mention indexes can be used to implement data integrity (which is more of a hack than covering indexes could ever be).&nbsp; Just because it's outside <EM>your</EM> definition of what indexes <EM>should</EM> do, doesn't make it so.<BR><BR>
    Anonymous:
    However, a covering index has no hack elements to it.
    Yes, it does.&nbsp; It, by definition, contains more information than is required to index the table.
    <BR>Covering indexes simply index better than non-covering indexes.&nbsp; By that logic clustered indexes and fillfactors are beyond the basic concept of indexing, so they are hacks too.&nbsp; Unless, of course, you apply recursive logic so that anything you approve of isn't a hack simply because you approve of it.<BR><BR>
    Anonymous:
    It performs its job without any adverse effects (there are zero non-performance side effects of implementing covering indexes).
    First, you're wrong; second, that's irrelevant to deciding whether it's a hack or not.
    <BR>So how would you define a hack?&nbsp; Something that annoys you?<BR><BR>
    Anonymous:
    It is totally reversible in seconds even while the system is running.
    Wrong, situation dependent.&nbsp; Index builds on new tables may take hours.
    <BR>Sure index builds can take hours.&nbsp; But, I said "reversible".&nbsp; Removal of non-clustered indexes happens in seconds on even the largest tables.&nbsp; Only non-clustered indexes can be covering indexes, so my statement is correct.<BR><BR>
    Anonymous:
    It does not introduce any new maintenance issues.
    Wrong, depending on the data, adding new indexes may extend maintaince time or cause other issues.&nbsp; PostgreSQL for example, requires periodic reindexing that takes time, especially on large indexes.&nbsp; Most other databases have other such pitfalls.&nbsp;
    <BR>I said "new" maintenance issues.&nbsp; All DBMSs require index maintenance, but DBAs already know that.&nbsp; Also, indexing strategies change often so any DBA worth his paycheck will have an automated process of some sort that finds and rebuilds all indexes.&nbsp; In MSSQL, it's a freakin' wizard.<BR><BR>
    Anonymous:
    It is widely understood.
    And?&nbsp; Irrelevant to determining whether it's a hack or not.
    <BR>Back to what is a hack.&nbsp; Now you have rejected "has weird side effects" and "obscure" from the definition of hack.&nbsp; What's left?<BR><BR>
    Anonymous:
    It is how the database vendors intended the solution to be designed
    No, they didn't.&nbsp; If that were true, we'd use covering indexes all the time and we don't.&nbsp; It is a solution.
    <BR>Like all indexes, they introduce overhead.&nbsp; So, you never <EM>always</EM> use any indexes.&nbsp; If you <EM>never</EM> use them, then you are leaving a lot of tuning on the table.&nbsp; Since we <EM>often</EM> use them, when appropriate,&nbsp;they are a ligitimate solution just like defaults, check constraints, stored procedures, and 500 other solutions.<BR><BR>
    Anonymous:
    Indexes are performance tools, you include what gives the best performance.
    No, that is not there sole purpose in many RDBMS.&nbsp; They enforce data integrity in several.
    <BR>Ahhhh... so indexes have a side purpose other than performance.&nbsp; So,&nbsp;we don't implement them for best performance.&nbsp; Instead, we implement them according to our philisophical opions about how Dr. E. F. Cobb would have used them in the 1970s.&nbsp; So, how do you feel about JOINs?<BR><BR>
    Anonymous:
    I could understand your point if there were even a single downside other than the incredibly obvious and well-known downside of indexing in general -- it slows down data manipulation.
    There are, you seem to be quite igorant of the realities of DB maintance and planning.
    <BR>Nope.&nbsp; It happens to be something I do rather well.<BR>
    <P>
    Anonymous:
    Do you consider the fact that you have to turn your steering wheel&nbsp;counterclockwise in order to turn your car left a hack?&nbsp; That is how it was designed, but I feel "I shouldn't have to do it".&nbsp; Therefore, it is a hack.&nbsp; Brillant.
    Except this isn't a comparable scenario.&nbsp; Indexes were meant to index, not store data for retreival.&nbsp; They're mearly lists of pointers to the actual data, ordered in some useful fashion.&nbsp; They're addtionally used for relational integrity in certain situations, though not all DBMS use them for that, and it's not the only way to enforce uniqueness.<BR></P>
    <BR>Covering indexes are being used for data retrieval.&nbsp; Just optimized one step beyond non-covering indexes.&nbsp; It's like the fundamantals of Object Oriented Programming:&nbsp;it doesn't matter how an ArrayList is implemented, what matters is what it's does.&nbsp; As long as you use it in appropriate situations, it will work for you.&nbsp; The writers of your chosen framework will evolve the internal implementation of ArrayList in future versions, and they won't even tell you they did it.&nbsp; Indexes are the same way -- every major database vendor has evolved indexes to the point where they will shortcut the table lookup step if possible.&nbsp; So, we take advantage of that by sometimes creating indexes that encourage shortcutting.&nbsp; Having some sort of chip on your shoulder about doing so is a bit weird.
  • Mr Goaty 2006-03-22 07:24
    ammoQ:
    <br><br>1. To access the database ;-)<br>There are better alternatives like Hibernate, but sometimes they are an overkill. It's arguably a bad idea to bury SQL deep inside the business logic but it's IMO ok to do it in a source module dedicated to database access. Some people have a tendency to add more and more levels of&nbsp; indirection but this only complicates things and (in many cases) gains nothing.<br>4. a) It's easier to understand the code if you don't have to lookup the config files.<br>b) If a statement needs to access a lot of host variables (like an insert or update statement), putting it into a config file doesn't improve anything.<br>c) If you use a precompiler, it can check the SQL code during compile time.<br>5. Yes<br>
    <br><br>All right, I'll give you that one about a source module dedicated to database access, but I've seen so many bad examples where inexperienced programmers litter their code with SQL statements, where it would be far more readable to lookup the sql from some place else, especially if (something common to all coding debates, the dreaded 'if') the statement is itself well-named.&nbsp; I guess this is the "levels of indirection" you talk about?&nbsp; Arguably, a source module dedicated to database access/statements is a form of indirection, but any full sql statement embedded along side logic is a maintenance nightmare, especially when SQL statements need to be re-used across an application.&nbsp; Couple this with the inherent "Copy &amp; Paste Inheritance" used by the general OO software-engineering public (ok, VB programmers ;P) and such a simple "Why not embed?" turns into a "I'm sure I've updated all the copies of that SQL... Where's the last one/ten?".&nbsp; <br><br>So, I'd ask you to pick from one of the follwing:<br><span style="font-style: italic;">Snippet one:</span><br><font size="2"><span style="font-family: Courier New; color: rgb(0, 100, 0);">// Lots of logic</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">String mrQuery = "SELECT wibble FROM wobble WHERE bounce = ?";</span><br style="font-family: Courier New;"><span style="font-family: Courier New; color: rgb(0, 100, 0);">// Do thngs with mrQuery</span></font><br><br><span style="font-style: italic;">Snippet two:</span><br><font style="font-family: Courier New;" size="2"><span style="color: rgb(0, 100, 0);">// At top of class, file or whereever constants are declared</span><br>public static final String mrQuery = "SELECT wibble FROM wobble WHERE bounce = ?";<br><span style="color: rgb(0, 100, 0);">// Later in file</span><br style="color: rgb(0, 100, 0);"><span style="color: rgb(0, 100, 0);">// Do things with mrQuery</span></font><br><br><span style="font-style: italic;">Snippet three:</span><br><font size="2"><span style="font-family: Courier New;">public interface MrQueryAndOtherAnimals</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">{</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">&nbsp;&nbsp;&nbsp; <span style="color: rgb(0, 100, 0);">// other queries</span></span><br style="font-family: Courier New;"><span style="font-family: Courier New;">&nbsp;&nbsp;&nbsp; String mrQuery = "SELECT wibble... you get the picture";</span><br style="font-family: Courier New;"><span style="font-family: Courier New; color: rgb(0, 100, 0);">&nbsp;&nbsp;&nbsp; // other queries</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">}</span></font><br><br><font style="font-family: Courier New; color: rgb(0, 100, 0);" size="2">// At some other place:<br>// Do something with MrQueryAndOtherAnimals.mrQuery;</font><br><br><span style="font-style: italic;">Snippet four:<br></span><font size="2"><span style="font-family: Courier New;"><span style="color: rgb(0, 100, 0);">// ok, I'm getting board, so assume the lookup set up is done elsewhere</span><br>String mrQuery = someLookupMechanism.find("mrQuery");</span><br style="font-family: Courier New;"><span style="font-family: Courier New; color: rgb(0, 100, 0);">// Do things with mrQuery that mrsQuery may find disturbing</span></font><br><br>Do you see my point?&nbsp; There's creeping indirection, but arguably snippet one leads to much more of a maintenance headace than say snippet 3, and snippet four is bordering upon the pathalogical.<br><br>The goal for me here is to formulate a strategy that:<br><ol><li>Isn't a complete pain in the rump to implement.</li><li>Isn't a complete pain in the left ventrical to maintain.</li><li>Encourages developers to think of the "right" solution, rather than just the "quick" solution (which may involve shooting your line manager if he's a complete smacktard, or finding a better job/line manager.&nbsp; I;ve worked for Line Manager who's philosophy was always "Do it the quick way".&nbsp; He's caused so many problems that he surely must be punishing us all for some indiscretion during a former life)<br></li></ol>We've got three large systems here, one in C++ and two in Java.&nbsp; The C++ project and one of the Java projects use embedded SQL and are both complete nightmares to maintain.&nbsp; The amount of effort it takes to make seemingly simple changes are now immense.&nbsp; The second Java project has its SQL embedded into a suite of configuration files which gives us:<br><ol><li>Ability to review SQL without reviewing the source code.</li><li>Abilty to review the source code without the SQL.</li><li>Review both together (you obviously need to!) :)<br></li><li>A step in the right direction for database vendor independence (theoretically) without the need to recompile.</li><li>Changes to the SQL are not spread across multiple files across multiple packages, they are neatly contained together, giving benefits for source control as the number of affected files for a given change can be small, again without having to recompile.</li></ol>This is not the first time this has happened.&nbsp; Unless you are bloomin careful and repeatedly beat your developers with the correctness-stick, emdedding your SQL in anything other than a mechanism that supports simple and striaghtforward re-usablility is the equivalent of sticking forks in your legs and wondering why you are bleeding.&nbsp; Anyhoo, I'll not convert any of you, Software Engineers by their very nature are stubborn beasts, but it's fun to soapbox :D<br><br>Kind regards<br>
  • Nick 2006-03-22 07:34
    Anonymous:
    A great design indeed, if you want to write your SQL requests in klingon language.<br>
    <br>
    Truly an Enterprise system! (despite the lack of XML)<br>
    <br><br>Wouldn't you then want the statements defined in a properties file?<br><br>Anyways, I can think of at least one reason for doing that.  Maybe they got tired of people making typos and not catching them in unit testing.  Now when they misspell 'SELECT' they will just generate a compile error...<br>
  • zamies 2006-03-22 07:36
    mpswaim:
    <p>
    Anonymous:
    Thats not really bad.<br>In fact, if you do a test -- you will see that using select * is on average faster that specifying all the columns!<br>
    </p>
    <p>But what if the number of columns changes? There's some stuff (.net
    typed datasets, among others) that get really upset when the number of
    columns changes. I've stored multiple values in a column because it was
    easier to decode values than to patch all the applications that assumed
    a certain number of columns. Eventually I went on a rampage and removed
    every "select *" from all the application code and stored procedures I
    could find. Life is better now.</p>
    <br>
    <br>
    OMG I can't believe my eyes...<br>
    mpswaim:
    <br>
    I've stored multiple values in a column because it was easier to decode
    values than to patch all the applications that assumed a certain number
    of columns.<br>
    <br>
    <br>
    Tell me where you work... I never ever ever want to work there...<br>
  • zamies 2006-03-22 07:44
    Anonymous:
    jsmith:
    A Hack?
    Yes, I
    shouldn't have to include the data I'm interested in in the index,
    mearly what I'm indexing by.<br><br>
    It's a very well respected
    tuning strategy recommended by all major database vendors.
    Which
    doesn't change the fact it's a hack, like many performance
    optimizations are.<br>
    <br>
    <br>
    If you have to do that you elsewhere made a BIG design error. It is a hack...<br>
  • geekgirl 2006-03-22 07:44
    First of all can I say how much I enjoy this forum and how much I am learning!

    In my organisation (I'm quite new), we LOVE stored procedures. Rather than running an update statement, we'll create a stored procedure that takes all the variables we need and create an update statement using them. None of these is ever reused or anything, we make a new one with table names hardcoded into the procedure each time.

    We also wrap up all sorts of processes that would be much nicer in a "real" programming language. For example today my task is to write a new stored procedure which writes data to a file, calls a shell command to combine that with some formatting information and write the result to another file, call another shell script to turn that result into a PDF and then send an email with the PDF file attached.

    What do you guys think about doing things in this way? It "smells" wrong to me, I don't really want to be operating on files from inside a stored procedure, but I'm not sure what I'm basing this on. The star programmer says its always the right way so that we can change programming languages in the future, but I'm not sure we're using the right tools for the right jobs. Any thoughts? I'm calling the procedure from a programming language that is quite capable of doing what is needed!
  • ammoQ 2006-03-22 07:49
    MrGoaty, why would I want to use the same SQL-Statement (as a string) in different places? Why not have exactly one method that does that query, and call that method whereever necessary?<br><br>Problem of the config file method:<br><br>Let's say you have a query that can be retrieved by calling <br><pre><span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;">String query = someLookupMechanism.find("mrQuery");</span></font></span></pre><pre><span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;"></span></font></span></pre><span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;"><br>How do you find all parts of the code that use mrQuery? Can be difficult if a wise guy writes code like that:<br><br></span></font></span><pre><span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;">String query = someLookupMechanism.find((person.isMale()?"mr":"ms")+"Query");</span></font></span></pre>
    <pre><span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;"></span></font></span></pre>
    <span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;"><br>
    </span></font></span>If you can't easily find the places where a query is used, changing a query becomes dangerous.<br>(Of course the same can happen with methods called by the means of reflection, but that seems rather unlikely to me)<br>
  • Mr Goaty 2006-03-22 07:53
    Anonymous:
    It "smells" wrong to me, I don't really want to be operating on files from inside a stored procedure, but I'm not sure what I'm basing this on. The star programmer says its always the right way so that we can change programming languages in the future, but I'm not sure we're using the right tools for the right jobs. Any thoughts? I'm calling the procedure from a programming language that is quite capable of doing what is needed!
    <br><br>I'd say your instincts are good :)&nbsp; However, unless you have the political and persuasive skills of a social genius, then you will be stuck whilst the "star programmer" maintains any political power.&nbsp; I've tried to convince a manager that reinventing J2EE wheel was a bad idea, that nearly cost me my job (I didn't realise he was on £100 an hour and thus needed an incredibly long task to perform).<br><br>The database is there to databasey things, like store information so it can be read back later.&nbsp; Can I think of another analogy?&nbsp; Yeah, why not.&nbsp; It's like cooking all your food on the hot engine block of your car, you can do it, sure it may even look cool, but the kitchen cooker is designed for that job and would be a far better tool to use.&nbsp; Safer too :)<br><br>Kind regards,<br>
  • ammoQ 2006-03-22 07:57
    geekgirl:
    First of all can I say how much I enjoy this forum and how much I am learning!

    In my organisation (I'm quite new), we LOVE stored procedures. Rather than running an update statement, we'll create a stored procedure that takes all the variables we need and create an update statement using them. None of these is ever reused or anything, we make a new one with table names hardcoded into the procedure each time.

    We also wrap up all sorts of processes that would be much nicer in a "real" programming language. For example today my task is to write a new stored procedure which writes data to a file, calls a shell command to combine that with some formatting information and write the result to another file, call another shell script to turn that result into a PDF and then send an email with the PDF file attached.

    What do you guys think about doing things in this way? It "smells" wrong to me, I don't really want to be operating on files from inside a stored procedure, but I'm not sure what I'm basing this on. The star programmer says its always the right way so that we can change programming languages in the future, but I'm not sure we're using the right tools for the right jobs. Any thoughts? I'm calling the procedure from a programming language that is quite capable of doing what is needed!
    <br><br>Unless you have most or all logic in stored procedures, I would not recommend writing files from a stored procedures. Oracle's DBMS_FILE has some limits and there are also security and performance issues. For example, the formatting script is called with the OS privileges of the database process, so it has the necessary rights to destroy the database.<br>
  • Mr Goaty 2006-03-22 08:00
    ammoQ:
    MrGoaty, why would I want to use the same SQL-Statement (as a string) in different places? Why not have exactly one method that does that query, and call that method whereever necessary?<br><br>Problem of the config file method:<br><br>Let's say you have a query that can be retrieved by calling <br><pre><span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;">String query = someLookupMechanism.find("mrQuery");</span></font></span></pre><pre><span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;"></span></font></span></pre><span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;"><br>How do you find all parts of the code that use mrQuery? Can be difficult if a wise guy writes code like that:<br><br></span></font></span><pre><span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;">String query = someLookupMechanism.find((person.isMale()?"mr":"ms")+"Query");</span></font></span></pre>
    <pre><span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;"></span></font></span></pre>
    <span id="_ctl0_PostForm_Reply"><font size="2"><span style="font-family: Courier New;"><br>
    </span></font></span>If you can't easily find the places where a query is used, changing a query becomes dangerous.<br>(Of course the same can happen with methods called by the means of reflection, but that seems rather unlikely to me)<br>
    <br><br>I'd say you are technically correct, which we all know is the best kind of correct :)&nbsp; TBH I'd already described snippet 4 as bordering upon the pathalogical and the method by which I'd chosen to advocate its use does open itself to this kind of retort.&nbsp; I should've been more robust.<br><br>Anyway, I believe we are beginning to argue a very similar philosophical point, which is good. I think we are both in agreement that randomly shoving SQL statements here, there and everywhere is tantamount to sociopathic behaviour, and that an organised approach is far superior. We merely differ in the means by which we'd choose to organise them.&nbsp; Thank you, this has been enlightening :)<br>
  • ammoQ 2006-03-22 08:10
    TBH, my view on the problem is strongly influenced by my history of C programming.<br><br>In C (using Oracle's preprocessor), it's eigher<br><br><pre>EXEC SQL select foo, bloo, doo into :foo:i1, :bloo:i2, :doo:i3 from bar where foofoo=:foofoo;</pre><br>or two pages of complex code to create the statement "object", assign the host variables, execute the statement, read the results etc.<br>It requires a certain amount of masochism <span style="font-style: italic;">not </span>to embedd SQL in that situation.<br>
  • devdas 2006-03-22 08:10
    Anonymous:
    <br>Wow, trying to keep application logic out
    of a database is the *stupidest* thing you've ever heard?&nbsp; If
    there is one property of "enterprise" applications, it is the fact they
    are often systems that deal with many data sources and services, often
    performing logic between the disparate sources.&nbsp; But lets list
    some reasons to keep the logic out of the database.<br><br>1) Stored procedures tie you to a particular vendor<br>2) Application logic in the database has to duplicate logic in the application<br>3) Horrible source code control (in every case I've witnessed)<br>4) Lack of OO design, hence the term "stored PROCEDURE"<br>5) Difficulty migrating to a different database vendor/version<br>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<br><br>Instead,
    put the logic in your objects, keep the database as minimal as
    possible, and certainly use a good persistence tool to ensure the
    updates and queries are correct.<br><br>I admit, you can instaed take a
    very data-centric view of things, and many people successfully work
    under the idea that the database *IS* the application, and everything
    else is just a GUI/front end.&nbsp; But this is not very scalable, not
    Object Oriented at all, and IMHO is only suitable for smaller systems,
    and certainly not large "enterprise" ;) systems.&nbsp; All these points
    can, of course, be debated...<br><br>But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!<br>
    <br>
    <br>
    I would say that treating the entire database as an object, and then
    having stored procedures being treated as accessor methods is probably
    the best thing you can do.<br>
    Application developers should look at the database as an amorphous blob of data, and use the given methods to access that data.<br>
    <br>
    Stored procedures tie you to a vendor. True. OTOH, not having stored procedures is worse.<br>
    <br>
    Lack of version control -- not too difficult to fix.<br>
    <br>
    Application logic duplicates data logic. I would say that this is a
    pretty small thing to worry about. It violates the DRY principle, and I
    would remove that logic from the application itself.<br>
  • ammoQ 2006-03-22 08:17
    devdas:
    <br>
    <br>
    I would say that treating the entire database as an object, and then
    having stored procedures being treated as accessor methods is probably
    the best thing you can do.<br><br>
    <br><br>This "object" is the reference implementation of the <a href="http://en.wikipedia.org/wiki/God_object">God Object antipattern</a>, right?<br> ;-)
  • firewireguy 2006-03-22 08:38
    Anonymous:
    <p>
    1) Stored procedures tie you to a particular vendor
    </p>
    <p>Who cares?</p>
    5) Difficulty migrating to a different database vendor/version

    <p>Again, who cares?</p>
    <br>
    <br>
    In the company I work for we mostly use MS-SQL Server for our
    application, then we had a customer come along, a far far bigger
    customer than all the existing ones combined.&nbsp; They wanted to use
    Oracle, no way they'd use SQL Server.&nbsp; We could do this very
    simply because we had almost no code that was SQL Server
    specific.&nbsp; We got a several million pound contract and completed
    the conversion very smoothly.&nbsp; Now we can run our application on
    any combination of AIX / Windows and Oracle / T-SQL.<br>
    <br>
    Don't be so short sighted.<br>
    <br>
    <br>
    I don't get the WTF in this article.&nbsp; It looks like it was code
    from a DB back end that no application programmer should have to touch
    - there are probably two or more layers of code on top of it.&nbsp; I
    would much rather use something like this using recordset objects than
    directly embedding SQL everywhere.&nbsp; Far easier to maintain.<br>
  • ammoQ 2006-03-22 08:45
    firewireguy:
    <br>
    <br>
    In the company I work for we mostly use MS-SQL Server for our
    application, then we had a customer come along, a far far bigger
    customer than all the existing ones combined.&nbsp; They wanted to use
    Oracle, no way they'd use SQL Server.&nbsp; We could do this very
    simply because we had almost no code that was SQL Server
    specific.&nbsp; We got a several million pound contract and completed
    the conversion very smoothly.&nbsp; Now we can run our application on
    any combination of AIX / Windows and Oracle / T-SQL.<br>
    <br>
    Don't be so short sighted.<br>
    <br><br>You are right, if you make some kind of standard software (for customers yet to come), DB independence is a good thing.<br>For in-house development or customized software, database switches are very unlikely.<br>
  • Matt B 2006-03-22 08:47
    Anonymous:
    <p class="MsoNormal">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.</p>
    <br><br>At first I couldn't tell which side of this discussion you were supporting. However, even in using "select *", your code is still likely to break even if you are only accessing the columns by column number (shudder), because now you're putting "project_id" in the variables for customer name...<br>
  • belugabob 2006-03-22 08:51
    <P>
    Iago:
    Er, spot the <SPAN style="FONT-STYLE: italic">deliberate </SPAN>mistake.&nbsp; Yeah, deliberate.&nbsp; Honest.<BR>
    </P>
    <P>If you're talking about the ninth bit, don't worry about it - that will come in handy for parity checks![:D]</P>
    <P>&nbsp;</P>
  • Mr Goaty 2006-03-22 08:55
    ammoQ:
    TBH, my view on the problem is strongly influenced by my history of C programming.<br><br>In C (using Oracle's preprocessor), it's eigher<br><br><pre>EXEC SQL select foo, bloo, doo into :foo:i1, :bloo:i2, :doo:i3 from bar where foofoo=:foofoo;</pre><br>or two pages of complex code to create the statement "object", assign the host variables, execute the statement, read the results etc.<br>It requires a certain amount of masochism <span style="font-style: italic;">not </span>to embedd SQL in that situation.<br>
    <br><br>That's a fair point.&nbsp; I suppose it's horses for courses isn't it?&nbsp; In my experience, the embedding in Java and C++ has caused an awful lot of avoidable pain, but it depends very much on how the SQL is used and with what library or mechanism.&nbsp; Fascinating! :)&nbsp; However, if anyone does it here, POW!!!! ;)<br><br>Kind regards.<br>
  • oview 2006-03-22 09:09
    Anonymous:
    How about creating a code generator to generate
    the data access layer for the database? Although you will spend a lot
    of time implementing such a generator, you may save a lot of time when
    the database is big or&nbsp;when its schema&nbsp;changes. And of course
    you can reuse such a tool for more than one application.
    <br>
    <br>
    So basicly you mean something like Middlegen?<br>
    <br>
    http://sourceforge.net/projects/middlegen<br>
  • LordHunter317 2006-03-22 09:43
    jsmith:
    I like how you state the any use outside of "indexing rows" is a hack, but down below you mention indexes can be used to implement data integrity (which is more of a hack than covering indexes could ever be).
    I didn't revise this statement.  They have two purposes.  I would go as so far to say that an index and an unique index are different beasts, simply because the latter are usually automatically generated and destroyed and have extreme constraints placed on them as to index method used and the like.<br><br>
    Covering indexes simply index better than non-covering indexes.
    No, they don't.  Once I have all the information needed to index the database, any other data is excessive.<br><br>
    By that logic clustered indexes and fillfactors are beyond the basic concept of indexing, so they are hacks too.
    Rather, they are implementation details.  However, you're correct in that they're beyond the basic concept.<br><br>
    Anonymous:
    So how would you define a hack?
    Any usage that abuses the abstract data structure.  For a relational database, that's probably reasonable enough.  And they're all over the place.  Your problem is that you're assuming hack == bad, but that's no tthe case.  <br><br>
    Anonymous:
    But, I said "reversible".
    And who cares?  The entire point of an RDBMS is that most operations are indeed reversible.<br><br>
    Anonymous:
    I said "new" maintenance issues.
    Compouding existing issues is an isuse in and of itself.<br><br>
    Anonymous:
    Like all indexes, they introduce overhead.  So, you never <em>always</em> use any indexes.  If you <em>never</em> use them, then you are leaving a lot of tuning on the table.  Since we <em>often</em> use them, when appropriate,
    Which is a far cry from saying their the vendor's intended solution.  They are one solution of many.  There are many other solutions, all legimiate, all recommended, to RDBMS performance problems.<br><br>
    Covering indexes are being used for data retrieval.  Just optimized one step beyond non-covering indexes.
    And that optimization changes how the index is being used.  It's no longer merely a pointer.<br><br>
    It's like the fundamantals of Object Oriented Programming: it doesn't matter how an ArrayList is implemented, what matters is what it's does.
    Ahh, but we frequently do care about both.  We care about all sorts of semantics about an ArrayList's implementation.<br><br>
    Having some sort of chip on your shoulder about doing so is a bit weird.
    I don't in the least, beyond the normal early optimization one.<br>
  • Whiskey Tango Foxtrot? Over. 2006-03-22 09:49
    <P>
    Jackal von ÖRF:
    Whiskey Tango Foxtrot:
    <BR>
    sebmol:
    So we all agree here that this is a stupid way of doing things. But let me ask you then: how do you write a generic data access layer for a SQL database? Or do you even do that? I can understand that performing direct queries makes a lot of sense in a desktop application, but how does that scale on a multithreaded web or server application? And how do you automate schema changes? Any thoughts?<BR>
    <BR><BR>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:<BR><BR>public interface SampleDataAccessAbstraction<BR>{<BR>&nbsp; Person [] getPersonList();<BR>&nbsp; Person getPersonDetails(int personNumber);<BR>&nbsp; Person [] searchForPerson(string request);<BR><BR>}<BR><BR>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.<BR><BR>
    <BR>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?<BR>
    </P>
    <P>They accomplish "genericity" (don't you just love coining your own terms? I do!) by doing exactly what I suggested: by providing an interface and&nbsp;knowing about and dealing with all of the issues with all of the databases that they support behind the scenes. As another poster said in response to your original question "with a lot of code". Even then, Hibernate still lets you get platform specific by allowing you to do direct queries on the database in a platform-specific way, essentially defeating the concept of a generic data access layer. </P>
    <P>Still, that's a moot point, because Hibernate is not a generic access layer, it is an object-relational mapping framework.&nbsp;&nbsp;</P>
  • MikeMontana 2006-03-22 09:52
    This qualifies as a textbook example of "WTF". And I agree with the
    trend of recent postings - that "enterprise" is the new rallying cry of
    clueless managment. I guess they finally retired "Object Oriented Land
    Ho!" <br>
    <br>
    And I'm so happy to see "Object Oriented" being retired. This week I'm
    asked to add some features to a java component that we're licensing.
    The features are important to our business - and these changes affect
    the UI, the network security, and some of the runtime behavior. So,
    when I started looking into the code base, its 500 classes. Many many
    situations are simply object-oriented-silliness: Class A derives from
    Class B which implements Interface C, D and E. Seems straightforward
    enough, but, there are no other instances of ClassB - not instantiated,
    not derived from... its just an mental-abstraction placeholder! Sure,
    maybe someone was planning to use 'class B' elsewhere. Maybe there are
    other code bases that use 'class B'....but, given this repeated
    pattern, i conclude that&nbsp; most likely, its just OO run amok.<br>
    <br>
    What are other signs of OO silliness are to be found? Entire class
    declarations that are essentially constants. The idea of over-riding
    static values in a class architecture is clever, and I can appreciate
    its usefulness. But, not as a design paradigm. Especially not for
    primitive data types like INTs and STRINGs. <br>
    <br>
    What kind of silliness can we expect from the "enterprise" cry ? So
    far, I've seen blind allegiance to "it must be XML" - to the point of
    utter stupidity. In the 90s, it made sense to marshall data from native
    data to stream an object from one process to another and do so in a
    standard format (call it COM, CORBA, RMI - whatever flavor you like),
    and there was the silliness of making anything and everything 'COM'
    compatible. There was inherent waste and overhead, and when applied
    apporpriately, those drawbacks were overcome by great gains in
    interoperability. Now, we take the same concept and add the bloat of
    text representation of data, and forced inclusion of structure. <br>
    Again, there will be gains in interoperability between disconnected
    processes. But, already, I'm seeing code where parameters are being
    passed from a process to another via an XML tag set! A simple string
    user-id being passed as an xml node selection. Rediculousness. But its
    got enterprise written all over it.<br>
    <br>
    Taking that a step further, it would seem that "enterprise" silliness
    will lead to entire XML schemas, XSLT transforms, and XML datasets
    simply to exchange runtime values between processes. <br>
  • Biff 2006-03-22 10:04
    Jeff S:
    VGR:
    <br>
    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.<br>
    <br><br>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.<br>
    <br><br>You can write a database and SQL interpreter in one of them fancy OOP langauges.  You can use data structures and even indexes into collections of objects.  Your claim is quite silly.  OO does not imply iterating linearly over a list of rows/objects/whatever.<br>
  • OMG 2006-03-22 10:11
    devdas:
    Anonymous:
    <br>Wow, trying to keep application logic out
    of a database is the *stupidest* thing you've ever heard?  If
    there is one property of "enterprise" applications, it is the fact they
    are often systems that deal with many data sources and services, often
    performing logic between the disparate sources.  But lets list
    some reasons to keep the logic out of the database.<br><br>1) Stored procedures tie you to a particular vendor<br>2) Application logic in the database has to duplicate logic in the application<br>3) Horrible source code control (in every case I've witnessed)<br>4) Lack of OO design, hence the term "stored PROCEDURE"<br>5) Difficulty migrating to a different database vendor/version<br>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<br><br>Instead,
    put the logic in your objects, keep the database as minimal as
    possible, and certainly use a good persistence tool to ensure the
    updates and queries are correct.<br><br>I admit, you can instaed take a
    very data-centric view of things, and many people successfully work
    under the idea that the database *IS* the application, and everything
    else is just a GUI/front end.  But this is not very scalable, not
    Object Oriented at all, and IMHO is only suitable for smaller systems,
    and certainly not large "enterprise" ;) systems.  All these points
    can, of course, be debated...<br><br>But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!<br>
    <br>
    <br>
    I would say that treating the entire database as an object, and then
    having stored procedures being treated as accessor methods is probably
    the best thing you can do.<br>
    Application developers should look at the database as an amorphous blob of data, and use the given methods to access that data.<br>
    <br>
    Stored procedures tie you to a vendor. True. OTOH, not having stored procedures is worse.<br>
    <br>
    Lack of version control -- not too difficult to fix.<br>
    <br>
    Application logic duplicates data logic. I would say that this is a
    pretty small thing to worry about. It violates the DRY principle, and I
    would remove that logic from the application itself.<br>
    <br><br>No logic in the application?  What, do I query the database everytime the user fills out a field to make sure they have done something consistant?  Do I just spout out errors from the database when their stored procedure fails?  How the hell are you supposed to do object oriented development with NO LOGIC in the OBJECTS, and instead put into COBOL like stored procedures?<br>
  • Jeff S 2006-03-22 10:24
    Anonymous:
    Jeff S:
    VGR:
    <br>
    I have to agree.&nbsp; Logic embedded in the database can't possibly be
    OO.&nbsp; SQL is a decent query language, but a HORRIBLE programming
    language.<br>
    <br><br>SQL is horrible programming language.&nbsp; But it is an excellent *set-based* data processing language.&nbsp;&nbsp; Literally hundreds of times more efficient than using one of them fancy OOP languages to process the rows one by one at the client.<br>
    <br><br>You can write a database and SQL interpreter in one of them fancy OOP langauges.&nbsp; You can use data structures and even indexes into collections of objects.&nbsp; Your claim is quite silly.&nbsp; OO does not imply iterating linearly over a list of rows/objects/whatever.<br>
    <br><br>Biff -- I thought we were discussing ways to use existing database products.&nbsp; If you want to go ahead and write your own database engine from scratch, by all means go for it.&nbsp; <br><br>
  • Alex Papadimoulis 2006-03-22 10:27
    <P>
    Anonymous:
    I have to disagree.&nbsp; 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.
    </P>
    <P>Have you ever heard of ODBC?&nbsp;Or, perhaps, ADO / ADO.NET?</P>
    <P>You are on a road to maintenance hell. If you are in the business of creating custom software, then unless you Invent Everything, you will depend on something else, be it a PDF-creator, Active Directory, File System, etc. </P>
    <P>What you *should* be doing is designing your system so external dependency are kept in one logical layer: for example, the data access layer. This way, if you ever need to support a different vendor, it's simply a matter of editing the classes in your DAL and changing this. The time involved in doing this is magnitudes smaller than the time trying to invent some "generic data access layer".</P>
    <P>
    Anonymous:
    ... the call might look like:&nbsp; SQLSelect("*", "Employees", "employeeID &gt; 0", "hireDate", "1");&nbsp; The SQLServerDataAccessLayer would create and run "SELECT TOP 1 * FROM Employees WHERE employeeID &gt; 0 ORDER BY hireDate" returning a dataset.&nbsp; The MySQLDataAccessLayer would create and run "SELECT * FROM Employees WHERE employeeID &gt; 0 ORDER BY hireDate LIMIT 1".&nbsp;
    </P>
    <P>What are you going to do when you need to optimize&nbsp;a query? You can't -- everyone does it differently. Orable uses comments, SQL Server uses keywords, DB2 uses [I have no idea]. So now your entire "generic layer" is useless.</P>
    <P>This is yet another reason you need to be using stored procedure. Every real database [*] has them, and they all function the same way when you use something like ODBC or your own invented "abstract data layer." That's the whole point of stored procedures -- simplifed access to a complex database system.</P>
    <P>[*] I do not acknowledge MySql as anything more than&nbsp;a toy. See <A href="http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/26/428527.aspx">http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/26/428527.aspx</A>.</P>
  • ammoQ 2006-03-22 10:29
    OMG:
    <br>No logic in the application? What, do I query the database everytime the user fills out a field to make sure they have done something consistant? Do I just spout out errors from the database when their stored procedure fails?<br>
    <br>This is quite possible, exactly like that. The performance is better than you think.<br>
    <br>How the hell are you supposed to do object oriented development with NO LOGIC in the OBJECTS, and instead put into COBOL like stored procedures?<br>
    <br>At least in Oracle, stored procedures are not COBOL-like, rather Pascal-like (or ADA-like, to be more precise).<br>This kind of development is not OO.<br>
  • jsmith 2006-03-22 10:32
    <P>
    Anonymous:
    jsmith:
    I like how you state the any use outside of "indexing rows" is a hack, but down below you mention indexes can be used to implement data integrity (which is more of a hack than covering indexes could ever be).
    I didn't revise this statement.&nbsp; They have two purposes.&nbsp; I would go as so far to say that an index and an unique index are different beasts, simply because the latter are usually automatically generated and destroyed and have extreme constraints placed on them as to index method used and the like.<BR><BR>
    Covering indexes simply index better than non-covering indexes.
    No, they don't.&nbsp; Once I have all the information needed to index the database, any other data is excessive.<BR><BR>
    By that logic clustered indexes and fillfactors are beyond the basic concept of indexing, so they are hacks too.
    Rather, they are implementation details.&nbsp; However, you're correct in that they're beyond the basic concept.<BR><BR>
    Anonymous:
    So how would you define a hack?
    Any usage that abuses the abstract data structure.&nbsp; For a relational database, that's probably reasonable enough.&nbsp; And they're all over the place.&nbsp; Your problem is that you're assuming hack == bad, but that's no tthe case.&nbsp; <BR><BR>
    Anonymous:
    But, I said "reversible".
    And who cares?&nbsp; The entire point of an RDBMS is that most operations are indeed reversible.<BR><BR>
    Anonymous:
    I said "new" maintenance issues.
    Compouding existing issues is an isuse in and of itself.<BR><BR>
    Anonymous:
    Like all indexes, they introduce overhead.&nbsp; So, you never <EM>always</EM> use any indexes.&nbsp; If you <EM>never</EM> use them, then you are leaving a lot of tuning on the table.&nbsp; Since we <EM>often</EM> use them, when appropriate,
    Which is a far cry from saying their the vendor's intended solution.&nbsp; They are one solution of many.&nbsp; There are many other solutions, all legimiate, all recommended, to RDBMS performance problems.<BR><BR>
    Covering indexes are being used for data retrieval.&nbsp; Just optimized one step beyond non-covering indexes.
    And that optimization changes how the index is being used.&nbsp; It's no longer merely a pointer.<BR><BR>
    It's like the fundamantals of Object Oriented Programming:&nbsp;it doesn't matter how an ArrayList is implemented, what matters is what it's does.
    Ahh, but we frequently do care about both.&nbsp; We care about all sorts of semantics about an ArrayList's implementation.<BR><BR>
    Having some sort of chip on your shoulder about doing so is a bit weird.
    I don't in the least, beyond the normal early optimization one.<BR>
    <BR><BR>So you're saying that if the vendor simply implemented a brand new type of object whose purpose was specifically to reduce index-to-table lookups, then it would all be fine because "that's what it's supposed to do"?&nbsp; The only reason this is a hack is that they are called "indexes"?&nbsp; That goes back to a "the world revolves around me" position.&nbsp; You don't like covering indexes because they aren't "right".</P>
    <P>Here is an interesting analogy:</P>
    <P>Indexes are called "indexes" because they work like the indexes in these things we call "books".&nbsp; Well, a typical book index contains a keyword and a page number.&nbsp; If you were looking for how many pages reference the word "widget" in a given book, you could entirely answer that question from the index.&nbsp; Hey, look, database servers do that too!!!!&nbsp; Covering indexes don't abuse the abstract concept, the concept of covering indexes has been in books for hundreds of years.</P>
  • Anita Tinkle 2006-03-22 10:41
    I did this already between MySQL 5.0 and SQL Server.  They both support stored procs.<br><br>The data layer exposes interfaces from System.Data, but it's a factory that switches to MySQL or SQL Server that actually does the work.  I use switch statements where needed for the subtle differences (booleans and names of stored procs params are different a bit), but the creation and population of stored procedure parameters is done by calling a factory to produce them, and returning instances that support the IDbParameter, for example.  I found it to be quite painless.<br><br>Here's code that demonstrates:<br><br><pre><font size="1">        public static void Connect()</font></pre><pre><font size="1">        {</font></pre><pre><font size="1">            if (_connector == DataConnector.MySql)</font></pre><pre><font size="1">            {</font></pre><pre><font size="1">                _cn = new MySqlConnection();</font></pre><pre><font size="1">                _cn.ConnectionString = "server=" + _databaseHost + ";database=" +</font></pre><pre><font size="1">                    _databaseName + ";uid=" + _databaseLogon + ";pwd=" + _databasePassword;</font></pre><pre><font size="1">                _cn.Open();</font></pre><pre><font size="1">            }</font></pre><pre><font size="1">            if (_connector == DataConnector.SqlServer)</font></pre><pre><font size="1">            {</font></pre><pre><font size="1">                _cn = new SqlConnection();</font></pre><pre><font size="1">                _cn.ConnectionString = "Server=" + _databaseHost + ";uid=" + _databaseLogon + ";pwd=" +</font></pre><pre><font size="1">                    _databasePassword + ";Initial Catalog=" + _databaseName;</font></pre><pre><font size="1">                _cn.Open();</font></pre><pre><font size="1">            }</font></pre><pre><font size="1">        }</font></pre>And some more..<br><br><font size="1">        public static DataSet RunProc(IDbCommand command)<br>        {<br>            if (_cn == null || (_cn.State != ConnectionState.Open))<br>            {<br>                Connect();<br>            }<br><br>            IDbDataAdapter adapter = GetAdapter(command);<br>            DataSet data = new DataSet();<br>            adapter.Fill(data);<br>            return data;<br>        }</font><br><br>
    sebmol:
    So we all agree here that this is a stupid way of doing things. But let me ask you then: how do you write a generic data access layer for a SQL database? Or do you even do that? I can understand that performing direct queries makes a lot of sense in a desktop application, but how does that scale on a multithreaded web or server application? And how do you automate schema changes? Any thoughts?<br>
  • devdas 2006-03-22 10:47
    ammoQ:
    devdas:
    <br>
    <br>
    I would say that treating the entire database as an object, and then
    having stored procedures being treated as accessor methods is probably
    the best thing you can do.<br><br>
    <br><br>This "object" is the reference implementation of the <a href="http://en.wikipedia.org/wiki/God_object">God Object antipattern</a>, right?<br> ;-)


    Nope.

    The view to the world is that the database is an amorphous blob with data. It knows what the data should look like, but not the semantics of the data itself (This field is a numeric type is important, the fact that it represents the money in your bank account is not import to the database itself). <br>

    The database don't know anything more than it should. It is a large object, with lots of getters and setters, but it definitely does not do anything outside its domain (store valid and consistent relations of data).

    The database does not implement business rules. It implements rules for data validation.

    From the wikipedia link:
    :
    Instead, much of a program's overall functionality is coded into a single object.


    The database object solves the data maintainance issue. It does not solve other parts of the application domain.

    Application developers should not have to worry about the implementation of the storage system, tables and columns (or even if the tables exist). They need to be able to obtain data from the store, and put data into it, without worrying about where the data goes.

    I would be happy to be enlightened why things should be otherwise.
  • Dylan 2006-03-22 10:53
    <P>
    firewireguy:
    <BR>Don't be so short sighted.<BR>
    <BR></P>
    <P>My points are 1) vendor tie-in is overrated, esp if you're spending $100,000 + on software and hardware, that ties you in already, and 2) converting data would be just as big a headache as SPs, if not more so, and migrating apps etc.&nbsp; No DBA wants to go through what you described, converting MSSQL to Oracle, regardless of SPs.</P>
    <P>Are you saying you use NO SPs, or just don't embed business logic in them, because they're different issues.</P>
    <P><BR>
    firewireguy:
    <BR>I don't get the WTF in this article.&nbsp; It looks like it was code from a DB back end that no application programmer should have to touch - there are probably two or more layers of code on top of it.&nbsp; I would much rather use something like this using recordset objects than directly embedding SQL everywhere.&nbsp; Far easier to maintain.<BR>
    </P>
    <P>I don't think a DB back end would be defining so much application-specific words, or SELECTs.&nbsp; Looks like an awful application design to me.</P>
    <P>My 0.02</P>
    <P>Dylan</P>
  • LordHunter317 2006-03-22 11:10
    jsmith:
    So you're saying that if the vendor simply implemented a brand new type of object whose purpose was specifically to reduce index-to-table lookups, then it would all be fine because "that's what it's supposed to do"?
    Certainly.<br><br>
    The only reason this is a hack is that they are called "indexes"?
    Precisely.  You're not using the data structure for what it was originally intended.  It's meant to be a pointer to a lookup, no more, no less.  The fact it's frequently useful from a performance standpoint to do so doesn't change the fact that such an action is a hack.  It's not what the datastructure was meant to do.<br><br>
    You don't like covering indexes because they aren't "right".
    I only don't like them when they're used inappropriately and have never said anything other than that.  You seemd to anyway, suggest them as quite a regular solution and not as a performance optimization.  That's the only thing they are, and it's critical to remember that.<br><br>
    If you were looking for how many pages reference the word "widget" in a given book, you could entirely answer that question from the index.
    Actually, you may not be able to, because book indexes don't work like database indexes.  I see your point however, the fact that a structure of pointers can be used for counting isn't a new concept, and is an acceptable use of such a strucutre.<br><br>
    Hey, look, database servers do that too!!!!  Covering indexes don't abuse the abstract concept, the concept of covering indexes has been in books for hundreds of years.
    That's a non-sequitur.  Counting isn't covering, by your own definition.<br>
  • Rob 2006-03-22 11:19
    It's a parameterized query. The database implicitly caches it in compiled form. After the first time it's used, running the query is just as efficient as calling a stored procedure. I'm a little curious why there's a PARAMS_FIRST and PARAMS_SECOND constant. On the databases that I've used, the place holder for a parameter is "?".<br>
  • mike 2006-03-22 11:25
    <P>"the enterprisocity of an application is directly proportionate to the number of constants defined "</P>
    <P>what theorem is this under? I don't recall this from college !</P>
  • Rob 2006-03-22 11:26
    SELECT * doesn't guarantee any field order. To use it safely, you need to access fields by name rather than by position. If you still need the same fields, than except for the extra memory usage, additional columns should be irrelevant. Of course, if you are accessing SELECT * fields by position, you could be setting yourself up for a disaster.<br>
  • Rob 2006-03-22 11:44
    >><span id="_ctl0_PostForm_Reply">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?<br><br>So it doesn't solve world hunger, therefore it is worthless? I haven't used this trick for SQL specifically, but there are many other cases where it has proven to be invaluable. It's handy everywhere from referencing a window to displaying the value of an enumeration.<br></span>
  • JASON 2006-03-22 12:07
    &gt;<span id="_ctl0_PostForm_Reply">In fact, if you do a test -- you will see that using select * is on average faster that specifying all the columns!<br><br>The DB engine might find the results faster on the server, but if you only need data from one field on a 30 field table, how would returning all the fields across a network be faster?<br></span>
  • JASON 2006-03-22 12:10
    Anonymous:
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).&nbsp; Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br>
    CAPTCHA: SOLDIER, I mean<br>
    CAPTCHA: salad<br>
    <br><br>I've worked with DBA's like that for a very short while.&nbsp; They never wanted to work with me again when I throw the BULLSH*T flag at them and tell them that they'll lazy SOB's.<br>
  • UDontWantToKnow 2006-03-22 12:39
    <span style="font-family: Courier New;">Well, really you should have:</span><br style="font-family: Courier New;"><br style="font-family: Courier New;"><span style="font-family: Courier New;">public class SqlBits</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">{</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">    public const int BIT_ZERO = 0;</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">    public const int BIT_ONE = 1;</span><br style="font-family: Courier New;"><br style="font-family: Courier New;"><span style="font-family: Courier New;">    public const int BIT_POSITION_ZERO = 0;</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">    public const int BIT_POSITION_ONE = 1;</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">    public const int BIT_POSITION_TWO = 2;</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">    // ...</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">}</span><br style="font-family: Courier New;"><br style="font-family: Courier New;"><span style="font-family: Courier New;">public class SqlLetters</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">{</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">    public const string SPACE_CHAR = BIT_ZERO << BIT_POSITION_SEVEN +</span><br style="font-family: Courier New;"><span style="font-family: Courier New;">                                     BIT_ZERO << BIT_POSITION_SIX +<br>                                     BIT_ONE  << BIT_POSITION_FIVE +<br>                                     BIT_ZERO << BIT_POSITION_FOUR +<br>                                     BIT_ZERO << BIT_POSITION_THREE +<br>                                     BIT_ZERO << BIT_POSITION_TWO +<br>                                     BIT_ZERO << BIT_POSITION_ONE +<br>                                     BIT_ZERO << BIT_POSITION_ZERO;<br><br>    public const string A_CHAR = //... you get the idea<br>}<br><br>(There needs to be a vomit emoticon like Yahoo IM has...)<br style="font-family: Courier New;"></span>
  • jsmith 2006-03-22 13:21
    Anonymous:
    jsmith:
    So you're saying that if the vendor simply implemented a brand new type of object whose purpose was specifically to reduce index-to-table lookups, then it would all be fine because "that's what it's supposed to do"?
    Certainly.
    <BR>So, until someone names a technique, and you accept that name as a non-hack, it's a hack.&nbsp; Funny thing is that there is a name for a covering index, it's called a "covering index".&nbsp; A well accepted, widely used name.&nbsp; The only reason you won't release it from hack status is because you don't accept that name.<BR><BR>
    Anonymous:
    The only reason this is a hack is that they are called "indexes"?
    Precisely.&nbsp; You're not using the data structure for what it was originally intended.&nbsp; It's meant to be a pointer to a lookup, no more, no less.&nbsp; The fact it's frequently useful from a performance standpoint to do so doesn't change the fact that such an action is a hack.&nbsp; It's not what the datastructure was meant to do.
    <BR>So, the evolution of any technology is always and forever, a hack?&nbsp; How about a mouse?&nbsp;&nbsp;Computers were not originally designed to be used with mice.&nbsp; Oh, wait, I forgot -- if you "accept" it, then it is absolved.&nbsp; BTW, indexes were created specifically to enhance performance.&nbsp; They really have no reason to exist other than to accelerate read operations.<BR><BR>
    Anonymous:
    You don't like covering indexes because they aren't "right".
    I only don't like them when they're used inappropriately and have never said anything other than that.&nbsp; You seemd to anyway, suggest them as quite a regular solution and not as a performance optimization.&nbsp; That's the only thing they are, and it's critical to remember that.
    <BR>Both.&nbsp; They are used quite regularly as performance optimizations.&nbsp; From the beginning that's what this was about.&nbsp; Besides, you can't use the fact that a technology <EM>could</EM> be misapplied to categorize that technology as a hack.&nbsp; Maybe if it were likely to cause a level of confusion that often results in misuse.&nbsp; I figured that we were all professionals and it didn't need to be stated that <EM>any</EM> decision to index data would always be accompanied by an analysis of the costs and benefits and if it wasn't a net gian, it wouldn't be done.<BR>Also, If I suggested them as "quite a regular solution and not as a performance optimization", then what did I suggest them for?&nbsp; My argument has been, from the beginning, that covering indexes are an excellent performance tool.<BR><BR>
    Anonymous:
    If you were looking for how many pages reference the word "widget" in a given book, you could entirely answer that question from the index.
    Actually, you may not be able to, because book indexes don't work like database indexes.&nbsp; I see your point however, the fact that a structure of pointers can be used for counting isn't a new concept, and is an acceptable use of such a strucutre.
    <BR>:-)<BR><BR>
    Anonymous:
    Hey, look, database servers do that too!!!!&nbsp; Covering indexes don't abuse the abstract concept, the concept of covering indexes has been in books for hundreds of years.
    That's a non-sequitur.&nbsp; Counting isn't covering, by your own definition.<BR>
    <BR>Come on....&nbsp; It's trivial to modify the scenario to "finding out if a&nbsp;page is in the first or second half of a book".&nbsp; I wanted to keep the book example a bit more believable, so I used counting instead of&nbsp;some other read operation.&nbsp; Any type of lookup using the index key or bookmark information as the data is an analogy to a covering index.
  • jimolina 2006-03-22 13:25
    <P>I think the more 'enterprise' a system gets, the more WTF it gets.&nbsp; I took several ABAP training courses&nbsp;(the 'programming' language for SAP) and my jaw was perpetually dropped to the desk.&nbsp; Here I thought this 'enterprise' system was going to be so advanced, so perfectly architected (if that's a word) and developed by the best of the best.&nbsp; I thought I'd be learning top techniques for a top system.&nbsp; Oh.&nbsp; My. God.&nbsp; </P>
    <P>It would take a 1000 page tome just to touch the surface of the abject ugliness behind the glossy corporate appearance of that system.&nbsp; For instance no SQL--it's all procedural loops.&nbsp; To do a Join on the Customers table from the Orders table, you instead nest a loop of the entire customers table inside the loop of the orders table (not the real table names--those are 4-digit cryptic German names, but that's another story).</P>
    <P>Anyway, bottom line is that there is no santa claus.&nbsp; There is no "enterprise" system that's magically 'better'--better coded, better designed, etc, etc. than what you have..&nbsp; If you have a system that does what it's supposed to for the users--then going 'enterprise' is probably going to be a few large steps backwards.</P>
    <P>--Jim</P>
  • Enterprise Man 2006-03-22 13:37
    Iago:
    <span style="font-family: Courier New;">public class SqlBytes<br>{<br>    public const </span><span style="font-family: Courier New;">byte</span><span style="font-family: Courier New;"> SqlByteZero = (SqlBits.SqlBitZero << 8) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 7</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 6</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 5</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 4</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 3</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 2</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 1</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 0);<br></span><span style="font-family: Courier New;">    public const </span><span style="font-family: Courier New;">byte</span><span style="font-family: Courier New;"> SqlByteOne = (SqlBits.SqlBitZero << 8) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 7</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 6</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 5</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 4</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 3</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 2</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitZero << 1</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitOne << 0);<br>    // ...snip...<br></span><span style="font-family: Courier New;">    public const byte SqlByteTwoHundredAndFiftyFive = (SqlBits.SqlBitOne << 8) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> << 7</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> << 6</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> << 5</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> << 4</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> << 3</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> << 2</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.</span><span style="font-family: Courier New;">SqlBitOne</span><span style="font-family: Courier New;"> << 1</span><span style="font-family: Courier New;">) | (</span><span style="font-family: Courier New;">SqlBits.SqlBitOne << 0);<br>
    </span><span style="font-family: Courier New;"></span><span style="font-family: Courier New;">}</span><span style="font-family: Courier New;"><br></span>
    <br><br>And with 9 bits per byte, the computer will run 12.5% faster too!<br>
  • Sam 2006-03-22 13:46
    <P>
    Anonymous:
    Thats not really bad.<BR>In fact, if you do a test -- you will see that using select * is on average faster that specifying all the columns!<BR>-Ricky<BR>
    </P>
    <P>&nbsp;</P>
    <P>Speed of response is hardly the only thing you should be looking at.&nbsp; Select * IS pretty bad.</P>
  • . 2006-03-22 13:49
    <span id="_ctl0_PostForm_Reply"><pre style="color: rgb(0, 0, 0);"><span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> SELECT = <span style="color: rgb(132, 130, 132);">" SELECT "</span>;<br><br>I see the real WTF.<br><br> " SELECT " should be an externalized string.<br></pre></span>
  • mainframe hell... 2006-03-22 15:03
    is there another way to select from a table besides "SELECT *"?<br><br>a major telecom company generally uses either the global "SELECT *" or its HUGE ADABAS tables, or the far-more-efficient "SELECT", followed by a complete list of all the columns in the table.  and remember, these are tables with in excess of 400 columns.<br><br>we love job security. <br>
  • Just Another WTF 2006-03-22 16:52
    <P>
    Anonymous:
    Iago:
    <SPAN style="FONT-FAMILY: Courier New">public class SqlBytes<BR>{<BR>&nbsp;&nbsp;&nbsp; public const </SPAN><SPAN style="FONT-FAMILY: Courier New">byte</SPAN><SPAN style="FONT-FAMILY: Courier New"> SqlByteZero = (SqlBits.SqlBitZero &lt;&lt; 8) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 7</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 6</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 5</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 4</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 3</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 2</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 1</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 0);<BR></SPAN><SPAN style="FONT-FAMILY: Courier New">&nbsp;&nbsp;&nbsp; public const </SPAN><SPAN style="FONT-FAMILY: Courier New">byte</SPAN><SPAN style="FONT-FAMILY: Courier New"> SqlByteOne = (SqlBits.SqlBitZero &lt;&lt; 8) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 7</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 6</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 5</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 4</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 3</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 2</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitZero &lt;&lt; 1</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitOne &lt;&lt; 0);<BR>&nbsp;&nbsp;&nbsp; // ...snip...<BR></SPAN><SPAN style="FONT-FAMILY: Courier New">&nbsp;&nbsp;&nbsp; public const byte SqlByteTwoHundredAndFiftyFive = (SqlBits.SqlBitOne &lt;&lt; 8) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBitOne</SPAN><SPAN style="FONT-FAMILY: Courier New"> &lt;&lt; 7</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBitOne</SPAN><SPAN style="FONT-FAMILY: Courier New"> &lt;&lt; 6</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBitOne</SPAN><SPAN style="FONT-FAMILY: Courier New"> &lt;&lt; 5</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBitOne</SPAN><SPAN style="FONT-FAMILY: Courier New"> &lt;&lt; 4</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBitOne</SPAN><SPAN style="FONT-FAMILY: Courier New"> &lt;&lt; 3</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBitOne</SPAN><SPAN style="FONT-FAMILY: Courier New"> &lt;&lt; 2</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBitOne</SPAN><SPAN style="FONT-FAMILY: Courier New"> &lt;&lt; 1</SPAN><SPAN style="FONT-FAMILY: Courier New">) | (</SPAN><SPAN style="FONT-FAMILY: Courier New">SqlBits.SqlBitOne &lt;&lt; 0);<BR></SPAN><SPAN style="FONT-FAMILY: Courier New"></SPAN><SPAN style="FONT-FAMILY: Courier New">}</SPAN><SPAN style="FONT-FAMILY: Courier New"><BR></SPAN>
    <BR><BR>And with 9 bits per byte, the computer will run 12.5% faster too!<BR>
    </P>
    <P>My bytes go to 11!</P>
  • Phred 2006-03-22 22:24
    HEY!

    db vendor independence? wtf?

    If you aren't locked into a db vendor
    by blowing hundreds of thousands of dollars,
    YOU AREN'T ENTERPRISE!

    Thank you for your time in this matter.
  • Hinek 2006-03-23 03:53
    Ok, I agree, that this is more than just overdone.

    But to answer your last sentence: Have you heard, that MySQL uses "LIMIT" instead of "TOP" ...
  • AsIfYouCare 2006-03-23 09:11
    <span id="_ctl0_PostForm_Reply"><span id="_ctl0_PostForm_Reply">Amen!  I'm a network guy, and the bane of my life is shitty little DB apps that work well on a LAN being implemented on a WAN.  If the server that does the "SELECT *" is on the same LAN as the DB server then it doesn't affect me (its still crap though), but on a network it is poison.<br><br><br></span></span>
  • NotImportant 2006-03-23 10:03
    I think if I found this code it would inspire a new form of SQL injection attack.&nbsp; This SQL causing the injection of my foot up the author's a$$!
  • Jeff 2006-03-23 10:24
    The problem here is that the SQL words are all hard-coded, they should have been stored in a config file.<br>
  • mlk 2006-03-23 11:17
    Anonymous:
    A great design indeed, if you want to write your SQL requests in klingon language.<br>
    <br>
    Truly an Enterprise system! (despite the lack of XML)<br>
    Using this system, you could simply change the keywords, and bang you have XPath! Rar!&nbsp;&nbsp; <br>
  • fsquare 2006-03-23 13:33
    <P><EM>Vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line.</EM></P>
    <P>You forgot best-of-breed!</P>
    <P>*smack*</P>
  • javaxman 2006-03-23 14:18
    hjweth:
    Anonymous:
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).  Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br><br>Well, once you start using stored procedures and foreign keys and triggers and other fancy bells and whistles like that, you run a real risk of actually enforcing data integrity, and nobody wants that as far as I can tell. <br><br>


    Thank you very very much. To DBAs who don't like to put 'business logic' in the database : WTF is a schema design all about ? Do you really design a database with no thought at all as to how it's going to actually be used?

    Oh, and two words about triggers : referential integrity. It can be important, and unless you think your app programmers are as *perfect* as your DBAs ( note: it's called sarcasm, folks ) , you might want to guard against corrupting your data as much as possible. Of course, maybe you're using some half-assed database which doesn't include referential integrity support via triggers, or you just don't know how to do such things. I don't know. People build all sorts of crazy systems that end up being mission-critical.
  • pah 2006-03-23 14:28
    <i>No, you're not thinking enterprisey. It's:</i><p><PRE>public const string GIMMIE = " GIMMIE ";<BR>public const string SELECT = GIMMIE;</PRE>
    <p>
    Enterprisey my ass. Try
    <p><pre>
    public const Object SELECT = new Object() {
    public String toString() {
    if (Enterprise.version>1.3 && Enterprise.version<1.5)
    throw new EnterpriseException("SELECT is deprecated");
    else return GIMMIE;
    }
    };
    </pre>
  • pah 2006-03-23 14:31
    How I love this forum software. Sigh. I give up.
  • lofwyr 2006-03-23 17:25
    Anonymous:
    <br>1) Stored procedures tie you to a particular vendor<br>2) Application logic in the database has to duplicate logic in the application<br>3) Horrible source code control (in every case I've witnessed)<br>4) Lack of OO design, hence the term "stored PROCEDURE"<br>5) Difficulty migrating to a different database vendor/version<br>6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record<br>
    <br>
    <br>
    1) Could very well be.<br>
    2) Now why would someone do this? If it's in the database, why duplicate it on another layer?<br>
    3) Don't see the logic here ... either you use source control or you don't.<br>
    4) Like OO is the end to all means ...<br>
    5) Like those database are so easy to replace. Different locking &amp; transaction modells, anyone?<br>
    6) That is quite the boldest statement I've ever heard. <br>
    <br>
    Anonymous:
    <br>
    Instead, put the logic in your objects, keep the database as minimal as
    possible, and certainly use a good persistence tool to ensure the
    updates and queries are correct.<br>
    <br>
    <br>
    And use a lot more ressources (with less performance) than without. <br>
    <br>
    Anonymous:
    <br>
    I admit, you can instaed take a very data-centric view of things, and
    many people successfully work under the idea that the database *IS* the
    application, and everything else is just a GUI/front end.&nbsp; But
    this is not very scalable, not Object Oriented at all, and IMHO is only
    suitable for smaller systems, and certainly not large "enterprise" ;)
    systems.&nbsp; All these points can, of course, be debated...<br>
    <br>
    <br>
    Not _can_, the are debateable. Database agnostic approach can have it's
    advantages, but all that you're doing is shifting the complexity of a
    given problem to another layer - and probably adding some (complexity)
    to it.<br>
    <br>l.
  • Steve 2006-03-24 01:08
    I just have to say how inspiring I found this whole thing. I was moved to enhance it:<br>(pardon the syntax, I'm not a .net guy, consider it psuedocode in a language where you have an eval)<br><br><br><br>public const string SELECT = " SELECT ";<br>public const string STAR = " * ";<br>public const string FROM = " FROM ";<br><br>public const string SELECT_VAR_NAME = "SELECT";<br>public const string STAR_2VAR_NAME= "STAR";<br>public const string FROM_VAR_NAME= " FROM ";<br><br>public const string SPACE = " ";<br>String SELECT_STAR_FROM=eval("SELECT_VAR_NAME + STAR_VAR_NAME + FROM_VAR_NAME" );<br><br>...<br>public const string EVAL = "eval";<br>...<br>aaaaaaaaaaaaaaaaaaaah!<br><br><br>
  • Henrik Gram 2006-03-24 09:15
    This is in many ways similar to code I see in my new job, except it's worse, much worse. The SQL string would actually look like this: (in Java)

    public final String UPDATE_LOGON = new StringBuffer().append(SqlWords.UPDATE).append(SqlTables.CREDENTIALS).append(SqlWords.SET).append(SqlColumns.CREDENTIALS_LOGON_NAME). (etc....) .toString()

    The reasoning behind this was that earlier versions of the java compiler was pretty darn stupid with regards to string concatenations, which is true, but still.. you gotta factor in the readability of such code when you decide to do such optimizations - thankfully today, the java compiler is much smarter and actually produces code that is faster than the above optimization, so I guess now we can optimize away the previous optimization.
  • hq 2006-03-24 10:04
    <P>???,????c???</P>
    <P>?????????????</P>
  • Auxon 2006-03-29 00:54
    <P>You can't really write a 100% generic data access layer unless it's a very trivial, read-only application (ie. list the tables in my database, pick a table, show the table columns and values etc...) and/or you use dynamic&nbsp;SQL all over the place essentially making your application a DAL generator at runtime.&nbsp; Essentially you'd end up writing a IDE for creating SQL queries against your database,&nbsp;and unless you're writing a database tool, it doesn't make sense.&nbsp; </P>
    <P>Some reporting applications may use this kind of feature however, but usually you'd try to abstract your report designer to stick the domain you are working on, providing simpler expressions for the user to string together and evaluate the expressions at runtime.</P>
    <P>For most scenarios, at some point you&nbsp;have to say what table/view/stored proc you are querying, and at some point you&nbsp;have to specify columns.&nbsp;&nbsp;&nbsp;Yes, it's very tedious, especially when the database schema changes.&nbsp; </P>
    <P>However, you can use code generation against databases using generic templates.&nbsp; There are a ton of code generating programs that will build the DAL and BLL for you, then you can add custom&nbsp;code on top of that where the code generation can't do the job.&nbsp; CodeSmith is a good tool for this.</P>
    <P>&nbsp;</P>
  • anon 2006-03-29 09:34
    Anonymous:
    Alex Papadimoulis:

    <P>If you're looking at today's code and thinking, <I>gee, shouldn't these be in stored procedures, or views, or something...</I></P>
    <P>
    <BR><BR>For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes).&nbsp; Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.<BR><BR>I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<BR>
    </P>
    <P>..."you're starting to embed application logic in the database" as opposed to embedding SQL queries in the application code?</P>
    <P>WTF?</P>
    <P>Why not start doing what my company has started doing.&nbsp; We have always been required to use&nbsp;stored procedures but&nbsp;now&nbsp;we make&nbsp;constants for the stored procedure names and parameter names in the application.&nbsp; Actually we are currently "enterprising" our development process so all of these&nbsp;<STRONG>constants</STRONG> are externalized in a configuration file so that changes (like stored procedure name&nbsp;or parameter changes)&nbsp;can be made without recompiling the application.</P>
    <P>All we have to do then is go into the code and modify the classes to&nbsp;handle any new data fields, make sure the data is validated by the application, make sure any new parameters added to the configuration file are used properly.</P>
    <P>THEN we recompile the application.</P>
    <P>Sincerely,</P>
    <P>- SQL Server DBA (being seduced by the Dark Side of Oracle)</P>
  • Dear God NO! 2006-03-29 13:56
    Anonymous:
    <p>..."you're starting to embed application logic in the database" as opposed to embedding SQL queries in the application code?</p>
    <p>WTF?</p>
    <p>Why not start doing what my company has started doing.&nbsp; We have always been required to use&nbsp;stored procedures but&nbsp;now&nbsp;we make&nbsp;constants for the stored procedure names and parameter names in the application.&nbsp; Actually we are currently "enterprising" our development process so all of these&nbsp;<strong>constants</strong> are externalized in a configuration file so that changes (like stored procedure name&nbsp;or parameter changes)&nbsp;can be made without recompiling the application.</p>
    <p>All we have to do then is go into the code and modify the classes to&nbsp;handle any new data fields, make sure the data is validated by the application, make sure any new parameters added to the configuration file are used properly.</p>
    <p>THEN we recompile the application.</p>
    <p>Sincerely,</p>
    <p>- SQL Server DBA (being seduced by the Dark Side of Oracle)</p>
    <br><br>What the hell? If someone puts a single DB Query in the app code, they should be shot. This includes a SP name. <br><br>Ever hear of an ORM? Look it up...seriously...<br><br>SPs have their place, but they should not contain business logic. <br>
  • Dear God NO! 2006-03-29 14:02
    Alex Papadimoulis:
    <p>Have you ever heard of ODBC? Or, perhaps, ADO / ADO.NET?</p>
    <p>You are on a road to maintenance hell. If you are in the business of creating custom software, then unless you Invent Everything, you will depend on something else, be it a PDF-creator, Active Directory, File System, etc. </p>
    <p>What you *should* be doing is designing your system so external dependency are kept in one logical layer: for example, the data access layer. This way, if you ever need to support a different vendor, it's simply a matter of editing the classes in your DAL and changing this. The time involved in doing this is magnitudes smaller than the time trying to invent some "generic data access layer".</p>
    <p> 
    </p>Which is what you would get if you used something like Hibernate or ActiveRecord for Ruby (nHibernate for .NET). I know you love your SPs, but there are better, cleaner approaches.<br><br><p>
    </p><p>What are you going to do when you need to optimize a query? You can't -- everyone does it differently. Orable uses comments, SQL Server uses keywords, DB2 uses [I have no idea]. So now your entire "generic layer" is useless.</p>
    <p>This is yet another reason you need to be using stored procedure. Every real database [*] has them, and they all function the same way when you use something like ODBC or your own invented "abstract data layer." That's the whole point of stored procedures -- simplifed access to a complex database system.</p>
    <p>[*] I do not acknowledge MySql as anything more than a toy. See <a href="http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/26/428527.aspx">http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/26/428527.aspx</a>.</p>
    <br><br>The point is, should you put any logic in the SPs or should it be outside the database? I am going to agree with the rest of the non-MS world and say "outside the db". The DB should be a receptacle, nothing more. No business logic should reside there. If you need one or two specific SPs for somehting silly, fine...I don't really care that much. But if you are going to use them to actually make business rules and things like that...you are just smoking crack. <br><br>And, speaking of this...the whole SP debate is one of those questions I ask when I am hiring someone for my consulting company. If they say "just create a SP", ..no thanks...<br><br>Not saying people who use them are stupid, but I have found they just don't understand complex systems enough to know why they are bad. Not enough real experience to know that SPs are not the way to get the job done. <br><br><br>
  • Barnacle Wes 2006-03-29 14:38
    Anonymous:
    <br>For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).&nbsp; Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br><br>Yes, I have.&nbsp; I find the best tool for dealing with these sorts of DBAs to be a 2 foot piece of iron rebar with a nice duct tape handle for the API.&nbsp; Evil Bill Paul had another fine tool, a hardened steel spline about 4 inches in diameter and 10 inches long. 30 pounds of hardened steel, with those lovely serrated splines on the business end.&nbsp; It was known throughout the office as "the damn thing."<br><br>Coding "application logic" out in the application that could be done with a trigger is stupid, that's why triggers were invented.&nbsp; Ditto for the performance benefits of stored procedures, etc.&nbsp; Use the tools in the ways that make the application perform, not based on some idiot voodoo rules.<br>
  • Dear God NO! 2006-03-29 15:26
    Barnacle Wes:
    <br><br>Coding "application logic" out in the application that could be done with a trigger is stupid, that's why triggers were invented.  Ditto for the performance benefits of stored procedures, etc.  Use the tools in the ways that make the application perform, not based on some idiot voodoo rules.<br>
    <br><br>Triggers, stored procedures and the like are great when you are doing data related tasks (moving data from one db to another (i.e replication)) and all those common, day to day  DBA tasks. If you put application logic into a stored procedure or trigger , you should just give up your programming rights. Seriously. <br><br><br><br><br>
  • Maurits 2006-03-29 20:13
    Anonymous:
    If you put application logic into a stored procedure or trigger , you should just give up your programming rights. Seriously.
    <br><br>Go sit in the corner.<br>
  • karma police 2006-04-01 19:20
    Anonymous:
    <p>Let's take this to n'th degree</p>
    <p><span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">class</span> SqlWords<br>{<br>  <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> SPACE _CHAR= <span style="color: rgb(132, 130, 132);">" "</span>;</p>
    <p>//...<br>  <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> E_CHAR = <span style="color: rgb(132, 130, 132);">"E"</span>;</p>
    <p>//...<br>  <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> L_CHAR = <span style="color: rgb(132, 130, 132);">"L"</span>;</p>
    <p>//....<br>  <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> S_CHAR = <span style="color: rgb(132, 130, 132);">"S"</span>;<br>}</p>
    <p><span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">class</span> SqlWords<br>{<br>  <span style="color: rgb(0, 0, 255);">public</span> <span style="color: rgb(0, 0, 255);">const</span> <span style="color: rgb(0, 0, 255);">string</span> SELECT = <span style="color: rgb(132, 130, 132);">SPACE_CHAR + S_CHAR + E_CHAR + //...</span></p>
    <p><span style="color: rgb(132, 130, 132);"><font color="#000000">}</font></span></p>
    <p><span style="color: rgb(132, 130, 132);"><font color="#000000">Perhaps that's n-2'th, but there's no way I'm going to hex of binary.</font></span></p>
    <br>...<br>i'm still laughing...<br>
  • GreatWhiteDork 2006-04-04 13:48
    <P>Sadly enough, I used the corporate-eese at the beginning of this post for the beginning of a proposal for management.&nbsp; Literally:</P>
    <BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
    <P class=MsoNormal style="MARGIN: 0in 0in 0pt"><?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on"><st1:City w:st="on">Enterprise</st1:City></st1:place> applications are vital for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line.</P>
    <P class=MsoNormal style="MARGIN: 0in 0in 0pt"><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p>&nbsp;</o:p></P>
    <P><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">With this paradigm in mind, <COMPANY Name>proposes to deliver a product ...</SPAN></P></BLOCKQUOTE>
    <P dir=ltr><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">The boss word-smithed a few bullet points below that, but never blinked.&nbsp; Sad.</SPAN></P>
  • lokers 2006-04-11 13:49
    My company uses an application where the company has put ALL of their procedure code into fields in the db.&nbsp; So they call the procedure which in turn calls the database to see what it should do.&nbsp; Is this normal?!?!?!?!<br>
  • hq 2006-04-15 23:53
    <P>???,????????????,????????</P>
    <P>???????????????,??????????????,?????????????QQ:183808137</P>
  • mjc 2006-04-16 01:19
    Actually, you don't need the separating spaces since the original has spaces surrounding each token. Of course, this means that concatenating them results in two spaces between tokens.<br><br>
  • Chris 2006-04-19 02:20
    I know!<br><br>I mean, seriously - it's obviously:<br><br>public const string CHAR_E = "E";<br>public const string CHAR_L = "L";<br>public const string CHAR_S = "S";<br><br><br>!!
  • typoer 2006-04-26 03:56
    There is one good thing that happens when you use constants the way they are used in this WTF and that is that Java compilers do not check the syntactic correctness of your SQL when you compile. This way you at least can avoid typos.<br><br>"UDPATE * FORM FOO" will compile whereas<br><br>SqlWords.UDPATE + SqlWords.STAR + SqlWords.FORM + "FOO" will not (unless someone defines UDPATE and FORM)<br>
  • ammoQ 2006-04-26 08:15
    Anonymous:
    There is one good thing that happens when you use constants the way they are used in this WTF and that is that Java compilers do not check the syntactic correctness of your SQL when you compile. This way you at least can avoid typos.<br><br>"UDPATE * FORM FOO" will compile whereas<br><br>SqlWords.UDPATE + SqlWords.STAR + SqlWords.FORM + "FOO" will not (unless someone defines UDPATE and FORM)<br>
    <br><br>Use embedded SQL and a preprocessor (like the one Oracle offers) and it will not only check the syntax, but table names, column names etc. as well; and the code might be more readable, but this is rather a matter of taste (at least in Java; in C+SQL, it's no question).<br>
  • Emil Brink 2006-05-04 04:24
    Amusing. :) I guess one actual reason is to protect against spelling errors in embedded strings, since the compiler will catch an attempt to use the wrong constant. I do know it'd drive me insane though, and not just only because one of the constants in the snippet seems to be ... missspelled. Heh. I'm thinking of the first line in the second class, i.e. SqlQueries.SELECT_ACTIVE_PRODCUTS. I'm pretty sure that ought to end in "PRODUCTS" ... The irony! :D<br>
  • Peter M 2006-05-04 06:56
    Anonymous:
    Alex Papadimoulis:
    <p>If you're looking at today's code and thinking, <i>gee, shouldn't these be in stored procedures, or views, or something...</i></p>
    <br>
    <br>
    For some reason or another, I've worked with many DBA's in the past
    that absolutely forbid the use of stored procedures, views, triggers,
    etc (basically anything besides tables and indexes).  Basically,
    the argument is that once you start doing that, you're starting to
    embed application logic in the database.<br>
    <br>
    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?<br>
    <br>
    <br>I'm not a DBA but just a practical developer with DB knowledge. I have used stored procedures (oracle, pl/sql) for years, but don't recommend them for anything but very simple logic and data handling. Indeed you are embedding application logic, and also spreading it out over multiple environments and languages. It will create a mess in a short time.<br>Views are, IMHO, the only exception.<br><br>
  • Stoneman 2006-05-04 13:22
    <P>Hey just to let you know! I used to run a 5000 user system on hardware just above 486's!!! and they fastest performance came about by putting as much logic as possible in the stored procedures!!! </P>
    <P>So anyone that tells you this is bad!&nbsp; needs to come back to reality They are the same people that say 5th normal form is the way to go!!! hahaha when in the real world most people run 3rd normal form so they can "USE" their database! </P>
    <P>&nbsp;</P>
    <P>Anyway I highly recommend putting logic in the stored proc's if speed is your holy grail! </P>
  • Scrub 2006-07-13 12:45
    <P>I could somehow see that making constants for table names and possibly column names could have a purpose, but it takes away readability and makes it so much harder to alter the SQL in the code. Think about it, if you need to proof read or&nbsp;test run this SQL in the database. </P>
    <P>If you use bind variables (which you almost always do except when it is blatantly obvious that the parameters never change) 'select *... ' will break your prepared statements, you will have to restart the application to add a column </P>
    <P>The SqlMisc.PARAMS_FIRST, SqlMisc.PARAMS_SECOND probably comes from Oracles older syntax where you could name the bind parameters :1, :2 etc</P>
    <P>&nbsp;</P>
    <P>&nbsp;</P>
  • Narendra Venkataraman 2007-02-11 01:42
    Very true.. In fact I was planning on writing on "Parody of constants" but this is too good.. some more enterprisocities like coffee talks -

    http://devdiary-naren.blogspot.com/2007/02/coffee-talks-may-also-be-referred-to-as.html
  • Jonadab 2007-02-12 10:35
    For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes). Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.


    I am against putting application logic in the database, which is the only thing I've ever seen stored procedures used for. In the case I'm thinking of, 25+ pages of seriously inscrutable SQL performed what would have been less than 20 lines of code in pretty much any high-level language. Because of the wrong-headed design, which apparently used triggers just to demonstrate that the programmer knew how, various things had to be coded in SQL up to three times: once in the stored procedure that runs nightly as a job, again in the stored procedure that the trigger calls, and again in the one that is called in response to a user action at some later time. Another time I found stored procedure code created, propagated, and iterated over entire temporary tables that were strictly necessary, making for 5+ pages of stored procedure, which when refactored came to less than 1 page of SQL, let alone what it would have been in a high-level language.

    And this wasn't even really in an enterprise product, just a garden variety dubiously-implemented one.

    I don't know that I would categorically forbid stored procedures, but I would certainly discourage gratuitous overuse of them.
  • rdrunner 2007-06-22 04:54
    jsmith:
    So you're saying that if the vendor simply implemented a brand new type of object whose purpose was specifically to reduce index-to-table lookups, then it would all be fine because "that's what it's supposed to do"?&nbsp; The only reason this is a hack is that they are called "indexes"?&nbsp; That goes back to a "the world revolves around me" position.&nbsp; You don't like covering indexes because they aren't "right".
    Here is an interesting analogy:
    Indexes are called "indexes" because they work like the indexes in these things we call "books".&nbsp; Well, a typical book index contains a keyword and a page number.&nbsp; If you were looking for how many pages reference the word "widget" in a given book, you could entirely answer that question from the index.&nbsp; Hey, look, database servers do that too!!!!&nbsp; Covering indexes don't abuse the abstract concept, the concept of covering indexes has been in books for hundreds of years.


    Actually there is a new feature in SQL2005 that allows you to explicitly include columns into an index (Those new columns will be added to the leaf nodes of the index, so you can save some performance, since the extra Columns dont have to be sorted)

    To create a covering index, you need to know how you DB is used. Its not something you do light hearted. If you have a wide table, and you often retrieve only a very small subset of columns, then its a perfect solution. Yes, it creates some aditional overhead when you modify your data, but the net result can be quite effective.

    [edit]

    And it is by NO WAY a "hack"
  • Ömer Faruk Z 2009-01-07 04:26
    where is the enterprise sql?
  • cheap g-star jeans 2009-07-20 15:28
    <a href=http://www.agogoplaza.com/productinfo.asp?id=24960>cheap g-star jeans</a> <a href=http://www.guildwarsgoldvip.com/>cheap guild wars gold</a> <a href=http://www.guild-wars-gold.net/>cheap guild wars gold</a> <a href=http://www.tradeworlda.com>cheap handbags</a> <a href=http://putonnike.com>cheap handbags</a> <a href=http://www.okaybags.com/>Cheap handbags</a> <a href=http://www.totradeunion.com>cheap jeans</a> <a href=http://www.ineedjordanshoes.com/>cheap Jordan</a> <a href=http://www.kicksforever.com/>cheap Jordan</a> <a href=http://www.agogoplaza.com/>cheap jordan shoes</a>
  • �������Ƿ�Ⱦ 2009-07-21 16:34
    �й�<a href=http://www.wjgb120.cn/news/gqw/089229203409A8GJJ00DGB6HG88BHI.html>�������Ƿ�Ⱦ</a>���Ǵ������Ƿ�Ⱦ��ҵ�Ĵ������Ƿ�Ⱦ�ƹ㡢�������Ƿ�Ⱦ�ɹ����������Ƿ�Ⱦ�������������Ƿ�Ⱦ��չ���������Ƿ�Ⱦ�б���ҵƽ̨��ɽ����<a href=http://www.wjgb120.cn/news/gqw/08722102744KGJKF3D1FHDFAI009K0.html>�������и�</a>����. ��ɽ�������и�����. ��ɽ�������и�����. ��ɽ�������и���˾���� �й�<a href=http://www.wjgb120.cn/ask/show.asp?id=377>��������ô����</a>�������ۺ��ԵĴ�������ô������ҵ��վ,�����Դ�������ô����ý�顢��������ô���ƴ��⡢��������ô�����г��ȴ�������ô�������ºʹ�������ô����������<a href=http://www.wjgb120.cn/news/gqw/089229203409A8GJJ00DGB6HG88BHI.html>������תС����</a>��˾-��������魴�����תС�����˾���׶�����׿���Ĵ�����תС�����˾,��������תС�����˾�ṩ50������ֵĴ�����תС�������.<a href=http://www.buctedu.cn/>��ѧ����</a>��˾�Ľܳ����������Ϻ���ѧ���й�˾���й���������Դ�ѧ���з����ṩ�̣������ѧ���й�˾����ǿ���ѧ������Դ��ͨ<a href=http://www.chaye.org.cn/dzx.htm>��բз</a>]��˾���ൺ֪���Ĵ�բз��˾,�ൺ��բз,�ൺ��բз��ƹ�˾,�ൺ��բз����й�<a href=http://www.bjzyxc.com/>�������</a>���Ǵ��������ҵ�Ĵ�������ƹ㡢������˲ɹ���������˼�����������˻�չ����������б���ҵƽ̨�����ش���Ҫ�š�<a href=http://www.nanpuhospital.com.cn/fuke/view.asp?newsid=869&classid=3>����</a>��֪�͵��ش������¸�ʾ��
  • Cartier jewelry 2009-07-22 00:24
    <a href=http://www.cjewelry.net/>Cartier jewelry</a> <a href=http://www.davismicro.com/>cell phone for sale</a> <a href=http://www.wholesalesglobal.com/mobile-phones-c-65.html>cell phone wholesale</a> <a href=http://www.komanmotor.com/Products.asp?NodeCode=00030004>centrifugal fan</a> <a href=http://www.komanmotor.com>centrifugal fan</a> <a href=http://www.yixinghaldenwanger.com/enProductShow.asp?ID=69>ceramic Rollers</a> <a href=http://www.easthillfx.co.jp/cn/cfd_cn/cfd2.htm>CFD</a> <a href=http://www.easthillfx.co.jp/cnc/cfd_cnc/>CFD</a> <a href=http://www.easthillfx.co.jp/cnc/cfd_cnc/cfd2.htm>CFD</a> <a href=http://www.easthillfx.co.jp/cn/cfd_cn/>CFD</a>
  • ���̲߻� 2009-07-23 11:57
    <a href=http://www.08logo.com/cehua.asp>���̲߻�</a> <a href=http://www.egica.com.cn/product.aspx?sort_id=382>�նȼ�</a> <a href=http://www.chinashade.com/about.asp>������</a> <a href=http://www.phica.com.cn/product.aspx?sort_id=479>������</a> <a href=http://www.zghdmt.com/profile.asp>�����</a> <a href=http://www.zju.zj.cn/advance/>���ְҵ��������ѵ</a> <a href=http://www.zju.zj.cn/>�㽭EMBA</a> <a href=http://www.zju.zj.cn/>�㽭��ѧEMBA</a> <a href=http://www.zju.zj.cn/>�㽭��ѧMBA</a> <a href=http://www.yzspd.com/c_03.asp>�㽭���׹���</a>
  • �¾���Ѫ�������� 2009-07-25 02:00
    ����ȥ�¾���Ѫ����������Ϊ���ṩ����ʵ��<a href=http://www.nanpuhospital.com.cn/yuejing/view.asp?newsid=344&classid=1>�¾���Ѫ��������</a>������ܡ��¾���Ѫ���������������С������¾���Ѫ���������¾���Ѫ�������������·���¾���Ѫ����������������·���¾���Ѫ�������𾰵�·�߹��ԣ��л��¾����ڼ������л���ս�Ժ��������;<a href=http://www.nanpuhospital.com.cn/yuejing/view.asp?newsid=343&classid=1>�¾����ڼ���</a>��ȫ����Ӫ���ṩ�¾����ڼ�����·��ѯ����ȫ��λ�¾����ڼ�������Լ���Ȩ�����¾����ڼ�����Ѷ��Ϣ���ְ�˹<a href=http://www.nanpuhospital.com.cn/yuejing/view.asp?newsid=343&classid=1>�¾�������ô��</a>,�����¾�������ô��,�¾�������ô�����,ƽ���¾�������ô��,�����¾�������ô��,��Ц�¾�������ô��,�����¾�������ô��,���<a href=http://www.nanpuhospital.com.cn/yuejing/view.asp?newsid=343&classid=1>�¾������Ƴټ���</a>�������¾������Ƴټ�����������¾������Ƴټ��췢������¾������Ƴټ���IJĵ������Ѷ.����<a href=http://www.fuxingxing.com>��ɩ</a>��˾,רҵ������ɩ��˾,�Ϻ�����ʱ����ɩ��˾Ϊ���ṩרҵ������ɩ,�������������ͬ��ɩ���<a href=http://www.86477518.com>Կ�׸��ƻ�</a>�����ṩ������Կ�׸��ƻ����߲��ź�Կ�׸��ƻ���Կ�׸��ƻ���רҵ����ƵԿ�׸��ƻ���վ���Ϻ�<a href=http://www.nanpuhospital.com.cn/jiha/view.asp?newsid=377&classid=4>�и�����</a>Ϊרҵ�и�������˾���и�����������Ϊרҵ���и�������˾֮һ�����ɹ��и�С�����Ż���վ,<a href=http://www.wjgb120.cn/news/gqw/08722102744KGJKF3D1FHDFAI009K0.html>�и�С����</a>���ɹ��������и�С����Ƶ��,�и�С���������������.
  • ��Ĥ�컨 2009-07-25 10:02
    <a href=http://www.m-c.cn/>��Ĥ�컨</a> <a href=http://www.mnjiaju.com/>��ľ��</a> <a href=http://www.hzqs.com.cn/www/48/>������</a> <a href=http://www.kaka925.com>��ʿ�ֱ�</a> <a href=http://www.sepmem.com/products.asp>�������</a> <a href=http://www.travel-sichuan.com/mdd/sanxia/>��Ͽ</a> <a href=http://www.travel-sichuan.com/mdd/sanxia/>��Ͽ����</a> <a href=http://www.zqlcfj.com/>��Ҷ�޴ķ��</a> <a href=http://www.zqlcfj.com/>��Ҷ�޴Ĺķ��</a> <a href=http://www.shunlidz.com/>ɢ��Ƭ</a>
  • ��ǻ������ 2009-07-25 13:20
    <a href=http://www.nanpuhospital.com.cn/fuke/view.asp?newsid=415&classid=4>��ǻ������</a> <a href=http://www.nanpuhospital.com.cn/fuke/view.asp?newsid=943&classid=4>��ǻճ��</a> <a href=http://www.96096055.com/html/388/200701/20070110105206.htm>Ƥ���������</a> <a href=http://www.ps120.net/>Ƥ���</a> <a href=http://www.ps120.net>Ƥ������</a> <a href=http://www.ps120.net/>Ƥ��</a> <a href=http://www.huoshe.com/pixiuzonghui.htm>����</a> <a href=http://www.rx-smc.com/>Ƭ״ģ����</a> <a href=http://www.szhipower.com/products/Leica/Metallographic/detail/15.html>ƫ����΢��</a> <a href=http://www.sd1718.com/yiqi/6-16.html>Ƶ�׷�����</a>
  • ��ְ�о����������� 2010-03-31 12:03
    ��������ְ�о�������������,<a href=http://www.ruzzy.cn/>��ְ�о�����������</a>�Żݣ��������㣬��ְ�о�������������·�������Σ������μǣ�������ְ�о���������������ϢΪ�����ܽ�����ְ�о�������,�Ͼ�<a href=http://www.ruzzy.cn/>��ְ�о�������</a>,,��ְ�о������԰����ȷ���Ϊ����ְ�о��������Ͼ��ṩ�Ͼ���ְ�о������Ծ�����Ͼ���ְ�о���������·�����Ĵ���ְ�о������ṩ��ɫרҵ���Ĵ�<a href=http://www.ruzzy.cn/>��ְ�о�����</a>,��կ����,�ɶ�,��կ����ְ�о�����,����,��üɽ,��ɽ,������ְ�о�����������Ϣ��ְ�о�������,������,<a href=http://www.ruzzy.cn/>��ְ�о�������</a>��,�й���ְ�о��������Ż���վ���ṩ��ְ�о�������http://www.ruzzy.cn/����ְ�о��������������棬��ְ�о�������http://www.ruzzy.cn/��ȫ��������ְ�о����������й��������ڸ�Ӳ���ļ�飬���ڸ�Ӳ���ļ��Ƶ��������ʡ�������ڸ�Ӳ���ļ����Ϣ��Ѷƽ̨����������ȫ���ڸ�Ӳ���ļ�����µ����ڸ�Ӳ���ļ����Ѷ
  • ϴ�س� 2010-04-09 09:34
    �й��Ļ�ϴ�س�������̽���й�<a href=http://www.jlg.cn/sdp/106097/2/pd-1068964/356500-673912/BD530_BD55_40�Զ�.html>ϴ�س�</a>�������й��Ļ����й�ϴ�س������ṩ��Ӧ��ϴ�س��Ӵ�����ϴ�س���Ʒ��ϴ�س���ѯ����ϴ�س��Ż������<a href=http://www.jlg.cn/>ϴ�س�</a>�����Ժ������ҽ�Ŀ���ṩϴ�س���·��ѯ��רҵȨ����ϴ�س���Ѷ��Ϣ������ϴ�س����ϴ�س��Ż�<a href=http://www.jlg.cn/sdp/106097/2/pd-1068964/1570542-673914/�Զ�ˢ��_ϴ�ػ����.html>ϴ�ػ�</a>ר������Ȩ����ϴ�ػ�ר����ϴ�ػ�ר��Ϊ����ѡ��ϴ�ػ���Ʒ��ӭ����չʾ����ϴ�ػ���Ʒ<a href=http://www.jlg.cn/>ϴ�ػ�</a>��˾-���������ϴ�ػ���˾���׶�����׿����ϴ�ػ���˾,��ϴ�ػ���˾�ṩ50������ֵ�ϴ�ػ�����.ϴ��������,����<a href=http://www.bjfsf.com/>ϴ������</a>,���ϻ���ϴ������,����ϴ�����칫˾��ϴ������߻�����,ϴ�����취�ɷ���,ϴ����������
  • Jim McMaster 2010-04-25 16:13
    This is not a new phenomenon. I worked in IBM 370 Assembler language for several years. My company decreed you had to define constants for everything. There was a constant for "the number of bits in a byte." Apparently, if IBM ever changed, we could just rebuild and continue.
  • ship 2010-10-18 10:00
    It's really silly approach!
    I can e.g. say, that next generation of Java will completely use different definition of constants...
    I'm really curious how much time u would spend by refactoring your Java code to allign this changes with...
  • Frosty 2012-03-20 05:38
    I've met an incredible enterprisey guy.
    See his work: http://stackoverflow.com/questions/6514260/php-string-constants-overuse