MySQL und Zeos und Stored Proc Problem

[guidok]

Ich habe schon wieder ein Problem mit Stored Procedures.

Das hier hat funktioniert:

SQL-Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `bmm`.`sp_import_t_tepl`$$
CREATE DEFINER=`admin_bmm`@`%` PROCEDURE  `bmm`.`sp_import_t_tepl`(in_id varchar(45),
                                                 in_stext varchar(45),
                                                 in_kst varchar(7),
                                                 in_anl varchar(10),
                                                 in_anr varchar(10),
                                                 in_cuser varchar(20),
                                                 in_state varchar(45))
BEGIN
  DECLARE id_count int;

  SELECT COUNT(id) INTO id_count FROM t_tepl
    WHERE id=in_id;

  IF id_count>0 THEN
    UPDATE t_tepl SET stext=in_stext, kst=in_kst, anl=in_anl, anr=in_anr, clast=now(), cuser=in_cuser, state=in_state
      WHERE id=in_id;
  ELSE
    INSERT INTO t_tepl VALUES(in_id, in_stext, in_kst, in_anl, in_anr, now(), in_cuser, in_state);
  END IF;

END $$

DELIMITER ;

Jetzt habe ich ein wenig geändert.

SQL-Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `bmm`.`sp_handle_t_tepl`$$
CREATE DEFINER=`admin_bmm`@`%` PROCEDURE  `bmm`.`sp_handle_t_tepl`(in_action varchar(20),
                                                 in_id varchar(45),
                                                 in_stext varchar(45),
                                                 in_kst varchar(7),
                                                 in_anl varchar(10),
                                                 in_anr varchar(10),
                                                 in_cuser varchar(20),
                                                 in_state varchar(45))
BEGIN

DECLARE id_exists int;

SELECT COUNT(id) INTO id_exists FROM t_tepl WHERE id=in_id;

CASE

-- Alle Datensätze auswählen
  WHEN in_action='list_all' THEN
    SELECT * FROM t_tepl;

-- Technische Plätze importieren
  WHEN in_action='import' THEN
    IF id_exists>0 THEN
      UPDATE t_tepl SET stext=in_stext, kst=in_kst, anl=in_anl, anr=in_anr, clast=now(), cuser=in_cuser, state=in_state
        WHERE id=in_id;
    ELSE
      INSERT INTO t_tepl VALUES(in_id, in_stext, in_kst, in_anl, in_anr, now(), in_cuser, in_state);
    END IF;

END CASE;

END $$

DELIMITER ;

Ich führe die Procedure in einer Query aus.

Delphi-Quellcode:

begin
  with Query do
  begin
    Close;
    SQL.Clear;
    //Aufruf einer Stored Procedure zum Importieren eines Datensatzes
    //Hier wird auch geprüft, ob der DS bereits existiert und entsprechend behandelt
    SQL.Add('CALL sp_handle_t_tepl(:pACTION, :pID, :pSTEXT, :pKST, :pANL, :pANR, :pCUSER, :pSTATE);');
    Params.ParamByName('pACTION').Value := spImport;
    Params.ParamByName('pID').Value := ATP.TechnischerPlatz;
    Params.ParamByName('pSTEXT').Value := ATP.Bezeichnung;
    Params.ParamByName('pKST').Value := ATP.Kostenstelle;
    Params.ParamByName('pANL').Value := ATP.Anlage;
    Params.ParamByName('pANR').Value := ATP.ANummer;
    Params.ParamByName('pCUSER').Value := Global.EnviromentData.User;
    Params.ParamByName('pSTATE').Value := ATP.Status;
    try
      ExecSQL; //Hier habe ich auch schon Open versucht - das selbe Ergebnis
    except

    end;
  end;
end;

Leider bekomme ich anschließend die Meldung:

"procedure can't return a result set in the given context"

Bei der Ausführung der sp im MySQL Query Browser funktioniert es, anscheinend aber nicht in Verbindung mit der Zeos Query. Was läuft hier nicht richtig?

[Sharky]

Hai guidok,

hast Du mal versucht anstelle eines TZQuery ein TZStoredProc zu verwenden?

[guidok]

