Possible database command sets in EA

[McMannus]

Hi guys,

I'm making extensive use of direct database querying/modifying bypassing the EA API, because in many cases this is the only way to have an acceptable performance with realistic model sizes.

It has often been mentioned in the community that
1) not all commands of the database querying language are supported through the respective EA methods Repository.SQLQuery() and Repository.Execute()
2) one has to manage the different database types supported in EA manually (only while by-passing of course)

I'm wondering,
1) if the available command set is restricted artificially by EA itself so that it even refuses some commands although they are supported by the database server
2) in which way database type and version restrict the available command set
3) if there is a list available, which commands are supported for which database type

Thanks for your thoughts/evidences on that!
Jan

[qwerty]

1) I don't know
2) Several suppliers have implemented their own flavor/dialect of ANSI SQL. There is no rule you can apply. You need to find out by trial and error (or reading the suppliers documentation if you prefer).
3) No. See 2) Except someone would be willing to write those flavors down which is a) unpleasant b) expensive (since you have to have all those expensive ones in access too) and c) will not in any chance return the money for the effort you have to put in.

q.

[Eve]

1) There are a couple of places where EA modifies SQL (model views being one) this can cause problems, but more often than not it's a restriction of the ODBC driver EA is connecting with or similar.

2) Every database implements things slightly differently, and even if some SQL is valid it may not be efficient. As I pointed out for point 1, ODBC drivers can add even more complexity.

3) Way too complex. I'd just be looking at the corresponding websites of each databases you need to support.

[Geert Bellekens]

Since I have an add-in that is supposed to work on all supported databases I had to tweak my SQL a bit depending on the database I'm working on.
I defined one function that wraps the Repsitory.SQLQuery and from that function I do all the tweaking.
No guarantee that this will work on all queries. I only wrote what I needed for the queries I use.
The code is available from Github on Model.cs

Here is the first part:

Code: [Select]

/// generic query operation on the model.
/// Returns results in an xml format
public XmlDocument SQLQuery(string sqlQuery)
{
      sqlQuery = this.formatSQL(sqlQuery);
      XmlDocument results = new XmlDocument();
      results.LoadXml(this.wrappedModel.SQLQuery(sqlQuery));
      return results;
}

/// <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)
      {
            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;
}
Geert

[Geert Bellekens]

And here is the second part:

Code: [Select]

/// <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;
}