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@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@gmail.com wrote:
On Wed, Jul 27, 2011 at 12:47 PM, Robert Vogel vogel@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:
- 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)
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@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@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@gmail.com
wrote:
On Wed, Jul 27, 2011 at 12:47 PM, Robert Vogel vogel@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:
- 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@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
wikitech-l@lists.wikimedia.org