Variable Length String for Database?

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

Variable Length String for Database?

Postby john1598360627 » Thu Oct 03, 2024 2:55 am

PROBLEM
I want to make a Database column that is a large size to accommodate for an HTML Editor value. Something where the user could right up a full review of a subject matter.

In the Servoy Database manager, when you create a column with the 'String' datatype it is an 'nvarchar(x)' with the initial length value of 50 (but it can be changed to another number).

My question is about having a variable size. Because I would like to set a huge max size value, but have the actual size change based upon how big the String value actually is.

HELP
Would it be okay to use 'nvarchar(max)' and not worry about the huge size of it? Or just setting a huge number size limit for the String value type? Like is there any downside to having like 12 columns all with huge or max sizes? Will it cause instability? I want to be sure to avoid any issues like that.

Or, is there a way of implementing a Variable size for a String value? Like would the Media datatype allow for that? I'm just trying to think of a way of avoiding overloading the database server per-emptively.
john1598360627
 
Posts: 187
Joined: Tue Aug 25, 2020 3:03 pm

Re: Variable Length String for Database?

Postby john1598360627 » Thu Oct 03, 2024 3:11 am

And not to answer my question immediately I suppose :D , but for comparison this is what Microsoft suggests.


https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN

If you use nchar or nvarchar, we recommend that you:
  • Use nchar when the sizes of the column data entries are consistent.
  • Use nvarchar when the sizes of the column data entries vary considerably.
  • Use nvarchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 4,000 byte-pairs.


So nvarchar(4000) might be enough for my use case.

But again, I'm curious if there's anything specifically that Servoy differs from this 'official' suggestion.
john1598360627
 
Posts: 187
Joined: Tue Aug 25, 2020 3:03 pm

Re: Variable Length String for Database?

Postby mboegem » Thu Oct 03, 2024 8:19 am

Hi John,

What to do and what to avoid does depend on the type of database.
In general you do want to keep the size of a database row as small as possible, the more data, the more time it takes to load the data.
From that point of view, it is sometimes better to separate the metadata and large content into 2 separate tables.
For example keep all product data in 1 table, but the product images in a separate.

Concerning Servoy side of things:
When you need a large text-field (similar to media-type) you can just create a text-field with size '-1'.

Hope this helps.
Marc Boegem
Solutiative / JBS Group, Partner
Servoy Specialist
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image
User avatar
mboegem
 
Posts: 1800
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Variable Length String for Database?

Postby john1598360627 » Fri Oct 04, 2024 11:36 pm

mboegem wrote:Hi John,

What to do and what to avoid does depend on the type of database.
In general you do want to keep the size of a database row as small as possible, the more data, the more time it takes to load the data.
From that point of view, it is sometimes better to separate the metadata and large content into 2 separate tables.
For example keep all product data in 1 table, but the product images in a separate.

Concerning Servoy side of things:
When you need a large text-field (similar to media-type) you can just create a text-field with size '-1'.

Hope this helps.

Yeah these large database columns are in a separate table entirely.


Basically I'm trying to figure out how to make a TEXT database column have a dynamically allocated size. From what I'm understanding here, setting an nvarchar to MAX will do so (which is the TEXT datatype in Servoy).... How do I set that 'max' value in Servoy itself? I can't type in 'max'' so do I just put -1' ?
john1598360627
 
Posts: 187
Joined: Tue Aug 25, 2020 3:03 pm

Re: Variable Length String for Database?

Postby mboegem » Sun Oct 06, 2024 9:46 am

Yes '-1', that's all
Marc Boegem
Solutiative / JBS Group, Partner
Servoy Specialist
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image
User avatar
mboegem
 
Posts: 1800
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Variable Length String for Database?

Postby john1598360627 » Mon Oct 07, 2024 7:01 pm

mboegem wrote:Yes '-1', that's all


Roger!

... Seems to have worked!


Much easier typing '-1' than '214783647' that's for sure. :P
john1598360627
 
Posts: 187
Joined: Tue Aug 25, 2020 3:03 pm


Return to How To

Who is online

Users browsing this forum: No registered users and 1 guest