Question

SqlExecutor not respecting attribute encoding ?

  • 13 July 2020
  • 8 replies
  • 10 views
lifalin2016
Rank
Userlevel 1
Badge +22

Hi.

I'm trying to use SqlExecutor to update a table in my MS/SQL database. The table has a Danish character in it's name (Graveforespørgselssvar), but the database has collation Danish_Norwegian_CI_AI, so that's perfectly acceptable.

But FME fails, because it mangles the name as UTF-8 (Invalid object name 'dbo.Graveforespørgsselssvar').

I then tried to instead add the complete command as an attribute, and use AttributeEncoder to encode this attribute to both "Latin-1 Western European (iso-8859-1)" and "Windows Latin-1 (windows-1252)", both of which do contain all Danish characters, but it still yields the same error.

Why do FME insist on sending SQL commands as UTF-8 even though both the attribute encoding and the database collation dictates otherwise ?

It looks like a severe bug to me.

-- Cheers, Lars I.

8 replies

ebygomm
Rank
Userlevel 1
Badge +21

What version of FME? Are you able to share the sql?

I am able to update a table called Graveforespørgselssvar via an sqlexecutor with no issues

e.g.

UPDATE DBO."Graveforespørgselssvar"
SET TOWN = 'Nottingham'

ø is in utf-8 characterset anyway, you get a Ã¸ if it's mistakenly interpreted as 8bit encoded iso-8859-1 

lifalin2016
Rank
Userlevel 1
Badge +22

What version of FME? Are you able to share the sql?

I am able to update a table called Graveforespørgselssvar via an sqlexecutor with no issues

e.g.

UPDATE DBO."Graveforespørgselssvar"
SET TOWN = 'Nottingham'

ø is in utf-8 characterset anyway, you get a Ã¸ if it's mistakenly interpreted as 8bit encoded iso-8859-1 

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement.  Query Text `UPDATE [dbo].[Graveforespørgsselssvar] SET [type] = 'igangværende' where [id] = 'E4A4D3BA-B1C1-4A7E-A992-9F60ADE0149D''. Provider error `(-2147217865) Invalid object name 'dbo.Graveforespørgsselssvar'.'

-- Cheers, Lars I.
lifalin2016
Rank
Userlevel 1
Badge +22

What version of FME? Are you able to share the sql?

I am able to update a table called Graveforespørgselssvar via an sqlexecutor with no issues

e.g.

UPDATE DBO."Graveforespørgselssvar"
SET TOWN = 'Nottingham'

ø is in utf-8 characterset anyway, you get a Ã¸ if it's mistakenly interpreted as 8bit encoded iso-8859-1 

Using 2020.0 x64 build 20252

-- Cheers, Lars I.
ebygomm
Rank
Userlevel 1
Badge +21

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `UPDATE [dbo].[Graveforespørgsselssvar] SET [type] = 'igangværende' where [id] = 'E4A4D3BA-B1C1-4A7E-A992-9F60ADE0149D''. Provider error `(-2147217865) Invalid object name 'dbo.Graveforespørgsselssvar'.'

Missed that it was SQL Server - i know with Oracle if the table name contains characters like ø it is necessary to enclose the table_name in quotes for it to work. I'm not sure if something similar maybe required for SQL Server?

lifalin2016
Rank
Userlevel 1
Badge +22

What version of FME? Are you able to share the sql?

I am able to update a table called Graveforespørgselssvar via an sqlexecutor with no issues

e.g.

UPDATE DBO."Graveforespørgselssvar"
SET TOWN = 'Nottingham'

ø is in utf-8 characterset anyway, you get a Ã¸ if it's mistakenly interpreted as 8bit encoded iso-8859-1 

As for UTF-8, I know how it works. But if I encode an UTF-8 string to iso-8859-1, "ø" is represented by as single char. I want FME to send this single char as-is to MS/SQL without it automatically encoding it back to UTF-8, which is what I see happening.

-- Cheers, Lars I.
steveatsafe
Rank
Badge +11

@lifalin2016, can you see if this is reproducible with 2020.1 (b20596) and possibly try the JDBC format in the SQLExecutor to see if that might help?

SteveatSafe
lifalin2016
Rank
Userlevel 1
Badge +22

@lifalin2016, can you see if this is reproducible with 2020.1 (b20596) and possibly try the JDBC format in the SQLExecutor to see if that might help?

I just installed 2020.1, and the problem remains (even got worse) with the writer. The good news is that now SQLExecutor gets it right, it doesn't do any interpretation, but passes the command on as-is to the database.

I sent a new test workspace to support to exemplify this.

Writing to problematic named MSSQL table.fmwt

-- Cheers, Lars I.
lifalin2016
Rank
Userlevel 1
Badge +22

@lifalin2016, can you see if this is reproducible with 2020.1 (b20596) and possibly try the JDBC format in the SQLExecutor to see if that might help?

Hi @steveatsafe. I'm a little curious.

I just checked the workspaces in which I had severe problems in 2020.0, expecting to see that all the SQLExecutor transformers needed to be updated. But no.

The transformers are all version 8, and if I create a new workspace and insert a SQLExecutor, it's also version 8.

Does this mean that the obvious changes, that fixed the problem, was not done in the transformer part, but somewhere closer to the core ?

If so, why aren't the writers fixed too ?

Cheers

Lars

-- Cheers, Lars I.

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.