Question

Define an Sqlite table with VARCHAR(n) from a source.

  • 5 April 2022
  • 5 replies
  • 12 views
kimo
Badge +1

I have to manually define VARCHAR(n) instead of TEXT <null> in the writer. This is very frustrating as I have hundreds of tables already defined. Using a reader resource ignores the DDL stored in the table. So is there a workaround? Here is and example DDL from the source SQLITE table:

CREATE TABLE "System_Code" (

   "OID" INTEGER NOT NULL,

   scg_code VARCHAR(4),

   code VARCHAR(4),

   desc_ VARCHAR(2048),

   status VARCHAR(4),

   date_value VARCHAR(25),

   char_value VARCHAR(2048),

   num_value FLOAT,

   start_date VARCHAR(25),

   end_date VARCHAR(25),

   et_created VARCHAR(25),

   et_edited VARCHAR(25),

   PRIMARY KEY ("OID")

Which ends up half complete:

image

5 replies

nielsgerrits
Rank
Userlevel 6
Badge +32

If the tables already exist, you should be able to import them.

2022-04-05_08h48_56 If the tables do not exist I think you can create them using your sql and a SQLExecutor, then import the schema's in the writer as described.

kimo
Badge +1

When I import the table schema from a Reader the schema is rubbish as illustrated. That is my problem. The tables already exist, I have just cut and pasted the DDL to show the actual schema of a sample table, you cannot see this from FME.

I have given up on the expected method and am looking for a workaround. I could of course run a python script to make a new table with the correct schema, but then I may as well discard FME entirely and continue as before with Python. (Actually the examples are different tables, but basically a VARCHAR(n) translates to TEXT <infinite_width>

This matters because fixed width databases will use the maximum field width when translating. Dbase will be 255 and Geodatabases will be 8000 chars wide per field, just in case... so the next process will crash.

I really want to update the tables from CSV change sets, but I am having even more trouble using dynamic schema to define the output which is slightly different from the CSV, and of course the CSV has no schema at all.

A
Rank
Badge +10

Hi @kimo​ ,

Thanks for sharing the sample DDL of the table definition.

This helped us identify a bug with the SQLite format. (I presume you are using FME 2022 with testing the updated SQLite/SpatiaLite format.)

The issue is the reader is expecting the data type definition to be "varchar" when created and not "VARCHAR". So currently the reader is case sensitive to the data types. I've filed a ticket with our development team about this issue and will update you when it's been resolved (internal ref: FMEENGINE-72938).

-Andrea

kimo
Badge +1

[The sqlite reader does not read uppercase or mixed case field names in existing DDL definitions. Hope this is fixed soon.]

A
Rank
Badge +10

Hi @kimo​ ,

The issue (FMEENGINE-72938) with the case sensitivity for defining the data type definition is resolved in FME 2022.1 which is now available here: safe.com/downloads

If you are encountering a different issue in the new version of FME 2022.1, then please file a new case with a simple reproduction for us here: https://community.safe.com/s/submit-case

Thanks!

Andrea

Reply


Community Stats

28,670
Posts
109,366
Replies
36,531
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.