Embrace Sql

Rather than converting back and forth between SQL and the app language, or twiddle with complicated wrapper/mappers that need dedicated experts; perhaps the industry should build an open-standard language that integrates well with it. PL/SQL and other vendor's competitors sort of do this, but they are highly proprietary, somewhat outdated, and do not have a dynamic/free-typing variation for those who enjoy loose typing.

In TableOrientedProgramming (TOP) thinking, most of the complexity of an app is in tables and to a lessor extent the queries. Simplifying table usage and querying is more important than simplifying app code if TOP is done right, or if it's for a data-centric task. "Bloat-ified" query syntax (ExpressionApiComplaints) gets in the way of this goal. I agree the repetitious parts of queries can be simplified via helper API's and tools, but don't try to bury entire queries behind goofy, unnatural API's.

(Note that I don't believe SQL is the ideal query language. I've even suggested an alternative, SmeQl. However, SQL is here to stay for a good while so we might as well learn to work better with it.)


Okay, what sort of integration are you talking about that can't be built as a DSL inside an existing dynamic language or statically typed functional language? If you're talking about more mainstream languages like CsharpLanguage, I would say the answer is that they do no worse at SQL integration than anything else that requires dynamic behavior. --JesseMillikan

Off the top (no pun) of my head:

       // mapped - error-handling not shown here
         SELECT * FROM addresses WHERE houseID = 7
       END QUERY
       print("zipcode: " & MYMAP.zipcode)  

// variables QUERY TO VARIABLES SELECT * FROM addresses WHERE houseID = 7 END QUERY print("zipcode: " & zipcode)

I think LanguageIntegratedQueryProject solves all this problems. --unknown

I'd be interested to see an example, but I'm not sure that's a useful response to such a wide range of issues. --JesseMillikan

I'm weary of Microsoft though. I've been burned by them one too many. They subtely but determinedly lore you and others into their proprietary extensions and before you know it, you are married to Bill Gates yet again. And it still creates too big of a wall between "application" and "SQL".

Regarding Alternatives

More alternatives?

In DatabaseAbstractInterfaceDiscussion, influenced from ColdFusion, I've been kicking around a "section-based" syntax that may fit SQL well. Here's an example: -t

 query name="qry", foo=7, bar=flip, zog="trag",
       flig="nab", grog=firb:
   SELECT * FROM sample
   WHERE X=47 AND Y=$thing
   PRINT "You see this if no rows returned"
 colHead auto=yes:   // column headings
 eachRow rowvar="r":
   PRINT "Name: " || name || ", Rank: " || qry.currow.rank  // short and long name
   // Alt: PRINT "Name: $name, Rank: ${qry.currow.rank}"
   // Alt2: PRINT "Name: $r.name, Rank: ${r.rank}"  (uses rowvar)
   // Note: Brackets in ${...} are optionally used to prevent 
   //       ambiguity between var names and literals.
   PRINT "End of list. Count: " || qry.rowCount
 error log=no:
   ? "Bleep happened: " || qry.errMsg   // ? is short for PRINT
 end query

I would normally add spacing between sections, but this wiki has a space bug. ( http://www.mckenna3d.com/cms/index.php?option=com_content&view=article&id=6:space-bug&catid=5:2d&Itemid=3 )

The order of the sections wouldn't matter such that "noResult" could be put at the bottom if we wanted. An "eachColumn" section is an optional idea to also consider so we can loop for each column within each row. We may also want an "eachHeadColumn".

Query info and results would go into a "map tree" structure. Example:

  qry.text  // text of the query, usually sql
  qry.textExpanded  // sql with variable substitutions (if any)
  qry.rowcount  // number of result rows
  qry.1.columnA  // "columnA" on first row (see caveat)
  qry[1].columnA // same thing, longer format useful for variable-based map indexing.
  qry.currow.columnA // columnA of current row
  qry.dbType.columnA  // Database type of columnA (such as VARCHAR).

Note that drivers/options that return cursor-based results (one row at a time) will not necessarily have position-indexable rows such that "qry.1.columnA" may not exist.

I realize that languages such as Ruby may be able to create a DomainSpecificLanguage kind of like this, but it would probably either be punctuation-happy or produce cryptic error messages for many syntax errors. Hard-wired DSL syntax tends to (potentially) produce friendlier syntax or structural error messages, whereas a Ruby error message would require knowing Ruby's syntax to understand.

I agree that with enough programming you might be able to make Rudy give meaningful error messages, but you are essentially inventing an interpreter and doing almost as much work as would be done making an interpreter written in C, which improves portability and speed over Ruby.
See also: ExpressionApiComplaints, TighterAppAndDatabaseIntegration, DatabaseAbstractInterfaceDiscussion
CategoryDatabase, CategoryProgrammingLanguage, CategoryQueryLanguage

View edit of March 30, 2013 or FindPage with title or text search