Mysql – Execute triggers stored procedures on SqlFiddle. Mysql

MySQLsqlfiddle

Does SQL-fiddle facilitate execution of triggers/stored procedures?

I have been unable to execute even the simplest form of stored procedure on sqlfiddle

DELIMITER $$
DROP PROCEDURE IF EXISTS myProc $$

CREATE PROCEDURE myProc()
BEGIN

END$$
DELIMITER ;

Sqlfiddle does not allow executing this(above) sql in build schema, but allows create table etc

Note: The same syntax is working for me on my localhost using wamp with mysql 5.5.24

Can anyone guide please?

Best Answer

Instead of using the delimiter option (which is not a real SQL statement, but rather only a command for the mysql command prompt) use the "Query Terminator" option on SQL Fiddle to establish your delimiter.

For example:

http://sqlfiddle.com/#!2/88fcf

Note the // dropdown below the schema box? That's the SQL Fiddle equivalent to the mysql DELIMITER command.

Longer example with queries in the stored procedure (note that within the stored procedure, ; is still used as a delimiter):

http://sqlfiddle.com/#!9/4db78

Full disclosure: I'm the author of SQL Fiddle.

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Mysql – How to output MySQL query results in CSV format

From Save MySQL query results into a text or CSV file:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.

Best Answer

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

Mysql – How to output MySQL query results in CSV format

Related Topic