SQLite insert size beyond declaration

[emileverh 24]

I have a SQLite table. Which has a column 'Description' and is initially defined as varchar(256). But that limitation is too small for the customers and I want to go to varchar(1024). According to the SQLite website they don't care;  

"SQLite does not enforce the length of a VARCHAR. You can declare a VARCHAR(10) and SQLite will be happy to store a 500-million character string there. And it will keep all 500-million characters intact. Your content is never truncated. SQLite understands the column type of "VARCHAR(N)" to be the same as "TEXT", regardless of the value of N.""

 

I changed in the TFDQuery the field .Size definition of that field from 256 to 1024 in the Fields-editor. But still Delphi cuts of my string to 256 chars. When I Google I see all kinds of scripts of creating a temp-table and redefine the thing. I don't want to do that. Question; is there a property (somewhere, no size checking?!?) which I can change so that Delphi does not cut of the string?

 

Thanks!

-Emile

CREATE TABLE IF NOT EXISTS cardlines (
  UniquePK INTEGER PRIMARY KEY AUTOINCREMENT,
  Description varchar(256) NOT NULL,
........

 

[John R. 18]

I would start with manual testing by crafting SQL queries which goes over the limits of your table definition with a tool such as SQLiteSpy: https://www.yunqa.de/delphi/apps/sqlitespy/index

If that works from there, it probably means that the limit is still defined somewhere in your Delphi app. Searching for "varchar(256)" or "256" in your *.pas and *.dfm files might be useful, depending on your project's size.

[emileverh 24]

35 minutes ago, John R. said:

I would start with manual testing by crafting SQL queries which goes over the limits of your table definition with a tool such as SQLiteSpy: https://www.yunqa.de/delphi/apps/sqlitespy/index

If that works from there, it probably means that the limit is still defined somewhere in your Delphi app. Searching for "varchar(256)" or "256" in your *.pas and *.dfm files might be useful, depending on your project's size.

What an other tool does, says nothing about Delphi. And the search for '256' I already... so still no result ;-((

[John R. 18]

I was mentioning another tool to make sure that the problem is actually in your Delphi app, not in your SQLite DB. As an example, a SQLite trigger could cause such problems. But if you've already eliminated that possibility (which was not clear from your original message), then the problem is clearly in your Delphi code somewhere.

[emileverh 24]

10 minutes ago, John R. said:

I was mentioning another tool to make sure that the problem is actually in your Delphi app, not in your SQLite DB. As an example, a SQLite trigger could cause such problems. But if you've already eliminated that possibility (which was not clear from your original message), then the problem is clearly in your Delphi code somewhere.

Or in a Delphi connection-setting (TFDConnection) which I don't know yet....

[Dmitry Arefiev 106]

https://docwiki.embarcadero.com/RADStudio/Athens/en/Using_SQLite_with_FireDAC#SQLite_Data_Types

[emileverh 24]

3 minutes ago, Dmitry Arefiev said:

https://docwiki.embarcadero.com/RADStudio/Athens/en/Using_SQLite_with_FireDAC#SQLite_Data_Types

Okay, and where is the setting which I am searching for? Thats why I am asking this here on this forum....

[Cristian Peța 107]

18 hours ago, emileverh said:

Okay, and where is the setting which I am searching for? Thats why I am asking this here on this forum....

Have you tried FideDAC Mapping?

From the link provided by Dmitry I see two things you can try (specify type name in column alias and mapping):

Quote

For an expression in a SELECT list, SQLite avoids type name information. When the result set is not empty, FireDAC uses the value data types from the first record. When empty, FireDAC describes those columns as dtWideString. To explicitly specify the column data type, append ::<type name> to the column alias:

SELECT count(*) as "cnt::INT" FROM mytab

If the Delphi application requires SQLite native data type representation, then use FireDAC mapping rules. For example, map TEXT columns to dtAnsiString and INT columns to dtInt64:

with FDQuery1.FormatOptions do begin
  OwnMapRules := True;
  with MapRules do begin
    SourceDataType := dtMemo;
    TargetDataType := dtAnsiString;
  end;
  with MapRules do begin
    SourceDataType := dtInt32;
    TargetDataType := dtInt64;
  end;
end;

 

Edited 3 hours ago by Cristian Peța
typo

[emileverh 24]

11 hours ago, Cristian Peța said:

Have you tried FideDAC Mapping?

From the link provided by Dmitry I see two thing you can try (specify type name in column alias and mapping):

 

Hmm this feels good. I think we are in the right direction. I am gonna try this and come back with the results!

[emileverh 24]

Nope! Still no result.

 

After Googling I found a link on this forum. And I think the only option is to create a temp table ;-((.  https://en.delphipraxis.net/topic/9099-sqlite-varchar-field-overflow/