Jak vytvorit stored proceduru za behu pres Nette\Database

blueticket

Zdravim,

narazil jsem na trochu zapeklitej problem. Potreboval bych vytvorit stored proceduru za behu:

$sql = "DELIMITER $$

DROP PROCEDURE IF EXISTS `recalculate_warehouse` $$
CREATE PROCEDURE `recalculate_warehouse`()
BEGIN
    /* BEGIN CREATE BOM */
    DROP TABLE IF EXISTS `sma_bom_temp`;
	.
	.
	.

END$$

DELIMITER ;";

kdyz to vsak takhle predam do $this->database->query($sql); tak dostanu hlasku

„Syntax error or access violation: 1064 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 ‚DELIMITER $$ DROP PROCEDURE IF EXISTS recalculate_warehouse $$ CREATE PROCEDU‘ at line 1“

Nepasoval se s tim uz nekdo z vas? Nevite poradit nejakou zazracnou formulku? :)

Dekuji moc predem

David Matějka

myslim, ze nemusis resit ten delimiter, ale musis to tam poslat jako samotne dotazy, tedy

$connection->query('DROP PROCEDURE IF EXISTS `recalculate_warehouse');

$connection->query('CREATE PROCEDURE `recalculate_warehouse`()
BEGIN
    /* BEGIN CREATE BOM */
    DROP TABLE IF EXISTS `sma_bom_temp`;
    .
    .
    .

END');

blueticket

No, one se to pak pokousi spoustet kazdej radek v tele stored procedury samostatne. Proto je tam vlastne i ten set delimiter.

David Matějka

a zkusil si to? mne to totiz funguje.

blueticket

Jo, promin. Ja Te spatne pochopil a spatne jsem to precetl. Ja psal i jednotlive radky procedury do samostatneho $db->query(). Nevsiml jsem si, ze cely CREATE PROCEDURE davas do jedineho $db->query. Parada, vse funguje jak ma. Dekuji moc!