[yunqa.de] Re: Field sizes - SQLite tables
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Sun, 10 May 2009 16:31:55 +0200
At 05:13 10.05.2009, Hal Corbould wrote:
>Referring to my post below, the DISQLite3 help file answered my concern about
>field sizes - fields/columns are dynamically sized, and not rigidly typed.
>Because SQLiteSpy accepted my MySQL table create statement extracted from the
>MySQLdump.exe *.sql file , I assumed that the two currency fields were limited
>to 3 and 30 chars respectively. Not so, I now understand, much better.
Right. DISQLite3 has virtually no limit on the length of string fields. It
interprets the column declaration 'char(3)' as TEXT without limits. See
DISQLite3 Help -> SQLite Documentation -> Data Types
>I'd still like some help on the 20 char limit to any text column as exhibited
>in DISQLite3_ClientDataSet_Grid.dpr. Might be something to do with WideStrings
>and the TStringField.Size property mentioned in Ralf's reply to the Yeoh Ray
>Mond's post of 5 April 2008?
Correct. Even though DISQLite3 does not impose string length limits, Delphi
sometimes still does. TClientDataSet, especially, tends to truncate overlong
strings. I tried hard to figure out how and where this happens, but since there
is no source code there is not much anyone can do about it.
This is the chain of events which leads to your problem:
1. As TClientDataSet loads the record set from TDISQLite3UniDirQuery it
observes the TField.Size limit and truncates longer strings.
2. TDbGrid also now sees the TClientDataSet's TField.Size = 20 limit and
prevents entering any more characters.
3. If you modify data and apply your changes, TDataSetProvider passes all data
back to DISQLite3 to prevent changing modified records. Unfortunately, this
also includes the truncated string. Now the database now sees that the
shortened and unshortened strings do not macht and (rightly so) refuses the
update.
Proposed solutions:
1. Increase the TField.Size for strings so they can hold all your data. Here is
an example patch for DISQLite3_ClientDataSet_Grid_Form_Main.pas:
procedure TfrmMain.DISqlite3UniDirQueryInitFieldDef(
const AColumn: TDISQLite3Column;
const AFieldDef: TFieldDef);
begin
case AFieldDef.DataType of
{$IFNDEF COMPILER_5_UP}
{ TClientDataSet in D4 does not support WideStrings. This works around this
problem by changing the ftWideString type to ftString. It has also
problems assigning ftLargeInt (Int64) to a Variant so scale it down to
ftInteger. }
ftLargeInt:
begin
AFieldDef.DataType := ftInteger;
end;
{$ENDIF !COMPILER_5_UP}
ftWideString:
begin
{$IFNDEF COMPILER_5_UP}
AFieldDef.DataType := ftString;
{$ENDIF !COMPILER_5_UP}
{ Increase size for string fields to 32 (default is 20). Depending on
your data, you might add even more. }
AFieldDef.Size := 32;
end;
end;
end;
2. Experiment with TDataSetProvider.UpdateMode. The default upWhereAll passes
all values (changed and unchanged) back to the database engine. Other settings
pass less data, so problems with truncated strings should be less likely.
3. Assign your custom TDISQLite3UniDirQuery.ModifySQL statement for more fine
tuning. Example:
update tcurr set
currcode = :currcode,
currname = :currname
where
currcode = :old_currcode;
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
