Hi all,

Am I the only one who finds it strange that TryStrToBCD returns false when it succeeds, and true when it fails?

The Delphi documentation says otherwise.

I don't understand the code, but I can revert the true/false logic, and a quick test seems to work. But it will imply backwards incompatibility. Is that a problem if it's documented in the user-changes document on the wiki?

Joost.

Joost van der Sluis wrote

Hi all, Am I the only one who finds it strange that TryStrToBCD returns false when it succeeds, and true when it fails?

It is simply a bug; You can just fix it. No need to mention this.

Michael.

Hi Joost,
it seems, that you have started applying patch in
http://bugs.freepascal.org/view.php?id=18160 or
http://bugs.freepascal.org/view.php?id=16924
Great!
I have some comments, ideas, please consider them. Because in mean time was enhanced FmtBCD unit you can remove some commented lines in fields.inc

1. VarFMTBcdCreate() should already work, so:
2447-                Result := BCDToDouble(bcd)//remove when complete
variant support in fmtbcd.pp will be implemented
2448-                //Result := VarFMTBcdCreate(bcd) //later invalid
variant type cast ?
2448+                Result := VarFMTBcdCreate(bcd)
2. VarToBCD() is already implemented, so:
2548            procedure TFMTBCDField.SetVarValue(const AValue: Variant);
2549-           var E:double;
2550            begin
2551-             E:=AValue;
2552-             SetAsBCD(DoubleToBCD(E));//remove when VarToBCD in
fmtbcd.pp will be implemented
2553-             //SetAsBCD(VarToBCD(AValue));
2553+            SetAsBCD(VarToBCD(AValue));
2554            end;

3. BCDToStrF there is waiting patch
http://bugs.freepascal.org/view.php?id=18388 if applied, then also can be removed comments in procedure TFMTBCDField.GetText

4. Add 3th parameter to TryStrToBCD( const aValue : FmtBCDStringtype; var BCD : tBCD; DecimalSeparator : char = #0) : Boolean; so we can supply directly decimal separator '.' used in SQL and to avoid saving existing, set '.' and then restoring ...
http://svn.freepascal.org/cgi-bin/viewvc.cgi/trunk/packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp?r1=16948&r2=16954 (like TryStrToDate, TryStrToTime)
or
add overloaded TryStrToBCD( const aValue : FmtBCDStringtype; var BCD : tBCD; FormatSettings : TFormatSettings) : Boolean; (like TryStrToFloat)

Thanks
Laco.

LacaK wrote

1. VarFMTBcdCreate() should already work, so:

Done

2. VarToBCD() is already implemented, so:

Done

3. BCDToStrF there is waiting patch http://bugs.freepascal.org/view.php?id=18388 if applied, then also can be removed comments in procedure TFMTBCDField.GetText

I'll leave this bug open for Marco.

And could you add a bug-report for this? I think that Marco or MvC should have a look at it and have to decide which design is best and also compatible to Delphi. Then the db-changes can be made.

Joost

Hi Joost,
thanks!
Thinking about TFmtBCDField it seems to me, that also dsparams.inc must be adjusted to support ftFMTBcd ... add AsBCD: TBCD etc. ... at least my test with new TSQLite3Connection shows, that there is missing it (when applyng updates to record)
Do you have already finished implementing ftFMTBcd into TSQLite3Connection ?

Bug report is here http://bugs.freepascal.org/view.php?id=18807 If this way will be approved, then we can very nice use it (no need to initialize something, store/restore) for example:
...

  if not TryStrToBCD(bcdstr,bcd,SQLFormatSettings) then
  ...

we must only add into TSQLConnection:

    property SQLFormatSettings: TFormatSettings read

FSQLServerFormatSettings;

FSQLServerFormatSetting already exists as private variable

Laco.

Hi,
we have in fcl-db TField.AsBCD: TBCD; but we do not have TParam.AsBCD, nor TParam.AsFMTBCD
Delphi defines:
TParam.AsBCD: Currency ...
http://docwiki.embarcadero.com/VCL/XE/en/DB.TParam.AsBCD TParam.AsFMTBCD: TBCD ...
http://docwiki.embarcadero.com/VCL/XE/en/DB.TParam.AsFMTBCD

But for me sounds more logical and consistent, that TParam.AsBCD will be like TField.AsBCD so result type will be TBCD
(TParam.AsBCD: Currency is more like TParam.AsCurrency: Currency)

So what way to go ?
1. TParam.AsBCD: TBCD
2. TParam.AsFMTBCD: TBCD

Laco.

Well, the Delphi compatible way is usually preferred.

It makes no sense to have both .asBCD and asFMTBCD to have the same result type.

The same can of course be said about .asBCD and .AsCurrency; But since Delphi compatibility is a goal, I think we should follow them.

Borland could perfectly have sufficed with 2 properties (.AsCurrency, AsBCD). For some reason they introduced 3.

Michael.

Ok, here is patch for AsFMTBCD: TBCD ...
http://bugs.freepascal.org/view.php?id=18809
Please review.
It seems, that methods TParam.GetData and SetData are not used at all ... strange ;-)
Laco.

