Saving unicode characters with a DynamicForm

[edulid]

I am trying to support Unicode characters in my forms, but this is only working for some characters.
I discovered that the problem is the MSSQL DB I am using.
If I update the field using:
update t_schueler set f_field = 'ě'
the character is converted to 'e'. So that is why SmartGWT shows me an 'e' when updating this field (instead of 'ě').

If I update the field using:
update t_schueler set f_field = N'ě'
(note the "N"), then the field is saved correctly and smartgwt also shows the correct value.

But the problem is I need a smartGWT DynamicForm to update the values. How to get SmartGWT to use the "N" variant when saving the DynamicForm? Or something similar?

If this is not possible, how to save the value 'ě' using a DynamicForm?

In order to test, here is my testcase: (it is only a dynamicForm which adds values to the table). If you type ě, and push "Add", you will see that it is converted to e.

public class BuiltInDS implements EntryPoint {

	ListGrid lg = new ListGrid();

	public void onModuleLoad() {

		final DynamicForm singleFieldForm = new DynamicForm();
		TextItem formItem = new TextItem("f_name", "Name");
		singleFieldForm.setFields(formItem);
		singleFieldForm.setDataSource(DataSource.get("table"));

		VLayout vlayout = new VLayout();
		vlayout.setHeight100();
		vlayout.setWidth100();
		vlayout.addMember(singleFieldForm);

		final IButton button = new IButton("Add");
		button.addClickHandler(new ClickHandler() {

			@Override
			public void onClick(ClickEvent event) {
				singleFieldForm.saveData(new DSCallback() {

					@Override
					public void execute(DSResponse dsResponse, Object data,
							DSRequest dsRequest) {
						SC.say("Correctly saved");
					}
				});
			}
		});

		ListGrid lg = new ListGrid();
		ListGridField idField = new ListGridField("f_schueler_id");
		idField.setHidden(true);
		ListGridField nameField = new ListGridField("f_name", "Name");
		lg.setFields(nameField);
		lg.setHeight100();
		lg.setWidth100();
		lg.setDataSource(DataSource.get("table"));
		lg.setAutoFetchData(true);
		lg.setSortField("f_name");

		vlayout.addMember(button);
		vlayout.addMember(lg);

		vlayout.draw();
	}

<DataSource ID="table" serverType="sql" tableName="t_schueler" 
	 >

	<fields>
		<field name="f_schueler_id" type="sequence" primaryKey="true" />
		<field name="f_name" type="text" required="true" />
		
	</fields>
</DataSource>

Using smartGWT 4.1p 2014-09-06 and MSSQL 2014. The field "f_name" is of type nvarchar.

[Isomorphic]

This is a known SQLServer issue covered in the docs - take a look at dataSourceField.sqlStorageStrategy and note the discussion of setting sqlStorageStrategy / field.type to "ntext".

[edulid]

Thank you, the 'ntext' type works well.

Is it any way to have a listGrid filter which ignores the special characters?
For example, when filtering on 'c', so that it matches the characters 'c', 'č', 'ć', etc.

In MSSQL I would write some query like
... WHERE Name = 'c' COLLATE Latin1_General_CI_AI

Is there any smartGWT-way of achieving this?

[Blama]

Hi edulid,

I don't think the standard operators for Criterion will help you there.
You could use a SQL Server Computed Column applying your normalisation, add that column as hidden column to you .ds.xml and have the ListGridField produce Criteria for that column instead of the normal one.

This is for the case: DB="č", search="c".
For the other way around: DB="c", search="č" you should be able to handle it with just the ListGridField.

Best regards,
Blama

[edulid]

Thank you Blama for the ideas.

since this is a MSSQL-specific issue, maybe there is some simple workaround, similar as the 'ntext' type?

[Blama]

Hi edulid,

I don't think it is an "issue" (neither MSSQL or in general). You are using a Unicode charset with Unicode characters and want to compare against a normalized version. So IMO it is more of an use case. But let's see what Isomorphic says. Perhaps it is possible.

After thinking about it, my suggestion should also be possible without DB model changes if you can call your MSSQL normalisation function from a SmartGWT customSelectExpression in your ds.xml. It still involves the changes I mentioned to your code, but no changes to the data model.

Keep in kind that both versions (customSelectExpression or Computed Column) both interfere with existing indexes. If that is a concern for you, you'll have to create the indexes for the new column as well.

Best regards,
Blama

[Isomorphic]

We don't currently have a flag that would cause the "COLLATE.." SQL expression to be generated. There may be settings you could apply in your database so that the behavior of that "COLLATE.." phrase is effectively the default, or you could use SQL Templating settings such as dataSourceField.customSelectExpression to add it selectively.

If this was used however, note that client-side filtering will not agree with server-side filtering, and would possibly need to be disabled, which would have significant performance consequences.