BigInt Data type - issue?

  
REF: http://www.outsystems.com/help/integrationstudio/9.0/Reference/SQL_Server_Data_Types_to_OutSystems_Data_Types.htm

If you look at the first line, it shows that
A SQL Server "BigInt" data type is converted to an Outsystems "Integer" Data type.


Now wouldnt this cause an issue?

Last I checked, internally Outsystems "Integer" data type is int32.

What will happen when the SQL Server "BigInt" data type value is greater than 2,147,483,647 ? (the maxmium value of int32) 




Hi Robert,

The help docs state it like that... But this would lead to errors when greater than 2,147,483,647. So, is it really like that? Or maybe it really is using a "Decimal" in OutSystems instead of "Integer" (int32).

In SQL Server 2014, with Transact-SQL, integer constants greater than 2,147,483,647 are converted to the decimal data type, not the bigint data type.
hello tiago,

The issue isn't with the sql server database capability, it outsystems platform limited capability issue.

initially If your sql server value is a bigint data type with value of 1, outsystems will convert bigint to a int32 outsystems data type.

Under the hood outsystems maps outsystems integer datatype as INT32, this solution will work for awhile, until your database size exceeds 2,147,483,647 (int32 maximum value)

Once this limit is reached, there wouldnt be an issue with the database server, the tables columns have been assigned with bigint. But now you will have a issue with outsystems int32 conversation, the conversion wouldnt work, a value that exceeds 2,147,483,647 is out of range for int32.

Now we have the same problem as before for all of outsystems integer data type, for large SaaS apps, the int32 value will be exceeded within hours, days, weeks, or months, however most enterprise apps it will not face any issues for a number of years. issues - still an issue but just wouldnt see it right away - or never will face the issue at all.
Robert,

If OutSystems states support for BigInt, but at the same time it converts data type to Integer (int32)... that's the same as really not supporting BigInt, imho.
Tiago

Int64 maps directly to bigint SQL Server data type.

However the last time I checked there is no bigint support, outsystems uses "int" for it's integer data type variables, int is is 32 bit value, once this limit is exceeded, It will cause a stack overflow!  
In that case, there should be a note in Integration Studio's help docs alerting for that.

OS maps SQL Server BigInt to OS Integer, which is to say that if BigInt > 2,147,483,647 OR BigInt < -2,147,483,648 there will be an exception: stack overflow.
Solution
Hi,

Thank you for the feedback.

In Integration Studio, the SQL Server data type BigInt is converted by default to Integer, which internally is represented by an int.

We believe this is enough for most cases, but you are right and it might not be enough for some cases. In those cases where it is not enough, you will get a runtime error if your application needs to use a number higher than 2,147,483,647, when this number comes from an external entity attribute that has been mapped to an Integer.

However, you can always manually change the mapping in Integration Studio to a Decimal, which will solve these limitation problems and allow you to use larger numbers, if you really need them. This, of course, comes at the one-time cost of changing the applications that use these entitiy attributes to deal with decimals.

We understand that having support for 64-bit integers is important. Priorities tradeoffs are many times conflicting, but they drive our product roadmap decisions - where roadmap implies what we do first, what we relay for later, or what we actively and consciously choose not to do. This topic falls into the 2nd category.

Having said all that, we are already looking at supporting 64-bit integers in the platform.

We will keep you posted and provide more details on this topic when we can disclose that information.
Solution
Hi Ricardo,

Thanks for sharing. It suits you well this kind of feedback. I think it's worth mentioning that.
I will give feedback on that help page indicating there should be some note on this subject.

Cheers
Ricardo Marques wrote:
We believe this is enough for most cases, but you are right and it might not be enough for some cases.  
 
It is enough for mosts enterprise apps! most enterprise apps would not reach 2 billion records anyways.

BigInt/Int64 is in the 2nd categorised for outsystems (these are SaaS/Cloud service providers), using outsystems integer (i.e int32) will evenutally cause issues! 100% known.

It can be solved with a work around, by using GUID, however it will cost the developer too much time to implement and maintain a non supported data type (in a large app, using GUID will cost hundreds of hours to implement, you will spend ~5 to 15 minutes per table, this is because GUID is not outsystems native data type, you can not simply assign a primary key with GUID data type, by selecting it from the drop down list like integer or text)

Ideally you would want to use natively support outsystems data type such as integer with auto increment, (this will save you alot of time!)

GUID has problems of its own

  • GUID inserts/updates performance are slower than int.
  • JOIN performance are slower than int
  • Page fragmentation is higher than int
  • Storage is 4 times higher than int
  • Memory usage is larger than int

 
