Find() doesn't return record while SQL_query does

Classic Servoy
1

Hi,

I have the following situation where I have to do a search on a field that contains a special character.
The string that I’m looking for contains a non plain text character. I can’t even show it here in this text, so I’ll show a screenshot:

[attachment=1]Capture1.JPG[/attachment]

As you can see the quotes around Provinciaaltje are special qoutes.

My coding is very simple:

_fs_structure.find()
_fs_structure.structurename = _article_subgroup
_fs_structure.search(true, false)

If no record found, then a new record is added to the database

if ( _fs_structure.getSize() == 0)
{
	_fs_structure.newRecord()
	_fs_structure.structurename = _article_subgroup

      databaseManager.saveData()
}

The record is really in the database, when I do SQL-query in query tool:

[attachment=0]Capture2.JPG[/attachment]

What strange is, is that the quotes are replaced by ? in the output. But you can see that I used correct quotes in the query.

The problem is that when I run for the second time my script, the find() should return getSize() of 1 and not zero. Now it returns zero, so a newRecord() is done again.
Since structurename is a unique name I get duplicate indexvalue from my database.

How can I solve this situation?

Martin

Capture2.JPG
Capture2.JPG692×236 25.5 KB

Capture1.JPG
Capture1.JPG213×123 7.2 KB

2

if you do the same insert but then without those special qoutes it does work?

3

Yes it works like expected without the special quotes.

I’ve made some other tests.

I changed the type of the field from varchar() to nvarchar()

Now it seems to work within Servoy, but now SQL-Server doesn’t recognize the query, but that is for the moment less important

4

ahh yes sql server only seems to work with unicode chars if the column is of type nvarchar