Laravel migration raw sql error

Question

I want to use Levenshtein distance for search in Laravel. I found SQL function (saved as app/database/migrations/levenshtain.sql):

    DELIMITER //
    CREATE FUNCTION `LEVENSHTEIN`(s1 VARCHAR(255) CHARACTER SET utf8, s2 VARCHAR(255) CHARACTER SET utf8) RETURNS int(11)
        DETERMINISTIC
    BEGIN
        DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
        DECLARE s1_char CHAR CHARACTER SET utf8;
        -- max strlen=255 for this function
        DECLARE cv0, cv1 VARBINARY(256);

        SET s1_len = CHAR_LENGTH(s1),
            s2_len = CHAR_LENGTH(s2),
            cv1 = 0x00,
            j = 1,
            i = 1,
            c = 0;

        IF (s1 = s2) THEN
          RETURN (0);
        ELSEIF (s1_len = 0) THEN
          RETURN (s2_len);
        ELSEIF (s2_len = 0) THEN
          RETURN (s1_len);
        END IF;

        WHILE (j  c_temp) THEN
              SET c = c_temp;
            END IF;

            SET c_temp = ORD(SUBSTRING(cv1, j+1, 1)) + 1;
            IF (c > c_temp) THEN
              SET c = c_temp;
            END IF;

            SET cv0 = CONCAT(cv0, CHAR(c)),
                j = j + 1;
          END WHILE;

          SET cv1 = cv0,
              i = i + 1;
        END WHILE;

        RETURN (c);
      END//
    DELIMITER ;

And in migration:

    public function up () {
        DB::statement(file_get_contents(__DIR__.'/levenshtein.sql'));
    }
    public function down () {
        DB::statement('DROP FUNCTION `LEVENSHTEIN`;');
    }

I tried DB::statement, DB::unprepared and DB::raw, but Laravel migrations throw error:

    [Illuminate\Database\QueryException]
      SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in yo
      ur SQL syntax; check the manual that corresponds to your MySQL server version f
      or the right syntax to use near 'DELIMITER //
      CREATE FUNCTION `LEVENSHTEIN`(s1 VARCHAR(255) CHARACTER SET utf8, ' at line 1

It works when I execute it by HeidiSQL, what is the problem?

Community · Accepted Answer · 2017-05-23 12:31:01Z

4

I found solution at Creating MYSQL Procedure in Laravel 4 Migrations answered by @peterm.

Problem in DELIMITER // that is not a valid sql statement. It's just a MySql client command.

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Dec 12, 2014 at 11:14

Michael Febalist

591 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel migration raw sql error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related