Enterprise Architect SQL: Issue on selecting note-fields

Please note, that this article belongs to EAP files only!
You won’t have a problem with the given SQL Statement, if you’re running the repository on SQL Server or Oracle or whatever database. Except EAP:

As you might know, EAP files are MS Access database files with just some other extension.
Therefore, SQL Statements in EA are limited the way they are in Access itself.

So you want to quote the text from a memo-field like t_objects.Note? Bad idea!

select Name, '"' + Note + '"' as QuotedNote from t_object

will result in incomplete data, if the note’s length of content exceeds 255 characters.

Why is this?
Adding quotes to the data from a memo makes the database convert the column into text. And text fields are limited to 255 characters.
The more, every additional character will be cut off without warning.

If you don’t try to add quotes (or any other character!) to a memo field, you will receive the whole text.
Sadly, there’s no convert-to-memo function available. Just CStr, CVar and stuff.
And it makes no difference, how you connect to the database, as it is: a limitation of the database itself.

3 comments on “Enterprise Architect SQL: Issue on selecting note-fields

  1. This conversion from memo to text is really a pity. But it’s a general problems with memo columns ( or clob or text as they are called elsewhere). Concatenating, always leads to issues with conversion and truncation.

    Fortunately there is a way to prevent it. The idea behind it is that when multiple select statements are combined with a UNION ALL, the columns returned by the first select statement determine the type of the returned column (this is by the way the case not only in Access but generally). So you can get quoted notes with such a statement:

    select Name, Note as QuotedNote from t_object where 1=0 union all select Name, ‘”‘ + Note + ‘”‘ as QuotedNote from t_object;

    It’s doesn’t look nice and basically doubles the length of your statement. But it does work… Just tried it by putting 1000 characters in a note and selecting it in Access.

  2. Hello Henri!

    Thanks for your response, but text still get’s truncated. Tested it with EA’s ‘Find In Project’ function as well as with an addin with a plain OleDb-Connection.

    No luck here.

    Sadly, Enterprise Architect’s JET-Driver refuses Memos in UNIONs completely:
    “Can’t use Memo or OLE Object field ‘Note’ in the SELECT clause of a union query.”

  3. Hi Andreas,

    Sorry, just noticed you had responded.

    I couldn’t test it from within EA. But from a C# console application it seems to work (see example below). I’ve created a 500 character long note for an element in my test model and it was properly printed to the console.

    So I guess it’s possible outside of EA but probably not within EA.

    Code:

    const string sqlQuery =
    “select Name, Note as QuotedNote from t_object where 1=0 union all select Name, ‘\”‘ + Note + ‘\”‘ as QuotedNote from t_object;”;
    var con = new OleDbConnection(“Provider=Microsoft.JET.OLEDB.4.0;Data Source=D:\\Temp\\Test.eap”);
    con.Open();
    var cmd = new OleDbCommand(sqlQuery, con) {CommandText = sqlQuery};
    using (var reader = cmd.ExecuteReader())
    {
    while (reader != null && reader.Read())
    {
    Console.WriteLine(reader[1]);
    }
    }
    con.Close();

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *

Du kannst folgende HTML-Tags benutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>