Problem with ODBC and Firebird

Roberto Schneiders · 2013-01-18 18:45:14

After solving the problem in the creation of tables in firebird I found another problem.
http://synopse.info/forum/viewtopic.php?pid=6279#p6279

I can not add any records. I tried to debug the EngineAdd method of TSQLRestServerStaticExternal class. The problem with this method is that the field fFieldsDynArray apparently does not have the table fields.

I found a problem in the Create method of this class.
This code:

  // compute the SQL statements used internaly for external DB requests
  fSelectOneDirectSQL := StoredClassProps.SQL.TableSimpleFields[true,false];
  if IdemPChar(pointer(fSelectOneDirectSQL),'ROWID') then
    System.Delete(fSelectOneDirectSQL,1,3); // RowID -> ID when executed in direct
  fSelectOneDirectSQL := FormatUTF8('select % from %',[fSelectOneDirectSQL,fTableName]);
  if fOwner<>nil then // override only if was not retrieved  from fModel[0]
    fStoredClassProps.SQL.SelectAll[true] := fSelectOneDirectSQL;
  fSelectOneDirectSQL := fSelectOneDirectSQL+' where ID=?';
  fSelectAllDirectSQL := FormatUTF8('select %,ID from %',
    [StoredClassProps.SQL.InsertSet,fTableName]);
  fSelectTableHasRowsSQL := 'SELECT ID FROM '+fStoredClassRecordProps.SQLTableName+' LIMIT 1';

generates this select:

SELECT ID FROM People LIMIT 1

This select is not compatible with firebird. The right way is:

SELECT FIRST 1 ID FROM People

I changed the code to see if the cause of the problem is only that, but it is not.

Apparently the methods "Init" or "ReHash" are not working as expected in this part of the code (TSQLRestServerStaticExternal.Create):

  fFieldsDynArray.Init(TypeInfo(TSQLDBColumnDefineDynArray),fFields,nil,nil,nil,nil,true);
  fFieldsDynArray.ReHash;

The fFields has this value:

(('ID                             ', '160', ftInt64, 8, 8, 0, True), ('NAME                           ', '2611', ftUTF8, 8, 8, 0, True), ('AGE                            ', '160', ftInt64, 8, 8, 0, True))

The FindHashed method always returns -1 in this code (TSQLRestServerStaticExternal.ExecuteFromJSON):

    for F := 0 to Decoder.FieldCount-1 do begin
      k := fFieldsDynArray.FindHashed(Decoder.FieldNames[F]);
      if k<0 then
        exit; // JSON field does not exist in the external DB table
      Types[F] := fFields[k].ColumnType;
    end;

Decoder has this value:

(('ID', 'Name', 'Age', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''), ('1', 'Roberto', '25', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''), [], 3, 19, False, $18EED8, 1)

ab · 2013-01-18 19:03:46

  fSelectTableHasRowsSQL := 'SELECT ID FROM '+fStoredClassRecordProps.SQLTableName+' LIMIT 1';

is in fact followed by:

AdaptSQLForEngineList(fSelectTableHasRowsSQL);

So the generated SQL should be adapted to Firebird.

Something like: SELECT FIRST 1 ID FROM TableName

Are you sure you got the latest version?
Is the connection is identified as dFirebird kind of database ?

Roberto Schneiders · 2013-01-18 19:15:21

Yes, Connection.DBMS = dFirebird;

I was wrong again. I did not realize that this line was the SQL parser.

The Create method is correct.

Last edited by Roberto Schneiders (2013-01-18 19:18:19)

Roberto Schneiders · 2013-01-21 12:52:17

I still can not identify why the fFieldsDynArray.FindHashed not find the fields of the class / table.

Any tips?

ab · 2013-01-21 13:39:55

Because the array is void?

Roberto Schneiders · 2013-01-21 15:49:38

Not exactly. Does not appears to be empty.

After the ReHash (TSQLRestServerStaticExternal.Create) method fHashs (fFieldsDynArray.fHashs) property has this value:

((0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), ( 0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0) , (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), ( 0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0) , (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), ( 0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0) , (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), ( 0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0) , (3930815333} {$ EA4B7B65, 2), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0) , (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), ( 0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0) , (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), ( 0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (1307410844 4DED819C} {$, 0), (0, 0) , (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), ( 0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0) , (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), ( 0, 0), (0, 0), (3472868801 CEFFC5C1} {$, 1), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0) , (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), ( 0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0) , (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), ( 0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0) , (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0))

Apparently have 3 values.

ab · 2013-01-21 16:26:21

