[yunqa.de] Re: TDISQLite3UniDirQuery select * thinks string field is ftFloat
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Tue, 10 Jul 2012 19:06:41 +0200
Radovan is right. SQLite interprets the "STRING" datatype as numeric. So
does TDISQLite3UniDirQuery, which translates to ftFloat, Delphi's most
general representation of a numeric type.
You have two options to change this to your desired behavior:
1. Change your field type to TEXT as Radovan suggested.
2. Add a TDISQLite3UniDirQuery.OnInitFieldDef handler which remaps the
"STRING" field to ftString. The example project attached demonstrates this.
Ralf
----------
program DISQLite3_InitFieldDefs;
{$APPTYPE CONSOLE}
uses
System.SysUtils, TypInfo, DB, DISQLite3Database, DISQLite3DataSet;
type
TTest = class
private
procedure InitFieldDef(
const Column: TDISQLite3Column;
const FieldDef: TFieldDef);
public
procedure Execute;
end;
procedure TTest.InitFieldDef(
const Column: TDISQLite3Column;
const FieldDef: TFieldDef);
begin
if CompareText(Column.ColumnDeclaration, 'string') = 0 then
begin
FieldDef.DataType := ftString;
FieldDef.Size := 128; // Increase default TField string size.
end;
end;
procedure TTest.Execute;
var
DB: TDISQLite3Database;
Query: TDISQLite3UniDirQuery;
begin
DB := TDISQLite3Database.Create(nil);
DB.DatabaseName := 'usenet.db';
Query := TDISQLite3UniDirQuery.Create(nil);
Query.OnInitFieldDef := InitFieldDef;
Query.Database := DB;
Query.SelectSQL := 'select name from newsgroups';
Query.Open;
WriteLn('Field Name: ',
Query.Fields[0].FieldName);
WriteLn('Field DataType: ',
GetEnumName(TypeInfo(TFieldType), Ord(Query.Fields[0].DataType)));
WriteLn('Field Size: ',
Query.Fields[0].Size);
Query.Free;
DB.Free;
end;
var
t: TTest;
begin
try
t := TTest.Create;
t.Execute;
t.Free;
except
on e: Exception do
WriteLn(e.ClassName, ': ', e.Message);
end;
WriteLn;
WriteLn('Done - Press ENTER to Exit');
ReadLn;
end.
----------
On 10.07.2012 13:04, Radovan Antloga wrote:
> You have declared fields as STRING.
> You should declare it as TEXT.
> Look at this article:
>
> http://www.sqlite.org/datatype3.html#affinity
program DISQLite3_InitFieldDefs;
{$APPTYPE CONSOLE}
uses
System.SysUtils, TypInfo, DB, DISQLite3Database, DISQLite3DataSet;
type
TTest = class
private
procedure InitFieldDef(
const Column: TDISQLite3Column;
const FieldDef: TFieldDef);
public
procedure Execute;
end;
procedure TTest.InitFieldDef(
const Column: TDISQLite3Column;
const FieldDef: TFieldDef);
begin
if CompareText(Column.ColumnDeclaration, 'string') = 0 then
begin
FieldDef.DataType := ftString;
FieldDef.Size := 128; // Increase default TField string size.
end;
end;
procedure TTest.Execute;
var
DB: TDISQLite3Database;
Query: TDISQLite3UniDirQuery;
begin
DB := TDISQLite3Database.Create(nil);
DB.DatabaseName := 'usenet.db';
Query := TDISQLite3UniDirQuery.Create(nil);
Query.OnInitFieldDef := InitFieldDef;
Query.Database := DB;
Query.SelectSQL := 'select name from newsgroups';
Query.Open;
WriteLn('Field Name: ',
Query.Fields[0].FieldName);
WriteLn('Field DataType: ',
GetEnumName(TypeInfo(TFieldType), Ord(Query.Fields[0].DataType)));
WriteLn('Field Size: ',
Query.Fields[0].Size);
Query.Free;
DB.Free;
end;
var
t: TTest;
begin
try
t := TTest.Create;
t.Execute;
t.Free;
except
on e: Exception do
WriteLn(e.ClassName, ': ', e.Message);
end;
WriteLn;
WriteLn('Done - Press ENTER to Exit');
ReadLn;
end.
Other related posts:
