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" <vo...@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 > vo...@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.katt...@gmail.com> wrote: >>On Wed, Jul 27, 2011 at 12:47 PM, Robert Vogel <vo...@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@lists.wikimedia.org > https://lists.wikimedia.org/mailman/listinfo/wikitech-l _______________________________________________ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