Hi,
can anybody look into dsparams.inc at above mentioned method. There are using 5 assigments : DataType := ...
1. because DataType is property it uses SetDataType method
2. SetDataType method try convert existing FValue into new FieldType
3. but later in AssignFieldValue is FValue rewritten by Value:=AValue (or Cleared)

So it seems to me that using DataType is unnecessary, I would suggest using FDataType:=...
What do you think ?

TIA
Laco.

Hi,
I am writting here to discuss bug
http://bugs.freepascal.org/view.php?id=17268
(I do not want reopen bug and writte there because I am not sure about my arguments)

IMHO root of problem is in different definition of TFieldDef.Size and TField.Size
Documentation says, that
1. TFieldDef.Size should be "For string and byte fields, *Size* is the number of bytes reserved "
http://docwiki.embarcadero.com/VCL/en/DB.TFieldDef.Size
2. TField.Size should be "the maximum number of characters in the string" (for ftString and I guess that also for ftWideString) http://docwiki.embarcadero.com/VCL/en/DB.TField.Size http://docwiki.embarcadero.com/VCL/en/DB.TStringField.Size

And now let's go look into fields.inc:
1. Function TFieldDef.CreateField copies Result.Size:=FSize (so TField.Size:=TFieldDef.Size)
2. function TStringField.GetDataSize returns for ftString ... Size+1
3. function TWideStringField.GetDataSize returns for ftWideString ... (Size + 1) * 2

Points 2,3 are clear Size is expected to be number of characters, DataSize number of bytes.
But point 1 says, that TFieldDef.Size and TField.Size should be the same

And now let's go look into bufdataset.pas:
4. function TCustomBufDataset.GetFieldSize works with TFieldDef and returns number of bytes needed in record buffer

  case FieldDef.DataType of
    ftString, ftGuid, ftFixedChar: result := FieldDef.Size + 1;
    ftFixedWideChar, ftWideString:result := (FieldDef.Size + 1)*2;
    ...

So also here we can see, that FieldDef.Size is expected to be number of characters not bytes.

So IMHO logical conclusion will be say, that TFieldDef.Size for string fields has same menaing as Field.Size, so it is number of characters (so documentation is wrong in this case ... also fast test in Delphi shows, that FieldDef.Size=Field.Size=number of characters)

Laco.

Agreed. In MSEgui tmsestringfield.size is the maximum allowed character count for the field. 0 = no limit. tmsebufdataset stores string data as UnicodeString instead to use a fixed record layout. With some databases it is a little bit tricky to get the field size in characters instead in bytes. In case you don't know MSEgui:

http://developer.berlios.de/projects/mseide-msegui/

Martin

Correct, if you assume 1 char = 1 byte.

(so documentation is wrong in this case ... also fast test in Delphi shows, that FieldDef.Size=Field.Size=number of characters)

Your story is confusing to me. Where do you see a problem ?

Do you see an implementation problem or a documentation problem ?

