5

I'm writing 3 shapefiles to an MSSQL Spatial database with FME. I'm using a writer with dynamic settings to simplify future updates of the input files.

In order to create a spatial index with the dynamic writer I need a primary key column. Right now, I'm accomplishing this with a Counter that writes to a manually created "pid"-attribute marked as primary key, but this feels like a hack.

So, how do I create an auto-incrementing primary key on a dynamic database writer in FME?

3

I think @buddiebubba is correct. Here's what my SQL Server definition looks like:

enter image description here

Note that it's a dynamic translation. The Feature Type also has a parameter like this:

enter image description here

By setting that to NO I cause SQL Server to increment that attribute, which it does nicely. However, I do get a warning message in the FME log:

Microsoft SQL Server Spatial Writer: 
Data type `identity' of attribute `NewMarkID' of feature type `Parks' 
is not supported for writing.  Attribute will be ignored by writer

I think that must be just a bad warning (or just badly worded). I don't see that the attribute is ignored. It all looks good to me.

Now, if I want to read from a SQL table and write the data back to it, then I would set the above parameter to YES. Then the existing values are written back. Extra features can be given an ID with the Counter and they will get incremented from the previous max ID I believe.

NB: I've filed a request to update the warning message, so that it is not so confusing. It's PR#81236

5

No experience with MSSQL but for PostgreSQL it works by selecting the datatype serial and the index PrimaryKey. See screenshot.

I suspect the datatype should be identity with index PrimaryKey. See also this documentation.

FeatureWriter Parameters

Big StackExchange fan but you will have better chances getting a good answer on the Safe Software forum. Lots of knowledge and activity.

  • I can set up an attribute with identity and Index PrimaryKey, but MSSQL reports that it can write to such an attribute and ignores it. I guess I might have to use a SQL command for this rather than doing it in the writer dialog? As for StackExchange vs vendor specific forums, I like the idea of collecting all spatial IT knowledge here even though it might be a bit of a futile project. – hexamon Jan 9 '18 at 12:16
  • I like that idea as well and reading / answering both, but I do not know everything. And I do need solutions now and then :) – nielsgerrits Jan 9 '18 at 12:38
2

Edit: answer by buddiebubba is better.

If using a dynamic writer type, it is not possible to simply put serial in there since the schema is feature driven.

The easiest way to do this, I found was to use the SQL To Run After Write feature: enter image description here

For postgresql the code would be:

ALTER TABLE testing
ADD COLUMN pkid serial;
alter table testing add CONSTRAINT pk_testing PRIMARY KEY (pkid);

I am sure there is an equivalent for SQL server. It would be something like:

ALTER TABLE Parent MODIFY Identifier smallint(10) AUTO_INCREMENT;

if the internet is to be trusted.

  • 1
    It surely is possible to add columns when writing dynamic. Just hit the Dynamic radio button in the Attribute Definition part and add the needed fields. – nielsgerrits Jan 9 '18 at 6:07
  • I stand corrected. I just tested it, works fine. Sorry for the confusion. – Lennert De Feyter Jan 9 '18 at 6:10
  • :) I tend to keep working with solutions I know as it is not always easy to keep up with new developments. Happy I could share some know how. – nielsgerrits Jan 9 '18 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.