CiviCRM Community Forums (archive)

the menu rebuild worked for menu, but if you're curious the actual SQL (from show create ... ) is:

'CREATE DEFINER=`<myciviusername`@`<MySQLServer>` FUNCTION `civicrm_strip_non_numeric`(input VARCHAR(255) CHARACTER SET utf8) RETURNS varchar(255) CHARSET utf8
    NO SQL
    DETERMINISTIC
BEGIN
      DECLARE output   VARCHAR(255) CHARACTER SET utf8 DEFAULT '''';
      DECLARE iterator INT          DEFAULT 1;
      WHILE iterator < (LENGTH(input) + 1) DO
        IF SUBSTRING(input, iterator, 1) IN (''0'', ''1'', ''2'', ''3'', ''4'', ''5'', ''6'', ''7'', ''8'', ''9'') THEN
          SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
        END IF;
        SET iterator = iterator + 1;
      END WHILE;
      RETURN output;
    END'

Yes. We don't do that in CiviCRM core because we are using the old php mysql_connect which doesn't support changing delimiters and ignores the semicolons, but if you are entering this query manually you'll have to rewrite it slightly to do as Joe suggests.

DROP FUNCTION IF EXISTS INSERT_CORRESPONDENCE_ACTIVITY;
DELIMITER //
CREATE FUNCTION INSERT_CORRESPONDENCE_ACTIVITY (
source_contactid INT(10),
activity_type_id INT(10),
status_id INT(10),
file VARCHAR(255),
date_in VARCHAR(255),
date_out VARCHAR(255),
origin VARCHAR(255),
staff_initials VARCHAR(255),
response VARCHAR(255),
archived_in VARCHAR(255),
comments TEXT,
linked_person_id INT(10),
linked_person_2_id INT(10),
issue VARCHAR(255),
position VARCHAR(255),
their_file VARCHAR(255),
file_location VARCHAR(255),
target_contact_id INT(10)) RETURNS INT(10) MODIFIES SQL DATA
BEGIN
  INSERT INTO civicrm_activity (activity_type_id, subject, activity_date_time, status_id, priority_id, medium_id )
    VALUES (activity_type_id, CONCAT('NDP Vote Correspondence ', issue), date_in, status_id, priority_id, medium_id);
  SET @activity_id = LAST_INSERT_ID();
  IF source_contactid IS NOT NULL THEN
    INSERT INTO civicrm_activity_assignment(activity_id, assignee_contact_id) VALUES ( @activity_id, source_contactid);
  END IF;
  INSERT INTO civicrm_value_issue_5 (entity_id, position_56, issue_15, file_18, linked_person_22, linked_person_2_23, their_file__24, archived_in_25, option_34, response_35, file_location_38 ) VALUES (@activity_id, position, issue, file, linked_person_id, linked_person_2_id, their_file, archived_in, origin, response, file_location);
  INSERT INTO civicrm_activity_target (activity_id, target_contact_id) VALUES (@activity_id, target_contact_id);
  RETURN @activity_id;
END//
delimiter ;

How does one access this built in feature?  Is it available in 4.6.6?  I'm running with joomla 2.5.24. I managed to delete my 'civicrm_strip_non_numeric'.  I've been reading this forum to figure out how to restore it but mysql doesn't like the syntax of the SQL statements posted (I overrode delimiters, but still didn't work) and the path for the php file doesn't work on my setup for some reason.  Any thoughts on what to try next would be greatly appreciated!

Thanks for responding.  This is not working in my version, for what it's worth.  I'm using 4.4.6 upgraded from 4.3.5.
I'm confident it was there after the upgrade because I didn't receive errors on my pages when we tested.   (Then, when I dumped the test database and refreshed with my backup, I lost the function and things stopped working.)

I managed to get something working by adding the following to my administrator/components/com_civicrm/admin.civicrm.php at bottom of civicrm_initialize():
CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::DROP_STRIP_FUNCTION_43);
CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::CREATE_STRIP_FUNCTION_43);
CRM_Core_DAO::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");
Logging into the admin console, going into civicrm to make sure it initialized, then logging out and removing the code.

This worked to at least create a function definition in my database, but I think it might just be a shell with no code.... under myphpadmin, there's no edit link for it.  Is it supposed to modify the value that gets written to the database or is it just used for processing?   

I'm just wondering if something about the syntax doesn't work with some versions of MySQL; I'm on 5.5.  I saw at least another person got similar syntax errors as I with running the query in MyPHPAdmin -- I was getting bizarre syntax errors at the quote marks, tried different editors, typing directly into the screen, no quotes where they wouldn't be necessary, etc. gave up.  Tried with and without delimiter replacement.)  I also tried creating it within my database manually through the myphpadmin routines screen, but was unsuccessful -- the error was that <myuser>@localhost didn't have permissions, even though the <myuser> is setup on the database with full permissions.

It's a sql permission issue, due to the fact that your server is running an outdated version of mySQL. You could solve it by upgrading mySQL or else give your user SUPER privileges.

Unfortunately I cant be SUPERUSER and I cant disable logging.

How may I rebuild database so it can save the phone number?

Thanks!

I will definitely consider it.

But for now I need to find solution that will fix this bug. anyone?