Note that nowhere in the documentation pages you mention, widestrings are mentioned (which is convenient, since that's where 1 char 1 byte).

Michael.

But here you implicitly assume that you have a fixed number of bytes per character. You should always be explicit about such things, since this is a non-trivial assumption.

Michael.

I don't understand.

Martin

"tmsebufdataset stores string data as UnicodeString instead to use a fixed record layout."

If you say "fixed record layout", this means you assume that each character uses the same amount of bytes, and that the size of the string is limited, otherwise I fail to see how you can have fixed record layout.

Michael.

I really should learn English, but I fear i am too old for the task... What I meant:
Original tbufdataset uses a fixed record layout where for every stringfield memory is reserved to hold the maximum possible amount of bytes of the field. MSEgui tmsebufdataset has a fixed record layout too but stores a UnicodeString which actually is a pointer and uses sizeof(pointer) memory space in record layout. The string data fetched/posted from/to the database is converted from/to system encoding or utf-8 (can be selected with an option flag in the dataset or the connection component) to utf-16 while fetching/posting the data. Because MSEgui completely works with UnicodeString the users don't need to convert the DB-encoding.

Martin

See please
http://svn.freepascal.org/cgi-bin/viewvc.cgi/trunk/packages/fcl-db/src/sqldb/odbc/odbcconn.pas?r1=16094&r2=16988 And look at ftWideString, ftFixedWideChar cases
What happens if we have NVARCHAR(20) column on MS SQL Server (uses UCS-2 1char=2bytes):
1. in AddFieldDefs method is created TFieldDef object with Size=20*2 (because FieldSize:=ColumnSize*sizeof(Widechar))
2. later when TField objects are created in TFieldDef.CreateField method, TWideString object is created with Size=40 (because Size is copied from TFieldDef object) ... and this is wrong, because: 2.1 TWideString.Size should IMO return max.number of characters ... Size is used for example in data-aware controls to limit MaxLength 2.2 TWideStringField.GetDataSize will return 82, which is also wrong (because (40+1)*2)
2.3 in record buffer will be allocated also 82 bytes (because of TCustomBufDataset.GetFieldSize)

So IMO correct will be in step 1 FieldSize:=ColumnSize not
FieldSize:=ColumnSize*sizeof(WideChar)
(like in my original fix in bug report)

Note also, that Size is used (also in Delphi) to limit the number of characters in data-aware controls (for ftString, ftWideString), so it must be number of characters in both cases.

Do you see an implementation problem or a documentation problem ?

Implementation problem at first
but also Documentation is confusing, because do not clearly says about menaing of TWideStringField.Size (only indirect guess can be done, that it is inherited from TStringField.Size, so it is number of characters) See, that also other users are confusing by it
https://forums.embarcadero.com/thread.jspa?messageID=188792

Note that nowhere in the documentation pages you mention, widestrings are mentioned (which is convenient, since that's where 1 char 1 byte).

Yes, but in Delphi6 help is for TWideStringField.DataSize: "Inspect DataSize to determine the number of bytes required to store the fields value. Use DataSize to determine the size needed for a buffer when working with the GetData and SetData methods. For wide string fields, DataSize is the value of the Size property plus one (for the trailing NULL character), multiplied by two."

So we can estimate, that Size is number of characters, while DataSize=(Size+1)*2

Laco.

The only drawback from this system is memory fragmentation for all the strings. This is the advantage of the TBufDataset.

In one of the forms of our application, the users load up to 200.000 records. each containing at minimum 3-4 strings (don't ask why..., they just do) with your system, much more memory would be allocated.

As for encoding:
if ever the unicode string is finished, encoding issues can be dealt with in the DB components 'automatically'.

Michael.

This seems correct, yes.

Yes, but that is an embarcadero bug :-)

Indeed.

Michael.

tmsebufdataset has been tested with more than 1 million records, still usable. Today often varchar fields with big character lengths are used (>32000) which wastes much space in the FPC record layout. Postgres and Sqlite3 users often use unlimited "TEXT" fields which must be handled as blobs. Because of the UnicodeString ability of MSEgui datasets MSEgui can handle such unlimited length text fields as normal text fields.
Although I must say that the handling of UnicodeString in datasets is horribly complicated, the implementation of tmsebufdataset led to many gray hairs and headache...
Also the UnicodeString memory overhead especially on 64 bit is huge, I don't like the plans to add even more fields because of the new encoding aware string type.

Martin

Please, be patient. I'm working on it, as you can see in the bug reports and commits.

As I said earlier, widestringfields aren't available in fpc yet. Someone wrote some code for it, but it is never properly tested and probably only worked by accident.

mse is another story, they have their own implementation.

But I don't commit everything in one go, it's work-in-progress. And I need to write tests for everything that I commit. You mixed two things in one patch, so I had to split it into pieces.

I did some tests with different Delphi versions, with and without unicode. And beside finding some bugs in XE's string-db code I think I know a solution for all problems regarding strings and unicode.

But let me thing it out, write tests, write code and test if it works indeed.

I don't care what the documentation of others say. It is about how we implemented it and how we document things.

I've read the documentation and did some tests. The documentation is simply wrong here. The behavior changed with different versions of Delphi, the documentation is never adapted.

As I wrote in the bug-report, TFieldDef.Size should contain the number of bytes to hold the string, without the leading 0.

Note that with the unicode-versions of Delphi have in TFieldDef.Size the amount of characters * 4 in TFieldDef.Size. For WideStrings and normal strings.

I'm thinking about adding a property to T(Buf)Dataset in which you can specify if you want to use unicode-length strings, or 'normal' lengths. So that we can add backwards-compatilibity, which Delphi doesn't.

2. TField.Size should be "the maximum number of characters in the string" (for ftString and I guess that also for ftWideString) [?]http://docwiki.embarcadero.com/VCL/en/DB.TField.Size [?]http://docwiki.embarcadero.com/VCL/en/DB.TStringField.Size

That's simply true. Also for widestrings. (tested)

Those functions have to be adapted, yes. I have the changes here but I need to fine-tune the tests, clean up etc.

So also here we can see, that FieldDef.Size is expected to be number of characters not bytes.

This was the case in the past. And it didn't lead to problems as widestrings were never used properly. (That's why no-one bothered how it was implemented for widestrings)

So IMHO logical conclusion will be say, that TFieldDef.Size for string fields has same menaing as Field.Size, so it is number of characters (so documentation is wrong in this case ... also fast test in Delphi shows, that FieldDef.Size=Field.Size=number of characters)

No, our implementation is wrong and we have to adapt our code. That way it is also possible to fix the unicode-problems you've reported.

ps: if you want to know what the Delphi-XE-bug is. when using the TClientDataset, for widestrings, TFieldDef.Size is four times the amount of characters (ok), but when you get TWideSTringField.Datasize the answer is two times TfieldDef.Size +2. So if you have a wide-string of 40 characters. TFieldDef.Size is 160, but TField.Datasize 322. (obviously wrong)

Joost.

Joost.

Please, be patient. I'm working on it, as you can see in the bug reports and commits.

ok, of course
I did not know your plans, ideas, thoughts etc.

As I said earlier, widestringfields aren't available in fpc yet. Someone wrote some code for it, but it is never properly tested and probably only worked by accident.

I did not know that, I was thinking about TWideStringFields as a implemented, supported thing

But I don't commit everything in one go, it's work-in-progress. And I need to write tests for everything that I commit. You mixed two things in one patch, so I had to split it into pieces.

ok

ok
if you can inform about your plans, order of tasks etc. ... it will be welcomed
if I can help you, let me know ... for example I have fix for TODBCConnection.SetParameters ... there is missing ftTime and ftWideString,ftWideFixedChar cases ... as revealed by testing with ODBC/MySQL

ok

why do you think so ?
If we do not care about Delphi documentation, it seems to me more consistent, that TFieldDef.Size and TField.Size says about equal things (this is similar story as TField.AsBCD: TBCD and TParam.AsBCD: Currency ... same name, different meaning ... in my opinion something very strange)

would not be better add such property to TSQLConnection class, as IMO all SQLQueries connected to one DB will work with same type of strings. So users can set it once and not for every TCustomSQLQuery object individualy ?

Yes same as in FPC GetDataSize:=(Size+1)*2

Yes, TField.Size = TFieldDef.Size, TField.DataSize = (TField.Size+1)*2

Laco.

LacaK wrote

if you can inform about your plans, order of tasks etc. ... it will be welcomed

The order.. well, that's difficult.. What comes first, comes first. :)

if I can help you, let me know ... for example I have fix for TODBCConnection.SetParameters ... there is missing ftTime and ftWideString,ftWideFixedChar cases ... as revealed by testing with ODBC/MySQL

I'm adding some ftTime tests. All widestring-issues are postponed until I found a way to deal with them, unicode, and have tests to test them.

but now the TFmtBcd field is finally implemented, you can add support for that? Now it only works with sqlite, maybe you can add it for the other databases that you use? (odbc,mysql?)

It depends on how they are used. I think it is very usefull for a programmer to know the maximum amount of characters. (TField.Size) And a programmer should be able to detect how many bytes are reserverd to store it (TFieldDef.Size).
This is especially important if we are supporting unicode-strings, where both values can differ. So when you are getting/setting data from the server, you use TFieldDef.Size, and then you want to check what the maximum amount of characters is, TField.Size.

Maybe. but I have to think about it better. Because if code in fields.inc depends on it, we have to add the property to TDataset itself.

There are more ways to solve that. And we need to find how the Delphi datasets which do not have this problem do that.

One way is: TField.Size := (TFieldDef.Size div 2) if it's a WideString, and div 4 if unicode is enabled. Or the TDataset descendant has to correct the value for WideStrings when creating fields. (How?)

Joost.

I'm adding some ftTime tests.

Did you noticed, that I already posted such tests
http://bugs.freepascal.org/view.php?id=18763 ?
Then let me know, I send you fix for ftTime for TODBCConnection ... but depends on http://bugs.freepascal.org/view.php?id=18773

All widestring-issues are postponed until I found a way to deal with them, unicode, and have tests to test them.

ok

but now the TFmtBcd field is finally implemented,

please look at http://bugs.freepascal.org/view.php?id=18809 and IMO also this bugs must be solved first to continue with implementing TFmtBcdField into connectors
http://bugs.freepascal.org/view.php?id=18388 and
http://bugs.freepascal.org/view.php?id=18807

you can add support for that? Now it only works with sqlite,

yes, but there is missing support for parameter ftFmtBCD in Procedure TSQLite3Cursor.bindparams

maybe you can add it for the other databases that you use? (odbc,mysql?)

yes i can do it, but would wait for fixing above mentioned bugs, to not touch code twice if something in fmtbcd unit changes

yes

And a programmer should be able to detect how many bytes are reserverd to store it (TFieldDef.Size).

yes, but I would preffer introduce new TFieldDef.DataSize property, which will serve for "byte length".
So we will have TFieldDef.Size, TField.Size="character length" and TFieldDef.DataSize, TField.DataSize="byte length"
Then we will be able use TFieldDef.DataSize instead of TCustomBufDataset.GetFieldSize (we can completely drop this) and all fields stuff will be in one unit fields.inc

This is especially important if we are supporting unicode-strings, where both values can differ.

yes

So when you are getting/setting data from the server, you use TFieldDef.Size, and then you want to check what the maximum amount of characters is, TField.Size.

yes, my only objection is, that same name is used for different things, which is confusing, non-intuitive and will/can lead to "misunderstandings"

yes I also must think about it more

hm, we must think about it more

Laco.

I did some tests with different Delphi versions, with and without unicode. And beside finding some bugs in XE's string-db code I think I know a solution for all problems regarding strings and unicode.

Good luck :-)

