TSynBinaryDataSet Error [SQLite3]

delphi_911 · 2019-05-28 12:15:49

Hi, how to fix this ERROR?

ToDataSet(aDataSet, AProp.Execute('select cast(2.2*3.4 as Currency) as F1', []));

AProp is TSQLDBSQLite3ConnectionProperties, connecting to a SQLite3 DB, ERROR message :

"GetFieldData ColumnType=Unknown"

---------------
Many thanks!

ab · 2019-05-28 13:28:02

With SQlite3, the 'as Currency' is a no-operation: there is no "currency" type, only SQLITE_DOUBLE values.

Anyway, could you try to debug a little and see why the column is not identified as ftDouble or ftCurrency, as it should?

delphi_911 · 2019-05-29 07:40:11

I have debugged the code, with SQLite3, if there was a computed field in SELECT statement, e.g.

select 1 as ID;
select F1*F2  from Tbl;

function TSQLRequest.FieldDeclaredType(Col: Integer): RawUTF8;
var P: PUTF8Char;
begin
  if cardinal(Col)>=cardinal(FieldCount) then
    raise ESQLite3Exception.Create(RequestDB,SQLITE_RANGE,'FieldDeclaredType');
  P := pointer(sqlite3.column_decltype(Request,Col));
  FastSetString(result,P,SynCommons.StrLen(P));
end;

// Result here was empty. Then ColumnTypeNativeToDB won't get the field type correctly.

ab · 2019-05-29 11:32:41

Please try https://synopse.info/fossil/info/41e834407b

delphi_911 · 2019-05-29 13:40:48

Thanks,it works perfectly.

delphi_911 · 2019-05-30 03:11:16

@ab, Hi, I found another question. There's a table t0, 2 fields: x and y, if no data(0 row in table), still raise error with the following statement.

select x*y as value from t0;
//or
select 1 as ID, x, y from t0;

-----
Best regards

ab · 2019-05-30 08:50:23

The same error?

delphi_911 · 2019-05-30 08:58:17

The same error?
-----
Yes.

delphi_911 · 2019-06-14 01:34:40

@ab, There still has the same error with following statement, "GetFieldData ColumnType=Unknown"

select F1, F2 from Table1 where 1=2

ab · 2019-06-14 08:54:52

I am not able to reproduce it when I replace in sample 21, line 80 by the following:

const SQL_PEOPLE = 'select firstname from People where 1=2';

delphi_911 · 2019-06-14 12:08:55

@ab Sorry, I changed the code, with a computed field:

const SQL_PEOPLE = 'select 1 as KeyID, firstname from People where 1=2';

then got the error:"GetFieldData ColumnType=Unknown".

Last edited by delphi_911 (2019-06-14 12:09:44)

ab · 2019-06-14 13:40:45

So correct the SQL, which is obviously wrong.

delphi_911 · 2019-06-14 14:23:25

I do not think the SQL is wrong, consider these SQL: table t0, 2 fields: x and y, if no data(0 row in table), still raise error with the following statement.

  const SQL = 'select x*y as value from t0';

or this SQL:

  const SQL_PEOPLE = 'select lastname || firstname as FULLNAME, * from People';

ab · 2019-06-14 16:05:10

select lastname || firstname as FULLNAME, * from People

works.

delphi_911 · 2019-06-17 01:57:16

@ab, I wrote a demo, it can reproduce the problem. Please download and check，Many thanks!
http://129.204.55.202/ilabstar/files/Test.rar

delphi ver. 10.2.3

Last edited by delphi_911 (2019-06-17 02:00:26)

delphi_911 · 2019-07-12 02:38:36

const SQL_PEOPLE = 'select 1 as KeyID, firstname from People where firstname = ''YANG''';

Error:"GetFieldData ColumnType=Unknown".

---------------------
@ab, Still this problem. Please check, Thanks.

delphi_911 · 2019-08-22 01:47:54

const SQL_PEOPLE = 'select 1 as KeyID, firstname from People where firstname = ''YANG''';

With a computed field, if no data(0 row) retrieved, raise error:"GetFieldData ColumnType=Unknown".

I have changed source code to avoid this problem in SynDBVCL.pas, but I am not sure whether this is a correct modification.

-------------------------------------------------------------------------

procedure TSynBinaryDataSet.InternalInitFieldDefs;
var F: integer;
    DBType: TFieldType;
begin
    ......
    case ColumnType of
    ......
    SynTable.ftDouble, SynTable.ftCurrency:
      DBType := ftFloat;
    else
      if (fDataAccess.DataRowCount = 0) then //------ Check RowCount  here------
        DBType := ftDefaultMemo else
      raise EDatabaseError.CreateFmt(
        'GetFieldData ColumnType=%s',[TSQLDBFieldTypeToString(ColumnType)]);
    end;
    FieldDefs.Add(UTF8ToString(ColumnName),DBType,ColumnDataSize);
  end;
end;

wienani · 2019-09-30 18:16:11

I already state this topic concatenation https://synopse.info/forum/viewtopic.php?id=3932

The problem sqlite does not know type for concatenation or constant, you can test with SqlLiteStudio

CREATE TABLE t1 AS SELECT 1 AS KeyID, goodsname||goodstype FROM t0

Then look at structure table t1, Data Type is blank, so the solution add CAST every constant or concatenation to tell SQLite for correct data type

CREATE TABLE t1 AS SELECT CAST(1 AS NUMERIC) AS KeyID, CAST(goodsname||goodstype AS TEXT) FROM t0

And i think, at mormot perspective it should yield ColumnType=Unknown for consistency SQLite table structure

Last edited by wienani (2019-09-30 18:18:34)

ab · 2019-09-30 18:57:10

IIRC you may have DataRowCount=0 with some providers, even if data is actually available.

mORMot Open Source

#1 2019-05-28 12:15:49

TSynBinaryDataSet Error [SQLite3]

#2 2019-05-28 13:28:02

Re: TSynBinaryDataSet Error [SQLite3]

#3 2019-05-29 07:40:11

Re: TSynBinaryDataSet Error [SQLite3]

#4 2019-05-29 11:32:41

Re: TSynBinaryDataSet Error [SQLite3]

#5 2019-05-29 13:40:48

Re: TSynBinaryDataSet Error [SQLite3]

#6 2019-05-30 03:11:16

Re: TSynBinaryDataSet Error [SQLite3]

#7 2019-05-30 08:50:23

Re: TSynBinaryDataSet Error [SQLite3]

#8 2019-05-30 08:58:17

Re: TSynBinaryDataSet Error [SQLite3]

#9 2019-06-14 01:34:40

Re: TSynBinaryDataSet Error [SQLite3]

#10 2019-06-14 08:54:52

Re: TSynBinaryDataSet Error [SQLite3]

#11 2019-06-14 12:08:55

Re: TSynBinaryDataSet Error [SQLite3]

#12 2019-06-14 13:40:45

Re: TSynBinaryDataSet Error [SQLite3]

#13 2019-06-14 14:23:25

Re: TSynBinaryDataSet Error [SQLite3]

#14 2019-06-14 16:05:10

Re: TSynBinaryDataSet Error [SQLite3]

#15 2019-06-17 01:57:16

Re: TSynBinaryDataSet Error [SQLite3]

#16 2019-07-12 02:38:36

Re: TSynBinaryDataSet Error [SQLite3]

#17 2019-08-22 01:47:54

Re: TSynBinaryDataSet Error [SQLite3]

#18 2019-09-30 18:16:11

Re: TSynBinaryDataSet Error [SQLite3]

#19 2019-09-30 18:57:10

Re: TSynBinaryDataSet Error [SQLite3]

Board footer