Skip to main content
  • EN
Hi,

 

I'm looking for best practice solutions for writing to a SQL Server database mid-translation. I need to write mid-translation because I have foreign key constraints on the database and some feature types need to be inserted before others.

 

 

As far as I'm aware there's no way of telling the writer to insert feature types in a specific order.

 

 

I would prefer to do all this in a single workspace (ie. not have multiple workspaces called by the WorkspaceRunner).

 

 

I think my only solution is to use the SQLExecutor but inserting records using this transformer is not well documented and it does not seem to provide any feedback as to it's success.

 

 

Any thoughts?

 

 

thanks,

 

Nic

 

Hi,

 

 

you're right about the SQLExecutor, it works very well for this and I do it myself quite frequently. Any errors raised in the database during insertion will automatically propagate to FME, so there is normally no reason to worry about feedback regarding success.

 

 

That being said, there are a couple of tricks when it comes to writing data with foreign key constraints: http://fmepedia.safe.com/articles/FAQ/Write-Feature-Types-in-a-Specific-Order

 

 

David
Thanks David. I'm using the SQLExecutor and it's working for the most part. Just trying to figure out how to insert dates with it... They're going through from FME as numbers but SQL Server is complaining.
Hi,

 

 

not sure about SQL Server, but in Oracle I'd do something like

 

 

insert into mytable (mystring, mydate) values ('@Value(MYSTRINGATTRIBUTE)', to_date('@Value(MYDATEATTRIBUTE)', 'yyyymmdd'));

 

 

The clue is to explicitly cast your string/number into a timestamp object while specifying the exact format of your date field. I think the SQL Server equivalent is documented here: http://msdn.microsoft.com/en-us//library/ms187928.aspx

 

 

David
Got it. Had to convert the dates to strings then use the DateFormatter to put them into a format that SQL Server would understand. Using the DateFormatter directly on the dates had no effect.

Reply


Community Stats

30,108
Posts
114,295
Replies
37,742
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.