Best to use integer when you can because... outsystems is already said their going to fix the int issue! (When you do end up fixing the int32 issue, please contact me and we will have the regular meetings, calls, email sections to fix this software design issue ).

 

Dear community, 
 
As always thank you for your comments.
 
As mentioned by my colleague Ricardo, we are already looking at supporting 64-bit integers in the platform. In fact we started engaging with some customers to enroll in a private beta program regarding this capability. Nevertheless we believe it may be interesting to open this program to community users.
 
If you'd like to enroll in this beta program, please send me a private message (just click my name in this post to navigate to my profile page and then click the "SEND PRIVATE MESSAGE" button).
 
We'll then followup with those who may show interest. We'll choose the candidates to enroll in this program taking into consideration several factors (e.g.: what are the specific use cases / scenarios described by the user, what is the previous experience of the user with the platform, what is our own capacity to give a good support, etc.).
 
Thank you again,
  António
 
Antonio, I have requested bigint, int64 since 2009 and have a valid use case, outsystems has always been aware of the int32 limitations! so yes sign me up! :)
António Melo wrote:
we are already looking at supporting 64-bit integers in the platform. In fact we started engaging with some customers to enroll in a private beta program regarding this capability. 

I can see from the new REST Expose documentation, outsystems is hinting that it has already made the decision to use  int64 variables!? here we see outsystems started to use "long". Long is an int64 alias in c# (Signed 64-bit integer)



unless outsystems made a mistake here? (it should be defined as integer but its not?.. OS is already well prepared and ready for the future?! :) ).
Robert,
I can't see the image, it gives a 403 forbidden error from googleusercontent
Tiago Neves wrote:
Robert,
I can't see the image, it gives a 403 forbidden error from googleusercontent
updated:
 
 
Hi Robert,

Nice catch... but it's actually a bug in in the swagger-ui side that displays "integer" types as "longs" if they are not explicitly set to either "int32" or "int64" in the service specification.


Regards,
João Rosado
When you use entity identity in a query, outsystems define the entity identity as integer (i.e int32).
When you use entity identity in an json, outsystems define the entity identity as long (i.e int64).

This might be a bug, same data type, with two different definations!
Yup :) noticed that as well.

They are not consistent in the fallback when the int32/int64 format is not defined.
I just changed our code to allways specify it, does the trick:



Regards,
João Rosado
joao, if you change it to integer, that would mean it's int32. what will happen when outsystems supports int64? it will change once again to long?
you should actually cast the entity integer identify as a string value! that way you can support int32 right now and int64 in the future. Besides this JavaScript has issues with int64 values, it only support int 54 bits! not int64 bits, the way around this issue is to cast int as string values,please check Facebook api and twitter api. Twitter also outlines the issue here https://dev.twitter.com/overview/api/twitter-ids-json-and-snowflake hope that helps! This should be a sufficient reason for outsystems to cast integer as string :) (avoid all future issues right now, instead of creating a mess later)
could you add a property to the structure attribute, to cast integer as string for rest Json output. (available options yes, no)
Ricardo Marques wrote:
Hi,

Thank you for the feedback.

In Integration Studio, the SQL Server data type BigInt is converted by default to Integer, which internally is represented by an int.

We believe this is enough for most cases, but you are right and it might not be enough for some cases. In those cases where it is not enough, you will get a runtime error if your application needs to use a number higher than 2,147,483,647, when this number comes from an external entity attribute that has been mapped to an Integer.

However, you can always manually change the mapping in Integration Studio to a Decimal, which will solve these limitation problems and allow you to use larger numbers, if you really need them. This, of course, comes at the one-time cost of changing the applications that use these entitiy attributes to deal with decimals.

We understand that having support for 64-bit integers is important. Priorities tradeoffs are many times conflicting, but they drive our product roadmap decisions - where roadmap implies what we do first, what we relay for later, or what we actively and consciously choose not to do. This topic falls into the 2nd category.

Having said all that, we are already looking at supporting 64-bit integers in the platform.

We will keep you posted and provide more details on this topic when we can disclose that information.
@Ricardo Marques
The problem with changing the mapping in Integration Studio to a decimal, is that we loose the ability to have keys to that field...

@António Melo
Is there any news about the int64 support? How can we use it?

Best regards,
PC

Hi,

Starting with the upcoming version of P9 (Bali), we are changing some of the mappings and bigints will become mapped to Long Integer.

The expected GA date for such functionality is November 17, 2015.
Awesome, thx for the fast answer Ricardo Marques

Best regards,
PC