Integer vs. Number

Classic Servoy
1

Hello,

I have to switch between SQL Server and ORACLE quite a bit recently. One thing seems to get me in trouble there.

In SQL Server most ID colums are Integer columns. Oracle doesn’t know about Integers, so Servoy correctly translates Integer into Number (0) when transferring the repository.

Now my customer works on Oracle and sends me a module of his own, that communicates with my solution. If I import that module into my repository, Number is not translated to Integer. The result is that Servoy complains about key mismatches in relations (Number on one side, Integer on the other).

Could the translation of datatypes be changed to a way where a Number(0) is translated to an Integer if the database knows Integers?

Thanks
Patrick

2

Hello,

is there any information on this?

Thanks
Patrick

3

Huhu? Could anyone comment on this, please?

Thanks!
Patrick

4

Hello anyone? This is quite a problem here! Once you move from for example from SQL Server to Oracle all your integer columns are “converted” to Number and I don’t see a way back.

5

Can you post the MSSQL Server table create statement?, we failed to reproduce this, Integers stay Integers in Servoy in the oracle database they are NUMBER(10,0) which represents an integer in oracle terms

6

The problem is:

You have a solution on MS SQL. You export that and import into Oracle. Integers are (correctly) converted to NUMBER(10,0). Now you create a module on Oracle that works with the tables that come from MS SQL and have been “converted”. You move that module to MS SQL, where new tables of the module are created, but Number(10,0) stays a Number. Now you can’t join between your original solution and the module, because you have integers here and numbers there.

My question was: could a NUMBER(10,0) be converted to an Integer if the database knows what an integer is? I think nobody creates a NUMBER(x,0) on a database that knows integers. By moving between those database types you can destroy your relations quite a bit…

Thanks
Patrick

7

Mmm, Oracle popping up again, ain’t it? :lol:

You’re assumption that people don’t define number type columns if the Db knows integer is not true, at least in Oracle.

In Oracle, integer is an old type, now replaced by Number. We use Number everywhere…

Just my 2 cents,

Paul

8

As far as I know, Oracle NEVER had an integer datatype. And of course, if you don’t have an integer, you use a NUMBER(10,0). The problem only arises when you move between databases that know about integers and ones that don’t. I don’t care how the database stores a 5456 or whatever, but I do care if Servoy tells me that it can’t join between 5456 and 5456. I know it’s not Servoys fault, I am just trying to find a solution. And my suggestion is, convert a NUMBER(10,0) to INTEGER when creating tables on a database that knows integers.

9

I think Servoy should handle this as well during import/export…

Guess Servoy should look at the type of comlun as it is defined in Servoy and then map to the proper, preferred columntype for the DB into which is imported.

As for the Integer type in Oracle: I stand corrected… Oracle has Integer types, but not for columns… :roll:

10

We found the issue of integers becoming number, what happens is:
Sybase Integer “INT”
becomes
Oracle Integer “Number(10,0)” (before restart)
Oracle Decimal “Number(10,0)” (before after servoy restart, as reported by db)
exported to mssql
MSSQL Number “Number”

We changed the relation key check for this (less strict), will be availeble in Servoy 2.2 rc5