mORMot Open Source

SHEePYTaGGeRNeP

Member

Registered: 2014-02-10

Posts: 17

Array params to server

Hello,

I am getting the hang of mORMot now I think, but the problem I now have is whenever I send an Array from the client to the server as a parameter for a function, the array becomes huge.
I have a very simple call function from the client:

 TMormotConnection.Connection.OpenDataset(lvDataset,
      'BEGIN SETHOUSEHOLDHELPSTATUS(:P1); END;', [63101, 1300135]);

and the OpenDataset function looks like this:

function TMormotConnection.OpenDataset(pvDataset: TClientDataset;
  pvSQL: RawUTF8; const pvParams: array of const): Boolean;
var
  fJSON: RawUTF8;
  // stmt : TSQLDBStatement;
begin
  if fService <> nil then
  begin
    fJSON := fService.ExecuteWithParams(pvSQL, pvParams, true, true);

When I look at the pvParams I can clearly see 2 values being correct.
However the function on the server which looks like this:

function TServiceRemoteSQL.ExecuteWithParams(const aSQL: RawUTF8;
  const pvParams: array of const; aExpectResults, aExpanded: Boolean): RawJSON;
var
  I: Integer;
  lvStatement: ISQLDBStatement;
begin
  if fProps = nil then
    raise Exception.Create('Connect call required before Execute')
  else
    lvStatement := Self.fProps.NewThreadSafeStatementPrepared
      ('BEGIN tempprocedure(?); END;', false);
  for I := 0 to Length(pvParams) do
    lvStatement.Bind(I + 1, pvParams[i].VInteger, paramIn);
  lvStatement.ExecutePrepared;

Has about 30.000 values in the array.
How does this happen, is this intentional and how do I fix this?
This is used for Stored Procedures and making a new function with the correct parameters for every Stored Procedure is not optimal.

Please help.

Last edited by SHEePYTaGGeRNeP (2014-03-12 15:45:15)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,237

Website

Re: Array params to server

I suppose you are using Oracle, right?
SynDBOracle is the only native SynDB class handling array binding by now (in addition to SynDBFireDAC).

But your code won't work as such.

If you write

BEGIN tempprocedure(?); END;

You need to bing ONE parameter, since there is ONE ? in the statement.

What you need is to bind an array of parameters, i.e. BindArray*() methods.

SHEePYTaGGeRNeP

Member

Registered: 2014-02-10

Posts: 17

Re: Array params to server

Hello,

I am using Oracle yes.
I also didn't check the code before posting here, because I was only looking at the parameters and forgot I left the test procedure in on the Server side.

The actual procedure requires 2 parameters ( ofc there are other procedures that should go through this function ).
I don't want to create a new function for every stored procedure with their respective parameters.

I also tried a pvParams as an array of Variants, but the same happend - ALOT of values while I only sent 2.

I apologize again for not checking my code before posting it here.

lvStatement := Self.fProps.NewThreadSafeStatementPrepared
      (aSQL, false);

which aSQL is 'BEGIN SETHOUSEHOLDHELPSTATUS(?,?); END;'

But the problem Ab wasn't with binding or anything, it is about the pvParams parameter having alot more data than I sent from the client.

What I want is ?an array? as parameter of the function where all parameters from the procedure are stored in, which could be string, dates etc.
Then Bind all those parameters to the statement one by one.

Is this the correct way, because I assume you should be able to send a variant or const array from the client to the server?

Last edited by SHEePYTaGGeRNeP (2014-03-13 08:01:22)

SHEePYTaGGeRNeP

Member

Registered: 2014-02-10

Posts: 17

Re: Array params to server

I also tried out a simple Integer Array, but that also gave me 150.000 values in the array.

I assume you should be able to send an Array to the server?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,237

Website

Re: Array params to server

At SynDB SQL library level, there is not such notion as "Array".
You can have an array of parameters, but not an array as parameter.

SHEePYTaGGeRNeP

Member

Registered: 2014-02-10

Posts: 17

Re: Array params to server

Thank you for your reply Arnaud.

However in the future we might need to send an array as a parameter for an procedure.
It was a pleasure working with mORMot, hopefully what ever my boss will find as a replacement can resolve this issue.

Best of luck in the future

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,237

Website

Re: Array params to server

AFAIK Delphi's TParam is not able to handle an array as parameter.
Even FireDAC's TADParam does not have this feature.

Are you sure you did not misunderstood the point?
Are you sure you are not confusion between "an array as parameter" and "an array of parameters"?

AFAIK only mORMot's SynDB and FireDAC's TADParam handle "array of parameters" to run multiple times the same statement.
None is able to bind an array as one parameter value.

What you can do is bind a cursor.
And SynDB is able to do this (with Oracle only by now).