SProc misslungen: multiple rows in singleton select

[Jürgen Thomas]

Hallo,
nachdem ich mich in WinForms, C# und Interbase mit IBConsole eingearbeitet habe, möchte ich jetzt die Datenbank mit der Anwendung verbinden. Der Einfachheit halber verwende ich die BDP-Klassen, möchte aber die Eigenschaften überwiegend zur Laufzeit zuordnen.
Connection und Select klappen.
Probleme habe ich mit dem Aufruf einer StoredProcedure, die 2 Werte zurückgeben soll:

SQL-Code:

CREATE PROCEDURE Logbuch_Start
        ( Daten_Nr INTEGER, Selektion_Nr INTEGER, Formular_Nr SMALLINT )
RETURNS ( neu_ID INTEGER, Neuer_Monat BOOLEAN ) AS
BEGIN
  /*  alle Inhalte beschränkt auf das, was die Rückgabewerte betrifft  */
  neu_ID = GEN_ID( Logbuch_id, 1 );
  INSERT INTO Logbuch Values ( ... );
  /*  Einstellungen aktualisieren  */
  Neuer_Monat = False;
  IF ( Formular_Nr = 0 )
  THEN BEGIN
    /*  einige weitere Befehle  */
    Neuer_Monat = True;
  END
  /*  die folgende Zeile ist der einzige Suspend-Befehl in dieser Prozedur  */
  SUSPEND;
END

Im Programm benutze ich folgende Befehle:

Code:

cmdUpdate.CommandType = CommandType.StoredProcedure;
//  die eckigen Klammern oder @ werden entgegen Borland-Doku nicht benötigt,
//  sondern von Interbase als Fehler bemängelt
sCommand = "Logbuch_Start";
cmdUpdate.Parameters.Clear();
cmdUpdate.ParameterCount = 5;
param1 = new BdpParameter("Daten_Nr",      BdpType.Int32, 0);
param2 = new BdpParameter("Selektion_Nr", BdpType.Int32, 0);
param3 = new BdpParameter("Formular_Nr",  BdpType.Int16, 0);
param4 = new BdpParameter("Neu_ID", BdpType.Int32);
param4.Direction = ParameterDirection.Output;
param5 = new BdpParameter("Neuer_Monat",  BdpType.Boolean);
param5.Direction = ParameterDirection.Output;
cmdUpdate.Parameters.Add(param1);
cmdUpdate.Parameters.Add(param2);
cmdUpdate.Parameters.Add(param3);
cmdUpdate.Parameters.Add(param4);
cmdUpdate.Parameters.Add(param5);
try   {
  cmdUpdate.Connection  = Connect;
  cmdUpdate.CommandText = sCommand;
  Connect.Open();
  cmdUpdate.ExecuteNonQuery();
  //  Fehler tritt ebenso auf bei:
  //  BdpDataReader Reader = cmdUpdate.ExecuteReader();
  //  usw. }
catch { ... }
finally { Connect.Close(); } }

Dies führt zu folgender Exception.Message:

Zitat:

Ausführung misslungen - multiple rows in singleton select

Die Ausführung der SP durch IBConsole funktioniert wunschgemäß. Was habe ich hier noch nicht verstanden? (Bitte beachten: Es gibt wirklich nur einen suspend-Befehl in der Prozedur; also kann es auch nur eine einzelne Zeile mit 2 Rückgabewerten geben.)

Außerdem: Wie komme ich an die Rückgabewerte heran - durch Abfrage von cmdUpdate.Parameters oder durch Auswertung von Reader oder...?

Danke für Tipps! Jürgen

PS. Bitte nicht wundern: ich werde in den nächsten Tagen nicht am PC sein; aber dieses Problem wollte ich vorher noch los werden.

[dataspider]

Hi,

multiple rows in singleton select kommt vom Interbase Server und besagt,
dass du irgendwo einen Select hast, der nur einen Wert liefern darf, aber mehrere Records liefert.

Z.B.

