Integration issue with "Nchar" data types (Sql Server - Azure)

Integration issue with "Nchar" data types (Sql Server - Azure)

  

Hello all,

I have a web application that I built on top of an Azure SQL Database. So I created an extension to connect to this database and built my application on top of it without problems. 

The only annoying thing that I found is while I'm updating text fields in my web application. For some reason, the text fields being pulled from my aggregates always shows "white spaces" at the end of my text (see an example below). 

This issue doesn't happen while I'm pulling data directly from a platform created database/table.

I'm also attaching more information about the field mapping configuration for this specific field just for you to take a look (the issue happens with all "text" fields from my Azure SQL Database - and not only with this one called "ContaNome").

 

 

Bottom line is that I'm able to workaround the issue this way described below (which I don't think is the most elegant way and probably there is a better one)

1. Created a "trimmed" version of the field through the aggregate that is pulling the data (preparation of myscreen page) : TrimmedContaNome =Trim(Tb_Conta.ContaNome)

2. Mapped this new trimmed field to my web page "input" field variable: TbContaForm.Record.Tb_Conta.TrimmedContaNome

3. During the update/savescreen action, I assign the trimmed input field variable w/ the updated user input back to the original field and then I call the table "Update/CreateServer Action"

While this work around worksfine, I would like to not have this "white spaces" issue on the first place.

Did you guys already have this integration issue with SQL server NChar fields before? Or do you have a better/more elegant way to show the input fields in the web page without this annoying "white spaces" filling the entire input field?

I appreciate some help from the "Experts" who works w/ Database Integrations :)

Many thanks, Daniel

 

Solution

Hello,

The NChar data type is NOT trimmed by the database when returning content and being fixed length, it is padded with spaces when inserted in database.

If you don't want to have to use trim, the only option is to change the column data type to nvarchar, for example.

Cheers, 

Eduardo Jauch

Solution

Eduardo Jauch wrote:

Hello,

The NChar data type is NOT trimmed by the database when returning content and being fixed length, it is padded with spaces when inserted in database.

If you don't want to have to use trim, the only option is to change the column data type to nvarchar, for example.

Cheers, 

Eduardo Jauch

Jesus! I don't believe I didn't check these sql server side particularities !! 

Many thanks @Eduardo Jauch! I changed the data type in my database and it solved the problem!! 

Thank you! :)