[solved] getting stored procedures to work?

[sean]

I have a procedure that working fine on the mysql command line, but cannot find a way to see it in Delphi 7/mydac 5.00.1.6.

The prodecure is defined as:
j_extot(IN JOBNR INT, OUT extime TIME, OUT exkilos INT, OUT exmachine INT, OUT extotal DOUBLE)
It also selects some information to stdout (not just the params above)

1) I tried to create a MyStoredProc
----------------------------------------

with MyStoredProc1 do begin
try
Close;
ParamByName('JOBNR').AsInteger:=31491;
Execute;
// Now print return param: but always zero
ShowMessage('Time=' +ParamByName('extime').AsString);
finally
//MyStoredProc1.Close;
end;

The OUT parameters are not filled. In DB mon I can see the proc being called, but no out values. And the next query my app does causes a "Commands out of sync"


2) Using a TQuery
-------------------
with MyQuery1 do begin
Close;
SQL.Clear;
SQL.Add(
'call j_extot(31491, @extime, @exkilos, @exmachine, @extotal); select @extime;'
);
Open;
ShowMessage('Time=' +ParamByName('@extime').AsString);
Close;
end;

In this case I get a "parameter extime not found".


I had a look at the Demo for SP, but its didn't help me much...
- I'd like to call an SP, return values,
- and if possible also capture any selects made by the SP

Thanks in advance,

Sean

[eduardosic]

I have a procedure that qorking fine on the mysql command line, but cannot find a way to see it in Delphi 7/mydac 5.00.1.6.

The prodecure is defined as:
j_extot(IN JOBNR INT, OUT extime TIME, OUT exkilos INT, OUT exmachine INT, OUT extotal DOUBLE)
It also selects some information to stdout (not just the params above)

1) I tried to create a MyStoredProc
----------------------------------------

with MyStoredProc1 do begin
try
Close;
ParamByName('JOBNR').AsInteger:=31491;
Execute;
// Now print return param: but always zero
ShowMessage('Time=' +ParamByName('extime').AsString);
finally
//MyStoredProc1.Close;
end;

The OUT parameters are not filled. In DB mon I can see the proc being called, but no out values. And the next query my app does causes a "Commands out of sync"


2) Using a TQuery
-------------------
with MyQuery1 do begin
Close;
SQL.Clear;
SQL.Add(
'call j_extot(31491, @extime, @exkilos, @exmachine, @extotal); select @extime;'
);
Open;
ShowMessage('Time=' +ParamByName('@extime').AsString);
Close;
end;

In this case I get a "parameter extime not found".


I had a look at the Demo for SP, but its didn't help me much...
- I'd like to call an SP, return values,
- and if possible also capture any selects made by the SP

Thanks in advance,

Sean

Try to use FieldByName...

Code: Select all

  with MyQuery1 do begin
    Close;
    SQL.Clear;
    SQL.Add(
       'call j_extot(31491, @extime, @exkilos, @exmachine, @extotal); select @extime;'
    );
    Open;
    ShowMessage('Time=' +FieldByName('@extime').AsString);
    Close;
  end;

and Update you mydac version.. the version 5.0.1.6 is very out-of-date..

[sean]

Try to use FieldByName...

Yes, it should be FieldByName and not ParamByName. But it still says "Field extime not found'.
I tried with @extime as you suggested, and "extime" using the "as extime", both cause the same error message.

and Update you mydac version.. the version 5.0.1.6 is very out-of-date..

The Mydac version is about 2 years old, I no longer have a support contract, so updates are not available....

[eduardosic]

Try to use FieldByName...

Yes, it should be FieldByName and not ParamByName. But it still says "Field extime not found'.
I tried with @extime as you suggested, and "extime" using the "as extime", both cause the same error message.

and Update you mydac version.. the version 5.0.1.6 is very out-of-date..

The Mydac version is about 2 years old, I no longer have a support contract, so updates are not available....

You can post the script of procedure and table?

about mydac update's, see the history...

http://www.devart.com/mydac/revision_history.html

the Devart Team send a e-mail to the custumer when a New version of mydac is avaliable..

[sean]

Ok, Looking at the revision log, I should upgrade first. I'll then post back again.

[eduardosic]

Ok, Looking at the revision log, I should upgrade first. I'll then post back again.

ok, you can get updates in http://secure.devart.com/
you need of the your custumer information..

[sean]