SQL-Code:

Select id from tabelle where feld=wert into :variable;

Wenn 2 Datensätze im Feld den Wert habe, weiss die DB nicht, welche ID sie dir nun senden soll.
Somit würde ich in der Procedure schauen...


Frank

[Jürgen Thomas]

Danke, das hatte ich auch angenommen:

Zitat:

Zitat von dataspider

Hi,

multiple rows in singleton select kommt vom Interbase Server und besagt,
dass du irgendwo einen Select hast, der nur einen Wert liefern darf, aber mehrere Records liefert.

Wenn 2 Datensätze im Feld den Wert habe, weiss die DB nicht, welche ID sie dir nun senden soll.
Somit würde ich in der Procedure schauen...

Frank

Ich habe die Stored Procedure in meiner Anfrage bereits angegeben:

Es gibt eine einzige Anweisung, die neu_ID etwas zuweist.
Es gibt zwei Anweisungen, die Neuer_Monat etwas zuweist.
Es gibt erst danach eine einzige SUSPEND-Anweisung.

Wieso soll Interbase glauben, dass multiple rows zurückgeliefert werden?

1. Ersatzversuch:

SQL-Code:

SELECT * FROM Logbuch_Start (0, 0, 0)

aufgerufen durch C# (ursprünglich cmdUpdate, jetzt cmdSelect):

Code:

sCommand = "SELECT * FROM Logbuch_Start";
cmdSelect.Parameters.Clear();
cmdSelect.Parameters.Add(new BdpParameter("Daten_Nr",      BdpType.Int32, 0));
cmdSelect.Parameters.Add(new BdpParameter("Selektion_Nr", BdpType.Int32, 0));
cmdSelect.Parameters.Add(new BdpParameter("Formular_Nr",  BdpType.Int32, 0));
...
cmdSelect.CommandText = sCommand;
tblResults.Rows.Clear();
Connect.Open();
Adapter.Fill(ds, tblResults.TableName);

Dies liefert die Interbase-Fehlermeldung:

Zitat:

parameter mismatch for procedure LOGBUCH_START

2. Ersatzversuch:
Aufruf durch C#:

Code:

sCommand = "SELECT * FROM Logbuch_Start(0,0,0)";
cmdSelect.Parameters.Clear();
//  keine Parameter ausdrücklich eingetragen
//  Aufruf wie beim 1. Ersatzversuch

Dies liefert keine Fehlermeldung, aber die Procedure wird nicht ausgeführt, und es gibt die Rückgabewerte (null, true) statt (3, true); der Parameter neu_ID kann - siehe SQL-Quelltext - niemals als null zurückgegeben werden.

Also Problem: Wie kann ich mit BdpProvider eine Stored Procedure aufrufen, die Parameter will und Werte zurückgibt? Wie kann ich die Rückgabewerte auslesen? (Der Weg über tblResults und Adapter ist nur eine schnelle Ersatzlösung, aber nicht wirklich sinnvoll.)

Hilfe! Jürgen

[dataspider]

Zitat:

Zitat von Jürgen Thomas

Ich habe die Stored Procedure in meiner Anfrage bereits angegeben:

Dann hatte ich deine Kommentare falsch verstanden:

SQL-Code:

/*  alle Inhalte beschränkt auf das, was die Rückgabewerte betrifft  */
/*  einige weitere Befehle  */

Es wird nichts mit dem Rückgabewert zu tun haben.
Vielleicht solltest du doch mal den kompletten Code der SP posten.

Frank

[Jürgen Thomas]

Bitte sehr:

SQL-Code:

/*  im ersten SQL-Skript:  */
CREATE PROCEDURE Logbuch_Start
        ( Daten_Nr INTEGER, Selektion_Nr INTEGER, Formular_Nr SMALLINT )
RETURNS ( neu_ID INTEGER, Neuer_Monat BOOLEAN ) AS
BEGIN
  EXIT;
