Skip to main content
  • EN

Hi.

I know that FME is case sensitive per se, but I've just had it report some curious errors in an UPDATE operation.

Apparently 5 fields with similar names were spelled differently case-wise in a database that's case-insensitive. E.g. one field was "ImageFile", another "Imagefile1". Renaming the attributes fixed that of course.

Before that, it erred because I attempted to update a field with type "varchar(250)" with a value (created in AttributeManager) that FME determined to be "varchar(200)". Yes, really.

And lastly, FME erred because there were extra attributes in the feature, that aren't in the database table. Instead of just ignoring them, which really ought to be an option. Removing them solved this issue.

Updating a larger field with a smaller value should also just be accepted, and not throw an error.

And FME already prompts the target database for all sorts of information before pushing data into it. Would it then be too much to ask to have it also check the database's case setting using "sys.[databases]" and "sys.[fn_helpcollations]()", and ignore field name case if it's case insensitive ?

Just my Thursday woes.

Cheers

Been using these writers in all kind o manners for quite a while, never had any such woes you name.

You have to take care to what you try to write to tables, seems obvious to me.

Personally, I think the most important thing to do when writing to an existing database table is to never type in the field names or datatypes yourself in FME.

Let FME do the heavy lifting for you and import the feature type definitions from the database. That is really the only way to ensure that things run smoothly, in particular if there are table or field names that don't conform to "fool proof" database naming conventions, meaning 7-bit ASCII letters, numbers and underscore only.

My general recommendation, if possible, is to only use lower case a-z plus underscore and numbers for databases defaulting to lower case names, e.g. Postgresql, and only use upper case A-Z plus underscore and numbers for databases defaulting to upper case, e.g. Oracle. Please don't use mixed case, international characters or spaces unless you want to make enemies or your own life harder than necessary ;-)

Personally, I think the most important thing to do when writing to an existing database table is to never type in the field names or datatypes yourself in FME.

Let FME do the heavy lifting for you and import the feature type definitions from the database. That is really the only way to ensure that things run smoothly, in particular if there are table or field names that don't conform to "fool proof" database naming conventions, meaning 7-bit ASCII letters, numbers and underscore only.

My general recommendation, if possible, is to only use lower case a-z plus underscore and numbers for databases defaulting to lower case names, e.g. Postgresql, and only use upper case A-Z plus underscore and numbers for databases defaulting to upper case, e.g. Oracle. Please don't use mixed case, international characters or spaces unless you want to make enemies or your own life harder than necessary ;-)

Hi David.

 

True, but in this case I need to first merge 5 columns into a single one, and then later explode them back into 5 columns for the update. So no point'n'click unfortunately :-)

 

 

Been using these writers in all kind o manners for quite a while, never had any such woes you name.

You have to take care to what you try to write to tables, seems obvious to me.

As I wrote, the database is case INsensitive, so case is inherently unimportant in e.g.. field names. At least within MS/SQL itself. The case sensitivity issue here is all FME. I just propose that FME adapt and respect the setting of the data sources and targets.

 

 

Personally, I think the most important thing to do when writing to an existing database table is to never type in the field names or datatypes yourself in FME.

Let FME do the heavy lifting for you and import the feature type definitions from the database. That is really the only way to ensure that things run smoothly, in particular if there are table or field names that don't conform to "fool proof" database naming conventions, meaning 7-bit ASCII letters, numbers and underscore only.

My general recommendation, if possible, is to only use lower case a-z plus underscore and numbers for databases defaulting to lower case names, e.g. Postgresql, and only use upper case A-Z plus underscore and numbers for databases defaulting to upper case, e.g. Oracle. Please don't use mixed case, international characters or spaces unless you want to make enemies or your own life harder than necessary ;-)

And as for choosing the names, it has been chosen beforehand, it's not up to me to name the fields.

 

 

Reply


Community Stats

29,723
Posts
112,941
Replies
37,376
Members
Terms & ConditionsCookie settings