stored procedure, select innerhalb bringt kein Ergebnis

OlafNagel

Benutzer
Beiträge
10
Guten Morgen liebe Leute,
ich habe mich heute her angemeldet und hoffe das ich hier noch ein bisschen lernen kann.

folgendes Problem (DB = Maria DB auf xampp) :
ich habe eine procedure die ansich auch durchläuft und das gewünschte Ergebnis liefert, ausser bei ein paar Optionen.
Ich habe mal die betreffenden Stellen markiert. Ich habe schon viele Tests laufen lassen, Variablen getestet. Alle Eingaben sind vorhanden. Die betreffenden Selects funktionieren auch außerhalb . Aber innerhalb liefern diese Selects kein Ergebnis. Kann mir jemand einen Tipp geben?

SQL:
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateStorage`(IN item INT(10) UNSIGNED, IN reason INT(5) UNSIGNED, IN quantity NUMERIC(10,2), IN unit int(10) UNSIGNED, IN magazin INT(5) UNSIGNED, IN users INT(10) UNSIGNED)
BEGIN
    /*declare used values */
    DECLARE piece INT(10) DEFAULT NULL;
    DECLARE itgroup INT(10) DEFAULT NULL;
    DECLARE content INT(10) DEFAULT NULL;
    DECLARE order_id INT(10) DEFAULT NULL;
    DECLARE piecequant NUMERIC(10,2) DEFAULT NULL;
    DECLARE store INT(5) DEFAULT NULL;
    
    -- change unit between package and content, deepends input
    #HIER LIEFERT DAS SELECT KEIN ERGEBNIS, AUSSERHALB JA
    SELECT  `cont` INTO content FROM `it` WHERE `id` = item; # contents of parts packaging
    
    #HIER LIEFERT DAS SELECT KEIN ERGEBNIS, AUSSERHALB JA
    SELECT (CASE WHEN `itgroup` = 1000 then
                `pack`
                ELSE
                 `unit` END) into piece FROM `it` WHERE `id` = item; # look for storage unit
  
    -- If the input is not like storage unit
    IF unit != piece
        THEN
            set piecequant  = quantity  * content; 
    else
            set piecequant = quantity;
            
    END IF;
    
    -- if input magazin is empty
    
    IF  magazin = 0  THEN
        #HIER LIEFERT DAS SELECT KEIN ERGEBNIS, AUSSERHALB JA
        SELECT  `magazin` INTO store FROM `stock` WHERE `it` = item;
      ELSE
        set store = magazin;
    END IF;
    
    
         -- when input is goods receipt   --
         IF reason = 2  THEN
        
                -- CHECK IF ORDER EXISTS
                select  `po` into order_id FROM `pohis` where `it` = item AND `stat` = 1 ORDER BY `id` DESC LIMIT 1;               
                
                    Update `stock` SET
                    `stand` = `stand` + `piecequant`,
                    `pUp` = users
                    WHERE `it` = item;
                    
                -- loge the process in the order history table
                IF order_id != NULL OR order_id !='' THEN
                    
                    UPDATE `pohis`
                    LEFT JOIN `stock` ON `stock`.`it` = `pohis`.`it`
                    SET
                        `pohis`.`pack` = unit ,
                        `pohis`.`deliv` = curdate(),
                        `pohis`.`quantdel` = quantity,
                        `pohis`.`po` = order_id,
                        `pohis`.`pUp` = users,
                        `stock`.`stat` = 0,
                        `pohis`.`stat` = 0
                        WHERE `pohis`.`po` = order_id AND `pohis`.`it` = item;
                ELSE
                    INSERT INTO `pohis` (`it`, `pack`, `deliv`, `quantdel`, `po`, `pIn`) VALUES (item, unit, curdate(), quantity, order_id, users);
                END IF;
              
                
            -- correction for inventory entries
            ELSEIF reason = 3 THEN
            
                UPDATE `stock` set `stand` = piecequant
                WHERE `it` = item;
                
            -- enter new  existing material --   
            ELSEIF reason = 8 THEN
            
                INSERT INTO `stock`( `it`, `magazin`, `stand`, `pIn`) VALUES (item , magazin, piecequant, users);
                
            ELSEIF reason = 4 OR reason = 5 OR reason = 6 OR reason = 9    THEN 
            
                UPDATE `stock`
                    SET `stand` = `stand` - piecequant,
                    `pUp`= users
                WHERE `it`= item;   
                
            
        ELSEIF reason = 10 THEN
            INSERT INTO `po`( `pIn` ) VALUES (users);
            INSERT INTO `pohis` (it, pack, quant, po,stat, pIn) VALUES (item, piece, quantity, last_insert_id(),1, users);   
            UPDATE stock SET stat = 1 WHERE it= item;
                
            
         END IF;# end if reason is income
        
         -- log in statistics
         INSERT INTO `stati`( `reason`, `q`, `it`, `magazin`, `cos`, `po`) VALUES ( reason,piecequant,item, store, users, order_id);
    
END
 
Werbung:
Also anhand der Prozedur (ohne Daten und Modell) kann ich da nichts feststellen.
Einziger Verdächtiger: create definer ..
Du hast im laufenden Betrieb mglw. einen anderen (den definierten) Rechtekontext, als bei der manuellen Probe einzelner Statements.

Falsche/ fehlende Zugriffsrechte sollten normalerweise mit Fehlermeldungen quittiert werden. Dabei ist Deine Formulierung "kein Ergebnis .. außerhalb ja" hinreichend unpräzise, um zu wissen, wie Du die Prozeduren feuerst. Etwas schlechter PHP Code reicht vermutlich, um "kein Ergebnis" zu bekommen, statt Fehlermeldungen beim Zugriff zu erhalten.
 
Also anhand der Prozedur (ohne Daten und Modell) kann ich da nichts feststellen.
Einziger Verdächtiger: create definer ..
Du hast im laufenden Betrieb mglw. einen anderen (den definierten) Rechtekontext, als bei der manuellen Probe einzelner Statements.

Falsche/ fehlende Zugriffsrechte sollten normalerweise mit Fehlermeldungen quittiert werden. Dabei ist Deine Formulierung "kein Ergebnis .. außerhalb ja" hinreichend unpräzise, um zu wissen, wie Du die Prozeduren feuerst. Etwas schlechter PHP Code reicht vermutlich, um "kein Ergebnis" zu bekommen, statt Fehlermeldungen beim Zugriff zu erhalten.
Danke für deine Antwort. ich weiß das es ohne Daten schwierig ist. Der Definer wird von der Workbench automatisch gesetzt.
Mein Problem ist, das nur diese drei Selects kein Ergebnis liefern.
Daher glaube ich nicht an ein Rechte Problem.
Die procedure läuft auch durch, solange die Eingabe vollständig ist bzw. der User nur in den gespeicherten werten auswählt. Wenn sie nun bei Inventur die Kartons zählen und eingeben, will ich, dass das ermittelt wird, im Lager aber in Stück errechnet wird wie z. B.: Eingabe * Kartoninhalt.
Da die User, sagen wir bequem sind, ist das schwierig, ich könnte die Eingabe stringenter machen, doch dann lassen sie Eingaben weg. Lieferscheine sind z.B.: immer nur Gesamtstückzahl.
 
Danke für deine Antwort. ich weiß das es ohne Daten schwierig ist. Der Definer wird von der Workbench automatisch gesetzt.
Mein Problem ist, das nur diese drei Selects kein Ergebnis liefern.
Daher glaube ich nicht an ein Rechte Problem.
Die procedure läuft auch durch, solange die Eingabe vollständig ist bzw. der User nur in den gespeicherten werten auswählt. Wenn sie nun bei Inventur die Kartons zählen und eingeben, will ich, dass das ermittelt wird, im Lager aber in Stück errechnet wird wie z. B.: Eingabe * Kartoninhalt.
Da die User, sagen wir bequem sind, ist das schwierig, ich könnte die Eingabe stringenter machen, doch dann lassen sie Eingaben weg. Lieferscheine sind z.B.: immer nur Gesamtstückzahl.
Hallo,

ich verstehe nicht warum du keine Daten / Strukturen schickst, damit man dir helfen kann. Wenn du die queries außerhalb
der SP startest sehen diese natürlich auch anders aus.
Poste doch zumindest mal den Kram sonst müssen wir wieder die Glaskugel auspacken.

Hier nur mal eine kleine Optimierung (hat aber nix mit deinem Fehler zu tun )
Code:
    SELECT `cont`,
             IF(`itgroup` = 1000, `pack`, `unit`),
             quantity * if(unit != IF(`itgroup` = 1000, `pack`, `unit`), content,1)
    INTO content, piece, piecequant
    FROM `it`
    WHERE `id` = item;

Gruß

Bernd
 
Sorry für die verspäteten Daten.
mal hier einen Fall mit dem Versuch die Funktionalität zu erklären.

Code:
-- item = 1024 welches in 28 magazin liegt, der reason ist Materiallieferung,
geliefert 1 Rolle (mit 500 Stck wäre cont aus select)
user ist 1004, danach sollte in stock + 500 Stck stehen.
call sb.updateStorage(1024, 2, 1, 5, 28, 1004);

select * from stock where it = 1024;
 
Danke, du würdest uns aber helfen wenn du einen SQL DUMP schicken könntest, dann können wir das direkt importieren, sowie
1-2 CALL Aufrufe.

Dann ist das schnell gemacht.

Bei Fragen kannst du auch kurz anrufen. 02163 / 9879851

Gruß

Bernd
Würde ich gerne, doch die Workbench liefert immer nur Fehlermeldungen wie diese und ich weiss nicht wie ich es reparieren kann. Ist wahrscheinlich der verschiedenen Versionen.
Operation failed with exitcode 2
11:12:42 Dumping sb views and/or routines and/or events
Running: mysqldump.exe --defaults-file="C:\Users\HP\AppData\Local\Temp\tmp7n9u7bis.cnf" --host=127.0.0.1 --port=3306 --default-character-set=utf8 --user=root --protocol=tcp --skip-triggers --no-data --no-create-db "sb" "viewupdateag" "assigview" "call_prodparts" "plangroups" "partbookhistory" "prodsap" "neededparts" "warehousestock" "assigstart" "material_storage_basics" "packaging" "itemview" "prodview" "proddetail" "productgroups" "itemdetail" "view_find_assig_by_number" "tableall" "products" "itgroups"
mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'sb' AND TABLE_NAME = 'viewupdateag';': Unknown table 'column_statistics' in information_schema (1109)

Operation failed with exitcode 2
11:12:42 Export of C:\Users\HP\OneDrive\Dokumente\dumps\Dump20230312 has finished with 25 errors
 
Würde ich gerne, doch die Workbench liefert immer nur Fehlermeldungen wie diese und ich weiss nicht wie ich es reparieren kann. Ist wahrscheinlich der verschiedenen Versionen.
Hier ist das Problem und Lösung Beschrieben:


Gruß

Bernd
 
Hier ist das Problem und Lösung Beschrieben:


Gruß

Bernd
danke aber da muss ich mit meinem Wissen passen. Beim Begin der Erstellung eines dumps werde ich schon aufgefordert die DB zu downgraden.
 
Ich habe jetzt versucht die procedur direkt in phpMyAdmin zu bearbeiten, und siehe da es gibt Fehlermeldungen.
Schon in der Kopfzeile "NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER" welches mich noch mehr wundert.
Im Anhang ist jetzt eine zip mit der sql der Db. Aber kein dump.
 

Anhänge

Der in phpMyAdmin angezeigte Fehler war nur die definition von quantity als numeric ist jetzt decimal. Aber nach wievor bringen die Selects keinen Fehler und kein Ergebnis, daher auch die nachfolgenden if's nicht das gewünschte Ergebnis bringen.
 
Aber nach wievor bringen die Selects keinen Fehler und kein Ergebnis
Ich habe noch nicht gesehen, dass Du irgendwo einen nachvollziehbaren Aufruf dieser Prozedur zeigst (mit Fehlermeldung).
Weder in der Console, noch mit PHP oder anderem Client Code oder in irgendeiner Datenbank IDE.
Es hilft an der Stelle gar nichts, wenn Du die gewünschte Funktionalität beschreibst, auch nicht in verschiedenen Worten. Es interessiert vor allem erstmal, ob und welche Fehler auftreten und wie die Aufrufe erfolgen (Stichwort Fehlerbehandlung).
 
Werbung:
Hier sind mal ein paar Aufrufe.
SQL:
      ##CALL `updateStorage` (
      # IN item INT(10) UNSIGNED, 
      # IN reason INT(10) UNSIGNED,
      # IN quantity NUMERIC(10,2), 
      # IN unit int(10) UNSIGNED, 
      # IN magazin INT(10) UNSIGNED,
      # IN users INT(10) UNSIGNED)
      
      -- Material Zugang mit kompletter Eingabe --
      call updateStorage(1001, 2, 100, 1, 1, 1004); -- ohne Fehler --
      -- Material Inventur mit kompletter Eingabe --
      call updateStorage(1001, 3, 100, 1, 1, 1004);-- ohne Fehler --
      -- Material Zugang mit kompletter Eingabe aber Warenmenge in Kartons, das bedeutet Eingabe Menge * Kartoninhalt --
       call updateStorage(1024, 2, 1, 5, 28, 1004);-- Fehler, Menge nur als 1 Stück übernommen sollte aber bei Rolle enthält 500 Stück ergeben -- 
       -- Materialentnahme, Eingabe in Stück oder M komplett --
       call updateStorage(1001, 4, 10, 1, 1, 1004); -- Fehlerfrei --
       -- Materialzugang komplett ausser Magazin  hier vergessen !! aber auch falsche Eingabe bringt Fehler, da 'it' in stock is unique--
       call updateStorage(1001, 2, 100, 1, 0, 1004);-- Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`sb`.`stati`, CONSTRAINT `statprotokoll_magazin_key` FOREIGN KEY (`magazin`) REFERENCES `magazin` (`id`) ON UPDATE CASCADE)    0.016 sec
       -- Materialentnahme, Eingabe in Stück oder M komplett --
       call updateStorage(1001, 9, 10, 1, 1, 1004);-- ohne Fehler --
       -- Materialbestellung komplette Eingabe --
       call updateStorage(1001, 10, 10, 1, 1, 1004);-- ohne Fehler --
 
Zurück
Oben