sqlite type affinity and multiple data types in one column

[sandy771]

I have a database where a column is defined in the DB schema as an int but the different rows contain both integers and reals (this happens surprisingly often in SQLite databases).

When I connect a DBGrid to a uniquery only the integral portion of the floating point types are displayed.

What I would like is a method of displaying the underlying data irrespective of the stored type and the table schema.

Thank You

[sandy771]

Any solution to this?

[AlexP]

Hello,

If you want to display float values correctly in INT fields, you should set a mapping rule for these fields. For example:

Code: Select all

UniTable1.DataTypeMap.AddFieldNameRule('id', ftFloat);

See more details in our documentation:

https://www.devart.com/unidac/docs/?dat ... apping.htm

[sandy771]

Thank You

Have tried that and it has not worked for me any ideas?

[AlexP]

If the field type was set as INT on database creation, then the converter won't work indeed. If you declare the field as INTEGER, then mapping will work and data will be displayed correctly.

We will add the missing converter in the next version.

[sandy771]

Hi Alex

Thank You for your reply - changing the table schema to integer does work after a fashion (at least on my contrived table) but it also gives me other problems.

All of the larger integers are now display with an exponent, i.e.



What I actually need is a generic solution that shows me the data EXACTLY as it is stored in the table, in the same way as the Firefox sqlite browser plugin does:



I should also emphasise that that the database schemas are outside of my control, my product can be used to look at any SQLite database. So I really need a generic way of at getting at the raw data irrespective of the column definition or the stored data. This is quite crucial to my application.

Thank You

[AlexP]

The below code demonstrates correct functioning of DataTypeMapping. Please modify so that the issue is reproduced, and send it back to us.

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, Data.DB, Uni, SQLiteUniProvider;

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;

begin
  UniConnection := TUniConnection.Create(nil, 'ProviderName=SQLite;Database=:memory:');
  try
    UniConnection.Connect;
    UniConnection.ExecSQL('create table test(id integer)');
    UniConnection.ExecSQL('insert into test values(1),(1.2)');
    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection;
      UniQuery.SQL.Text := 'select id from test';
      UniQuery.DataTypeMap.AddFieldNameRule('id', ftfloat);
      UniQuery.Open;
      while not UniQuery.Eof do begin
        WriteLn(UniQuery.Fields[0].AsString);
        UniQuery.Next;
      end;
    finally
      UniQuery.Free;
    end;
  finally
    UniConnection.Free;
    readln;
  end;
end.

[sandy771]

Hi

code is

Code: Select all

void __fastcall TForm2::Button3Click(TObject *Sender)
{
	{
		UniQuery1->DataTypeMap->AddFieldNameRule("raw", ftFloat);

		UniQuery1->SQL->Clear();
		UniQuery1->SQL->Add("select * from default_dates2");
		UniQuery1->Open();

		while(!UniQuery1->Eof)
		{
			Memo2->Lines->Add(UniQuery1->FieldByName("raw")->AsString);
			UniQuery1->Next();
		}
	}
}

output :

1170245478
1176469232719
1.28834235493174E16
219216022
392236067
1.28318913377338E17
3539622345
38401.25
42439.766146
1440439957

[AlexP]

Yes, if data is more than integer and the field is INT/INTEGER, then the data will be displayed incorrectly, not depending on whether mapping is used or not. We will add corresponding mapping rules in the next version.

[sandy771]

OK thanks Alex

Would it be possible to do something along the lines of :

->GetRawType
->AsRawType

that respectively get one of the underlying SQLIte types (Null, integer, real, text, blob) and then return the value as a Variant? This would of course be irrespective of any DataTypeMapping

Cheers

[AlexP]

Please describe the required functionality in more details.

[sandy771]

Hi Alex

The functionality I am suggesting is something along the lines of adding to the functionality of TUniQuery to allow me to a) determine what data type SQLite is actually using to store a particular value and b) get the correspondin value as a Variant.

so, one idea would be a function:

UniQuery->FieldByName("somefield")->GetRawType()

which would return either a string or better an integer that represented the type of data. The corrresponding function in SQLite is:

typeof(X) - The typeof(X) function returns a string that indicates the datatype of the expression X: "null", "integer", "real", "text", or "blob".

I would then need an associated function

UniQuery->FieldByName("somefield")->GetRawValue()

That would return a Variant with the value from the field, based on the type returned by GetRawType I could then take whatever action is appropriate with the Variant.

All of this would need to work irrespective of any DataTypeMapping rules and should allow me to detect any fields where someone has taken advantage of SQLites loose type affinity rules.

This is of course my suggestion of a way to resolve this - you may have a better idea

Thank You

[AlexP]

Yes, SQLite has a limited number of basic types, but a user can set any type, even not a corresponding to existing types, for example:

CREATE TABLE TEST(ID MY_TYPE1, TXT MY_TYPE2);

And SQLIte will create such fields and return the type name MY_TYPE1 and MY_TYPE2 respectively. It is not quite clear what basic type must be returned in this case, since data in such fields may be of various types.
Data type in a field is defined on reading each field, but we can't change field types for each record.
Therefore we set the ftMemo type for unknown fields and read data as string.

[sandy771]

Thanks Alex, that is correct and it is entirely the point of my request

While you can set a user type the underlying type, as returned by the sqlite function typeof(my_type1), is one of just 5 basic types that sqlite supports.

It is the basic underlying type that SQ:Lite chooses to store the data at a binary level that i am interested in. If I was not using UniDac but was using the SQLite library then I can get at this data using typeof(x).

It is this additional functionality that I need.

[AlexP]

The type returned by the typeof function, as well as by the sqlite3_column_type method, may differ for each record in the filed. I.e., for the table

Code: Select all

CREATE TABLE [TEST_TYPE] ([ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [TEST] MY_FIELD);
INSERT INTO [TEST_TYPE] ([TEST]) VALUES(NULL);
INSERT INTO [TEST_TYPE] ([TEST]) VALUES(1);
INSERT INTO [TEST_TYPE] ([TEST]) VALUES(1.1);
INSERT INTO [TEST_TYPE] ([TEST]) VALUES('A');
INSERT INTO [TEST_TYPE] ([TEST]) VALUES(x'0100');

the query

Code: Select all

select typeof([TEST]) from [TEST_TYPE]

will really return correct types:

Code: Select all

null
integer
real
text
blob

But we don't know where to return these types, since there is no classes describing each cell in the DataSet. The TField class is used for all fields, and it has a common type for every field.

You can complicate your query - and get these data using the typeof method by separate fields.

Code: Select all

select ID, TEST, typeof([TEST]) from [TEST_TYPE]

1  null
2 1 integer
3 1.1 real
4 A text
5  blob