Ok, I'm now upgraded to the latest mydac/sdac (which was painful, see http://www.devart.com/forums/viewtopic.php?p=49303)

So, lets start with a simple SP:

Code: Select all

delimiter  //
DROP PROCEDURE IF EXISTS test1//
CREATE PROCEDURE test1(IN var1 INT, OUT var2 DOUBLE)
  READS SQL DATA
BEGIN
  SET var2=2.02;
  select var1, var2;
END;
//
delimiter  ;

On the sql command line client, I can seel both selects with the SP, and the returned parameter 'var2':

mysql> call test1(27901, @var3);
+-------+------+
| var1 | var2 |
+-------+------+
| 27901 | 2.02 |
+-------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @var3;
+-------+
| @var3 |
+-------+
| 2.02 |
+-------+
1 row in set (0.00 sec)

In delphi:

with MyStoredProc1 do begin
try
Close;
StoredProcName:='test1';
ParamByName('var1').AsInteger:=27901;
Execute;
ShowMessage('var2=' +ParamByName('var2').AsString);
finally
MyStoredProc1.Close;
end;

This results in showmessage displaying nothing (i.e. 'var2=').
In the dbmonitor I see that the IN/OUT value for var1 is 2709, but in/out for var2 is null.

Ideas?

[eduardosic]

Try this...

Var2 in a resultset... use FieldByName to show the value..

Code: Select all

   MyConnection1.Connect;
   with MyStoredProc1 do begin
    try
     Close;
     StoredProcName:='test1';
     ParamByName('var1').AsInteger:=27901;
     Execute;
     ShowMessage( 'var2=' + FieldByName('var2').AsString );

    finally
     MyStoredProc1.Close;
    end;
   end;

[sean]

Oops, you already highlighted that further up.
Yes, that works, I see the return value! (although dbmon does not).

Could we complete the example by now calling the SP from a query?
I tried the following:

Code: Select all

  with dmJOB.MyQuery1 do begin
    Close;
    SQL.Clear;
    SQL.Add('call test1(27901, @var3); select @var3;');
    Open;
    ShowMessage('var3=' +FieldByName('@var3').AsString);
    Close;
  end;

But the error is "@var3 not found".

[eduardosic]

change you procedure..

Code: Select all

CREATE DEFINER = 'root'@'localhost' PROCEDURE `test1`(IN var1 INT, OUT var2 DOUBLE)
    NOT DETERMINISTIC
    READS SQL DATA
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  SET var2=2.02;
  -- COMMENT the Select. select var1, var2;
END;

and try this..

Code: Select all

   with MyQuery1 do begin
    try
     Close;
     SQL.Text := 'CALL test1(:var1, @var3);  Select @Var3 as New;';
     ParamByName('var1').AsInteger:=27901;
     Execute;
     ShowMessage( 'var3=' + FieldByName('New').AsString );
    finally
     Close;
    end;
   end;

[sean]

Hi,

I get "field 'New' not found", and use the code you suggest e.g.

Code: Select all

  with dmJOB.MyQuery1 do begin
   try
    Close;
    SQL.Text:= 'CALL test1(:var1, @var3);  Select @Var3 as New;';
    ParamByName('var1').AsInteger:=27901;
    Execute;
    ShowMessage( 'var3=' + FieldByName('New').AsString );
   finally
    Close;
   end;
  end;

[eduardosic]

Hi,

I get "field 'New' not found", and use the code you suggest e.g.

Code: Select all

  with dmJOB.MyQuery1 do begin
   try
    Close;
    SQL.Text:= 'CALL test1(:var1, @var3);  Select @Var3 as New;';
    ParamByName('var1').AsInteger:=27901;
    Execute;
    ShowMessage( 'var3=' + FieldByName('New').AsString );
   finally
    Close;
   end;
  end;

You has commented the select in the procedure?

[sean]

Yes, the procedure could not be simpler:
mysql> select body from mysql.proc where name='test1';

+-------------------------------+
| body |
+-------------------------------+
| BEGIN
SET var2=2.02;

END |
+-------------------------------+
1 row in set (0.00 sec)

Of course now, coming back a week later it works just fine. i.e.
The ShowMessage just display 2.02. Typical.

Anyway the key thing is that a Query that callls a prodedure will work, if the procedure has no Selects that would print out rows.

[Dimon]

Was the problem solved? If any questions come up, please contact us.

[sean]

Yes, problem solved.