[MySQL] Function/SP voor set sql_mode

[dj_vibri]

Allen,

ik ben aan het proberen om al mijn hard-coded select,insert, .... query's over te zetten naar stored procedures.
Nu blijkt dat wanneer je wil concatenaten dmv pipes ( || ) je een variabele in "sql_mode" moet wijzigen.

Ik zit op een shared hosting omgeving en mijn webhost wil de globale my.ini niet wijzigen.....
Ik heb al gevonden dat je deze ook kan plaatsen dmv van volgende:

code:

SET Sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT';

Wanneer ik dit in de mysql query browser test en vervolgens:

code:

SELECT @@sql_mode;

krijg ik de correcte waarde en kan dus met 'pipes' werken. Open ik daarin tegen terug een nieuw tabblad-sheet ben ik verplicht van eerst terug de sql_mode te setten en pas nadien mijn query uit te voeren...

Ik weet dat het hier over sessie_variabelen gaat, maar nu vraag ik mij het volgende af:

- Wat als je dit gaat 'live' zetten en verscheidene mensen gaan via de asp.net site connectie maken naar de DB. Gaan er dan conflicten optreden wanneer ik in ELKE stored procedure de sql_mode wil gaan setten?
- Moet ik dit slechts 1x doen vermits er eigenlijk met een specifieke user connectie gemaakt wordt? (ipv anonymous)
- Hoe kan ik ervoor zorgen dat dit slechts 1x kan gebeuren?

Voor het laatste had ik gedacht aan een Functie te maken welke controleert op de huidige sql_mode en indien deze verschilt van degene met pipes_as_concat, set dan de sql_mode naar de juiste en return een '1' oid zodat ik hierop kan checken in al mijn SP's.....

code:

DELIMITER $$

DROP FUNCTION IF EXISTS `testen`.`F_SQL_MODE` $$
CREATE FUNCTION `testen`.`F_SQL_MODE` () RETURNS tinyint(1)
BEGIN
  DECLARE Status tinyint(1);
  SET Status = 0;


  IF (SELECT @@sql_mode <> 'PIPES_AS_CONCAT,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION') THEN
    SET sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT';
    SET Status = 1;
  END IF;

  return Status;

END $$

DELIMITER ;

Wanneer ik nu een query uitvoer

code:

select F_SQL_MODE();
SELECT @@sql_mode;

Dan returnt de functie een '1' (wat wil zeggen dat de sql_mode inderdaad verschilt van degene met pipes_as_concat), maar wanneer ik de select uitvoer krijg ik niet de juiste sql_mode string terug (maar de default waarde).....

Any thoughts?

[cariolive23]

Waarschijnlijk draait een stored procedure in zijn eigen sessie, iedere andere sessie die je opstart heeft dan ook weer zijn eigen settings...

Dit is één van de redenen waarom ik een bloedhekel heb aan MySQL, je kunt het gewoon niet vertrouwen. Dit maakt bouwen, testen en beheer ook veel te kostbaar. Dit soort zaken zijn gewoon overbodig, andere databases leveren het bewijs.

[Voutloos]

cariolive23 schreef op woensdag 19 augustus 2009 @ 10:39:
Waarschijn ...
Dit is één van de redenen

Assumptions are ...

Ik heb het wel vaker tegen je gezegd: je uit vaak genoeg terecht commentaar op mysql, maar je rant ook net zo vaak in het wildeweg. SQL_MODE (hoewel het op zich een vd grootste WTF's is) is gewoon per connectie. Kwestie van in je DB classe bij het verbinden of voor de 1e query call deze query uit voeren.

[dj_vibri]

[b][message=32433840,noline] SQL_MODE (hoewel het op zich een vd grootste WTF's is) is gewoon per connectie. Kwestie van in je DB classe bij het verbinden of voor de 1e query call deze query uit voeren.

en met DB classe bedoel je de classe via asp.net ? en wat betreft de 1e query call zit je met 2 mogelijkheden: 1) eindgebruiker gaat zich meteen registreren (SP_Registration)
2) eindgebruiker gaat zich gewoon aanmelden (SP_Login)

waar je dus twee verschillende SP's hebt..... ik denk dat ik eens ga uitzoeken hoe het via asp.net zou moeten gaan dan.....

Bijkomend vraagje: vermits ik via een webhosting company werk veronderstel ik dat ik best geen Global sql_mode ga wijzigen maar enkel een 'SET SESSION sql_mode...' right?!? of kan ik zonder problemen de Global aanpassen?

[Voutloos]

Ik werk altijd met een eenvoudige abstractie db abstractie laag. Is ook erg handig met debuggen en een aantal dbms specifieke dingetjes verstoppen.

Globaal sql_mode aanpassen is absoluut een no-go, want dan breek je gegarandeerd andere applicaties.

[cariolive23]

of kan ik zonder problemen de Global aanpassen?

Alleen wanneer je superuser bent en dat zal bij shared hosting niet het geval zijn. Daarnaast bestaat er een goede kans dat 9 van de 10 applicaties dan niet meer werken, de bugs vliegen je dan om de oren.

