SQLite varchar field overflow

[Jeff Steinkamp 1]

I am updating an older application I wrote in Visual Studio that uses SQLite as the underlying database.  One of the tables has three fields that are defined as VARCHAR(30).  Evidently, tables created with the System.Data.SqLite assembly did not truncate data that was longer than 30 characters and I now have a number of records where one or more of these fields is overflowing with up to 50 characters and I get the overflow error message when running a FireDac query that included these records.  So, my dilemma is how to deal with this as I have close to 3000 users out there that may have similar records.  Is there an easy way around this, or will I need to analyze each table when it's opened and if those 3 fields are dimensioned as VARVHAR(30), alter the table and increase the size?  In most cases the tables should have less than 1000 records.

[programmerdelphi2k 237]

@Jeff Steinkamp

 

try this:

1. I would do the following test: make a copy of the DB and change the definitions of the 3 tables!
   1. Using an appropriate tool, you have one, I would reset the field size to a larger value, for example if you already have 50 characters then let's time make the field size 100!
      1. NOTE: when increasing the field size, normally no data should be lost, which would happen the other way around!
   2. now in FireDAC, if the fields are defined dynamically, in your forms and datamodules, then just do:
      1. right-click on the table component, and access the "Fields Editor", delete all the fields there, and, add them all again!
      2. done, that would be it!
      3. now do the necessary tests to visualize the data, normally, running the recompiled program.
   3. if everything is ok, now you can easily test the program in production, and you can create a "script" (FDScript - FireDAC can be a support application to analyze and change the DB of other users. Or, you create this function within your own application, however, I think a separate little application would be better, just for this evaluation function.
2. if it is not possible to change the column size, you could create a new column with the desired size (greater than the previous one), copy all records to the new column, delete the old column, and rename the new column with the name of old.  (if it is not a dependency on a "Foreign Key", for example. In this case you will need some other actions first!)

 

  <-- that way, it's more simple: now, just update your "FDQuery fields using "Fields  Editor -> delete old field, add new field", in my example: field is "NAMES"

Edited May 21, 2023 by programmerdelphi2k

[Stano 143]

Can't point 2.1 be replaced by overwriting the field size in the table? I didn't have a problem with it.

[programmerdelphi2k 237]

I use SQLite Expert Pro and it's possible just "resize" the field, later, update your FDQuery to new size, as above!

• BUT, the field (names) contains "more char than" new resize... then need update the values too! for that FireDAC dont accept this "way"

Edited May 21, 2023 by programmerdelphi2k

[Jeff Steinkamp 1]

I know where the problem is and what columns are problematic.  I have inspected the tables with an SQLite administration tool to verify this.  I have also inspected a few users' databases and their tables have the same issues in that the data did not get truncated using the older methods.

 

So, the task at hand is how to fix this on the fly when a table is opened?  One of the limitations to SQLite is the ALTER TABLE command does not have a method to modify a columns definition.  I came up with the following method to fix this on the fly.

 

First run the following SQL on the table.   PRAGMA table_xinfo(existing_table).  I can loop over the results and test the 'type' column  for VARCHAR(30).  if the test is true, then we'll modify the table with the following steps.

 

 1. Create a temporary table using the new field definitions
 2. insert into the temp table form the existing table
 3. Drop the existing table
 4. Alter the temporary table and rename it to the existing table.

 

Works fine, lasts long time, shouldn't rust, bust, or collect dust!!  I will however have to make sure that when new data is inserted into these tables, I am testing these fields to make sure data is truncated to the length of the field.

[programmerdelphi2k 237]

6 minutes ago, Jeff Steinkamp said:

I am testing these fields to make sure data is truncated to the length of the field.

I think that it's not necessary create a new table, at all!  just create a new field (varchr(50)) copy the records using "UDPATE" command (like above etc...), it's ready!, now you can do it a little script and use FDScript to execute it! (create field, update field, delete field) 

Of course, you need update your "EDITs" on forms/datasource as said above!

Edited May 21, 2023 by programmerdelphi2k

[Jeff Steinkamp 1]

7 minutes ago, programmerdelphi2k said:

I use SQLite Expert Pro and it's possible just "resize" the field, later, update your FDQuery to new size, as above!

• BUT, the field (names) contains "more char than" new resize... then need update the values too! for that FireDAC dont accept this "way"

I also use SQLite Expert Pro, but this is not going to do my users any good.  We need a method to do this on the fly and I think I have resolved that, see my previous post.

[programmerdelphi2k 237]

1 minute ago, Jeff Steinkamp said:

I think I have resolved that, see my previous post.

if solved, then, it's solved! 

[Fr0sT.Brutal 900]

Why not just update data and truncate these too long cells?