create MySQL Function with button

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Da_Elf
Posts: 311
Joined: Sun Apr 27, 2014 2:45 am

create MySQL Function with button

Post by Da_Elf » Sat Dec 12, 2020 12:16 am

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
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: create MySQL Function with button

Post by Mark » Tue Dec 22, 2020 12:25 am

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.
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

Da_Elf
Posts: 311
Joined: Sun Apr 27, 2014 2:45 am

Re: create MySQL Function with button

Post by Da_Elf » Tue Dec 22, 2020 1:49 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9801
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: create MySQL Function with button

Post by FourthWorld » Tue Dec 22, 2020 4:42 pm

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

But then there's also "desirable": is this MySQL instance running locally or remotely?
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: create MySQL Function with button

Post by Mark » Tue Dec 22, 2020 8:41 pm

@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.
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: create MySQL Function with button

Post by AxWald » Wed Dec 23, 2020 9:59 am

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!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

Post Reply

Return to “Databases”