I suspect the delimiter switch is not being handled by the code that splits
the file into statements, so the procedure definition gets split at the
first semicolon.
-- brion
On Jul 28, 2011 3:55 AM, "Robert Vogel" <vogel(a)hallowelt.biz> wrote:
Hi Roan,
thanks for the quick reply. This is (one of) the statement(s) we've got a
problem with.
------------------
DROP PROCEDURE IF EXISTS `insertfile_getFilePosition`;
DELIMITER $$
CREATE PROCEDURE `insertfile_getFilePosition`(filename VARCHAR(255))
BEGIN
SELECT tmp.rank FROM
(SELECT @row:=@row+1 rank, i.img_name
FROM /*$wgDBprefix*/image i, (SELECT @row:=0) r
WHERE (i.img_major_mime != 'image' AND i.img_minor_mime != 'tiff')
ORDER BY i.img_name ASC) tmp
WHERE tmp.img_name = filename;
END
$$
DELIMITER ;
------------------
If provided to the SQL Database directly, it works. But the update.php
throws a
syntax error.
In general, is it possible to provide stored
procedures this way?
Could there be a problem with the way the sql-file is read?
Greetings,
Robert Vogel
Social Web Technologien
Softwareentwicklung
Hallo Welt! - Medienwerkstatt GmbH
__________________________________
Untere Bachgasse 15
93047 Regensburg
Tel. +49 (0) 941 - 56 95 94 98
Fax +49 (0) 941 - 50 27 58 13
www.hallowelt.biz
vogel(a)hallowelt.biz
Sitz: Regensburg
Amtsgericht: Regensburg
Handelsregister: HRB 10467
E.USt.Nr.: DE 253050833
Geschäftsführer: Anja Ebersbach, Markus Glaser, Dr. Richard Heigl, Radovan
Kubani
On Wed, 27 Jul 2011 at 22:16 PM, Roan Kattouw <roan.kattouw(a)gmail.com>
wrote:
>On Wed, Jul 27, 2011 at 12:47 PM, Robert Vogel
<vogel(a)hallowelt.biz>
wrote:
>> Hello everybody!
>>
>> At my company we develop extensions for MediaWiki. We use the
"LoadExtensionSchemaUpdates" hook to create tables with the
"maintenance/update.php" script.
>> Recently we faced the question whether it is
possible to have stored
procedures/functions in an extensions SQL-File, or not. We
tried it out and
it didn't work for us. The update.php says we've got an error in the SQL
syntax, but there isn't one.
>>
>> Can anybody help us? Is it possible to provide stored procedures to the
database using the update.php? Is there an example anywhere? Thx.
>>
>The SQL syntax error message comes from the database engine, not from
MediaWiki.
So if it tells you there's an SQL syntax error, there's a syntax
error for sure. What you should look at:
>1. does the DB backend you connect to support the
syntax you're using?
>Infamously, MySQL 4.0 will reject anything containing subqueries as a
syntax
error, because subquery support wasn't introduced until 4.1 if memory
serves 2. is MediaWiki connecting to the DB that you think it's connecting
to? There might be a version-triggered error like #1 above, but you might
not notice if you're connecting to a different version than MediaWiki is 3.
are you using magic phrases like /*_*/, /*$wgDBTablePrefix*/, /*i*/ or
/*$wgDBTableOptions*/ ? MediaWiki substitutes these before sending the SQL
to the DB backend, so make sure you test your queries with these
substitutions applied
Roan Kattouw (Catrope)
_______________________________________________
Wikitech-l mailing list
Wikitech-l(a)lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l