2

I have an application that uses several different databases to collect similar information. I do this through an ODBC interface, which effectively abstracts my persistence layer from my application layer, and it works fairly well.

Here's the thing: I want to have a bit more specific queries than 

SELECT * FROM [TableName]

As far as I can tell, this works for all ODBC databases, but when you start to get a little more specific, syntax and language usage become more problematic. The query that will work in MySQL will not necessarily work in MSSQL; the query that runs fine against Oracle will not fare so well against PostgreSQL.

But ODBC is an abstraction layer, so I'm thinking there must be a language reference that all ODBC-compliant database systems support. If I write my queries using those language constructs, I should expect to get similar results back, regardless of the database system.

Am I right? Is there such a standard? If so, where can I find the language reference so I can write my queries accordingly? Things I'm interested in in particular are constructs like:

  • TOP (LIMIT)
  • MID (SUBSTR)
  • CAST (CONVERT)

etc. Thanks for your help!

Jeremy Holovacs
  • 22,480
  • 33
  • 117
  • 254
  • We have the same situation and never found anything that would work. Closest I've been able to come is just doing basic query dumps and then letting the app do the heavy lifting which is far from ideal. One alternative was to have the queries in separate XML files and then a set of connection classes. You can then call the query and the connection and if you have to make changes to a query you can do it in the xml file without recompiling. – Brian Apr 18 '12 at 13:06
  • Is LINQ-To-SQL/Entities an option? Here is a similar question which is addressed by LINQ: http://stackoverflow.com/questions/2938252/generic-sql-builder-net – mellamokb Apr 18 '12 at 14:03
  • Unfortunately the higher-level constructs are part of my problem. I have a database system that doesn't enforce column constraints and it blows up every handler there is. I am exploring a universal way to truncate column lengths in the query as part of a two-part process; first, get the schema of the query result, then run the query with inline column constraints... i.e., if column `foo` is varchar(15), I can `SELECT MID(foo, 1, 15) AS foo`... ugly no matter how I look at it. Sigh. – Jeremy Holovacs Apr 18 '12 at 14:22

1 Answers1

1

For functions the ODBC standard is listed at Appendix E: Scalar Functions. That includes substring. You need to use the syntax {fn func_name(arguments)}. The cast function in SQL 92 is convert in ODBC (e.g., CAST (value AS type) in SQL92 is {fn CONVERT (value, type)} in ODBC). Top is however, not a function but part of the SQL syntax your databases may or may not support (e.g., Oracle does not have top).

bohica
  • 5,932
  • 3
  • 23
  • 28