Skip to main content
  • EN

I need to revisit this answer, that I find to be a limited work-around, not a proper solution.

The offending and unsupported type "mediumint" is not from the MS/SQL database, where data are sourced, so it has to be something that FME itself generates on the fly. Aka a bug.

The database table has a field named ID, which is an "int" (32 bit), and it uses an identity value.

This is how the schema feature sees the field:

schema featureI really cannot understand, why FME cannot transfer this to a table in a Geopackage. The very same schema and data feature are used to create a GeoJSON with any problems.

Please advise.

Using 2022.1

Funny thing: I opened the very same workspace in 2021.2.6, and it worked flawlessly without any work-arounds. A bug in the 2022 Geopackage writer then ?

It's a specification of SQLite: https://www.sqlite.org/lang_createtable.html#rowid

The Primary key must be a 64bit integer. In your MS/SQL database it's probably a 32bit int column.

 

mediumint= 32 bit, int =64bit, smallint =16bit and tinyint = 8 bit (you can read the doc here: https://docs.safe.com/fme/2022.1/html/FME_Desktop_Documentation/FME_ReadersWriters/ogcgeopackage/user-attributes.htm

 

In the FME documentation it also specifies

"PrimaryKey: The primary index for the table. Only one attribute may have this index type, and the attribute type must be int."

 

Because the schema feature specifically says fme_32bit (which is mediumint) the writer is throwing an error.

So for this to work without error, FME would need to change the datatype to be 64bit int (which, to be fair, I can't really see a use case where this change wouldn't be wanted), however, it's not really a hard change to make in the workspace.

 

 

Funny thing: I opened the very same workspace in 2021.2.6, and it worked flawlessly without any work-arounds. A bug in the 2022 Geopackage writer then ?

Heh, now that is an interesting find!

It's a specification of SQLite: https://www.sqlite.org/lang_createtable.html#rowid

The Primary key must be a 64bit integer. In your MS/SQL database it's probably a 32bit int column.

 

mediumint= 32 bit, int =64bit, smallint =16bit and tinyint = 8 bit (you can read the doc here: https://docs.safe.com/fme/2022.1/html/FME_Desktop_Documentation/FME_ReadersWriters/ogcgeopackage/user-attributes.htm

 

In the FME documentation it also specifies

"PrimaryKey: The primary index for the table. Only one attribute may have this index type, and the attribute type must be int."

 

Because the schema feature specifically says fme_32bit (which is mediumint) the writer is throwing an error.

So for this to work without error, FME would need to change the datatype to be 64bit int (which, to be fair, I can't really see a use case where this change wouldn't be wanted), however, it's not really a hard change to make in the workspace.

 

 

Hi Matt.

If it's an absolute requirement in the output format, then it absolutely must be handled by the writer itself. I.e. casting other int forms to the required form, in this case.

I still regard such an omission to be a bug.

Cheers.

Hi Matt.

If it's an absolute requirement in the output format, then it absolutely must be handled by the writer itself. I.e. casting other int forms to the required form, in this case.

I still regard such an omission to be a bug.

Cheers.

Yeah in this case I can't see a reason why it shouldn't be cast in this case either. let's ping @nampreetatsafe​  and @andreaatsafe​  to see if they are able to say why this worked in 2021 and not 2022

@Lars I Nielsen​ I think the root cause of the issue is the FME schema readers that create the attribute{} schema list attribute. Some formats do recognize the primary key and some don't. So depending on the format and attribute FME might return:

attribute{0}.fme_data_type  fme_int32,PrimaryKey

attribute{0}.name                  id

But a table with RECORDID PrimaryKey FME returns:

attribute{0}.fme_data_type  fme_int32

attribute{0}.name                  RECORDID

and also:

attribute{0}.fme_data_type fme_int32

attribute{0}.name                 ID

(i.e. the last two do not identify the primary key). This is something we can look into for a future release of FME - but it probably means visiting all the database formats that FME supports - so a pretty large task.

@Lars I Nielsen​ I think the root cause of the issue is the FME schema readers that create the attribute{} schema list attribute. Some formats do recognize the primary key and some don't. So depending on the format and attribute FME might return:

attribute{0}.fme_data_type  fme_int32,PrimaryKey

attribute{0}.name                  id

But a table with RECORDID PrimaryKey FME returns:

attribute{0}.fme_data_type  fme_int32

attribute{0}.name                  RECORDID

and also:

attribute{0}.fme_data_type fme_int32

attribute{0}.name                 ID

(i.e. the last two do not identify the primary key). This is something we can look into for a future release of FME - but it probably means visiting all the database formats that FME supports - so a pretty large task.

Hi Mark.

So to summarize, the OGC/GPKG writer in 2022 demands something that most readers cannot deliver? Then why not make the demand optional ?

And why does it work in 2021 ?

It's a specification of SQLite: https://www.sqlite.org/lang_createtable.html#rowid

The Primary key must be a 64bit integer. In your MS/SQL database it's probably a 32bit int column.

 

mediumint= 32 bit, int =64bit, smallint =16bit and tinyint = 8 bit (you can read the doc here: https://docs.safe.com/fme/2022.1/html/FME_Desktop_Documentation/FME_ReadersWriters/ogcgeopackage/user-attributes.htm

 

In the FME documentation it also specifies

"PrimaryKey: The primary index for the table. Only one attribute may have this index type, and the attribute type must be int."

 

Because the schema feature specifically says fme_32bit (which is mediumint) the writer is throwing an error.

So for this to work without error, FME would need to change the datatype to be 64bit int (which, to be fair, I can't really see a use case where this change wouldn't be wanted), however, it's not really a hard change to make in the workspace.

 

 

I just tried 2022.2.2 with a database, where the ID field type was bigint (instead of int). Got exactly the same error message.

So it's a bug.

@Lars I Nielsen​ I think the root cause of the issue is the FME schema readers that create the attribute{} schema list attribute. Some formats do recognize the primary key and some don't. So depending on the format and attribute FME might return:

attribute{0}.fme_data_type  fme_int32,PrimaryKey

attribute{0}.name                  id

But a table with RECORDID PrimaryKey FME returns:

attribute{0}.fme_data_type  fme_int32

attribute{0}.name                  RECORDID

and also:

attribute{0}.fme_data_type fme_int32

attribute{0}.name                 ID

(i.e. the last two do not identify the primary key). This is something we can look into for a future release of FME - but it probably means visiting all the database formats that FME supports - so a pretty large task.

Still unfixed in 2022.2.2

Reply


Community Stats

30,110
Posts
114,302
Replies
37,745
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.