But let me thing it out, write tests, write code and test if it works indeed.

Just an idea:

First decision is made by the DB layouter, to use fixed-size fields or blobs for strings. Then a fixed DB field size imposes another limit on the maximum character count, depending on the encoding. Chinese users would be happier with a *word* count, or about 4 bytes per visual *glyph*.

With blobs any limit is questionable, at least it should strictly denote the number of visual characters - maybe Unicode codepoints?

DoDi

Very simple/primitive Idea No1:

add property DataSize: integer read GetDataSize write FDataSize; into TFieldDef class

add next parameter ADataSize to Constructor TFieldDef.Create(AOwner:
TFieldDefs; const AName: string;
      ADataType: TFieldType; ASize: Integer; ARequired: Boolean;
AFieldNo: Longint
      ADataSize: Integer=0);
which is used in every TSQLConnection descendant to create fielddefs

So connectors can supply ASize="character length" and also ADataSize="byte length" , which they require to write/read character data into local buffer (each connector should be aware of this information ... i.e. encoding of character data which exchanges with DB engine)
If not specified , then default =0 means use ASize+1 for ftString, (ASize+1)*2 for ftWideString ... for backward compatibility, for most others datatypes ignore it (because they are internaly stored in fixed length data structures).

Each TSQLConnection has CharSet property and each DB engine supports
limited number of connection charsets. So SQLConnection can read CharSet
property and determine "character width".
For example if CharSet='UTF-8' then char_width:=3 (or 4)
if CharSet='UCS-2' then char_width:=2
if CharSet='UTF-16' then char_width:=4;

