Sparx Systems Forum
Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: Dieter Goetz on December 15, 2014, 10:11:31 pm
-
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
-
Geert asked a similar question on SO. The answer: you need to care for that yourself :(
q.
-
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?
-
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.
-
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
-
Here's some of the stuff I do in Model.cs (https://github.com/GeertBellekens/Enterprise-Architect-Add-in-Framework/blob/master/EAAddinFramework/EAWrappers/Model.cs)
/// <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;
}
-
and here's the rest:
/// <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
-
And here's the part that checks the database type:
/// <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;
}