I need help on a simple example using ODBC.

Roberto Schneiders · 2013-01-11 17:50:13

I'm doing tests aiming learn better the framework. I read all the documentation but still do not understand everything. There are many things and the documentation is quite large. Very different from what I was used (Embarcadero libraries)

I tried to look at the code examples that exist in the documentation but I have not found how to do this.

what I want to do is a simple example:
- Connect to a database ODBC (This is ok).
- Test the command "CreateMissingTables" (This is not working yet)
- Write something using ORM.

Then, of course, want to go further in ORM.

First, I created an object to represent a table that does not yet exist in the database:

  TPeople = class (TSQLRecord)
  private
    FName: RawUTF8;
    FAge: Integer;
  public
    property Name:RawUTF8 read FName Write FName;
    property Age:Integer read FAge Write FAge;
  end;

I'm trying to do so, and certainly not the right way, because it does not work.

  fModel := TSQLModel.Create([TPeople]);
  fConnection := TODBCConnectionProperties.Create('teste', 'teste', 'user', 'password');
  VirtualTableExternalRegister(fModel,TPeople,fConnection,'People');
  fConnection.MainConnection.Connect;
  if fConnection.MainConnection.IsConnected then
  begin
    fClient := TSQLRestClientDB.Create(fModel,nil,'test.db3',TSQLRestServerDB);
    fClient.Server.CreateMissingTables;
  end;

Until line 6 all ok. I think the problem starts at line 7. The method TSQLRestClientDB.Create asks for the third parameter an "ADB: TSQLDataBase" or "aDBFileName: TFileName;". I have no idea how to get the object of type TSQLDatabase. In the examples of the documentation is always passed a string (TFileName) and I have no idea what it is.

Anyway. the line 7 works, gives no error.
The command fClient.Server.CreateMissingTables returns an access violation. This error occurs when calling the method "GetVirtualTableSQLCreate (RecordProps)" at line 17516 of mOTMot.pas unit.

I appreciate if someone can help me.

ab · 2013-01-12 09:38:12

Change "public" properties to "published" properties.

Roberto Schneiders · 2013-01-14 10:12:29

Thanks Arnaud.

Can you explain the third parameter of the constructor of the class TSQLRestClientDB? What 'test.db3' mean?

When I use the TSQLDataBase and when I use TFileName? how to get the object of type TSQLDatabase

ab · 2013-01-14 16:40:57

There is always a "main SQLite3 database", when using mORMot with its SQLite3 core.
This is the database file name of this main database.

This main database will have some SQLite3 or virtual tables defined by the ORM, to access the external databases, if needed.
Even if all your tables are externals, you need this main database in order to enjoy SQLite3 JOINed queries using those external tables.
Most of the time, for CRUD operations, the mORMot core will bypass the SQLite3 engine and will directly ask the external databases. But if you make a JOINed query, or a complex query, mORMot will use the main SQlite3 database definitions to work as expected.
You can even define your main database as "pure memory", by setting ':memory:' as database file name, if all your tables are external.
Or use virtual external tables as TSQLRestServerStaticInMemory, which are very fast TOBjectList-based in-memory storage.

The documentation is pretty complete about this flexible and very powerful mechanism.
See the "External database" chapter.

In this constructor, TSQLDataBase is a low-level SQLite3 database, as defined in SynSQLite3.
You should not use it usually, only if required by some low-level code, if you want to expose an already existing SQLite3 database instance to the ORM.

Roberto Schneiders · 2013-01-14 17:52:28

Ok I get it.

I've got a problem. I put the properties into published. The error no longer occurs. but ...

The method "CreateMissingTables" is not creating the table in my database.

The Add method (Client.add) always returns 0.

Any idea?

ab · 2013-01-17 19:22:41

Is your database existing?
Is your database void?

Could you step in the CreateMissingTables() procedure and see what's going on here?

I'm working on direct FireBird access, with no ODBC.
This would be a much better solution, and I would probably include the embedded version into the main mORMot regression tests.
See http://synopse.info/fossil/finfo?name=SynDBFirebird.pas
Not working yet at all, but I think you got the idea.
Any proposal is welcome.

Roberto Schneiders · 2013-01-18 10:30:18

Yes, the database exists.
No, it is not empty.

I'll try to debug. If I get any information I notice.

Native driver for firebird is excellent news. We work with Firebird and PostgreSQL. I was already satisfied with the performance using ODBC with native driver think it will be excellent. Thanks Arnaud.

