TSQLModel and multiple external tables

negativethirteen · 2012-07-30 22:21:51

I have a TSQLModel with multiple TSQLRecord and TSQLRecordExternal.
The idea is to have the option to switch between a local SQLite3 DB and an external DB on the fly.

I get an access violation when I try to create the client (and it's stand-alone server) when there are multiple TSQLRecordExternal in the TSQLModel.

MySQL := TOLEDBODBCSQLConnectionProperties.Create('', 'Local MySQL', '', 'user', 'password');
...
VirtualTableExternalRegister(Model, TSQLsn_log_ext, MySQL, 'sn_log');
VirtualTableExternalRegister(Model, TSQLfile_ext, MySQL, 'file');
Client := TSQLRestClientDB.Create(Model, Model, 'test.db3', TSQLRestServerDB, false);
Client.Server.CreateMissingTables(0);
...

Everything works if I use only 1 TSQLRecordExternal per TSQLModel.
Is this intended? I dont remember reading anything in the SAD explicitly saying that TSQLModel can only handle 1 TSQLRecordExternal and I assumed the TSQLRecordExternal are handled the same way as TSQLRecord, that is, one can have multiple TSQLRecordExternal same as having multiple TSQLRecord.
Am I using VirtualTableExternalRegister wrong?
Should I move my individual tables to separate TSQLModels or is there another way to do this?

Thanks in advance.

ab · 2012-07-31 05:09:23

You are right: there is no such limitation.

1. Ensure you are using the latest version from http://synopse.info/fossil
There is no TSQLRecordExternal kind of class any more: you create plain TSQLRecord types, then call VirtualTableExternalRegister() to make those external. It will do your dual-mode even easier to work with.

2. Each class shall be declared with its own sub-type, e.g. TSQLfile = class(TSQLparent) and not TSQLFile=TSQLParent, even if you do not modify properties.

I suspect there is something wrong elsewhere.
The exact line where the access violation occurs is needed to investigate.

negativethirteen · 2012-08-01 16:30:47

Thanks for the quick reply.

So I updated to the latest source (my source was from 7-20, I didn't think it was too out of date...how wrong I was ).

I am able to register the 6 tables as external tables.
No more access violation, however, I'm not completely error free just yet.

First issue is that CreateMissingTables() is not creating tables in the external MySQL database. (connected with admin privileges)

So as a quick workaround in order to test the rest of my code, I created the tables manually.

Anyway, I now have the following code:

...
    ODBC := TOLEDBODBCSQLConnectionProperties.Create('', 'Local MySQL', '', 'root', 'sqlroot');
    Model := CreateCableTestDataModel;
    VirtualTableExternalRegister(Model, TSQLcable_test, ODBC, 'cable.cable_test');
    VirtualTableExternalRegister(Model, TSQLref_data, ODBC, 'cable.ref_data');
    VirtualTableExternalRegister(Model, TSQLpass_criteria, ODBC, 'cable.pass_criteria');
    VirtualTableExternalRegister(Model, TSQLaux_data, ODBC, 'cable.aux_data');
    VirtualTableExternalRegister(Model, TSQLretest_count, ODBC, 'cable.retest_count');
    VirtualTableExternalRegister(Model, TSQLcable_desc, ODBC, 'cable.cable_desc');
    Client := TSQLRestClientDB.Create(Model, Model, ':memory:', TSQLRestServerDB, false);
    Client.Server.CreateMissingTables();
...

I then proceed to use the usual ORM stuff to create, read, update, and delete entries.
Since regular and external records have been unified into TSQLRecord, all the regular and external DB access code are now the same.
All of that code has been tested and works with a SQLite3 DB file.

Second issue is that only 1 or 2 of the tables are being written to when it should be writing to all 6 tables.
It also looks like data is not being read from the tables when querying.

I use CData := TSQLcable_test.Create()
then modify the data as necessary and Client.Update() or Client.Add().
After all of that is done, I free the CData.

ab · 2012-08-01 16:38:25

I can't understand exactly the context of the issue here.

Does it work as expected with SQLite3 external tables?
(i.e. TSQLDBSQLite3ConnectionProperties kind of external connection)

I did not test the MySQL database process at all: only with Oracle, MS SQL and Jet engines.
Some features are expected not to work, e.g. UNIQUE syntax which need to be tuned.

negativethirteen · 2012-08-01 18:49:41

Using TSQLDBSQLite3ConnectionProperties external connection, I can mostly read/write as expected.

However, the following does not return a table:

Table := Client.ExecuteList([TSQLresult, TSQLref_data, TSQLcable_desc, TSQLretest_count, TSQLpass_criteria], 'SELECT * FROM result, ref_data, cable_desc, retest_count, pass_criteria');

This is just a quick table dump that I use for testing. It works with non-external tables.
I can only get a table back if I'm querying just 1 table.
Should I not be using ExecuteList with external tables this way?

Any ideas on the CreateMissingTables() not working with external MySQL? You said you didn't test with MySQL, so I'm not expecting much on that end, but it doesn't hurt to ask.
Regardless, you have been very helpful so far so thank you .

I'm going to set up an MS SQL server and try it with that. I just need to support at least 1 external database that is not SQLite (MySQL just happened to already by set up on my machine so I started with that), so if that works, that might be good enough until I resolve the MySQL issue.

negativethirteen · 2012-08-01 23:46:17

An update on the MS SQL.
I've set up SQL Server Express 2012.

CreateMissingTables() work. (Yaay!)
Read/Write is almost 100%. For some reason it's still not updating 1 table.

As I mentioned before, everything works with a TSQLDBSQLite3ConnectionProperties external connection except for the ExecuteList thing (which I can work around, so not too big of an issue).
Here's that table's class:

  TSQLcable_desc = class(TSQLRecord)
  private
    fpart_number : RawUTF8;     // cable part number
    fdescription : RawUTF8;     // cable description
    fpicture : TSQLRawBLOB;     // cable picture
  published
    property part_number : RawUTF8 read fpart_number write fpart_number;
    property description : RawUTF8 read fdescription write fdescription;
    property picture : TSQLRawBLOB read fpicture write fpicture;
  end;

And I use it like so:

 // create cable_desc entry
    CDesc := TSQLcable_desc.Create(Client, 'part_number = "%"', [StringToUTF8(part_num)]);

    try
      if CDesc.ID > 0 then  // don't duplicate if entry for this part number already exists
      begin
        CData.cable_desc_id := CDesc.ID;
      end else
      begin
        CDesc.part_number := StringToUTF8(part_num);
        Client.Add(CDesc, true);
        CData.cable_desc_id := CDesc.ID;
      end;
    finally
      CDesc.Free;
    end;

It could be some data type mismatch that I'm missing.

ab · 2012-08-02 07:17:24

The ExecuteList() is not to be used on the Client side, in real applications.
For security reasons, as soon as you will use Client-server communication, remote execution of such SQL requests should be disabled.

IMHO, it does not make much sense either to create such a "square product" SQL statement.
The virtual table mechanism of SQLite3 seems not to like it. And I understand it.
For a "classic" SELECT with a LEFT or RIGHT JOIN, it should work as expected.

But for such SQL queries, you should better by-pass the ORM:
- Define it on the Server side only, and call it remotely as a service (via an interface);
- Since it is a SQL query, call directly the external engine (i.e. calling ODBC in your case), since ORM is not needed here: direct JOIN execution will be much faster then passing through the virtual table mechanism of the ORM.

As stated by the documentation, the best practice in this case is the following:
- Do not mix ORM and SQL queries, unless you really need a specific query;
- Embed SQL queries on the server side as services.

negativethirteen · 2012-08-02 20:59:26

You're right, the test SQL statement doesn't make much sense.
The actual query JOINs 4 tables, but it still does not work with external tables (it does work with non-external tables).
It's mostly used for report generation and creation of a TSQLTableToGrid. The TSQLTableToGrid is not really necessary (it's mostly an easily accessed sanity check of the data) and the report generation is done with just the ORM and built-in report generation, so I can just remove the SQL query part.

I am still having an issue with 1 of the tables not being written to (read seems fine), here's the relevant entry in the error log corresponding to the code snippet from my previous post:

TOleDBStatement(00DDF1A0) insert into cable_desc (ID,part_number,description) VALUES (1,'OC006','')
"EOleDBException(0267FDC0)":"The parameter is incorrect - The parameter is incorrect." stack trace 00622249 0062169D 006B7A13 006B7B3D 006B637F 006B4D64 006B4B19 006B4B2E 006B2FDA 00404712 00407570 00407B2C 00407B40 006B25F5 0063A4BF 006AB711 006366AA 00636B92 0063DB31 00782903 
EOleDBException ("The parameter is incorrect - The parameter is incorrect.") at 006B7A1A  stack trace 006B4D64 006B4B19 006B4B2E 006B2FDA 00404712 00407570 00407B2C 00407B40 006B25F5 0063A4BF 006AB711 006366AA 00636B92 0063DB31 00782903 0078261F 00782527 0078610D 0078A87C 007868A8 
"EOleDBException(0267FDC0)":"The parameter is incorrect - The parameter is incorrect." stack trace 00622249 0062169D 006B63A6 006B7A1A 773CB459 773CB42B 773CB3CE 77380133 77380133 75E9B9BC 006B7A1A 006B7A1A 006B7A1A 75E9B9BC 75E9B9BC 006B7A1A 006B7A1A 006B7A1A 006B7A1A 006B7A1A 
EOleDBException ("The parameter is incorrect - The parameter is incorrect.") at 006B7A1A  stack trace 00404712 00407570 00407B2C 00407B40 006B25F5 0063A4BF 006AB711 006366AA 00636B92 0063DB31 00782903 0078261F 00782527 0078610D 0078A87C 007868A8 004CB56E 004CB605 004C9AA0 004CB654

All tables are created via CreateMissingTables(). Read/Write to the other 4 tables are fine and use the similar code (create object, modify values, add/update database).
The record class (TSQLcable_desc) is on my previous post.
The BLOB field (picture) is left blank and no BLOB-specific operations are done.
The only error in the program is what is shown above.

ab · 2012-08-03 05:50:18

BLOB issue should be fixed by http://synopse.info/fossil/info/0dfada3aa2

About the SQL query, I did not say you had to remove the SQL query part, just that if you want to use such a SQL query, you shall not do it directly on the Client side, but create a dedicated Service which will explicitly call the external DB.
Such a query is indeed out of scope for a basic ORM call.

Thanks for the feedback.

negativethirteen · 2012-08-03 21:38:51

Updated my source and that gave me an idea - maybe something (mORMot or MS SQL) is not liking the empty or null values.

My code was leaving the description blank (i.e. CDesc.description := '').
So tried filling it in with some dummy value. Now it works!

I'm not sure if this is a bug in the Framework or something with MS SQL or OLE DB.
Regardless, I should probably go back and put some reasonable default values instead of leaving them null.

Thanks again. I should remember to check for updates every time I sit down and continue working on my project, I can't keep up with how quickly you guys update mORMot (which is a good thing ).

ab · 2012-08-04 08:56:04

There is no need to have something set to a blob field to have the DB layer work.

I still can't understand why your code is executing this statement, with the default StaticVirtualTableDirect := true setting:

insert into cable_desc (ID,part_number,description) VALUES (1,'OC006','');

If you use Client.Add(), it won't by default add blobs, which must be define with explicit calls to Client.UpdateBlob() methods.
Even if you set Client.ForceBlobTransfert := true if will call explicitely Client.UpdateBlob() for each blob.

So you would have, from the SQL point of view:

insert into cable_desc (ID,part_number,description) VALUES (?,?)      with ?=1 and ?='OC006' bound parameters
update cable_desc set description=? where ID=?  with ?=1 and the 2nd ? an explicit bound parameter in TSQLRestServerStaticExternal.EngineUpdateBlob()

In BATCH mode, Client.BatchAdd() will never transmit the BLOBs.

So what you are doing is quite impossible to reproduce.
Or you did set explicitly StaticVirtualTableDirect := false which will force to call the virtual engine?

In all cases, I've just made some other code changes which may help fixing the problem.
See http://synopse.info/fossil/info/be2c287f3f

negativethirteen · 2012-08-07 16:16:51

I'm not quite sure what you mean by

I still can't understand why your code is executing this statement, with the default StaticVirtualTableDirect := true setting:
insert into cable_desc (ID,part_number,description) VALUES (1,'OC006','');

Note that there are 4 fields: ID, part_number, description, and picture (the BLOB)
As you say, Client.Add() should not be updating the BLOB field, which it doesn't. It's only inserting ID, part_number (varchar), and description (varchar)

Everything else should have their default values (e.g. StaticVirtualTableDirect, etc.)

The problem seems to be that something does not like the empty string value for description.
I filled in a dummy value for description and it seems to work now

insert into cable_desc (ID,part_number,description) VALUES (1,'OC006','test');

ab · 2012-08-07 17:01:09

It should work without any dummy value.

Please try http://synopse.info/fossil/info/967322735e

I suspect it could override the error at parameter binding.
OleDB is definitively strange when it comes to binding...

negativethirteen · 2012-08-08 00:16:42

That works now.
Thanks a lot, you have been extremely helpful.

mORMot Open Source

#1 2012-07-30 22:21:51

TSQLModel and multiple external tables

#2 2012-07-31 05:09:23

Re: TSQLModel and multiple external tables

#3 2012-08-01 16:30:47

Re: TSQLModel and multiple external tables

#4 2012-08-01 16:38:25

Re: TSQLModel and multiple external tables

#5 2012-08-01 18:49:41

Re: TSQLModel and multiple external tables

#6 2012-08-01 23:46:17

Re: TSQLModel and multiple external tables

#7 2012-08-02 07:17:24

Re: TSQLModel and multiple external tables

#8 2012-08-02 20:59:26

Re: TSQLModel and multiple external tables

#9 2012-08-03 05:50:18

Re: TSQLModel and multiple external tables

#10 2012-08-03 21:38:51

Re: TSQLModel and multiple external tables

#11 2012-08-04 08:56:04

Re: TSQLModel and multiple external tables

#12 2012-08-07 16:16:51

Re: TSQLModel and multiple external tables

#13 2012-08-07 17:01:09

Re: TSQLModel and multiple external tables

#14 2012-08-08 00:16:42

Re: TSQLModel and multiple external tables

Board footer