Page 1 of 1
SQLITE TParams Only support TDateTime but not TDate?
Posted: Mon 04 Apr 2011 04:15
by TinTin
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;
Posted: Mon 04 Apr 2011 08:13
by AlexP
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]
Posted: Mon 04 Apr 2011 11:33
by TinTin
I have Sent sample to
[email protected],Please Use my Sqlite3.dll AND SQLITE DB.
Thanks !
Posted: Mon 04 Apr 2011 12:09
by TinTin
I think TParam.Value can Accept TDate Value because TParam.Value is Variants, and dont set DataType.
Such Like TUniConnection.ExecSQL(.....)
Posted: Mon 04 Apr 2011 14:30
by AlexP
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));