Skip to main content
Question

inserting rows into SQL SDE table

  • December 11, 2015
  • 6 replies
  • 100 views

I am using FME Desktop 2014 build 14430 on Windows 2008
Server, and ArcGIS 10.3.1 and ArcServer 10.2.2 (loaded on same server).  

I have a table with a few fields (table name, feature
count, timestamp, error message, status) in my SQL Server SDE database that I
would like to have updated every time one of my FME jobs run.  Sounds very simple but for some reason I
can't figure it out. 

I tried using a Timestamper, StatisticsCalculator, Counter,
Variable Setter, SQL Creator, and shutdown python scripts. 
The transformers don't produce errors and nothing writes into the
table.  Yes I did fanout the attributes.

My SQL Creator script is:

INSERT INTO UPDATE_RECORD (LAYER, RUN_TIME, FEATURE_COUNT, STATUS, FAILURE_MSG)
VALUES('TEST'CURRENT_TIMESTAMP42'TRUE''SUCCESS');

This SQL fails, I think because it is a reader and not a writer??  Also that the ObjectID column flags an error that it can't accept NULLS...  I tried doing this as a SQL script to run after translation against this table but in a "non-spatial" connection and that failed as well.

My shutdown python scripts fail because it says it doesn't recognize the module arcpy.

FME_END_PYTHON: evaluating python script from
string...Python Exception <ImportError>: No module named
arcpy
Precondition failed at stfconf.cpp:1298. 
Expression: instance_ != 0

I also get a crash error. 

  Problem Event
Name:APPCRASH  Application
Name:fme.exe  Application
Version:2014.7.12.14430  Application
Timestamp:5433a4a6  Fault Module
Name:fme.dll  Fault Module
Version:2014.7.12.14430  Fault Module
Timestamp:5433a49b  Exception
Code:40000015  Exception
Offset:0000000000ccfadd  OS
Version:6.1.7601.2.1.0.274.10  Locale ID:1033  Additional
Information 1:33b2  Additional
Information 2:33b2f743a840af5774d03291871f1c1a  Additional
Information 3:c689  Additional
Information 4:c6896e4fc5d12bde6ee05af79fe17dab

I tried a fix found here:
https://knowledge.safe.com/articles/418/some-pyth...  for the individual script 

import os os.environ["PATH"]=""

But it didn't seem to fix it.  I can't do any server fixes because it's not my machine to mess with.  

Here is my python script: (PWS_counter is the global variable from the Counter I set inside the workflow, can I use that?)

import os
os.environ["PATH"] = ""

import time
now = time.strftime('%H%M%A%d%m%Y')

# FME variables.
import fme
status = fme.status
errorMsg = fme.failureMessage

import arcpy

# table to be updated
update_record_table = (r'C:\Users\USER\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\NDEP_VECTOR_SDEuser.sde\VECTOR.SDE.Update_record')

# Create Insert Cursor for multiple fields
rows = arcpy.InsertCursor(update_record_table, ['LAYER''FEATURE_COUNT''RUN_TIME''STATUS''FAILURE_MSG'])

row = rows.newRow()
row.setValue("LAYER""FacilityManager_by_Wells")
row.setValue("FEATURE_COUNT", PWS_COUNTER)
row.setValue("RUN_TIME", now)
row.setValue("STATUS", status)
row.setValue("FAILURE_MSG", errorMsg)
rows.insertRow(row)
del row

So how can  I do a simple insert a row into a table in a SDE?  

6 replies

david_r
Evangelist
  • December 11, 2015

Hi

I think the easiest would be to create a parallell track in your workspace. At the very end, before your Writer, connect a StatisticsCalculator and connect the Summary to a separate SDE Geodatabase feature type for your table UPDATE_RECORD. Insert an AttributeCreator between the two to set the values for RUN_TIME etc.

If you want to use ArcPy you will have to set a custom Python interpreter first, if you haven't already.

David


mark2atsafe
Safer
Forum|alt.badge.img+44
  • Safer
  • December 11, 2015

Have you tried the DatabaseUpdater transformer?


  • Author
  • December 11, 2015
david_r wrote:

Hi

I think the easiest would be to create a parallell track in your workspace. At the very end, before your Writer, connect a StatisticsCalculator and connect the Summary to a separate SDE Geodatabase feature type for your table UPDATE_RECORD. Insert an AttributeCreator between the two to set the values for RUN_TIME etc.

If you want to use ArcPy you will have to set a custom Python interpreter first, if you haven't already.

David

Wow! That fixed the arcpy error! Thanks! I searched all day yesterday and couldn't find anything like that.


  • Author
  • December 11, 2015
mark2atsafe wrote:

Have you tried the DatabaseUpdater transformer?

looked into it, but I can't figure out how to grab the FME variables like status and failure messages. However, these aren't totally important as actually writing into the table to show it ran, which I can't even get that far. I fanned out attribute, connected them, even made constant variables. It's not permissions issue, so I am not sure why.


  • Author
  • December 12, 2015

Here is my final answer that I got to.

At the final step before the writer, I added a new workflow branch, starting with an AttributeCreator. I made 2 attributes, one naming the table I'm updating (fme_feature_type), and the dataset it is contained in. I added this onto all the workflows in my workspace (4 in this example).

Then I connected all 4 AttributeCreators to a StatisticsCalculator. I "grouped by" both the new attributes (to carry them thru the transformer). The Attributes to Analyze is fme_feature_type. Since Count is all I wanted, I deleted all the calculations except Count.

Then I connect the Stats to the Timestamper.

Then I connect that to my writer for this UPDATE_RECORD table, fanned out the attributes, and connected those up!

It works PERFECT!


  • Author
  • December 12, 2015
jacque wrote:

Here is my final answer that I got to.

At the final step before the writer, I added a new workflow branch, starting with an AttributeCreator. I made 2 attributes, one naming the table I'm updating (fme_feature_type), and the dataset it is contained in. I added this onto all the workflows in my workspace (4 in this example).

Then I connected all 4 AttributeCreators to a StatisticsCalculator. I "grouped by" both the new attributes (to carry them thru the transformer). The Attributes to Analyze is fme_feature_type. Since Count is all I wanted, I deleted all the calculations except Count.

Then I connect the Stats to the Timestamper.

Then I connect that to my writer for this UPDATE_RECORD table, fanned out the attributes, and connected those up!

It works PERFECT!

Just as a side note, with the help of the FME support staff, I couldn't even get my variables passed through my python script. That's why I went this route. I couldn't grab error messages or anything doing it this way but I got what was important!


Reply


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