Try to change line 2753 in SynDB.pas as such:

F.ColumnName := trim(ColumnUTF8(0));

Or in line 1597 of SynDBODBC.pas:

F.ColumnName := trim(ColumnUTF8(3));

Sounds like each column name has some unexpected spaces with FireBird.

Roberto Schneiders · 2013-01-21 16:35:20

it worked!

Roberto Schneiders · 2013-01-21 18:22:00

I changed the TPeople class definition. I added a Birthdate property.

  TPeople = class (TSQLRecord)
  private
    FName: RawUTF8;
    FAge: Integer;
    FBirthDate: TDateTime;
  published
    property Name:RawUTF8 read FName Write FName;
    property Age:Integer read FAge Write FAge;
    property BirthDate:TDateTime Read FBirthDate Write FBirthDate;
  end;

The framework did not create the new field on the firebird database.

The DB.GetFieldNames method (in TSQLRestServerDB.CreateMissingTables) returns these names:

('Name', 'Age', 'BirthDate')

But BirthDate field does not exist in the database.

ab · 2013-01-21 21:57:07

I'll try to reproduce it.

But GetFieldNames() ask the database for the available fields... so I do not understand how they may not exist in the database!

Roberto Schneiders · 2013-01-22 10:27:04

I think it exists in the SQLite database but not in the external database (Firebird).

If I find something I notice.

ab · 2013-01-22 11:10:42

I suspect TSQLRestServerStaticExternal.Create() creates any missing table, but does not check for missing field yet.

I've created a ticket http://synopse.info/fossil/info/9a821d26ee to follow this issue.

Roberto Schneiders · 2013-01-22 11:40:01

Thanks Arnaud.

ab · 2013-01-22 13:15:49

Should be fixed by http://synopse.info/fossil/info/87f5d4686a

Roberto Schneiders · 2013-01-22 13:27:40

It's working. Thank you.

Roberto Schneiders · 2013-02-01 10:59:21

Why this code:

var
  Municipio: TMunicipio;
  Pais: TPais;
  Estado: TEstado;
begin
  Estado := TEstado.Create;
  Estado.Sigla := 'SC';
  Estado.Nome := 'Santa Catarina';

  Pais := TPais.Create;
  Pais.Descricao := 'Brasil';

  Municipio := TMunicipio.Create;
  Municipio.Descricao := 'São Miguel D''Oeste';
  Municipio.DescricaoReduzida := 'SMO';
  Municipio.Cep := 89899000;
  Municipio.DDD := 49;
  Municipio.Estado := Estado;
  Municipio.Pais := Pais;

  fClient.Add(Estado, true);
  fClient.Add(Pais, true);
  fClient.Add(Municipio, true);
end;

writes this information in the tables of the database?

TABLE: PAÍS
ID = 1
DESCRICAO = Brasil

TABLE: ESTADO
ID = 1
SIGLA = SC
NOME = Santa Catarina

TABLE: MUNICÍPIO
ID = 1
DESCRICAO = São Miguel D'Oeste	
DESCRICAOREDUZIDA = SMO	
CEP = 89899000	
DDD	= 49
ESTADO = 57447296		
PAIS = 57447328

Should not write ESTADO = 1 e PAIS = 1 in table MUNICIPIO?

Before writing MUNICIPIO the objects ESTADO and PAIS have their IDs.

ab · 2013-02-01 13:03:25

Read the documentation about the TSQLRecord fields, especially paragraph " "One to one" or "One to many" ".

Those published fields are not real instances of TSQLRecord classes, but a typecast to an integer of their ID.
Only exception is TSQLRecordMany fields, which are real instances.
There is an item on the road map to allow "lazy-instantiation" of TSQLRecord properties, on need.

So you have to write:

 Municipio.Pais := pointer(Client.Add(Pais)); // compute a new Pais.ID, then store it as reference
 // or Municipio.Pais := TMunicipio(Client.Add(Pais));
 Client.Add(Municipio);

I've just introduced the AsTSQLRecord property, which has the advantage of working also on 64 bit:

 Client.Add(Pais); // you need Pais.Id to be available
 Municipio.Pais := Pais.AsTSQLRecord;
 Client.Add(Municipio);

I've also updated the documentation to clarify it.
See http://synopse.info/fossil/info/343160d845

When accessing the detail objects, you have an overloaded constructor accepting a TSQLRecord parameter as stored in a published property:

constructor TSQLRecord.Create(aClient: TSQLRest; aPublishedRecord: TSQLRecord: ForUpdate: boolean=false)