So with conjuction with "Column size" (ASize) can easy compute buffer size (ADataSize)

or

Constructor TFieldDef.Create(AOwner:
TFieldDefs; const AName: string;
      ADataType: TFieldType; ASize: Integer; ARequired: Boolean;
AFieldNo: Longint
      ACharEncoding: TCharEncoding);
TCharEncoding=(cheUnknown, cheAnsi, cheUTF8, cheUTF16);

and then

ADataSize can be computed inside TFieldDef.Create and stored into FDataSize for later usage.(as I wrote before TFieldDef.GetDataSize will replace TCustomBufDataset.GetFieldSize)

Laco.

Hi,
after doing some test with new implementation of TFmtBCDField for TSQLite3Connection connector I encounter this problem:

When you declare in SQLite some column as NUMERIC or DECIMAL then this column will have NUMERIC affinity.
CREATE TABLE t (d DECIMAL(30,7));

If you insert in such column any numeric value (quoted or unquoted):
INSERT INTO t VALUES('123456789123456789.123456');

SQLite converts such numeric value to 8-byte floating point representation (REAL) or 8-byte integer (INTEGER), so: (lost of precision occurs)
SELECT * FROM t;
returns 123456789123456784

See: http://www.sqlite.org/datatype3.html#affinity "A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values."

