Question

Set primary key in dynamic write to SQL.

  • 17 November 2021
  • 3 replies
  • 51 views

Badge +1

Hi community.

 

I have run into something that I am trying to automate but falling over at the last step.

The process I have is:

  • Download data-set names from FTP.
  • Split data-set name list into attributes.
  • Download data-sets from FTP to local drive.
  • Read downloaded data-sets using the feature reader.
  • Dynamically write data-sets into SQL server.

 

The last step I have missed is setting the primary key attribute for each data-set (fortunately it is the same 'ATTR_STR_1' for each data-set) as the dynamic writer is reading from the schema attributes coming out of the feature reader.

Has anyone had success with setting a primary key in dynamic writing?

I have tried a few things:

  • SQL To run after write: alter table landmap_polygon add primary key (ATTR_STR_1)
    • Error here is that the column is already populated and not set to no 'nulls' and I would need to set this for each table (we won't know table names until after the fact).
  • Read schema from existing table (after setting primary key).
    • The drop and create of the writer seems to ignore the primary key definition so the table is created without the key.
  • Run read/write in a second workbench to a static schema.
    • Works well but this means running and maintaining two workbenches.
  • Altering the schema to add a 'type' of 'primary key' to the schema list for the ATTR_STR_1 attribute and blank for others.
    • This is the current solution I am working on but haven't had any luck yet.

 

Workbench 

 

Any suggestions?

Thank you all.


3 replies

Badge +1

Since yesterday I have had some limited success in the writing mode.

Rather than drop/create the table I have set the table handling mode to truncate existing.

 

This seems to provide a solution in that the table schema (including the primary key and data-types) are maintained as long as I have created/imported the tables to begin with.

The change is marvellous in that the workbench now does exactly what I want with existing tables but will fail if there are new tables added to the FTP (and allowed through the stringsearcher/tester combo).

So I am still looking for a 'proper' way to set the primary key through the dynamic writer but can get around it with some initial set-up.

Badge +1

It would be good if there was an item in the list of a Schema Feature that is associated with the "Index" column in the Attribute Definition section of the writer. Something like Property: attribute{17}.fme_index; Value: PRIMARY KEY for the schema feature shown below would make sence but I don't think is currently possible.image

Badge +1

It would be good if there was an item in the list of a Schema Feature that is associated with the "Index" column in the Attribute Definition section of the writer. Something like Property: attribute{17}.fme_index; Value: PRIMARY KEY for the schema feature shown below would make sence but I don't think is currently possible.image

Hi @philipwhitten​.

That would be a nice addition and I didn't think of that initially when I had put the post up.

I'll add it to the ideas forum and see what happens.

Thanks mate.

Reply