Devart Forums

Posted: **Tue 01 Nov 2016 00:19**

Hello,

I have a data module in my project that has a UniConnection and some tables. The BeforeOpen event checks to see if the data table exists and creates it using the fields that I added visually at design time. One of the columns ("Oid") - the primary key - is defined as ftGuid.

It seams that no matter what I've listed as the data type for the column any attempt to open the table reports a type mismatch for field 'Oid', expecting: Guid actual: Integer.

My first thought was to try to set up data type mapping but the DataTypeMapping tab does not allow me to type in "GUID" or "UNIQUEIDENTIFIER" and it is not in the provided list.

Here's the code that generates and executes the SQLite CREATE TABLE command.

Code: Select all

procedure TDataServiceModule.CreateSQLiteTable(Connection: TUniConnection;
  Table: TUniTable);
var
  I: Integer;
  sql: String;
  field: TField;
  columns: TArray<String>;
begin
  if not (Assigned(Connection) or Assigned(Table)) then
    exit;

  try
    sql := 'CREATE TABLE [' + Table.TableName + '](' + sLineBreak;

    // Add the columns to the table
    for I := 0 to Table.Fields.Count - 1 do
    begin
      // Get the field definition
      field := Table.Fields[I];

      // If this is not the first column then add a comma and
      // line break to the end of the sql statement
      if I > 0 then
        sql := sql + ',' + sLineBreak;

      sql := sql + '[' + field.FieldName + '] ';

      case field.DataType of
        ftFixedChar, ftFixedWideChar, ftString, ftWideString, ftMemo, ftFmtMemo, ftWideMemo: sql := sql + 'TEXT';

        ftByte, ftInteger, ftShortint, ftSmallint, ftWord, ftLargeint, ftLongWord: sql := sql + 'INTEGER';
        ftBoolean, ftDate, ftTime, ftDateTime: sql := sql + 'NUMERIC';
        ftFloat, ftCurrency, ftSingle, ftExtended: sql := sql + 'REAL';
        ftBlob, ftBytes, ftVarBytes, ftGraphic: sql := sql + 'BINARY';
        ftAutoInc: sql := sql + 'INTEGER AUTOINCREMENT';
        ftGuid: sql := sql + 'UID';
      else
        raise Exception.Create('Unsupported data type');
      end;

      if field.Required or ContainsText(Table.KeyFields, field.FieldName) then
        sql := sql + ' NOT NULL'
      else
        sql := sql + ' NULL';
    end;

    if Table.KeyFields > '' then
    begin
      columns := Table.KeyFields.Split([';']);

      sql :=  sql + ',' + sLineBreak;

      if UniConnection1.ProviderName = 'SQLite' then
        sql := sql + 'PRIMARY KEY('
      else
        sql := sql + 'CONSTRAINT [PK_' + Table.TableName + '] PRIMARY KEY CLUSTERED (';

      for I := 0 to Length(columns) - 1 do
      begin
        // If this is not the first column then add a comma and
        // line break to the end of the sql statement
        if I > 0 then
          sql := sql + ',' + sLineBreak;

        sql := sql + '[' + columns[I] + '] ASC';
      end;

      sql := sql + ')';
    end;

    sql := sql + ')';

    Connection.ExecSQL(sql);
  except
    on E: Exception do
      ShowMessage(E.Message);
  end;
end;

Posted: **Tue 01 Nov 2016 15:17**

SQLite uses a dynamic data type system. Therefore, when mapping UniDAC data types, the list of possible values contains only the most common types. When UniDAC, working with SQLite cannot determine a field type, it creates it with a Memo type. You can change this behavior by using mapping by a field name:

Code: Select all

UniQuery.DataTypeMap.AddFieldNameRule(<your field name>, ftString, 38);

Detailed information about using DataMapping is available here : https://www.devart.com/litedac/docs/?da ... apping.htm

Devart Forums

How to define a GUID field for SQLite

How to define a GUID field for SQLite

Re: How to define a GUID field for SQLite