My original understanding was, that when I store big numbers as TEXT and lossless conversion to REAL can not be done, then value will be stored as TEXT, but in meaning in SQLite lossless is: " if the first 15 significant decimal digits of the number are preserved" (not all digits only first 15!!!)

I have asked about this in sqlite-users mailing list (topic "storing big numbers into NUMERIC, DECIMAL columns") and results are: (I attach some of comments from sqlite-users mailing list)

A.) if we want preserve precision of BCD values we must store them as BLOBs (using sqlite3_bind_blob()) ... see (1) and (2) in attachment (with optimalization like : if BCDPrecision(...) > 15 then use sqlite3_bind_blob(...) else sqlite3_bind_double(...) ) Here my test shows, that if I store value like BLOB I still can do select like:
SELECT d+100 FROM t;
SELECT sum(d) FROM t;
SELECT * FROM t WHERE d>12345678912345678;
(it seems, that SQLite converts BLOB->TEXT->REAL ...of course precision is lost)

B.) does not support numbers (big exact numbers), which are not supported by SQLite (SQLite supports only REAL and INTEGER). ... see (3) So DECIMAL, NUMERIC map to ftFloat if "decimal places">4 (out of range ftBCD) and map to ftLargeInt if "decimal places"=0

What do you think, which way to go ?

-Laco.

Simply said: So Sqlite doesn't support real BCD values. So we can't support it either for Sqlite.

More difficult: Sqlite is not a 'normal' sql-database. It has all kind of strange issues, it's own definitions and options. It is impossible to support all these gimmicks in sqldb. This holds also in a lesser degree for the other databse-engines, btw.

Practical: (the only important answer) Show me the problem, a bug, or some code that doesn't work. I think we don't have a problem. Sqldb is used to read data from existing tables. Sqlite only has very few data-types (the 'affinities') but to be gentle to our users we do support more, only based on the field-type name. So as long as users can't make tables with numeric fields that support more then 15 numbers, we don't have to support bcd-fields. So map decimal to BCD (not float, it will be invalid for values within the supported precision range). If users want to store numbers with an higher precision, that is simply impossible in sqlite. If they want to store their numbers in blob-fields, they can simply define a blob field and do so...

So where's the problem? (I see the problem of Sqlite not supporting bcd-fields, but the sqldb-problem?)

Joost.

Simply said: So Sqlite doesn't support real BCD values.

Yes, SQLite does not support them native

So we can't support it either for Sqlite.

It was my question. We can (if we want) partialy add work-arounds: DECIMAL(x,y)
if y=0 then map to ftLargeInt (64bit integer)
elseif y<=4 then map to ftBCD (as it is now)
else map to ftFloat (IMHO better than raising exception)

