Puzzled why string concatenation of in SQL query fails for *.feap repositories

[Richard Freggi]

I need to copy/paste from the query result window into Excel.  For results to paste correctly into Excel using "Paste - Use text import wizard" I need to enclose each field in the query result into a delimiter: I use ".
So I need to concatenate " before and each query result, using the || operator.  However this operation FAILS for the trailing concatenation for Memo fields of Tagged Values!

For example, the line
'"' || op4.Notes || '"' AS "Remarks",
Produces this result (note no " at the end of the string):
"Constrained by xyz interface

Only the following workaround seems to work (it's kludgy and I need to drop the last character of the memo field to make it work - so I'm looking for a cleaner solution)
'"' || (SUBSTRING(op4.Notes) FROM 1 FOR CHAR_LENGTH(op4.Notes)-1) || '"' AS "Remarks"
The result is then (the trailing collation succeeds but the last character is lost):
"Constrained by xyz interfac"

I checked the Memo fields for non-printing characters or anything that could mess up the collation but cannot find anything strange (actually I clean up the memo string before collation for carriage return and " text that could confuse Excel - my actual query is:
'"' || SUBSTRING(REPLACE (REPLACE (REPLACE (REPLACE (COALESCE(op4.Notes, ''), ASCII_CHAR(13), ' '), ASCII_CHAR(10), '. '), ASCII_CHAR(9), '   '), ASCII_CHAR(34), '''') FROM 1 FOR CHAR_LENGTH(op4.Notes)-1) || '"' AS "Remarks",

I checked Firebird documentation and googled around but cannot find anything related.  I suspect it's a EA bug related to Memo fields.

If anyone knows a more elegant solution that does not require me to lose the last character of the field let me know!
p.s. I add a couple spaces when I write the tagged value memos so all that gets cut off is just a space, but it's a chore
I did not have this problem with *.eap Access repositories
I could start messing around with either concatenating a sacrificial character to the string then removing it with SUBSTRING, or adding back the last character after collation or weird stuff like this but before I go down that kludge-fest I'd like to know if there is a better solution!
EA v. 1310

Thanks!

[qwerty]

Are you using EA to query the DB? If so, cross check with a native client and eventually send a bug report.

In any case: do the string operation outside the query in a script.

q.

[Richard Freggi]

Yes I am using the EA search function CRTL-F and "my queries"

I need to go from copying the search function result to paste directly into Excel

I made a macro to modify the result in Notepad++, doable but not ideal.  I need to do the string manipulation as part of the DML query.

[qwerty]

EA interferes with queries. And as we know there are a couple of quirks (the capital AS and accessing Notes come to mind immediately). Since a fix for 13.5 will not be made you were to wait for EA16 or so. I could think of using an add-in query to do the string magic. I only did this once. Also: if you need that for Excel, then why not writing a macro there. For sure you can get native client dlls to access a firebird (or was it -base? I always get confused) database.

q.

[Eve]

Interestingly, I only get the behavior you describe when querying t_operation.

Using the following queries, the results are correct for me.

Code: [Select]

select '"' || Note || '"' as Remarks from t_object where Object_Type='Class'
This is loses a character.

Code: [Select]

select '"' || Notes || '"' as Remarks from t_operation
This works for all fields until it doesn't. (Changing one field can break other fields)

Code: [Select]

select '"' || COALESCE(Notes, '') || '"' as Remarks,
'"' || COALESCE(Code, '') || '"' as Code,
'"' || COALESCE(StyleEx, '') || '"' as StyleEx,
'"' || COALESCE(Behaviour, '') || '"' as Behavior
from t_operation
My suspicion is that the error is inside the ODBC driver. Unfortunately, none of that helps you get what you are after.