SQLITE TParams Only support TDateTime but not TDate?

TinTin · Post by **TinTin** » Mon 04 Apr 2011 04:15

If Table Have DateTime DataType,it saves TDate Value (no Time value) ,Such is Example,If declare TDate Param Value ,it have no Qurey Data ,but if declare TDateTime Param Value,It is OK.

procedure TForm9.Button1Click(Sender: TObject);
var
D1,D2:TDate; //IF declare TDateTime,it is OK

begin
D1 := EncodeDate(2011,3,1);
D2 := EncodeDate(2011,3,31);
with UniQuery1 do
begin
Close;
sql.Text := 'Select * from F_Loan where BillDate>= :D1 And BillDate<=:D2';

ParamByName('D1').Value := D1;
ParamByName('D2').Value := D2;
Open; //No Data output
end;;
end;

AlexP · Post by **AlexP** » Mon 04 Apr 2011 08:13

Hello,

I could not reproduce the problem.
Please try to execute the following code:

Code: Select all

var
  D1,D2:TDate;
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
  i: integer;
begin
  UniConnection:= TUniConnection.Create(nil);
  UniQuery:= TUniQuery.Create(nil);
  UniConnection.ProviderName := 'SQLite';
  UniConnection.Database := ' ';
  UniConnection.LoginPrompt := false;
  UniConnection.Connect;
  UniConnection.ExecSQL('DROP TABLE IF EXISTS T_TEST',[]);
  UniConnection.ExecSQL('CREATE TABLE T_TEST (ID INT,BillDate DATETIME)',[]);
  for i:= 1 to 9 do
    UniConnection.ExecSQL('INSERT INTO T_TEST (ID, BILLDATE) VALUES (:i,DATE(:d))',[i,'2011-04-0'+IntToStr(i)]);

  D1 := EncodeDate(2011,4,5);
  D2 := EncodeDate(2011,4,7);
  UniQuery.Connection := UniConnection;
  UniQuery.SQL.Text:= 'SELECT * FROM T_TEST WHERE BillDate >= :a AND BillDate <= :b';
  UniQuery.ParamByName('a').DataType:= ftDate;
  UniQuery.ParamByName('b').DataType:= ftDate;
  UniQuery.ParamByName('a').value:=D1;
  UniQuery.ParamByName('b').Value:=D2;
  UniQuery.Open;
  ShowMessage(IntToStr(UniQuery.RecordCount));

if the problem doesn't arise, please modify the code to reproduce the problem, and send it me.[/code]

TinTin · Post by **TinTin** » Mon 04 Apr 2011 11:33

I have Sent sample to [email protected],Please Use my Sqlite3.dll AND SQLITE DB.

Thanks !

TinTin · Post by **TinTin** » Mon 04 Apr 2011 12:09

I think TParam.Value can Accept TDate Value because TParam.Value is Variants, and dont set DataType.

Such Like TUniConnection.ExecSQL(.....)

AlexP · Post by **AlexP** » Mon 04 Apr 2011 14:30

Hello,

Types of parameters are defined in Delphi (see the sample code below), and we can't change this behaviour.
So you should set the DataType property to the ftDate type or use TDateTime variables.

Code: Select all

var
d1: Tdate;
d2: TDateTime;
v1, v2 : variant;
begin
d1:= EncodeDate(2011,4,5); 
d2:= EncodeDate(2011,4,5); 
v1:= d1; 
v2:= d2;
ShowMessage(VarToStr(v1)+ ' - '+ VarToStr(v2));