Question

FME case sensitivity in a non-case sensitive database ??

lifalin2016
Rank
Userlevel 1
Badge +22

I'm restoring some data from/to a MS/SQL database, which has been stored in a PostGIS database.

PostGIS lowercases all attributes, and I'm getting errors in FME when writing them to the original database, where attribute names are mixed case. The database itself uses collation Danish_Norwegian_CI_AI, i.e. Case Insensitive.

FME is case sensitive, and I presume that's why it throws this kind of error:

Existing table for feature type `PARKER.d_basis_attribs_kontrolflader_traer' does not have attribute with name `tr_bemaerk'. Cannot continue

The database table has an attribute TR_BEMAERK, i.e. as uppercased.

How come FME doesn't respect the case INsensitivity of the target database ?

It feels kinda stupid to have to rename all attributes needlessly, and no, they're all very mixed case, not all uppercase.

(still) using 2020.2

-- Cheers, Lars I.

10 replies

david_r
Rank
Userlevel 4

Did you import the target table structure into FME?

The table collation shouldn't have any bearing on the column or table names.

lifalin2016
Rank
Userlevel 1
Badge +22

This is how you query a case insensitive database to check whether a column exists:

SELECT
	s.[name], T.[name], c.[name], ty.[name]
 
FROM
	sys.[schemas] [s] WITH(NOLOCK)
	INNER JOIN sys.[tables] [t] WITH(NOLOCK) ON T.[schema_id] = s.[schema_id]
	INNER JOIN sys.[columns] [c] WITH(NOLOCK) ON c.[object_id] = T.[object_id]
	INNER JOIN sys.[types] [ty] WITH(NOLOCK) ON ty.[user_type_id] = c.[user_type_id]
 
WHERE
	s.[name] = 'Parker'
	AND
	T.[name] LIKE 'D_BaSiS_attribs_KontrolFlader_TRAER'
	AND
	c.[name] = 'tr_bemaerk'

The result is:

query result

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

Did you import the target table structure into FME?

The table collation shouldn't have any bearing on the column or table names.

I have the attributes from the PostGIS table, which FME won't match to my MS/SQL table.

-- Cheers, Lars I.
david_r
Rank
Userlevel 4

I have the attributes from the PostGIS table, which FME won't match to my MS/SQL table.

There's a good probability that FME double-quotes the column names when doing the insert statements, which means that the column names are case sensitive.

Which means that if you're writing into an existing table, you need the writer attributes to 100% match the destination table. The easiest way to ensure this is to import the feature type from the target database.

david_r
Rank
Userlevel 4

This is how you query a case insensitive database to check whether a column exists:

SELECT
	s.[name], T.[name], c.[name], ty.[name]
 
FROM
	sys.[schemas] [s] WITH(NOLOCK)
	INNER JOIN sys.[tables] [t] WITH(NOLOCK) ON T.[schema_id] = s.[schema_id]
	INNER JOIN sys.[columns] [c] WITH(NOLOCK) ON c.[object_id] = T.[object_id]
	INNER JOIN sys.[types] [ty] WITH(NOLOCK) ON ty.[user_type_id] = c.[user_type_id]
 
WHERE
	s.[name] = 'Parker'
	AND
	T.[name] LIKE 'D_BaSiS_attribs_KontrolFlader_TRAER'
	AND
	c.[name] = 'tr_bemaerk'

The result is:

query result

When you're comparing field values, like above, collation is indeed applied. But collation is not applicable on column or table names referenced in DML statements, such as INSERT.

lifalin2016
Rank
Userlevel 1
Badge +22

When you're comparing field values, like above, collation is indeed applied. But collation is not applicable on column or table names referenced in DML statements, such as INSERT.

These "values" are from the system tables, that inform on schemas, tables, columns etc.

Collations in MS/SQL are much more than text comparisons. As I wrote, we use Danish_Norwegian_CI_AI, which defines the character order, whether or not it's case sensitive, and whether or not we should respect diacretics (?), i.e. special consideration for e.g. our "extra" Danish wovels: Æ, Ø, and Å.

My query just shows how to extract the correct cased names from the system tables, it has nothing to do with my current problem.

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

I have the attributes from the PostGIS table, which FME won't match to my MS/SQL table.

Escaping has nothing to do with case sensitivity. It's a setting on each database.

-- Cheers, Lars I.
david_r
Rank
Userlevel 4

I have the attributes from the PostGIS table, which FME won't match to my MS/SQL table.

Except for in bulk mode, I believe: https://docs.microsoft.com/fr-ch/archive/blogs/yingqin/column-mappings-in-sqlbulkcopy-are-case-sensitive

Try switching off bulk mode on the writer and see if that changes the behavior. It'll be slower, however.

lifalin2016
Rank
Userlevel 1
Badge +22

I have the attributes from the PostGIS table, which FME won't match to my MS/SQL table.

Well, bulk mode is usually the first thing I turn off when I encounter problems ;-)

Unfortunately it doesn't change anything, FME still makes case sensitive comparisons of attribute names, which makes sense, since the error is encountered before the data transfer is commencing.

I ended up creating a work-around, saving the data in a parallel schema, and using T-SQL to transfer the data to the final tables. This works with no case sensitivity involved.

-- Cheers, Lars I.
david_r
Rank
Userlevel 4

I have the attributes from the PostGIS table, which FME won't match to my MS/SQL table.

I completely agree re bulk mode: It's great when it works, but a nuisance when there's a problem. Thanks for sharing your workaround, that's pretty clever.

Reply


Community Stats

28,688
Posts
109,427
Replies
36,543
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.