TUniStoredProc with MySQL

[johnbitner]

From Delphi Code:

Code: Select all

sp:= TUniStoredProc.Create(nil);
sp.Connection:= dbconn; //TUniConnection
sp.StoredProcName:= 'spDELETE_Attendance';
sp.Prepare;
sp.ParamByName('pRECID').AsInteger:= RecordID;
sp.Execute;
sp.Free;

Watching the TUniStoredProc "sp" in the watch list after sp.Prepare I do not see the the parameter pRECID.


When I try to set the param with sp.ParamByName('pRECID').AsInteger:= RecordID I get the error Parameter 'pRECID' not found EDatabaseError

Here is the MySQL stored procedure. I can execute this simple procedure from your great dbForge Studio for MySQL application.

Code: Select all

PROCEDURE spDELETE_Attendance(IN pRECID INT)
BEGIN
  DELETE FROM csattend WHERE REC_ID = pRECID;
END

This is all with Delphi XE7 and Unidac 6.0.2 Professional.I know I must be missing something simple. Any suggestions?
Thanks for your help.

[johnbitner]

More information.
Test 1:
1. Add a TUniConnection to a form. Manually set the server, user, pass etc and connect.
2. On the form add TUniStoredProc. Attach the stored procedure connection to the TUniConnection.
3. Add a button and in the OnClick add code to setup the TUniStoredProc and execute it.
3. Run the application and click the button.
4. The parameter "pRECID" shows in the watch list, can be set and the proc executes successfully.

Test 2:
1. Add a button to a form. On the button click create an instance of TUniConnection in code UniConnection:= TUniConnection .Create(nil); and set the server, user, pass etc in code and connect. The connect is established as I've tested and can query other data while in the application.
2. From another button click add more code to create an instance of TUniStoreProc and in code set the TUniStoreProc properties and execute.

Code: Select all

UniStoreProc:= TUniStoreProc.Create(nil)
UniStoreProc.Connection:= UniConnection;
UniStoreProc.StoredProcName:= 'spDELETE_Attendance';
UniStoreProc.Prepare;
UniStoreProc.ParamByName('pRECID').AsInteger:= 638;
UniStoreProc.Execute;
UniStoreProc.Free;

I get the same errors as shown in my previous post. This really doesn't make any sense as why this would act differently. I have always created non visual comps like this in code as my application business logic code and db engine code is total divorced from the UI.

Thanks again for any help.

[FredS]

If you aren't returning anything from the SP then you may want to try this:

Code: Select all

UniConnection.ExecProc('spDELETE_Attendance', [638]);

[johnbitner]

Hello, thanks for the suggestion.

If I do this on the form design time created TUniConnection comp it executes correctly. If I do this on the run time created TUniConnection comp I get this error:

raised exception class EMySqlException with message '
#42000Incorrect number of arguments for PROCEDURE spdeleteAttendance; expected 1, got 0'.

This seems very odd to me that they 2 instances of the same TUniConnection class would act differently. I keep thinking I must be missing something but for now the problem eludes me.

[FredS]

Seems like you aren't setting something during connection creation..

[ViktorV]

Please run the following code after setting the correct values (Server, Port, Username, Password, Database), and check whether the problem is reproduced on it.

Code: Select all

program TestSP;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, Uni, MySQLUniProvider;

var
  dbConn: TUniConnection;
  sp : TUniStoredProc;

begin
  dbConn := TUniConnection.Create(nil);
  try
    dbConn.Server := Server;
    dbConn.Port := Port;
    dbConn.Username := Username;
    dbConn.Password := Password;
    dbConn.ProviderName := 'MySQL';
    dbConn.Database := Database;
    dbConn.Connect;
    sp:= TUniStoredProc.Create(nil);
    try
      sp.Connection:= dbConn;
      sp.StoredProcName:= 'spDELETE_Attendance';
      sp.Prepare;
      sp.ParamByName('pRECID').AsInteger:= 638;
      sp.Execute;
      Writeln('Done.');
    finally
      sp.Free;
    end;
    ReadLn;
  finally
    dbConn.Free;
  end;
end.

[johnbitner]

This was a rights issue from the start. I do appreciate everyone's help and I'm sorry to have waster your time.

Here is what happen and hopefully this could save someone else some time.
I created the stored procedures on the database with an "admin" level server account that can manage all the databases on that server. The problem was when I would connect to the database with a lower level user account that has more limited rights and can only see their particular database. This user does have rights to the stored procedure, execute, insert to the tables needed, etc but the account was not able to see the parameter metadata from the procedure. Once I deleted the stored procedures and created them as the user account, all was fine.

I guess we learn something everyday.

Thanks again.

[ViktorV]

Glad to see that the issue was resolved. Feel free to contact us if you have any further questions about UniDAC.