@Voutloos: Het hele idee van SQL_MODE staat mij niet aan, alles en iedereen kan deze mode voor zijn/haar sessie aanpassen of juiste vergeten om aan te passen. En vouten maken we allemaal, je weet dus zeker dat je vroeg of laat een keer met de verkeerde settings acties op je database gaat uitvoeren. Met een beetje pech help je daarbij de data naar de bliksem en goede kans dat je daar dan geen voutmelding van krijgt. Dataintegriteit mag niet afhankelijk zijn van een setting die door alles en iedereen runtime is aan te passen. Voor functioneel gedrag geldt hetzelfde, || en || zijn twee totaal verschillende dingen, afhankelijk van de settings.

Ik ben van mening dat het onbetrouwbaar gedrag is van MySQL. En als dit een rant is, dan is het maar een rant.

[Voutloos]

Nee, hoor je hebt op alle punten gelijk en ik kan zelf rustig een aantal uur volpraten waarom sql_mode een uberdom idee is. Maar je post begon gewoon met een aanname. Inderdaad vertrouw je liever niet op die setting, idealiter had die setting een betere default en (maar dat is een utopie) bestond die setting helemaal niet of was er in ieder geval een progressievere houding mbt het uitfaseren van de sloppy features.

offtopic:
O, wacht ze noemen het Drizzle

[cariolive23]

offtopic:
O, wacht ze noemen het Drizzle

offtopic:
Van de Drizzle in de rain

[dj_vibri]

Dus jullie raden dan toch aan om deze setting te vergeten en gewoon te werken met concat() voor de gehele string te maken?

Om een beetje duidelijker te zijn ben ik aan het testen met een tabel 'users' en wens ik gebruik te maken van één stored procedure welke de mogelijke acties op deze tabel kan opvangen (insert, update, delete).

Momenteel heb ik volgende code (excuses voor het 'pipes' gebruik....)

code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `UserActions` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UserActions`(
  IN InAction VARCHAR(25),                -- Define action to perform: Add new user / Update existing User / Delete User
  IN UIDParam VARCHAR(50),
  IN FN VARCHAR(25),
  IN LN VARCHAR(25),
  IN AddressParam VARCHAR(255),
  IN Zip CHAR(4),
  IN City VARCHAR(50),
  IN Birth DATETIME,
  IN Email VARCHAR(100),
  IN Phone VARCHAR(10),
  IN PSWD CHAR(128),
  IN MailingList TINYINT(1))

  READS SQL DATA
    COMMENT 'Perform different actions on TblUsers'
BEGIN
Declare a text;
Declare b text;
  IF (InAction = 'New') THEN


    Set a := 'UID,FirstName,LastName,Address,ZipCode,City,Birth,Email,Phone,PSWD,MailingList';
    Set b := UIDParam||','||FN||','||LN||','||AddressParam||','||Zip||','||City||','||Birth||','||Email||','||Phone||','||PSWD||','||MailingList;

    Set @s := 'INSERT INTO TblUsers('||a||') VALUES ('||b||');';


    PREPARE stmt FROM @s;
    EXECUTE stmt;

    deallocate prepare stmt;
  END IF;

END $$

DELIMITER ;

Wanneer ik nu deze procedure wil starten en ik correcte waarden invul krijg ik een foutmelding... (alleen onduidelijk waar deze zich ergens situeert.... een fout in mijn sql syntax).

Als ik letterlijk 'insert into .......' met de waarden die ik als parameters meegeef invul lukt dit wel.....

Bij deze dus een second vraagje: wat is er mis met bovenstaande code? Hij plaatst toch (volgens mij) bij 'Set b' overal een , (komma ) tussen de verschillende parameters? net zoals ik zou typen: Values('abc','def','ghi') .....

[cariolive23]

Dus jullie raden dan toch aan om deze setting te vergeten en gewoon te werken met concat() voor de gehele string te maken?

Ja, ik zou niet afhankelijk willen zijn van de configuratiesettings, zeker niet wanneer er een bruikbaar alternatief is.

[dj_vibri]

En eventjes een update met de oplossing die ik gevonden heb... (of anders gezegd: de correcte werkwijze voor geparameteriseerde stored procedures...)

code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `TestProcedure` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `TestProcedure`(
   IN InAction VARCHAR(25),                -- Define action to perform: Add new user / Update existing User / Delete User
   IN InNaam VARCHAR(45),
   IN InVoornaam VARCHAR(45))

      COMMENT 'Perform different actions on tblTest'
BEGIN

  IF (InAction = 'New') THEN

  Set @s = 'insert into tbltestprocedure (Naam,Voornaam) values (?,?)';

    PREPARE stmt FROM @s;

    Set @a = InNaam;
    Set @b = InVoornaam;

    EXECUTE stmt USING @a,@b;

    deallocate prepare stmt;
  END IF;

END $$

DELIMITER ;