Skip to main content
Question

Updating MS/SQL woes and case super-sensitivity ?

lifalin2016
Contributor
Forum|alt.badge.img+29

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

-- Cheers, Lars I.

5 replies

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • October 4, 2018

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.

david_r
Celebrity
  • david_rCelebrity
  • October 4, 2018

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 ;-)

lifalin2016
Contributor
Forum|alt.badge.img+29
  • lifalin2016Contributor
  • Author
  • Contributor
  • October 5, 2018
david_r wrote:

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 :-)

 

 

-- Cheers, Lars I.
lifalin2016
Contributor
Forum|alt.badge.img+29
  • lifalin2016Contributor
  • Author
  • Contributor
  • October 5, 2018
gio wrote:

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.

 

 

-- Cheers, Lars I.
lifalin2016
Contributor
Forum|alt.badge.img+29
  • lifalin2016Contributor
  • Author
  • Contributor
  • October 5, 2018
david_r wrote:

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.

 

 

-- Cheers, Lars I.

Reply


Helpful Members This Week

Community Stats

31,148
Posts
118,081
Replies
38,767
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.