hi!
so.. exist next table
CREATE TABLE [operations] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[_Date] DATE NOT NULL,
[_Count] REAL NOT NULL,
);
i use next query
SELECT t2.ID as ID
,t1.Name as Name
,sum((CASE t1.Credit
WHEN 1 THEN t2._Count
ELSE 0
END)) AS credit
,(CASE t1.Credit
WHEN 0 THEN t2._Count
ELSE 0
END) AS debit
,t2.Note as Note
FROM groups t1, operations t2
WHERE t1.ID=t2.groups_ID
AND t2._date = "2007-05-03"
GROUP BY t2.ID,t1.Name
ORDER BY t2.ID;
after query is open we have
credit TStringField... but must be TFloatField
i try to use CAST function but dosent matter TStringField
and because return as string delimiter is '.' but if field is float my delimite is ','(country settings)
wrong field casting
Moderators: gto, cipto_kh, EgonHugeist
The problem you describe relies on the fact that SQLite does not report a fieldtype when you do "SELECT ... AS".
Zeos defaults to use stString as fieldtype when it encounters an unknown fieldtype (in this case it is simply an empty string).
There is no way of knowing the fieldtype without parsing the returned data, which is impossible at the level where the fieldtypes are determined (prior to fetching the data).
So you have to do the conversion on your own, you could do a conversion via:
if DecimalSeparator<>'.' then
StringReplace(Value, '.', DecimalSeparator, [rfReplaceAll])];
which should work on all systems (even ones that use something entirely different as . and , for decimal seperator).
Hope this helps.
Zeos defaults to use stString as fieldtype when it encounters an unknown fieldtype (in this case it is simply an empty string).
There is no way of knowing the fieldtype without parsing the returned data, which is impossible at the level where the fieldtypes are determined (prior to fetching the data).
So you have to do the conversion on your own, you could do a conversion via:
if DecimalSeparator<>'.' then
StringReplace(Value, '.', DecimalSeparator, [rfReplaceAll])];
which should work on all systems (even ones that use something entirely different as . and , for decimal seperator).
Hope this helps.
hi...
Read your answer but something strange is happening...
Why is it that eg.
ZReadOnlyQuery3.SQL.Add('Select Max(inscDiam) As MaxDiam From objects Where ObjectID = 3');
Returns TStringField
And
ZReadOnlyQuery1.SQL.Add(Memo1.Text);
with
Memo1.Text beeing
Select Max(inscDiam) As MaxDiam From objects Where ObjectID = 3
Same as previous sql statement WORKS returning correct FLOAT????
Thank you!!!
Read your answer but something strange is happening...
Why is it that eg.
ZReadOnlyQuery3.SQL.Add('Select Max(inscDiam) As MaxDiam From objects Where ObjectID = 3');
Returns TStringField
And
ZReadOnlyQuery1.SQL.Add(Memo1.Text);
with
Memo1.Text beeing
Select Max(inscDiam) As MaxDiam From objects Where ObjectID = 3
Same as previous sql statement WORKS returning correct FLOAT????
Thank you!!!
Hi,
I tried using TZsqlmonitor as requested :
These are the two queries...
TMEMO
2007-05-22 11.39.41 cat: Execute, proto: sqlite-3, msg: Select Max(inscRadius) As tfMaxRadius From objectsdata Where ObjectID = 1
ADD(...)
2007-05-22 11.39.50 cat: Execute, proto: sqlite-3, msg: Select Max(inscRadius) As tfMaxRadius From objectsdata Where ObjectID = 1
Return Values:
TMEMO
112.498442747681
ADD(...)
112498442747681
Floating Point is lost....
I parsed the lines looking for hidden chars but no difference at this level... Perhaps ZSQLMonitor Cleans it up...
That's strange indeed...???? Yes!!!!
I tried using TZsqlmonitor as requested :
These are the two queries...
TMEMO
2007-05-22 11.39.41 cat: Execute, proto: sqlite-3, msg: Select Max(inscRadius) As tfMaxRadius From objectsdata Where ObjectID = 1
ADD(...)
2007-05-22 11.39.50 cat: Execute, proto: sqlite-3, msg: Select Max(inscRadius) As tfMaxRadius From objectsdata Where ObjectID = 1
Return Values:
TMEMO
112.498442747681
ADD(...)
112498442747681
Floating Point is lost....
I parsed the lines looking for hidden chars but no difference at this level... Perhaps ZSQLMonitor Cleans it up...
That's strange indeed...???? Yes!!!!
Yes... True... Both Tmemo and ADD now do the same thing I was doing something wrong... Now I get both wrong...According to the code (ZDbcSqLiteResultSet.pas) it seems impossible to make a difference...
which SQLite.dll do you use?
After further testing I figured out that SQLite Float is returned with Decimal Separator "." instead of "," as with MySql...
I created two small exactly same databases with sqllite and mysql...
I open one at a time with exactly same source...
MySql:
ShowMessage(ZReadOnlyQuery1['tfMax']);
Returns
112,498442747681 Recognized as Float...
SQLite:
ShowMessage(ZReadOnlyQuery1['tfMax']);
Returns
112.498442747681 Not Recognized...
procedure TForm1.Button1Click(Sender: TObject);
var
tfMaxRad: Double;
newStr: String;
P1: Integer;
begin
ZReadOnlyQuery1.SQL.Clear;
ZReadOnlyQuery1.SQL.Add(Memo1.Text);
ZReadOnlyQuery1.ExecSQL;
ZReadOnlyQuery1.Active := True;
ShowMessage(ZReadOnlyQuery1['tfMax']);
P1 := Pos('.', ZReadOnlyQuery1['tfMax']);
if P1 > 0 then
begin
newStr := ZReadOnlyQuery1['tfMax'];
Delete(newStr, P1, 1);
Insert(',', newStr, P1);
tfMaxRad := StrToFloat(newStr);
end
else
tfMaxRad := ZReadOnlyQuery1['tfMax'];
ShowMessage(FloatToStr(tfMaxRad));
end;
