create MySQL Function with button

[Da_Elf]

trying to setup my database with a click of a button including adding a function to mysql but nothing seems to happen
I tried this with two types of executes but neither worked.
I would also like to be able to add procedures and triggers to the database automatically as well. Im guessing they would need to be done the same way

Code: Select all

on mouseUp
put "USE `thisDbase`;" & cr into theSQL
put "DROP function IF EXISTS `thisFunction`" & cr after theSQL
put "DELIMITER $$" & cr after theSQL
put "USE `thisDbase` $$" & cr after theSQL
put "CREATE FUNCTION `thisFunction` (input1 int(1))" & cr after theSQL
put "RETURNS INTEGER" & cr after theSQL
put "BEGIN" & cr after theSQL
put "RETURN input1;" & cr after theSQL
put "END$$" & cr after theSQL
put "DELIMITER;" & cr after theSQL
revdb_execute(gConnectionIF,theSQL)
//revExecuteSQL gConnectionID,theSQL
End mouseUp

[Mark]

You need to execute all commands one by one in separate calls to the database.
If you need multiple statements to create your function, it means you can't create that function.
You should do the database management stuff using a database management tool. LiveCode can be a front end to your database but not a DBM tool.

[Da_Elf]

thanks was hoping i could do the entire database setup from within the program created with livecode. instead im resorting to running a saved SQL when im setting up.

[FourthWorld]

You may be able to. "Should" is different from "possible".

But then there's also "desirable": is this MySQL instance running locally or remotely?

[Mark]

@da_elf Perhaps you could try to run your saved SQL from the command line using LiveCode's shell() function. That may be a workaround for the limits of the built-in database functions.

[AxWald]

Hi,

you CAN execute more sophisticated SQL statements from LC. Examples copied from 1 of my programs:

Create a trigger (that keeps track when last insert occurred on a table ) :

Code: Select all

CREATE TRIGGER r_t_tree After insert on t_tree 
Begin 
Replace into t_tablestats (TName, LUPdate) VALUES('t_tree', strftime('%s','now'));
End

Create a view (that returns all used artNums that may be in 2 fields ...):

Code: Select all

CREATE VIEW `v_artnums` AS 
SELECT CASE WHEN nummer = 'DEL' 
THEN CAST(oldnum AS unsigned) 
ELSE CAST(nummer AS unsigned) 
END AS num 
FROM `t_artnames` GROUP BY num 
HAVING num > 0 ORDER BY num

These statements each are stored in my program, and are executed during installation/ update, when a new SQLite cache db is created/ updated. Each is send via one "revdb_execute". This works flawlessly.

Have you tried your resulting "theSQL" in your SQL manager? I couldn't get it to run in both mySQL and SQLite, w/o further research.

Hint: "USE `thisDbase`;" - Should be redundant - if you have a ConnID, you're already connected to thisDbase!

Have fun!