0

I am using the latest Zeos with SQLite 3. It is generally going well, converting from MySQL, once we made all the persistent integer field TLargeInt.

But when we use a column definition unsigned big int (the only unsigned type allowed according to https://www.sqlite.org/datatype3.html), Delphi is calling the resulting field a ftWidestring.

CL.
  • 173,858
  • 17
  • 217
  • 259
MarkAurelius
  • 1,203
  • 1
  • 14
  • 27
  • 2
    Those type names on the page you link to are *examples*, and show how SQLite interprets type names. There is no unsigned type. – CL. Sep 21 '15 at 07:13
  • OK, so it reverts to string as a default when the DDL is correct in the standard, but doesn't match a SQLite type? – MarkAurelius Sep 21 '15 at 07:14
  • 2
    SQLite uses dynamic typing, and does *not* have column types. Read the documentation! How Zeos then interprets type names is differrent issue. – CL. Sep 21 '15 at 07:17
  • Thanks! I'm so used to strongly typed databases like MSSQL, MySQL, Interbase / Firebird that my tiny mind is resisting this idea of flexible column types. – MarkAurelius Sep 21 '15 at 07:55

3 Answers3

6

No, it does not "revert" to string, SQlite just stores the data as it is provided.

As the documentation states:

SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".

If you supplied/bind a text value, it would store a text value. There is no conversion to the type supplied in the CREATE TABLE statement, as it may appear in other more strict RBMS, e.g. MySQL.

So in your case, if you retrieve the data as ftWideString, I guess this is because you wrote the data as TEXT. For instance, the tool or program creating the SQLite3 content from your MySQL is writing this column as TEXT.

About numbers, there is no "signed"/"unsigned", nor precision check in SQLite3. So if you want to store "unsigned big int" values, just use INTEGER, which are Int64.

But, in all cases, even if SQLite3 API does support UNSIGNED 64 bit integers, this sqlite3_uint64 type may hardly be supported by the Zeos/ZDBC API or by Delphi (older versions of Delphi do NOT support UInt64). To be sure, you should better retrieve such values as TEXT, then convert it as UInt64 manually in your Delphi code.

Update:

Are you using the TDataSet descendant provided by Zeos? This component is tied to DB.Pas, so expects a single per-column type. It may be the source of confusion of your code (which you did not show at all, so it is hard to figure out what's happening).

You should better use the lower level ZDBC interface, which allows to retrieve the column type for each row, and call the value getter method as you need.

Arnaud Bouchez
  • 42,305
  • 3
  • 71
  • 159
  • 1
    `sqlite3_uint64` is not used for column values. `ftWidestring` is assigned by Zeos/Delphi to a *column* without looking at the actual values; it's probably derived from the declared type name, somehow. – CL. Sep 21 '15 at 08:02
  • Yes, that makes sense. There was no data in the table. We only had the declaration at that point. – MarkAurelius Sep 22 '15 at 00:19
1

Zeos uses the following code (in ZDbcSqLiteUtils.pas) to determine a column's type:

Result := stString;
...
if StartsWith(TypeName, 'BOOL') then
  Result := stBoolean
else if TypeName = 'TINYINT' then
  Result := stShort
else if TypeName = 'SMALLINT' then
  Result := stShort
else if TypeName = 'MEDIUMINT' then
  Result := stInteger
else if TypeName = {$IFDEF UNICODE}RawByteString{$ENDIF}('INTEGER') then
  Result := stLong //http://www.sqlite.org/autoinc.html
else if StartsWith(TypeName, {$IFDEF UNICODE}RawByteString{$ENDIF}('INT')) then
  Result := stInteger
else if TypeName = 'BIGINT' then
  Result := stLong
else if StartsWith(TypeName, 'REAL') then
  Result := stDouble
else if StartsWith(TypeName, 'FLOAT') then
  Result := stDouble
else if (TypeName = 'NUMERIC') or (TypeName = 'DECIMAL')
  or (TypeName = 'NUMBER') then
begin
 { if Decimals = 0 then
    Result := stInteger
  else} Result := stDouble;
end
else if StartsWith(TypeName, 'DOUB') then
  Result := stDouble
else if TypeName = 'MONEY' then
  Result := stBigDecimal
else if StartsWith(TypeName, 'CHAR') then
  Result := stString
else if TypeName = 'VARCHAR' then
  Result := stString
else if TypeName = 'VARBINARY' then
  Result := stBytes
else if TypeName = 'BINARY' then
  Result := stBytes
else if TypeName = 'DATE' then
  Result := stDate
else if TypeName = 'TIME' then
  Result := stTime
else if TypeName = 'TIMESTAMP' then
  Result := stTimestamp
else if TypeName = 'DATETIME' then
  Result := stTimestamp
else if Pos('BLOB', TypeName) > 0 then
  Result := stBinaryStream
else if Pos('CLOB', TypeName) > 0 then
  Result := stAsciiStream
else if Pos('TEXT', TypeName) > 0 then
  Result := stAsciiStream;

If your table uses any other type name, or if the SELECT output column is not a table column, then Zeos falls back to stString. There's nothing you can do about that; you'd have to read the values from the string field (and hope that the conversion to string and back does not lose any information).

It might be a better idea to use some other library that does not assume that every database has fixed column types.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • It should use the API to retrieve the column type of the actual state, i.e. `sqlite3_column_type()` - see https://www.sqlite.org/c3ref/column_blob.html – Arnaud Bouchez Oct 01 '15 at 07:34
  • That function returns the type of the value *in the current row*, but the Delphi database framework assumes that there is a fixed type per column. – CL. Oct 01 '15 at 09:09
0

The latest Zeos, that is, which one? See if it's the same in 7.2 svn 3642: http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.2/

Michal

miab3
  • 1