END 
/*  in einem späteren SQL-Skript:  */
ALTER PROCEDURE Logbuch_Start
        ( Daten_Nr INTEGER, Selektion_Nr INTEGER, Formular_Nr SMALLINT )
RETURNS ( neu_ID INTEGER, Neuer_Monat BOOLEAN ) AS
  DECLARE VARIABLE Computer_Name VARCHAR(35);
  DECLARE VARIABLE Nutzer_Name   VARCHAR(35);
  DECLARE VARIABLE Nutzer_Nr     INTEGER;
  DECLARE VARIABLE b1 BOOLEAN;
  DECLARE VARIABLE t1 TIME;
  DECLARE VARIABLE d1 DATE;
  DECLARE VARIABLE d2 DATE;
BEGIN
  /*  alle frueheren Eintraege, die nicht beendet wurden, werden
      beim Programmstart mit dem Vermerk 'Programmabsturz?' versehen  */
  IF ( Formular_Nr IS NULL )
  THEN Formular_Nr = 0;
  IF ( Formular_Nr = 0 )
  THEN BEGIN
    t1 = '23:59:59';
    d1 = CURRENT_DATE;
    UPDATE Logbuch
       SET Ende = CAST( Ende AS DATE ) + :t1,
           Bemerkung = 'Programmabsturz?'
     WHERE ( ( Start = Ende )
         AND ( Ende < :d1 )
         AND ( Bemerkung = '' ) );
  END

  /*  Nutzer-ID wird durch USER aus der Tabelle Nutzer geholt  */
  Nutzer_Name = USER;
  Nutzer_Nr   = NULL;
  EXECUTE PROCEDURE ID_Suchen_Nutzer ( '', NULL, :Nutzer_Name )
                   RETURNING_VALUES :Nutzer_Nr, :Nutzer_Name;
  /*  sicherheitshalber ersatzweise auf 0 setzen  */
  IF ( ( Nutzer_Nr IS NULL ) OR ( Nutzer_Nr < 0 ) )
  THEN Nutzer_Nr = 0;

  /*  Computer-Name von Interbase holen  */
  Computer_Name = NULL;
  SELECT TMP$USER_HOST         FROM TMP$ATTACHMENTS      WHERE TMP$USER = USER
                               INTO :Computer_Name;
  IF ( Computer_Name IS NULL )
  THEN Computer_Name = '';

  /*  andere Angaben werden auf Standards gesetzt, sofern erforderlich  */
  IF ( Daten_Nr IS NULL )
  THEN Daten_Nr = 0;
  IF ( Selektion_Nr IS NULL )
  THEN Selektion_Nr = 0;
  neu_ID = GEN_ID( Logbuch_id, 1 );
  INSERT INTO Logbuch ( ID, Nutzer_ID, Daten_ID, Selektion,
                        Computer, Formular, Start, Ende, Bemerkung )
               Values ( :neu_ID, :Nutzer_Nr, :Daten_Nr, :Selektion_Nr,
                        :Computer_Name, :Formular_Nr,
                        CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '' );

  /*  Einstellungen aktualisieren  */
  Neuer_Monat = False;
  IF ( Formular_Nr = 0 )
  THEN BEGIN
    EXECUTE PROCEDURE Einstellungen_Computerstart
               ( Computer_Name, Nutzer_Name, neu_ID );
    /*  kontrolliere, ob ein neuer Monat begonnen hat und
        folglich die monatlichen Anpassungen zu erledigen sind  */
    SELECT Datum1              FROM Einstellungen        WHERE Bezeichnung = 'Interna-DB'
                               INTO :d2;
    IF ( d2 < d1 )
    THEN Neuer_Monat = True;
  END
  SUSPEND;
END

Wie früher bereits gesagt: Aus der IBConsole heraus durch

SQL-Code:

SELECT * FROM Logbuch_Start(0,0,0)

wird genau eine Zeile zurückgegeben.

Hilft diese Angabe? (Ich hoffe, dass es nicht nötig ist, auch die anderen Prozeduren und die Tabellen zu erläutern.) Jürgen

