SQLQuery for different databases

[Dieter Goetz]

I am using an addin programmed in C#. Now I have a problem with SQLQuery() method.

In EA directly I use  #DB=SQLSVR# / #DB=JET# to adapt the query to different databases. How can I do this in the C# SQLQuery() method?

Thanks for your help.

Dieter

[qwerty]

Geert asked a similar question on SO. The answer: you need to care for that yourself  

q.

[Dieter Goetz]

Thanks for your answer. But  - would it be possible to be more precise?

How can I take care for that about myself? Can I check the kind of database the EA repository is connected to from an addin?
Where can I find the post Geert asked?

[qwerty]

See here: http://stackoverflow.com/questions/27098059/get-an-escaped-multi-syntax-sql-update-string
Hope that explains where you're stuck. In deep ...

q.

[Aaron B]

SQLQuery() does not handle macros such as #DB=<DBNAME>#. These only work in SQL-based model search definitions.

Try calling Repository.RepositoryType() in your C# code to find the type of the current database, then change your SQL as appropriate.
http://www.sparxsystems.com/enterprise_architect_user_guide/11/automation_and_scripting/repository3.html

[Geert Bellekens]

Here's some of the stuff I do in Model.cs

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)
          {
                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 Bellekens]

and here's the rest:

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

[Geert Bellekens]

And here's the part that checks the database type:

Code: [Select]

 /// <summary>
    /// Gets the Repository type for this model
    /// </summary>
    /// <returns></returns>
    private RepositoryType getRepositoryType()
    {
          string connectionString = this.wrappedModel.ConnectionString;
          RepositoryType repoType = RepositoryType.ADOJET; //default to .eap file
    
          // if it is a .feap file then it surely is a firebird db
          if (connectionString.ToLower().EndsWith(".feap"))
          {
                repoType = RepositoryType.FIREBIRD;
          }
          else
          {
                //if it is a .eap file we check the size of it. if less then 1 MB then it is a shortcut file and we have to open it as a text file to find the actual connection string
                if (connectionString.ToLower().EndsWith(".eap"))
                {
                      System.IO.FileInfo fileInfo = new System.IO.FileInfo(connectionString);
                      if (fileInfo.Length > 1000)
                      {
                            //local .eap file, ms access syntax
                            repoType = RepositoryType.ADOJET;
                      }
                      else
                      {
                            //open the file as a text file to find the connectionstring.
                        System.IO.FileStream fileStream = new System.IO.FileStream(connectionString, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite);
                        System.IO.StreamReader reader = new System.IO.StreamReader(fileStream);
                        //replace connectionstring with the file contents
                        connectionString = reader.ReadToEnd();
                        reader.Close();
                      }
                }
                if (!connectionString.ToLower().EndsWith(".eap"))
                {
                      string dbTypeString = "DBType=";
                      int dbIndex = connectionString.IndexOf(dbTypeString) + dbTypeString.Length;
                      if (dbIndex > dbTypeString.Length)
                      {
                            int dbNumber;
                            string dbNumberString = connectionString.Substring(dbIndex,1);
                            if (int.TryParse(dbNumberString,out dbNumber))
                            {
                                  repoType = (RepositoryType) dbNumber;
                            }
                      }
                }
          }
          return repoType;
    }