How to call stored procedures ?

[Nobody/Anonymous]

Initiallly I've missed the release 1.2.2, so now i was very happy to find it! The multi results feature seem to work just fine, and I tought this was really the only requirement to use stored procedures (on MySql 5.1.11).
My initialization looks like

function InitMy (db, svr, port: pChar): OleVariant; stdcall;
    var _db: string;
begin
   _db := db;
   my := TMySqlClient.create;
   with my do begin
      Compress := true;
      ClientFlag:= ClientFlag or CLIENT_MULTI_RESULTS;
      connect(svr,'uid','pwd','',port,'',true);
      result := Connected;
      if Connected then begin
         SelectDb(pChar(_db));
         MultiResults := true;
      end else
         result := false;
   end;
end;

so i think it's fine (and, to repeat, multi result sets are returned correctly).
However, when i try something like

  var res: TMySqlResult;
begin
  res := my.Query ('CALL Try("Brin",2006)', true, ExecutedOk);

the flag ExecutedOk returns false, and no result set available! What am i doing wrong here ?

I will appreciate any suggestion about this problem.

    Maurizio

[Nobody/Anonymous]

Set MultiResults := true; before you actually connect to the server...

Kind regards,
Cristian Nicola

[Nobody/Anonymous]

Thank you very much for your kind and fast reply.

In the meantime i've found an error in my initialization code: port was passed as a pChar and Connect was using allways the default 3306 (on which of course I've a MySql 4.18 running!).
But even with this fixed, DirectSql seems to work only with the server set on port 3306. Similar
problem with ClientFlag, i had to pass it to Connect explicitly as last parameter. Maybe a glitch of Delphi 5?
Anyway, in my initial tests in reality I was querying MySql 4.18: no doubt ExecutedOk was false for the stored procedure!

With MySql 5.1.11 set on port 3306 (and MySql 4 temporarily shut down) the following code

function InitMy (db, svr: pChar; port: Cardinal): OleVariant; stdcall;
    var _db: string;
begin
   _db := db;
   my := TMySqlClient.create;
   with my do begin
      Compress := true;
      MultiResults := true;
      Connect (svr,'root','','',port,'',true,ClientFlag);
      result := Connected;
      if Connected then begin
         SelectDb(pChar(_db));
      end else
         result := false;
   end;
end;

var
   i: integer;
begin
  InitMy('ripple','localhost',3306);
  // WORKS
  Query('select sin(10); select * from fheadlav where stab="BRIN" and Anno=2006; select 4*5;');
  writeLn (nStoreResults);
  for i := 1 to nStoreResults do
     PrintResult(i);
  readln;

  // DOES NOT WORK
  Query('call TryFull("brin",2006)');
  for i := 1 to nStoreResults do
     PrintResult(i);
  readln;

end.

where the stored procedure (that works without problems in my ide, SqlYog) gives an ExecutedOk=true, but i have some difficulty to retrieve the returned recordset (should'nt be the last one?). In case you mind, Query procedure is

procedure Query (qry: string; withHeader: shortint = K_HDR);
   var res: TMySqlResult;
   label storeit;
begin
   nStoreResults := 0;
   try
     with my do begin
       res := Query (qry, true, ExecutedOk);
  storeit:
       if ExecutedOk then begin
          Inc (nStoreResults);
          StoreResults[nStoreResults] := Translate(res, withHeader)
       end;
       if MoreResults then begin
          res :=  NextResult (true, ExecutedOk);
          goto storeit;
       end;
     end;
   finally end;
end;

whose purpose is just to convert the resultsets to variant arrays (for interfacing with Excel), which is done by the called routine Translate.

You were very kind to reply to my post, and i don't want to abuse of your time. If you're interested i'll post, when (and if) i find a solution to the problem i faced.
Best regards

    maurizio nardò

[Nobody/Anonymous]

I'm not sure i can understand what the problem is - as far as i'm aware ExecutedOk should be set if the stored procedure is executed ok..
Unfortunatelly i don't have a mysql running around but i will give it a try when i get a chance. If you find anything please do let me know..

Kind regards,
Cristian Nicola

[Nobody/Anonymous]

I think i've solved the problem adding one line:

procedure Query (qry: string; withHeader: shortint = K_HDR);
  var res: TMySqlResult;
  label storeit;
begin
  nStoreResults := 0;
  try
     with my do begin
       res := Query (qry, true, ExecutedOk);
storeit:
       if ExecutedOk then begin
       Inc (nStoreResults);
       StoreResults[nStoreResults] := Translate(res, withHeader)
    end;
    if MoreResults then begin
       res := NextResult (true, ExecutedOk);
--->       ExecutedOk := ExecutedOk and (res<>nil);  <---
       goto storeit;
    end;
  end;
  finally end;
end;

Apparently with a stored procedure MoreResults returns once more, but then of course NextResults gives a null resultset. So it seems necessary to check for this.
I hope this describes correctly the library's intended behaviour.

Best regards

    maurizio nardò