Author Topic: Different SQL formats  (Read 573 times)

qwerty

  • EA Guru
  • *****
  • Posts: 8961
  • Karma: +136/-123
  • I'm no guru at all
    • View Profile
Different SQL formats
« on: October 17, 2016, 10:24:14 pm »
Does anyone have a reference that shows the differences between SQL syntaxes in various databases EA uses? Particularly I'm looking for differences between PostgreSQL and Oracle (though other xrefs are also nice to know).

Something like this:
  • Columns names: Oracle needs upper-case, postgres needs lower, others don't care
  • Wildcards (% in most DBs vs * in some MS products)
  • sub-selects (yes/no)

From personal pain I know some of those trap doors, but would like to see more in advance.

Thanks.

q.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7736
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Different SQL formats
« Reply #1 on: October 17, 2016, 10:35:17 pm »
I haven't really found a good reference, but I do have some translation functions in my framework that might be helpful.
The are not complete, they only treat the thing I had to deal with.
Here's what I have in my framework

Code: [Select]
/// <summary>
/// sets the correct wildcards depending on the database type.
/// changes '%' into '*' if on ms access
/// and _ into ? on msAccess
/// </summary>
/// <param name="sqlQuery">the original query</param>
/// <returns>the fixed query</returns>
private string formatSQL(string sqlQuery)
{
sqlQuery = replaceSQLWildCards(sqlQuery);
sqlQuery = formatSQLTop(sqlQuery);
sqlQuery = formatSQLFunctions(sqlQuery);
return sqlQuery;
}

/// <summary>
/// Operation to translate SQL functions in there equivalents in different sql syntaxes
/// supported functions:
///
/// - lcase -> lower in T-SQL (SQLSVR and ASA)
/// </summary>
/// <param name="sqlQuery">the query to format</param>
/// <returns>a query with traslated functions</returns>
private string formatSQLFunctions(string sqlQuery)
{
string formattedSQL = sqlQuery;
//lcase -> lower in T-SQL (SQLSVR and ASA and Oracle and FireBird)
if (this.repositoryType == RepositoryType.SQLSVR ||
this.repositoryType == RepositoryType.ASA ||
this.repositoryType == RepositoryType.ORACLE ||
this.repositoryType == RepositoryType.FIREBIRD ||
this.repositoryType == RepositoryType.POSTGRES)
{
formattedSQL = formattedSQL.Replace("lcase(","lower(");
}
return formattedSQL;
}

/// <summary>
/// limiting the number of results in an sql query is different on different platforms.
///
/// "SELECT TOP N" is used on
/// SQLSVR
/// ADOJET
/// ASA
/// OPENEDGE
/// ACCESS2007
///
/// "WHERE rowcount <= N" is used on
/// ORACLE
///
/// "LIMIT N" is used on
/// MYSQL
/// POSTGRES
///
/// This operation will replace the SELECT TOP N by the appropriate sql syntax depending on the repositorytype
/// </summary>
/// <param name="sqlQuery">the sql query to format</param>
/// <returns>the formatted sql query </returns>
private string formatSQLTop(string sqlQuery)
{
string formattedQuery = sqlQuery;
string selectTop = "select top ";
int begintop = sqlQuery.ToLower().IndexOf(selectTop);
if (begintop >= 0)
{
int beginN = begintop + selectTop.Length;
int endN = sqlQuery.ToLower().IndexOf(" ",beginN) +1;
if (endN > beginN)
{
string N = sqlQuery.ToLower().Substring(beginN, endN - beginN);
string selectTopN = sqlQuery.Substring(begintop, endN);
switch ( this.repositoryType)
{
case RepositoryType.ORACLE :
// remove "top N" clause
formattedQuery = formattedQuery.Replace(selectTopN, "select ");
// find where clause
string whereString = "where ";
int beginWhere = formattedQuery.ToLower().IndexOf(whereString);
string rowcountCondition = "rownum <= " + N + " and ";
// add the rowcount condition
formattedQuery = formattedQuery.Insert(beginWhere + whereString.Length,rowcountCondition);
break;
case RepositoryType.MYSQL :
case RepositoryType.POSTGRES :
// remove "top N" clause
formattedQuery = formattedQuery.Replace(selectTopN, "select ");
string limitString = " limit " + N ;
// add limit clause
formattedQuery = formattedQuery + limitString;
break;
case RepositoryType.FIREBIRD:
// in firebird top becomes first
formattedQuery = formattedQuery.Replace(selectTopN,selectTopN.Replace("top","first"));
break;
}
}
}
return formattedQuery;
}
/// <summary>
/// replace the wildcards in the given sql query string to match either MSAccess or ANSI syntax
/// </summary>
/// <param name="sqlQuery">the sql string to edit</param>
/// <returns>the same sql query, but with its wildcards replaced according to the required syntax</returns>
private string replaceSQLWildCards(string sqlQuery)
{
bool msAccess = this.repositoryType == RepositoryType.ADOJET;
int beginLike = sqlQuery.IndexOf("like",StringComparison.InvariantCultureIgnoreCase);
if (beginLike > 1 )
{
int beginString = sqlQuery.IndexOf("'",beginLike + "like".Length);
if (beginString > 0)
{
int endString = sqlQuery.IndexOf("'",beginString +1);
if (endString > beginString)
{
string originalLikeString = sqlQuery.Substring(beginString +1,endString - beginString );
string likeString = originalLikeString;
if (msAccess)
{
likeString = likeString.Replace('%','*');
likeString = likeString.Replace('_','?');
likeString = likeString.Replace('^','!');
}
else
{
likeString = likeString.Replace('*','%');
likeString = likeString.Replace('?','_');
likeString = likeString.Replace('#','_');
likeString = likeString.Replace('^','!');
}
string next = string.Empty;
if (endString < sqlQuery.Length)
{
next = replaceSQLWildCards(sqlQuery.Substring(endString +1));
}
sqlQuery = sqlQuery.Substring(0,beginString+1) + likeString + next;

}
}
}
return sqlQuery;
}

qwerty

  • EA Guru
  • *****
  • Posts: 8961
  • Karma: +136/-123
  • I'm no guru at all
    • View Profile
Re: Different SQL formats
« Reply #2 on: October 18, 2016, 12:53:34 am »
Thanks, Geert. That's a good start :)

Why have a single standard if you can have two of them? Or three?

q.

Simon M

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 6193
  • Karma: +47/-5
    • View Profile
Re: Different SQL formats
« Reply #3 on: October 18, 2016, 10:26:17 am »
Rather than a list of differences, here's a list of rules I've learned over the years that make a "kind" of standard.

  • Always alias column names using "as"
  • Never alias table names using "as"
  • When adding an order by to a union, always use column indexes
  • Always use single quotes on literal strings

Other notes.
  • Everything EA supports allows subselects now (no longer support MySQL 4)
  • Any casting, function calls, data extraction etc is going to require unique code for almost every database
  • Keep an eye out for the keywords for different databases. (If calling through EA, wrap the potential keyword in square brackets and let EA handle it)
  • In EA at least I always use the case sensitive table and column names. Oracle will return the columns in upper case, Postgres will return them in lower case. (Or something does...) Other databases are case sensitive in lookup of the table names, column names or both.
Simon

support@sparxsystems.com

qwerty

  • EA Guru
  • *****
  • Posts: 8961
  • Karma: +136/-123
  • I'm no guru at all
    • View Profile
Re: Different SQL formats
« Reply #4 on: October 18, 2016, 11:06:56 am »
Thanks Simon, that's helpful too. With respect to EA one should use uppercase AS, I guess  ;D

q.