Nachtrag zu @Hansa

Zitat:

Zitat von Hansa

Inwiefern ist hier ... sichergestellt, daß die Datenmenge nur aus einem Element besteht ? :shock:

Siehe auch den vollständigen Code; 'Bezeichnung' ist PrimaryKey von Einstellungen.

Nachtrag 2 zu @Hansa

Zitat:

Zitat von Hansa

Öfter mal was neues : Erläuterung zu später gestellter Nachfrage wird vor diese gesetzt. Jaja, die Kausalität.

Ich habe den Eindruck, dass die Moderatoren sauer werden, wenn ein neuer Eintrag hinzugefügt wird, statt dass ein vorheriger Eintrag mit Edit geändert wird...

Zitat:

Zitat von Hansa

'Bezeichnung' ist PrimaryKey von Einstellungen. - Das ist doch dem primary Key alles egal. Du kriegst (siehe Fehlermeldung) eben definitiv NICHT nur ein Element zurück !

Das kann doch nicht sein (und ist in der IBConsole auch nicht so)! PrimaryKey ist automatisch unique. Wenn ich also

SQL-Code:

SELECT Datum1        FROM Einstellungen     WHERE Bezeichnung = 'Interna-DB'

abfrage, gibt es definitiv nur den eindeutigen (= unique) Datensatz.

Nachtrag zu @Dataspider

Zitat:

Zitat von dataspider

Paramteter mismatch kommt von Formular_Nr -> Int32 statt Int16 (im ersten Beitrag richtig).

SQL-Code:

 SELECT TMP$USER_HOST FROM TMP$ATTACHMENTS WHERE TMP$USER = USER INTO :Computer_Name;

Erst mal nur als Frage:
TMP$ATTACHMENTS erzeugt für jede Verbindung einen Datensatz.
Wenn sich der gleiche User 2 mal verbindet, liefert dann dein Select evtll 2 Records?
Frank

Au ja, beides muss ich prüfen: (1) Formular_Nr habe ich vielleicht im C#-Code bei einem Parameter falsch eingetragen. (2) Es ist denkbar, dass zu diesem Zeitpunkt eine Verbindung über IBConsole (zur Kontrolle) und eine durch die Anwendung (mit dem o.g. Problem) besteht; ich muss die Abfrage nach dem Computer_Name verfeinern.

Dann ist dies natürlich nicht in Ordnung. Aber kann es wirklich Auswirkung auf den Suspend-Befehl und die eigentliche Fehlermeldung haben?

Mit diesen Hinweisen komme ich wenigstens weiter. Jürgen

PS. Das ist eines meiner Probleme als Einzelkämpfer: Manchmal erkennt man solche Situationen nicht selbst, sondern muss darauf hingestoßen werden.

[Hansa]

Inwiefern ist hier :

SQL-Code:

IF ( Formular_Nr = 0 ) 
  THEN BEGIN 
    /*  einige weitere Befehle  */ 
    Neuer_Monat = True; 
  END

sichergestellt, daß die Datenmenge nur aus einem Element besteht ? :shock:

[dataspider]

Hi,

erst mal nur auf die Schnelle...


Paramteter mismatch kommt von Formular_Nr -> Int32 statt Int16 (im ersten Beitrag richtig).

[EDIT]

SQL-Code:

 SELECT TMP$USER_HOST FROM TMP$ATTACHMENTS WHERE TMP$USER = USER INTO :Computer_Name;

Erst mal nur als Frage:
TMP$ATTACHMENTS erzeugt für jede Verbindung einen Datensatz.
Wenn sich der gleiche User 2 mal verbindet, liefert dann dein Select evtll 2 Records?

Frank

[Hansa]

Öfter mal was neues : Erläuterung zu später gestellter Nachfrage wird vor diese gesetzt. :zwinker: Jaja, die Kausalität. :mrgreen:

Zitat:

