2860
Views
16
Comments
Maximum size for text data type
Question

Hey!

I was trying to store JSON strings using text data type. It will be helpful if I could know what is the maximum size of text data type in outsystems?

mvp_badge
MVP
Rank: #89

Hi Shashankit Thakur,


After doing some search for this topic I found this community discussion and also please read out the João Batista reply. Hope this discussion will be a help for your query. 


Kind Regards,

Benjith Sam


Rank: #414

Shashankit Thakur wrote:

Hey!

I was trying to store JSON strings using text data type. It will be helpful if I could know what is the maximum size of text data type in outsystems?

Hi Shashankit Thakur,

It depends on the database which you are using. for example if you are using SQL server 12 the max length is 4000 Character.


mvp_badge
MVP
Rank: #15

Hi,

For strings bigger than 2000 outsystems uses nvarchar(max). 

The max size for a column of type NVARCHAR(MAX) is 2 GByte of storage.

Since NVARCHAR uses 2 bytes per character, that's approx. 1 billion characters

Regards,

Marcelo

Rank: #6177

Neither this thread, nor the one linked above, answers the question!


Outsystems may USE varchar(max), but if you set it to 4000, would that still limit it to 4000 characters?

mvp_badge
MVP
Rank: #15

Hi Nathan,

Yes Outsystems will limit it.

Regards,

Marcelo

Rank: #6177

So if you want the maximum space, what length value do you use... the max value for an int is 2,147,483,647, so can we just whack in 999999999 (999 million etc)

mvp_badge
MVP
Rank: #15

Hi Nathan,

Never tried it before but in theory it should work. If you end up doing the experiment share with us the result.

Regards,

Marcelo

Rank: #19735

In case anyone's interested, we gave it a spin so we could store the diff snapshot of a large form. It took 15 minutes to publish and there's no abort button so we're currently considering a different solution...

mvp_badge
MVP
Rank: #18

Hi,

We do storage of large JSON, or XML strings by defining the attribute data type as Binary. Then using TextToBinary from the Binary espace to convert the text prior to storing it, and BinaryToText to convert it back to a text after reading it. Now you do not have to worry about size property of the attribute. You could decide to redesign your entity and create a 1 on 1 entity just to store the binary separate from the other attributes of your entity, with the primairy key set to the identifier of your original table. 

Ideally OutSystems should support the JSON data type natively for an entity attribute. There is already an idea for that that you can vote for.

https://www.outsystems.com/ideas/5310/JSON+datatype+in+Outsystems?IsFromAdvancedSearch=True

Regards,

Daniel

Rank: #386

Hi All,

In my case as Ferreira said, I have increased text length to 2500 and OutSystems / SQL Server automatically created nvarchar(max) in database.


Sravan


Rank: #8147

Hi All, 

For a Text input parameter in a Process I'm having the error "String or binary data would be truncated. " at the moment of launching the process. 

This is the fragment of the stacktrace which reflects where is the problem launching the process:

String or binary data would be truncated.
The statement has been terminated.
at OutSystems.HubEdition.RuntimePlatform.Processes.ProcessBase.ThrowSpecificException(String failureMessage)
   at OutSystems.HubEdition.RuntimePlatform.GenericExtendedActions.ProcessLaunch(HeContext heContext, ObjectKey SSKey, ObjectKey espaceSSKey, Int32 parentActivityId, Int32 parentProcessId, List`1 inputs, List`1& nextActIds)


There is an additional constraint for the Text data type length when used as input parameter for a Process? the parameter in question had 3486 characters length in this issue.


Thanks in advance

mvp_badge
MVP
Rank: #15

Hi Roberto,

Yes there is a constraint . the limit is 2000. since processes are asynchronous that value is saved in the database and the field is a nvarchar(2000).

Regards,

Marcelo 

Rank: #8147

Thank you Marcelo for your quick response, I've already solved the problem by creating a record in DB and passing the identifier to the process.


Regars, 

   Roberto

mvp_badge
MVP
Rank: #128

Hi,


Please refer below best practice url,

https://success.outsystems.com/Documentation/Best_Practices/Performance_and_Monitoring/Performance_Top_10_Rules


Isolate large text and binary data

Avoid the use of 2000+ characters in a text field; data fields greater than 2000 bytes are converted into a Text data type which in turn are much slower to process and decrease performance overall. As a rule, isolate binary and/or large text fields in separate entities and only retrieve them when they are strictly necessary.

I would also look into the possibility to store these large texts or binaries outside the database. Although storing these is something a database can do you should really ask yourself the question if that is the best place to store it. Storing it in the database will cost performance, is not cost efficient and will not support a lazy-load experience. Perhaps storing it in a Azure Storage Account or an AWS S3 is a much better solution since that is a lot cheaper and more suitable for storing large amount of data.