Sparx Systems Forum

Enterprise Architect => Bugs and Issues => Topic started by: Bert Malliet on January 13, 2017, 11:05:09 pm

Title: Model Search SQL - behaviour of the plus-character
Post by: Bert Malliet on January 13, 2017, 11:05:09 pm

Dear All,
I need to simply add two numeric values within a query.
But I find the plus-character + to behave unexpectedly: it results in a concatenation, treating the numeric values as strings.
(the EA-repository is in an Oracle database, EA v9.3)
Example: "select 1 + 2 from dual" results in "12", believe it or not, same result as "select 1 | 2 from dual" and "select concat(1,2) from dual".
So, how to add numeric values?
I'm starting to loose faith in humanity when "select instr('abcde','d')+1 from dual" returns 41 in stead of 5 ...  ;)

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: Geert Bellekens on January 14, 2017, 03:29:23 am

Bert,

Do you see the same behavior when directly querying oracle?
In that case you might be better off searching for help on an oracle forum.

There are not too many oracle specialists around here.

Geert

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: qwerty on January 14, 2017, 04:26:56 am

I tried

Code: [Select]

select 1+2 from  t_object and got 3 (* rows in t_object) for an EAP.

q.

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: Bert Malliet on January 16, 2017, 08:39:34 pm

I did some tests:

• directly querying Oracle (from Oracle SQL Developer) the result is normal: "select 1 + 2 from dual" results in "3"; this seems to indicate that the problem is not in the database.
• when connecting to a local .EAP file, the result is normal too: "select 1 + 2 from t_object" renders "3"; this seems to indicate that the problem is not in my local EA-installation.
• same results on my colleagues computer.
• same result with EA-lite on another colleagues computer.

It looks like there is 'something' between our local EA-installation and the remote database that messes things up.

Here's the connection string my local EA-installation uses to connect to the database:
PROD Infrastructure on Oracle --- DBType=3;Connect=Provider=OraOLEDB.Oracle.1;Password=<nonofyourbusiness>;Persist Security Info=True;User ID=A573_A1133;Data Source=IOICTR2P
I'll check with our local Oracle guru if this OraOLEDB.Oracle.1 might be the something I'm looking for.

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: SamSepi0l on August 10, 2018, 12:47:23 am

Hi Bert,

I'm facing the same issue with the plus character, instead of adding the values is concatenating them as strings.

Were you able to solve this issue? how?

We are using EA Version 14.0

Thank you!

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: Eve on August 10, 2018, 09:08:37 am

One of your numbers is probably a string datatype in the database.

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: SamSepi0l on August 10, 2018, 11:31:16 pm

At the beginning I thought the same, but I ran the following query:

Code: [Select]

Select 5+5 from t_object  and it's returning 55.  ???


Thank you.

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: qwerty on August 11, 2018, 12:13:13 am

I suspect that the RDBMS is responsible for that. It's probably some implementation detail that varies between EAP, MySQL, etc.

q.

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: Eve on August 13, 2018, 09:37:57 am

JET, SQL Server, MySQL and ASA return what you would expect.

Oracle and Firebird use string concatenation. (Even when I tried Select cast(5 as integer)+cast(5 as integer) as num from t_cardinality)

PostgreSQL gave a database error.

I would expect that the error is in the driver being used to connect to the database. (I can't prove that because I only have the one method to connect and the one application using that connection)

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: SamSepi0l on August 13, 2018, 11:10:14 pm

What is strange is that if you use any other operator symbol, works perfect. I'm not sure if EA "translates" the query based on the defined database.

I also tried using #DB=ORACLE# macro with no success.

Simon, could you please review if EA is translating the query before sending it to the database?

if you are using the search, see in the result window, the header for the results displays something like "5||5", "double pipe" is used to concatenate strings in Oracle. (I don't know how to upload screen shots to illustrate my point).

Thank you!

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: Eve on August 14, 2018, 10:04:31 am

Your query is being sent to Oracle (or at least the Oracle OLE DB Driver) unmodified.

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: SamSepi0l on August 16, 2018, 11:31:22 pm

 ???
I have setup the connection using ODBC (http://www.sparxsystems.com/enterprise_architect_user_guide/14.0/model_repository/connecttoaoracle9idatarep.html (http://www.sparxsystems.com/enterprise_architect_user_guide/14.0/model_repository/connecttoaoracle9idatarep.html)) and the behavior is the same, and yes, it's very slow opening the project (I'm not going to use it).

In the other hand, If I run the same query in the "Database Builder" in EA, this returns the expected result.

So, then:

• It's not the OLE DB driver because using ODBC I can replicate the error
• Neither the error is because the ODBC driver, because "Database Builder" returns the expected result.

I'm still thinking that the problem is in the "Search".

Thank you.

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: qwerty on August 17, 2018, 12:44:37 am

Consider sending a bug report.

q.

Title: Re: Model Search SQL - behaviour of the plus-character
Post by: SamSepi0l on August 17, 2018, 02:08:46 am

Quote from: qwerty on August 17, 2018, 12:44:37 am

Consider sending a bug report.

q.

Thanks, I did it last week  ;D