Zitat von Jürgen Thomas

'Bezeichnung' ist PrimaryKey von Einstellungen.

Das ist doch dem primary Key alles egal. Du kriegst (siehe Fehlermeldung) eben definitiv NICHT nur ein Element zurück !

[Elvis]

@Jürgen,
Haben diese Tabellen auf den Spalten einen Unique key?

TMP$Attachments -> TMP$User
Einstellungen -> Bezeichnung

Wenn nicht kann nicht sichergestellt werden, dass du nur einen Datensatz bekommst für die Abfragen innerhalb der SProc.

Ich würde also einfach mal testen, ob diese Spalten eindeitige Werte enthalten.
Entweder durch das Setzen eines Unique keys, oder so:

SQL-Code:

SELECT TMP$User, Count(*)
FROM   TMP$Attachments
GROUP BY TMP$User
HAVING Count(*) > 1

SELECT Bezeichnung, Count(*)
FROM   Einstellungen
GROUP BY Bezeichnung
HAVING Count(*) > 1

Edit: Ahhh... Ich hatte den Tab hier zu lange auf bevor ich weiter editiert hatte. (3 Stunden, oder so :mrgreen: )
Einen Schlawiner hätten wir also gefunden. :)
Nachtrag2: Oki und Beichnung ist der PrimKey. Alles klar, bitte weiterfahren. Die Idee war also nix...

Hier ist mal ein möglichst minimalistischer Ansatz, der mit dem Firebird DataProvider laufen sollte. (Für den BDP einfach die @ durch : ersetzen)

Code:

public static void LogbuchStart(IDbConnection connection,
                                int           datenNr,
                                int           selectionNr,
                                out int       neuId,
                                out bool      neuerMonat)
{
    using (IDbCommand cmd = connection.CreateCommand())
    {
        cmd.CommandText = "Logbuch_Start";
        cmd.CommandType = CommandType.StoredProcedure;

        IDataParameter datenNrPrm = cmd.CreateParameter();
        IDataParameter selectionNrPrm = cmd.CreateParameter();
        IDataParameter formularNrPrm = cmd.CreateParameter();
        IDataParameter neuIdPrm = cmd.CreateParameter();
        IDataParameter neuerMonatPrm = cmd.CreateParameter();

        datenNrPrm.ParameterName = "Daten_Nr";
        datenNrPrm.Direction = ParameterDirection.Input;
        datenNrPrm.DbType = DbType.Int32;
        datenNrPrm.Value = datenNr;

        selectionNrPrm.ParameterName = "Selektion_Nr";
        selectionNrPrm.Direction = ParameterDirection.Input;
        selectionNrPrm.DbType = DbType.Int32;
        selectionNrPrm.Value = selectionNr;

        formularNrPrm.ParameterName = "Formular_Nr";
        formularNrPrm.Direction = ParameterDirection.Input;
        formularNrPrm.DbType = DbType.Int16;
        formularNrPrm.Value = datenNr;

        neuIdPrm.ParameterName = "neu_ID";
        neuIdPrm.Direction = ParameterDirection.Output;
        neuIdPrm.DbType = DbType.Int32;

        neuerMonatPrm.ParameterName = "Neuer_Monat";
        neuerMonatPrm.Direction = ParameterDirection.Output;
        neuerMonatPrm.DbType = DbType.Int16;

        cmd.ExecuteNonQuery();

        if (neuIdPrm.Value != null
            && neuIdPrm.Value != DBNull.Value)
            neuId = (int) neuIdPrm.Value;
        else
            neuId = -1;

        if (neuerMonatPrm.Value != null
            && neuerMonatPrm.Value != DBNull.Value)
            neuerMonat = (int) neuerMonatPrm.Value == 1;
        else
            neuerMonat = false;
    }
}

Falls er hier immer noch kracht könnte das an der Eigenheit von PSQL liegen, dass es einfach gar keine Unterstützung von Funktionen besitzt.
Rückgabewerte können nur in einer Ergebnismenge zurückgeliefert werden.
Flls dein DataProvider das nicht für ein single select automatisch umwurschtelt könnte es so mit einem Select aussehen:

