How to use mysql function to insert data

439 Views Asked by At

I've created a mysql function:

DELIMITER $$
USE `local_sysDB`$$
CREATE FUNCTION  `addPost` (
    postSource CHAR(20),
    hashtagId INT,
    coordinatesLat FLOAT,
    coordinatesLon FLOAT,
    created INT(20) UNSIGNED,
    filterlevel VARCHAR(45),
    postId CHAR(20),
    postLanguage CHAR(11),
    profanity TINYINT(1),
    retweeted TINYINT(1),
    postText TEXT,
    truncated TINYINT(1),
    userId INT(10),
    username CHAR(15),
    userFullname CHAR(20),
    media VARCHAR(255),
    mediaType1 CHAR(5),
    media1 VARCHAR(255),
    mediaType2 CHAR(5),
    media2 VARCHAR(255),
    mediaType3 CHAR(5),
    media3 VARCHAR(255),
    mediaType4 CHAR(5),
    media4 VARCHAR(255),
    filter VARCHAR(45),
    urlToPost VARCHAR(225),
    channelTitle VARCHAR(225),
    channelId CHAR(30),
    category INT(11)
)
RETURNS TEXT
LANGUAGE SQL
DETERMINISTIC
BEGIN 
    IF(inHashtagCampaign(hashtagId,created,postSource)) THEN
        CASE postSource
            WHEN 'twitter' THEN
                INSERT INTO `posts_twitter` (
                    `hashtagId`,
                    `coordinates`,
                    `created`,
                    `filterlevel`,
                    `postId`,
                    `language`,
                    `profanity`,
                    `retweeted`,
                    `text`,
                    `truncated`,
                    `userId`,
                    `username`,
                    `userFullname`,
                    `media`,
                    `mediaType1`,
                    `media1`,
                    `mediaType2`,
                    `media2`,
                    `mediaType3`,
                    `media3`,
                    `mediaType4`,
                    `media4`
                )
                VALUES (
                    hashtagId,
                    POINT(coordinatesLat,coordinatesLon),
                    created,
                    filterlevel,
                    postId,
                    postLanguage,
                    profanity,
                    retweeted,
                    postText,
                    truncated,
                    userId,
                    username,
                    userFullname,
                    COALESCE(media1,media2,media3,media4),
                    mediaType1,
                    media1,
                    mediaType2,
                    media2,
                    mediaType3,
                    media3,
                    mediaType4,
                    media4
                )
                ON DUPLICATE KEY UPDATE `hashtagId` = `hashtagId`;
            ELSE
                RETURN FALSE;
        END CASE;
    END IF;
    RETURN returnVal;
END;$$

DELIMITER ;
SHOW WARNINGS;

I added the function to mysql and now I'm trying to call this function like this:

addPost(
    'twitter',
    11,
    NULL,
    NULL,
    NOW(),
    'none',
    'postId4t3443535',
    'en',
    0,
    0,
    'demo tekst blaat',
    0,
    34579345739254,
    'demo account',
    'demo_account',
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null
)

But I'm getting this error:

#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 'addPost( 'twitter', 11, NULL, NULL, NOW(), 'none', 'postI' at line 1

Is it possible to call a mysql function like that? The function inHashtagCampaign does exist, but I don't think it is relevant to this question so I didn't add the code of that function here.

0

There are 0 best solutions below