Äh ja, ich habs mal kurz versucht, aber das war mir irgendwie zu aufwändig die Parameter zu übergeben. Kann auch sein, dass ich da was verkehrt gemacht habe, aber bei der Query geht das so ganz elegant.

Ganz abgesehen davon: Wenn der Parameter in_action = "list_all" ist, dann habe ich natürlich auch eine Rückgabemenge und dafür brauch ich auf jeden Fall ne Query, also...

Sollte das jetzt überhaupt nicht funktionieren, dann muss ich mir (leider) was anderes überlegen, weil ich den Rest der DB-Zugriffe (auf weitere Tabellen) gerne auch so geregelt hätte.

[guidok]

Ich habe jetzt noch ein wenig probiert und vor allem die SP wieder in der alten Zustand versetzt. Die SP zum Importieren der Daten funktioniert jetzt wieder. Zur Anzeige habe ich jetzt eine neue SP erstellt.

SQL-Code:

ELIMITER $$

DROP PROCEDURE IF EXISTS `bmm`.`sp_t_tepl_list` $$
CREATE DEFINER=`admin_bmm`@`%` PROCEDURE `sp_t_tepl_list`(in_state varchar(45))
BEGIN
  IF in_state='' THEN
    SELECT * FROM t_tepl;

  ELSE

    SELECT * FROM t_tepl
      WHERE state like in_state;

  END IF;

END $$

DELIMITER ;

Der zugehörige Delphi-Code

Delphi-Quellcode:

begin
  with Query do
  begin
    Close;
    SQL.Clear;
    //Aufruf einer Stored Procedure zum Anzeigen aller Datensätze
    SQL.Add('CALL sp_t_tepl_list(:pSTATE);');
    Params.ParamByName('pSTATE').Value := '';
    try
      Open;
    except

    end;
  end;
end;

Wie ich fast erwartet habe erhalte ich genau dieselbe Fehlermeldung, deshalb jetzt meine Frage:

Geht das grundsätzlich nicht? Mache ich was falsch (ich kenn mich mit SP noch nicht sehr gut aus)?
Es muss ja wohl gehen, eine Datenmenge aus einer SP zurück zu erhalten!

[guidok]

Ich glaube ich habe die Antwort gefunden. Das Problem scheint also bei MySQL zu liegen.

Zitat:

Anweisungen, die eine Ergebnismenge zurückgeben, dürfen nicht innerhalb einer gespeicherten Funktion verwendet werden. Dazu
gehören auch SELECT-Anweisungen, soweit sie nicht mithilfe einer INTO-Klausel Spaltenwerte in Variablen laden, sowie SHOW
und andere Anweisungen wie beispielsweise EXPLAIN. Anweisungen, für die zur Definitionszeit festgelegt werden kann, dass sie
eine Ergebnismenge zurückliefern, lösen einen Not allowed to return a result set from a function-Fehler
aus (ER_SP_NO_RETSET_IN_FUNC). Anweisungen, für die nur zur Laufzeit festgelegt werden kann, dass sie eine
Ergebnismenge zurückliefern, lösen einen PROCEDURE %s can't return a result set in the given
context-Fehler aus (ER_SP_BADSELECT).

Es irritiert mich allerdings, dass hier von gespeicherten FUNKTIONEN die Rede ist und noch viel mehr irritiert mich, dass es mit dem MySQL-Query-Browser funktioniert, wenn ich die SP dort aufrufe dann wird mir die Ergebnismenge angezeigt.

[hoika]

Hallo,

Selectable SP's , also solche die mehr als einen Datensatz zurückgeben,
werden grundsätzlich über Queries ausgelesen.

Delphi-Quellcode:

SQL.Add('Select * From MySP(:Param);');

Für anderen SP's nimm das TStoredProc (hier also TZStoredProc).
Dein Problem mit den Parametern kann ich nicht nachvollziehen.

Delphi-Quellcode:

SP.Prepare;  <<-- weglassen, wenn es auch ohne geht.
SP.ParamByName('Id').AsInteger:= 5;
SP.ExecProc;
iRetValue:= SP.ParamByName('TetValue');

Was die mit gespeicherter Funktion meinen, weiss ich nicht (kenne MySQL nicht),
Unter FB gibt es nur Prozeduren mit Return-Parametern.
Vielleicht meinen Sie ja das gleiche.


Heiko