Code:

public static void LogbuchStartSelect(IDbConnection connection,
                                      int           datenNr,
                                      int           selectionNr,
                                      out int       neuId,
                                      out bool      neuerMonat)
{
    using (IDbCommand cmd = connection.CreateCommand())
    {
        cmd.CommandText = "SELECT neu_ID\n" + 
                          "      ,Neuer_Monat\n"
                          + "FROM   Logbuch_Start(@datenNr, @selectionNr, @formularNr)";
        cmd.CommandType = CommandType.Text;

        IDataParameter datenNrPrm = cmd.CreateParameter();
        IDataParameter selectionNrPrm = cmd.CreateParameter();
        IDataParameter formularNrPrm = cmd.CreateParameter();

        datenNrPrm.ParameterName = "datenNr";
        datenNrPrm.Direction = ParameterDirection.Input;
        datenNrPrm.DbType = DbType.Int32;
        datenNrPrm.Value = datenNr;

        selectionNrPrm.ParameterName = "selectionNr";
        selectionNrPrm.Direction = ParameterDirection.Input;
        selectionNrPrm.DbType = DbType.Int32;
        selectionNrPrm.Value = selectionNr;

        formularNrPrm.ParameterName = "formularNr";
        formularNrPrm.Direction = ParameterDirection.Input;
        formularNrPrm.DbType = DbType.Int16;
        formularNrPrm.Value = datenNr;

        neuId = -1;
        neuerMonat = false;

        using (IDataReader reader = cmd.ExecuteReader())
        {
            if (reader.Read())
            {
                if (!reader.IsDBNull(0))
                    neuId = reader.GetInt32(1);

                if (!reader.IsDBNull(1))
                    neuerMonat = reader.GetInt32(1) == 1;
            }
        }
    }
}

Zitat:

Zitat von Hansa

Inwiefern ist hier :

SQL-Code:

IF ( Formular_Nr = 0 ) 
  THEN BEGIN 
    /*  einige weitere Befehle  */ 
    Neuer_Monat = True; 
  END

@H.
sichergestellt, daß die Datenmenge nur aus einem Element besteht ? :shock:

Weil nur ein einziges "suspend" ausgeführt wird?
Mann, Mann, wenn man dir jetzt auch noch das erklären muss wo normalerweise dein Horizont endet...

[dataspider]

Zitat:

Zitat von Jürgen Thomas

Es ist denkbar, dass zu diesem Zeitpunkt eine Verbindung über IBConsole (zur Kontrolle) und eine durch die Anwendung (mit dem o.g. Problem) besteht; ich muss die Abfrage nach dem Computer_Name verfeinern.

Dann ist dies natürlich nicht in Ordnung. Aber kann es wirklich Auswirkung auf den Suspend-Befehl und die eigentliche Fehlermeldung haben?

SQL-Code:

SELECT TMP$USER_HOST FROM TMP$ATTACHMENTS WHERE TMP$USER = USER INTO :Computer_Name;

Interbase erwartet in der SPL bei dem Konstrukt Into einen Select, welcher keinen oder einen Datensatz liefert.

Damit kann er als Wert für die Variable Computer_Name entweder null (kein Datensatz) oder den Inhalt von
TMP$USER_HOST (genau 1 Datensatz) erhalten.

Eine Ergebnismenge von mehr als 1 Datensatz ist bei Into (ohne for select .. do ..) ungültig und liefert dir folgende Fehlermeldung.

...multiple rows in singleton select

Das Suspend kannst du bei der Fehlersuche vergessen. Da in Interbase auch Selected Procedures unterstützt werden, kannst du Suspend beliebig oft aufrufen. Es produziert keine Fehlermeldung. Der letzte zugewiesene Wert vor dem Suspend wird zurückgegeen.

Cu, Frank