Skip to main content
  • EN

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.

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 

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 odbo].yGraveforespørgsselssvar] SET etype] = 'igangværende' where Âid] = 'E4A4D3BA-B1C1-4A7E-A992-9F60ADE0149D''. Provider error `(-2147217865) Invalid object name 'dbo.Graveforespørgsselssvar'.'

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

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 eid] = '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?

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.

@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?

@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

@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

Reply


Community Stats

30,190
Posts
114,572
Replies
37,851
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.