More difficult: Sqlite is not a 'normal' sql-database. It has all kind of strange issues, it's own definitions and options. It is impossible to support all these gimmicks in sqldb.

I agree, and most horrible is, that in one column you can have any datatype in different rows (in 1st row character data, in 2nd integer and in 3th real numbers and in 4th blob)

Sqlite only has very few data-types (the 'affinities') but to be gentle to our users we do support more, only based on the field-type name.

Yes and this is IMO good. If user defines column datatype as numeric or integer or float it can be expected, that also data in that column will be in that format

Yes it is impossible in native way ... but we can help him and do necessary conversion in the background ... question is what user expect, when defines column like DECIMAL(15,7) ?

If they want to store their numbers in blob-fields, they can simply define a blob field and do so...

Yes but BLOB is mapped to TBlobField and there are no methods like AsFloat, AsBCD, AsFMTBCD ... so user must handle all this in their code ... retrieve value convert them etc. , use TField.OnGetText etc. ... it is doable, but not very confort.

So where's the problem? (I see the problem of Sqlite not supporting bcd-fields,

Yes it is primary problem

but the sqldb-problem?

What to do in case when user defines column NUMERIC, DECIAML (x,y) where y>4 or x>15 ?
(I do not like raise exception if there is way how to handle this situation ... and also user IMHO does not expect exception)

As I wrote we should at least do:
DECIMAL(x,y)
if y=0 ---> ftLargeInt
elseif y<=4 ---> ftBCD
else ---> ftFloat (or ftString with Size=x+1 ?)

But we can go far (this is relative simple to implement and gives maximum transparency and easy to use):
if x > 15 or y>4 ---> ftFmtBCD (and here write as BLOB into SQLite3 database in background)
elseif y=0 ---> ftLargeInt
else ---> ftBCD

Laco.

LacaK wrote

> Simply said: So Sqlite doesn't support real BCD values. Yes, SQLite does not support them native

Then it's up to the 'user' (in our case the user is a programmer) to implement workarounds. If we add some work-around, nobody will know that that work-around exist. And other sqlite tools will not be able to handle it, and the 'user' will be puzzled.

No, map to ftfmtbcd, as it should. That will work fine as long as the values are within the sqlite-range.

What we could do is add a possibility for users to override the field-type definition. Add an event or something which is called to determine the field-type. But make this generic, so it works for all datasets. That could be a very strong feature.

What other db-tools do when you use sqlite: work as long as you don't really require the specified precision.

Yes, but it's a sqlite limitation. If users choose to use a tool that is not suited for their goal, it's not the task of sqldb to mask that, imho.

