Author Topic: Model Search SQL - behaviour of the plus-character  (Read 1696 times)

Bert Malliet

  • EA Novice
  • *
  • Posts: 5
  • Karma: +0/-0
  • Belgian Railways IT - functional analyst
    • View Profile
Model Search SQL - behaviour of the plus-character
« 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 ...  ;)
« Last Edit: January 14, 2017, 12:03:32 am by Bert Malliet »
Bert

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 8482
  • Karma: +207/-26
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Model Search SQL - behaviour of the plus-character
« Reply #1 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

qwerty

  • EA Guru
  • *****
  • Posts: 9692
  • Karma: +176/-150
  • I'm no guru at all
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #2 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.

Bert Malliet

  • EA Novice
  • *
  • Posts: 5
  • Karma: +0/-0
  • Belgian Railways IT - functional analyst
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #3 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.
« Last Edit: January 16, 2017, 08:45:26 pm by Bert Malliet »
Bert

SamSepi0l

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #4 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!

Simon M

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 6453
  • Karma: +55/-6
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #5 on: August 10, 2018, 09:08:37 am »
One of your numbers is probably a string datatype in the database.
Simon

support@sparxsystems.com

SamSepi0l

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #6 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.

qwerty

  • EA Guru
  • *****
  • Posts: 9692
  • Karma: +176/-150
  • I'm no guru at all
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #7 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.

Simon M

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 6453
  • Karma: +55/-6
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #8 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)
Simon

support@sparxsystems.com

SamSepi0l

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #9 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!

Simon M

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 6453
  • Karma: +55/-6
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #10 on: August 14, 2018, 10:04:31 am »
Your query is being sent to Oracle (or at least the Oracle OLE DB Driver) unmodified.
Simon

support@sparxsystems.com

SamSepi0l

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #11 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) 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.

qwerty

  • EA Guru
  • *****
  • Posts: 9692
  • Karma: +176/-150
  • I'm no guru at all
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #12 on: August 17, 2018, 12:44:37 am »
Consider sending a bug report.

q.

SamSepi0l

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: Model Search SQL - behaviour of the plus-character
« Reply #13 on: August 17, 2018, 02:08:46 am »
Consider sending a bug report.

q.

Thanks, I did it last week  ;D