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)