TSQLDBConnectionProperties.Execute on client side?

Roberto Schneiders · 2013-02-11 16:35:20

It is possible to use this method (TSQLDBConnectionProperties.Execute) on client side?

Basically i need a TSQLTableJson from a external complex table. This table not have a TSQLRecord mapping.

ab · 2013-02-11 17:17:28

No, it is not possible, by design.

What you have to do is to create a dedicated service on server side, which may return directly the JSON content as object.
Performance will be very good, and you won't need the DB client to be defined on server side.

Roberto Schneiders · 2013-02-11 17:50:19

OK. I don't know how work with Service-oriented architecture of mORMot yet, i have to study this.

I can't do this?

  IServiceTest = Interface(IInvokable)
  ['{638398D0-C0B5-4E1B-A46F-D30D26185477}']
    function GetSQLTableJSON(ASQL:RawUTF8):TSQLTableJSON;
  end;

  TServiceTest = class (TInterfacedObject, IServiceTest)
    function GetSQLTableJSON(ASQL:RawUTF8):TSQLTableJSON;
  end;

or this?

  IServiceTest = Interface(IInvokable)
  ['{638398D0-C0B5-4E1B-A46F-D30D26185477}']
    procedure GetSQLTableJSON(ASQL:RawUTF8; out Result:TSQLTableJSON);
  end;

  TServiceTest = class (TInterfacedObject, IServiceTest)
    procedure GetSQLTableJSON(ASQL:RawUTF8; out Result:TSQLTableJSON);
  end;

Last edited by Roberto Schneiders (2013-02-11 17:51:45)

ab · 2013-02-12 08:41:02

You can not directly access the SynDB classes on the client side.

BUT you can execute a SQL statement, and return a TSQLTableJSON, without the need of deploying a service.

See this method:

    /// retrieve a list of members as a TSQLTable (implements REST GET Collection)
    // - URI is 'ModelRoot/TableName' with GET method
    // - SQLSelect and SQLWhere are encoded as 'select=' and 'where=' URL parameters
    // (using inlined parameters via :(...): in SQLWhere is always a good idea)
    // - server must return Status 200/HTML_SUCCESS OK on success
function TSQLRestClientURI.List(const Tables: array of TSQLRecordClass;
  const SQLSelect, SQLWhere: RawUTF8): TSQLTableJSON;

I suppose this is what you need.

Roberto Schneiders · 2013-02-13 10:05:09

I tried this method, but not work for me.

My code is like this:

Table := Client.List([], 'Select * from SGRUSR01');

The result is nil.

I dont have de TSQLRecord classes for this tables.

You have another idea?

... and about those codes i posted (Services), its possible do that?

esmondb · 2013-02-13 20:21:29

You could try something like:

var
  JSONBuffer: RawUTF8;

procedure example;
var
  Table: TSQLTable;
  Rows: ISQLDBRows;
begin
  Rows := Client.Execute('select * from SGRUSR01',[]);
  fJSONBuffer := Rows.FetchAllAsJSON(false);
  Table := TSQLTableJSON.Create([],'',pointer(fJSONBuffer),length(fJSONBuffer));
...

Roberto Schneiders · 2013-02-14 10:06:47

I don't found the "execute" method in my client class (TSQLHttpClient).

ab · 2013-02-14 10:25:32

@esmondb You will need the SynDB* units on client side, also with the corresponding drivers... so perhaps not exactly what Robert expected!

Roberto Schneiders wrote:

I don't found the "execute" method in my client class (TSQLHttpClient).

Yes, it does not exist, for the reason it is not a TSQLRestClient, but a TSQLDBProperties.

I'm working on a new sample, showing how to implement this feature without the ORM.
Basicaly, a "SynDBExplorer" in remote mode.
And perhaps in the future adding this remote mode to SynDBExplorer.

ab · 2013-02-14 16:57:06

I've just added a new "16 - Execute SQL via services" sample.
See http://synopse.info/fossil/dir?name=SQL … a+services

This is a remote connection to any DB server.
The latest connection settings are persisted on the local folder, within a JSON .settings file.
The list of tables is retrieved from the database, for direct "SELECT * FROM" statements.
Returned data is displayed within a mORMot "grid".

It uses a sicClientDriven service, which is pretty common for handling a network connection property per client.
Some optimization is still needed - I'll for instance add a RawJSON new string type, which won't be escaped when transmitted (as done for a classic RawUTF8 function result).
A TSQLDBProperties cache on server side should better be implemented, since one instance is created per client access thread.

But I suppose it is a good example of Client-Server interfaces with mORMot.
Also showing how to use a TSQLTableJSON.

I also included some new wrappers in the framework (both RESTful core and UI layer).

See http://blog.synopse.info/post/2013/02/1 … SQL-access

Roberto Schneiders · 2013-02-14 19:17:47

Thank you very much. I will check this out.

mORMot Open Source

#1 2013-02-11 16:35:20

TSQLDBConnectionProperties.Execute on client side?

#2 2013-02-11 17:17:28

Re: TSQLDBConnectionProperties.Execute on client side?

#3 2013-02-11 17:50:19

Re: TSQLDBConnectionProperties.Execute on client side?

#4 2013-02-12 08:41:02

Re: TSQLDBConnectionProperties.Execute on client side?

#5 2013-02-13 10:05:09

Re: TSQLDBConnectionProperties.Execute on client side?

#6 2013-02-13 20:21:29

Re: TSQLDBConnectionProperties.Execute on client side?

#7 2013-02-14 10:06:47

Re: TSQLDBConnectionProperties.Execute on client side?

#8 2013-02-14 10:25:32

Re: TSQLDBConnectionProperties.Execute on client side?

#9 2013-02-14 16:57:06

Re: TSQLDBConnectionProperties.Execute on client side?

#10 2013-02-14 19:17:47

Re: TSQLDBConnectionProperties.Execute on client side?

Board footer