0

Update: [Background info: I'm working on a Silverlight app which uses PHP code to interact with the MySql dbase. I have a regular installation package on www.mobilesportscoach.com (demo site on www.mobilesportscoach.com/test) which works well when you run the SQL file in phpMyAdmin. But I want the whole package to be installable in Joomla without having people to run several scripts in several places.]

I'm creating an joomla components xml install file and in the install section I want it to create my tables / stored procedures with an sql script so I've tried:

    <install> 
     <sql> 
      <file charset="utf8" driver="mysql">install.sql</file> 
     </sql> 
     <install>

Tables aren't the problem, single line sql code runs fine but in my SQL create procedure statements I have to change the delimiter (that's how it works in MySQL), e.g:

    DELIMITER $$
    CREATE PROCEDURE `tp_delete_match_and_scores_onmatchid`(IN matchid VARCHAR(10))
    BEGIN
     DELETE FROM tp_scores WHERE (tp_scores.matchid=matchid);
     DELETE FROM tp_matches WHERE (tp_matches.recid=matchid);
    END$$
    DELIMITER ;

The extension installer gives me:

   JInstaller: :Install: Error SQL DB function failed with error number 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 $$

I've also tried the custom installer api but I can't get it to work: I have an installfile tag in my xml: install.component.php

My php:

 <?php
function com_install(){
global $database;

 echo  "TEST COM_INSTALL";

 //get database
 if(defined('_JEXEC')){
  //joomla 1.5
  $database = JFactory::getDBO();
 }   

 //table for item index
  $query = "CREATE PROCEDURE `tp_delete_match_and_scores_onmatchid`(IN matchid VARCHAR(10)"; 
  $query .= "\nBEGIN"; 
  $query .= "\nDELETE FROM tp_scores WHERE (tp_scores.matchid=matchid);"; 
  $query .= "\nDELETE FROM tp_matches WHERE (tp_matches.recid=matchid);";
  $query .= "\nEND"; 

  $database->setQuery($query);
  $database->query();

  ?>

The component installs alright but I can't find my procedure with phpMyAdmin / is not created. Seems like the install.component.php isn't fired at all?

I don't want users to run a separate sql script, I want it all in one package if possible...

Francisco
  • 10,918
  • 6
  • 34
  • 45
Mike Dole
  • 675
  • 2
  • 14
  • 30
  • were you able to check if the username your using is allowed to perform the creation of stored proc? – Christopher Pelayo Nov 12 '11 at 13:36
  • Joomla connects as root (localhost test dbase) with all privileges – Mike Dole Nov 12 '11 at 14:52
  • Any reason you are using a stored proc in the first place? http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-mode – Brent Friar Nov 13 '11 at 16:27
  • Sorry for the delay Brent, I'm using SP's because my Silverlight App talks to the MySQL dbase with SP's you can see the regular install / app on www.mobilesportscoach.com/test & www.mobilesportscoach.com Mike – Mike Dole Nov 16 '11 at 12:38

2 Answers2

1

Have the same question and found this about the manifest XML files:

http://docs.joomla.org/Manifest_files#Script_file

There's a section "Script file" describing what you need. You have to create a special class in your custom installer script.

xamdam
  • 11
  • 1
1

One common thing is to change keyword 'TYPE' to 'ENGINE' (for MySQL 5.5.x) but sometimes it still doesn't work...

Make sure your install.sql file is formatted/codded in UTF-8 WITHOUT BOM!!

inter
  • 11
  • 1