As extracted from the documentation:

Note that you those two assignments are the same:

MyFile.FirstOne := TSQLMyFileInfo(MyDataBase.Add(One,True));
MyFile.FirstOne := pointer(MyDataBase.Add(One,True));

Or you may have added the One row first:

MyDatabase.Add(One,true);

then assigned it to the MyFile record on one of the following expressions:

MyFile.FirstOne := TSQLMyFileInfo(One.ID);
MyFile.FirstOne := pointer(One.ID);
MyFile.FirstOne := One.AsTSQLRecord;

The first two statements, using a class/pointer type cast will work only in 32 bit (since ID is an integer). Using TSQLRecord.AsTSQLRecord property will work on all platforms, including 64 bit, and is perhaps easier to deal with in your code.

When accessing the detail objects, you should not access directly to FirstOne or SecondOne properties (there are not class instances, but integer IDs), then use instead the TSQLRecord. Create(aClient: TSQLRest; aPublishedRecord: TSQLRecord: ForUpdate: boolean=false) overloaded constructor, as such:

var One: TSQLMyFileInfo;
MyFile: TSQLMyFile;
begin
MyFile := TSQLMyFile.Create(Client,aMyFileID);
try
// here MyFile.FirstOne.MyFileDate will trigger an access violation
One := TSQLMyFileInfo.Create(Client,MyFile.FirstOne);
try
// here you can access One.MyFileDate or One.MyFileSize
finally
One.Free;
end;
finally
MyFile.Free;
end;
end;

Or with a with statement:

with TSQLMyFileInfo.Create(Client,MyFile.FirstOne) do
try
// here you can access MyFileDate or MyFileSize
finally
Free;
end;

Mapping a TSQLRecord field into an integer ID is a bit difficult to learn at first. It was the only way we found out in order to define a "one to one" or "one to many" relationship within the class definition, without any property attribute features of the Delphi compiler (only introduced in newer versions). The main drawback is that the compiler won't be able to identify at compile time some potential GPF issues at run time. This is up to the developper to write correct code, when dealing with TSQLRecord properties. Using AsTSQLRecord property and overloaded TSQLRecord. Create(aPublishedRecord) constructor will help a lot.

Roberto Schneiders · 2013-02-01 13:15:09

Got it.

Thank you.

Junior/RO · 2013-02-01 16:55:05

Roberto, I am from Brazil too. My parents live near to you, in Chapeco. Can I add you in Facebook?

Roberto Schneiders · 2013-02-01 17:08:30

sure.

Good to know that has more Brazilians using mORMot.

eraldo · 2013-02-02 13:10:45

I am from Brazil too.

ab · 2013-02-02 15:36:22

Viver Brasil!

mORMot Open Source

#1 2013-01-18 18:45:14

Problem with ODBC and Firebird

#2 2013-01-18 19:03:46

Re: Problem with ODBC and Firebird

#3 2013-01-18 19:15:21

Re: Problem with ODBC and Firebird

#4 2013-01-21 12:52:17

Re: Problem with ODBC and Firebird

#5 2013-01-21 13:39:55

Re: Problem with ODBC and Firebird

#6 2013-01-21 15:49:38

Re: Problem with ODBC and Firebird

#7 2013-01-21 16:26:21

Re: Problem with ODBC and Firebird

#8 2013-01-21 16:35:20

Re: Problem with ODBC and Firebird

#9 2013-01-21 18:22:00

Re: Problem with ODBC and Firebird

#10 2013-01-21 21:57:07

Re: Problem with ODBC and Firebird

#11 2013-01-22 10:27:04

Re: Problem with ODBC and Firebird

#12 2013-01-22 11:10:42

Re: Problem with ODBC and Firebird

#13 2013-01-22 11:40:01

Re: Problem with ODBC and Firebird

#14 2013-01-22 13:15:49

Re: Problem with ODBC and Firebird

#15 2013-01-22 13:27:40

Re: Problem with ODBC and Firebird

#16 2013-02-01 10:59:21

Re: Problem with ODBC and Firebird

#17 2013-02-01 13:03:25

Re: Problem with ODBC and Firebird

#18 2013-02-01 13:15:09

Re: Problem with ODBC and Firebird

#19 2013-02-01 16:55:05

Re: Problem with ODBC and Firebird

#20 2013-02-01 17:08:30

Re: Problem with ODBC and Firebird

#21 2013-02-02 13:10:45

Re: Problem with ODBC and Firebird

#22 2013-02-02 15:36:22

Re: Problem with ODBC and Firebird

Board footer