Probl. med å generere en spørring i en SP

[frenzy81]

Jeg bruker stored procedures i MySql 5, da jeg skal ha så liten datatrafikk som mulig.

 

 

Jeg har en databaselogg over en produksjonsprosess, og sender i prosedyren inn _startdato, _sluttdato og en bool som bestemmer om resultatet skal være pr dag eller pr måned.

 

I prosedyren så regner jeg ut hvor mange dager eller måneder tidsrommet strekker seg over, og så skal jeg ha ut så mange resultater som antall dager/måneder. Hvis tidsrommet er '2006-12-01' til '2006-12-05' og jeg skal ha det ut i dager så skal jeg ha 5 resultater, med produksjonsantall for de dagene.

 

Problemet mitt er at jeg ikke får til å manipulere med sql-setningen slik som jeg f.eks. gjør i Delphi. Der kan jeg bygge opp sql-setningen som en string, og så sende stringen inn som argument til databasespørringsmodulen. F.eks:

 

sql='select * from productionlog ';

if true then sql:=sql+'order by id asc';

 

Dette får jeg ikke til i ren sql. Noen som kan hjelpe?

 

Limer inn koden jeg har foreløpig:

 

DROP PROCEDURE IF EXISTS `ppr`.`sp_AntallEnheterDAYSorMONTHS` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_AntallEnheterDAYSorMONTHS`(
IN _start TIMESTAMP,
IN _slutt TIMESTAMP,
 IN _daysTRUEmonthsFALSE BOOL
)
BEGIN

DECLARE _antDager INT DEFAULT 0;
DECLARE _antMnd INT DEFAULT 0;
DECLARE _startDato TIMESTAMP;
DECLARE _sluttDato TIMESTAMP;
DECLARE _string TEXT DEFAULT "Hei";
SET _startDato = _start;
SET _sluttDato = _slutt;

#Her skal jeg bestemme hvor mange resultater som skal returneres - enten i dager eller måneder
IF (_daysTRUEmonthsFALSE = TRUE )
  THEN
     SET _antDager = datediff(_slutt,_start);

     WHILE _antDager > 0 DO

           #Her ønsker jeg å bygge opp spørringen før jeg kjører den... og det får jeg ikke til
           SET _string =	("hw.id AS HWID FROM productionlog
           LEFT JOIN hw ON (productionlog.HWid = HW.id)
           WHERE	(_start <= productionlog.Created AND	productionlog.Created <= _slutt)
GROUP BY HW.id");

           SET _antDager = _antDager - 1;
           SET _startDato = DATE_ADD(_startDato, INTERVAL 1 DAY);


     END WHILE;

     #Dette går ikke... Strengen forblir en streng, og i en spørring spyttes 
     #selve spørringen ut, og ikke resultatene jeg ønsker.
     
     SELECT _string;

  ELSE
     SET _startDato = DATE_FORMAT(_startDato,'%Y%d');
     SET _sluttDato = DATE_FORMAT(_sluttDato,'%Y%d');
     SET _antMnd = PERIOD_DIFF(_sluttDato,_startDato);

     WHILE _antMnd > 0 DO

           #SELECT _antMnd;

           SET _antMnd = _antMnd - 1;
           SET _startDato = DATE_ADD(_startDato, INTERVAL 1 MONTH);


     END WHILE;
END IF;

END $$

Endret 12. desember 2006 av as-man

[roac]

Har du forsøkt å google etter fornuftige ord, f eks dynamic, mysql og query?

 

PREPARE og EXECUTE er kommandoene du leter etter.

[frenzy81]

Tja... Jeg har allerede sett på det, men det er ikke noe jeg kan bruke, såvidt jeg kan se.

 

Kort sagt ønsker jeg å bygge opp statementen min av forskjellige deler. Se på eksempelet under så ser du noe av det jeg ønsker:

 

DECLARE teller INT DEFAULT 1;

SET @s =	'SELECT hw.id AS HWID, COUNT(productionlog.id) AS AntallTotalt, sum(if(productionlog.ErrorID<=0,1,0)) AntallOK,sum(if(productionlog.ErrorID>0,1,0)) AntallError,
sum(if(productionlog.ErrorID<=0,1,0))/COUNT(productionlog.id) ProsentOK,sum(if(productionlog.ErrorID>0,1,0))/COUNT(productionlog.id) ProsentError ';

WHILE teller > 0 DO
       @s = @s + ',(SELECT COUNT(*) FROM productionlog WHERE 
       productionlog.errorid=1)';

       teller = teller - 1;
END WHILE;

SET @s = @s + 'FROM	productionlog
LEFT JOIN hw ON (productionlog.HWid = HW.id)
WHERE	(? <= productionlog.Created AND productionlog.Created <= ?)
GROUP BY HW.id';

PREPARE theQuery FROM @s;
SET @a = _start;
SET @b = _slutt;

EXECUTE theQuery USING @a,@b;
DEALLOCATE PREPARE theQuery;

 

Denne koden funker ikke, men viser hva jeg vil få til...

 

For det første får jeg ikke gjort (SET @s = @s + 'noe tekst') og for det andre så kan jeg ikke ha variabelnavn inni teksten som statementen skal bygges opp av.

 

Har brukt litt kode jeg fant på mysql-dokumentasjonen for PREPARED STATEMENTS. Fant ikke helt ut hva @ foran s gjør, selv om jeg tror det betyr at det er en session variabel.

 

 

Noen bedre forslag, eller rettelser på det jeg sa her?

Endret 12. desember 2006 av as-man

[roac]

Tja... Jeg har allerede sett på det, men det er ikke noe jeg kan bruke, såvidt jeg kan se.

 

Kort sagt ønsker jeg å bygge opp statementen min av forskjellige deler. Se på eksempelet under så ser du noe av det jeg ønsker:

 

DECLARE teller INT DEFAULT 1;

SET @s =	'SELECT hw.id AS HWID, COUNT(productionlog.id) AS AntallTotalt, sum(if(productionlog.ErrorID<=0,1,0)) AntallOK,sum(if(productionlog.ErrorID>0,1,0)) AntallError,
sum(if(productionlog.ErrorID<=0,1,0))/COUNT(productionlog.id) ProsentOK,sum(if(productionlog.ErrorID>0,1,0))/COUNT(productionlog.id) ProsentError ';

WHILE teller > 0 DO
       @s = @s + ',(SELECT COUNT(*) FROM productionlog WHERE 
       productionlog.errorid=1)';

       teller = teller - 1;
END WHILE;

SET @s = @s + 'FROM	productionlog
LEFT JOIN hw ON (productionlog.HWid = HW.id)
WHERE	(? <= productionlog.Created AND productionlog.Created <= ?)
GROUP BY HW.id';

PREPARE theQuery FROM @s;
SET @a = _start;
SET @b = _slutt;

EXECUTE theQuery USING @a,@b;
DEALLOCATE PREPARE theQuery;

 

Denne koden funker ikke, men viser hva jeg vil få til...

 

For det første får jeg ikke gjort (SET @s = @s + 'noe tekst') og for det andre så kan jeg ikke ha variabelnavn inni teksten som statementen skal bygges opp av.

 

Har brukt litt kode jeg fant på mysql-dokumentasjonen for PREPARED STATEMENTS. Fant ikke helt ut hva @ foran s gjør, selv om jeg tror det betyr at det er en session variabel.

 

Noen bedre forslag, eller rettelser på det jeg sa her?

7487308[/snapback]

Du mangler ordet SET i forbindelse med tilordningen her.

WHILE teller > 0 DO
       @s = @s + ',(SELECT COUNT(*) FROM productionlog WHERE 
       productionlog.errorid=1)';

       teller = teller - 1;
END WHILE;

 

Whileløkken kan forøvrig i sin helhet elimineres siden teller er 1.

 

Videre er jeg ikke sikker så derfor spør jeg: Kan du deklarere variable hvis navn ikke begynner med @?

 

Hvilke feilmeldinger får du i koden? Har du sett på disse, for å prøve å finne ut hva som er galt?

[frenzy81]

Whileløkken skal til slutt telle opp antall dager mellom to datoer, f.eks. 1-3 måneder i slengen...

 

Declare skal kunne brukes til å lage lokale variabler, og jeg har ikke sett @ brukt noe annet sted enn på commandlinjen. ( http://dev.mysql.com/doc/refman/5.1/en/dec...-variables.html )

 

 

Når jeg velger 'Execute script' i MySQL Query Browser så sjekkes koden først for feil, hvis ingen feil så legges prosedyren inn. Dette er vel kun syntakssjekk.

 

Får ingen feil før jeg prøver å kalle prosedyren...

 

Derfor tenkte jeg å prøve eksempelet som stod i dokumentasjonen for Prepared Statements, på kommandolinjen. Og her er resultatet - med samme feil jeg får når jeg prøver å kalle prosedyren:

 

mysql> SET @s='SELECT id FROM hw ';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @s= @s + 'ORDER BY settingID ASC';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> PREPARE stmt FROM @s;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '0' at line 1

 

Det ser ut som at det er ( SET @s = @s + 'sql' ) som gjør at dette går ad undas. Og da er vi tilbake til hovedproblemet mitt - som er dynamisk oppbygging av en spørring.

 

Har i det minste funnet ut at jeg kan gjøre dette:

 

SET @s='Etter A';

SET @t=Concat(@s,' så kommer B');

 

Så da skal jeg vel klare å bygge det opp, ved hjelp av mange Concats. Men fortsatt så er problemet at jeg ikke kan kalle en variabel som står inni spørringen som er bygget opp. Jeg kan bruke et ?-tegn for å indikere ukjent, men det blir bare rot dersom jeg skal ha 2 ukjente pr dag, og det er 80-90 dager...

Endret 14. desember 2006 av as-man

[roac]

Slik som det ser ut for meg så bruker du galt verktøy, og at følgende skjer i demoen du viser:

 

I første spørring går alt greit, variabelen deklareres og tilordnes verdi. Så avsluttes batchen.

 

I andre spørring er vi i en ny batch, og variabelen fra batch 1 er ikke lenger i scope. Siden du legger til tekst på en allerede "eksisterende" men ikke initiert tekststreng får du en advarsel. Batchen avsluttes.

 

I tredje spørring får du feilmelding fordi variablene fra første og andre batch begge to er ute av scope, og du sender inn en tom sql-kommando, og da forteller mysql deg at det er en feil ved 0 (posisjonen null?).

 

Finn et verktøy (kan lastes ned fra mysql.com eller mysql.org) som lar deg kjøre flere kommandoer i batch, så tror jeg du er langt på vei. Evt se om det er noen måte å skru av automatisk batchhåndtering eller noe sånt i mysql(.exe).