(Well, show me the bug: as it is now, he won't get an exception? Just map to ftmbcd) the user will only get an invalid value when he stores a value outside the sqlite-precision scope. Exactly what he would expect when he uses sqlite.

No, but if you need whit work-around in your own projects, implement a general event to override this behavior. I think that's best.

Joost.

No, map to ftfmtbcd, as it should. That will work fine as long as the values are within the sqlite-range.

ok. in reading phase no problem
(ATM we read using sqlite3_column_text (so SQLite converts all storage classes (integer,real, blob) to string) and then converting to TBCD ... ok

(Well, show me the bug: as it is now, he won't get an exception? Just map to ftmbcd)

yes now no exception it is ok as is

the user will only get an invalid value when he stores a value outside the sqlite-precision scope. Exactly what he would expect when he uses sqlite.

Yes.
I can live with this, but do not forget, that ATM there is not implemented writting of ftFmtBCD parameters in Procedure TSQLite3Cursor.bindparams(AParams : TParams);
So writting never occurs!

So there we must add at least ftFmtBCD case, (behind ftbcd)

But If we add this code (excuse me, I must offer it) ;-):
//it is only pseudo code
ftFmtBCD:
begin
  if P.AsFMTBCD.Precision > 15 then //we are out-of-range
  begin
    str1:=BCDTOStr(P.AsFMTBCD, SQLFormatSettings);
    checkerror(sqlite3_bind_blob(fstatement,I,pcharstr(str1),
length(str1),@freebindstring));
  end
  else
  begin
    do1:=P.AsFloat;
    checkerror(sqlite3_bind_double(fstatement,I,do1));
  end;
end;
// all.
Then all cases will be covered and no additional work around are needed.

Laco.
P.S.: P.AsFMTBCD: http://bugs.freepascal.org/view.php?id=18809

He expects to get/put values of that type, i.e. TFmtBCDField if I understand the discussion right.

Question: is it assumed that the concrete DB is accessed only by FPC apps?

If not, the user is lost. So let's assume that the value can be stored in any format. Now you're free to use either a BLOB or an fixed-length string with the ASCII representation of the number (ToString).

The string representation has the advantage that the format can be determined from the stored data, making the whole thing somewhat type-safe. The size overhead of such an encoding should be compared to the overhead of an blob, in order to find out whether the verbose storage is acceptable. If it wastes too much space, the digits may be encoded in some way, that e.g. maps 2 digits into 100 distinct character codes. The sign can be encoded in the first character, either as its ASCII value, or like a digit with '0' for positive and everything else for negative values. Such an encoding can be used for every DB, that doesn't support BCD numbers natively.

DoDi

No, as long as we can implement it ... so we must do it in way, that values are confortable readable also by others

If not, the user is lost. So let's assume that the value can be stored in any format.

Yes in general value can be stored in any format, but my goal is provide such mechanizm, which will be transparent also to other database connectors outside FPC (idea is based on fact, that SQLite is able to do internaly automatic conversions between BLOB -> TEXT -> INTEGER , REAL

Now you're free to use either a BLOB

yes

or an fixed-length string with the ASCII representation of the number (ToString).

no, because SQLite will try ALWAYS convert such numbers into either INTEGER or REAL (if into string will be valid number i.e. integer like '123456789123456789' or floating point like '123456789123456789.123456') what can lead to lost of precision

I think, that there are 2 ways which are consistent and logical:

1.way is "support only datatypes which directly/natively supports SQLite" -------------------------------------------------------------------------- SQLite support this numeric columns types (called type affinities): 1.1 INTEGER 1-8 bytes (corresponds to FPC ftSmallInt, ftInteger, ftLargeInt) ... declared as TINYINT, SMALLINT, INT, INTEGER, BIGINT 1.2 REAL 8bytes floating point (corresponds to FPC ftFloat) ... declared as REAL, FLOAT, DOUBLE
1.3 NUMERIC combination of 1.1 and 1.2 ... declared as NUMERIC, DECIAML

As we see SQLite does not support exact numeric datatypes (with scale>0) So if we want be strictly consistent with this, then we should map NUMERIC, DECIMAL to either ftLargeInt (if scale=0) or ftFloat (if scale>0) ... so do the same thing as SQLite is doing (do not use FPC exact numeric datatypes ftBCD, ftFmtBCD because we can not save their values into SQLite database without lost of precision).

What we do now ? As example lets look at case when scale<=4 then we map to ftBCD.
1. When reading value from SQLite database (where is stored as 8byte IEEE floating point number) we use sqlite3_column_double ... so value is stored as double and we read it as double
2. then we convert them using FloatToCurr to currency (datatype used by ftBCD)
3. when writting back we write it AsFloat using sqlite3_bind_double

It seems to me strange, why we do all this conversions (we can not guarantee that value inserted into ftBCD will be saved without lost of precision) ? Why not simply map to ftFloat and be sure, that values are stored and read correctly (without conversions).

2. way is "support exact numeric at sqlDB level using some add-on logic and use SQLite REAL, INTEGER or BLOB base datatypes" --------------------------------------------------------------------------------------------------------------------------------------- This way adds some extra logic into SQLiteConnector which can guarantee, that all exact numeric values will be written/read without lost of precision. Base idea is for DECIMAL(precision,scale)
1. if scale=0 and precision<19 then map to ftLargeInt
2. if scale<=4 and precision<15 then map to ftBCD
3. else map to ftFmtBCD

When writting ftFmtBCD values we must test current precision of value and if > 15 then write it as BLOB (sqlite3_bind_blob) Value written as BLOB is still readable (with possible lost of precision) as TEXT (sqlite3_column_text) or INTEGER (sqlite3_column_int64) or REAL (sqlite3_column_double) ... so no problem for others .

Laco.