Question

Overrule dynamic writer

  • 3 November 2014
  • 5 replies
  • 3 views
jelle
Rank
Badge +11
Hi FME'ers, 

 

 

we are converting an Oracle DB to MS SQL (GeoMedia Warehouse). In total, we have a few hundreds of tables, all having a different table structure. 

 

A dynamic writer does the work pretty well. We don't have to define each table individually.

 

 

However, a field defined 'between' NUMBER(5) and NUMBER(10) in Oracle is written as a float in MS SQL. Is there any particular reason for it? How does FME handle the data types? Is there any way to overrule the dynamic schema and force it to be an 'Int'?

 

 

Since the DB we are writing to does not exist yet, we cannot use the 'Reader as Resource' functionality. This would have done the trick, but the input schema can change any time, so it doesn't really make sense.

 

 

best regards,

 

Jelle

5 replies

G
Badge +3
If there are no floats in the data (so no decimal points) you should be able to set it to int. In sql you could use CAST, in fme use stringformatter.
david_r
Rank
Userlevel 4
Gio,

 

 

I do not think a StringFormatter here is going to make a difference since the target schema is implied from the source schema, and not the data passing through the workspace.

 

 

Jelle,

 

 

perhaps you should look into (temporarily?) modifiying the FME metafiles that are responsible for mapping attribute types between formats. Perhaps in particular looking into the "C:\\Program Files (x86)\\FME\\metafile\\oracleAttrCommon.fmi" file:

 

 

Looking at line 32, we see that the Oracle datatype "number(10,0)" is translated to an unsigned integer internally in FME. However, if your field is something slightly different, like "number(9,0)" then it's probably translated, as per line 31, to "fme_decimal(9,0)", which in turn is probably stored as "numeric(9,0)" rather than integer in SQL Server.

 

 

David
jelle
Rank
Badge +11
Hi David, 

 

 

thank you for the tip. It didn't work out, however. Which is normal. A Number(10) (Oracle) cannot be translated to int (SQL), since the maximum values can be much higher in Oracle.

 

We have solved it with a postprocessing procedure, altering the table. The names of these columns won't change AND we know that the max value of int in SQL won't be exceeded. In this way, we can retain all the advantages of a dynamic writer.

 

 

 

best regards, 

 

Jelle

 

 
david_r
Rank
Userlevel 4
Hi,

 

 

you could've used a bigint, its range should be plenty for NUMBER(10): -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

 

 

Nonetheless, good to hear that you found a solution.

 

 

David
jelle
Rank
Badge +11
Sadly, GeoMedia does not support bigint. Otherwise, we would have gone that route indeed.

Reply


Community Stats

28,723
Posts
109,511
Replies
36,559
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.