Roberto Schneiders · 2013-01-18 12:52:12

I'm debugging CreateMissingTables method trying to identify the problem.

Apparently is behaving as expected. In line 725 we have this code:

DB.Execute(Model.GetSQLCreate(t)); // don't catch exception in constructor

the method Model.GetSQLCreate(t) returns the following SQL:

CREATE VIRTUAL TABLE People USING External(Name TEXT COLLATE SYSTEMNOCASE, Age INTEGER);

The method db.Execute apparently runs successfully.

When attempting to open the SQLite file using SQLiteExpert an error occurs when loading the schema of the table People.
"Error refreshing schema for table "People":_no such module:_External."

Last edited by Roberto Schneiders (2013-01-18 13:03:17)

ab · 2013-01-18 13:23:14

This does make sense.
The "CREATE VIRTUAL TABLE" is executed at the main SQLIte3 engine level, to create the virtual tables mapping the external tables.

Go to TSQLRestServerStaticExternal.Create(), but a breakpoint in it, and see why it does not create the external tables.

Roberto Schneiders · 2013-01-18 16:19:24

Here we go.

The SQL generated by the framework is this:

CREATE TABLE People (ID  INT64 PRIMARY KEY, Name BLOB SUB_TYPE 1 SEGMENT SIZE 2000 CHARACTER SET UTF8,Age INT64)

But the firebird does not support INT64. Actually it is declared as BIGINT.

/*******************************************************************************
The next statement causes the following error:

Dynamic SQL Error.
SQL error code = -607.
Invalid command.
Specified domain or source column INT64 does not exist.
*******************************************************************************/

The ExecuteDirect command returns nil.

  if fFields=nil then begin
    // table is not yet existing -> try to create it
    SQL := fProperties.SQLCreate(fTableName,TSQLRecordClassToExternalFields(aClass));
    if SQL<>'' then
      if ExecuteDirect(pointer(SQL),[],[],false)<>nil then
        fProperties.GetFields(fTableName,fFields); // fields from DB after create
  end;

This SQL command works fine:

CREATE TABLE People (ID  BIGINT PRIMARY KEY, Name BLOB SUB_TYPE 1 SEGMENT SIZE 2000 CHARACTER SET UTF8,Age BIGINT)

http://www.firebirdsql.org/manual/migra … types.html

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

ab · 2013-01-18 17:02:39

I was misleaded by http://www.firebirdsql.org/manual/migra … types.html
It clearly states "INT64" in the FireBird column!

FireBird is still experimental.
Direct access will make it much better.

Fixed by http://synopse.info/fossil/info/127d84967e

Thanks for the report.

Roberto Schneiders · 2013-01-18 18:08:59

I figured when I saw the documentation.

Thank you.

ab · 2013-01-18 19:05:39

By the way, you should better specify an "index 50" for the "Name" published property, to generate a varchar(50) instead of a textual blob, which will probably be slower and less optimized for just a name.

Roberto Schneiders · 2013-01-18 19:08:49

Absolutely. I had seen this in the documentation. Not applied yet because it is the first test.

Thanks for the tip.

mORMot Open Source

#1 2013-01-11 17:50:13

I need help on a simple example using ODBC.

#2 2013-01-12 09:38:12

Re: I need help on a simple example using ODBC.

#3 2013-01-14 10:12:29

Re: I need help on a simple example using ODBC.

#4 2013-01-14 16:40:57

Re: I need help on a simple example using ODBC.

#5 2013-01-14 17:52:28

Re: I need help on a simple example using ODBC.

#6 2013-01-17 19:22:41

Re: I need help on a simple example using ODBC.

#7 2013-01-18 10:30:18

Re: I need help on a simple example using ODBC.

#8 2013-01-18 12:52:12

Re: I need help on a simple example using ODBC.

#9 2013-01-18 13:23:14

Re: I need help on a simple example using ODBC.

#10 2013-01-18 16:19:24

Re: I need help on a simple example using ODBC.

#11 2013-01-18 17:02:39

Re: I need help on a simple example using ODBC.

#12 2013-01-18 18:08:59

Re: I need help on a simple example using ODBC.

#13 2013-01-18 19:05:39

Re: I need help on a simple example using ODBC.

#14 2013-01-18 19:08:49

Re: I need help on a simple example using ODBC.

Board footer