Connecting to a MySQL database on a Server

Are you using LiveCode to create server scripts or CGIs?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
trags3
Posts: 363
Joined: Wed Apr 09, 2014 1:58 am
Location: Las Vegas, NV

Connecting to a MySQL database on a Server

Post by trags3 » Mon Jul 07, 2014 11:27 pm

I just got a hosting account at HostM and they advertise the fact that they use LiveCode Server on their servers.
I am still struggling a bit trying to connect to a table with login credentials for a standalone mobile app. Before I signed up they offered me some advise on how to do what I want but without any real details. Someone who has been working with LiveCode for bore than 3 months and servers for more than a week might understand but I am at a complete loss.
They mentioned that I would need to write an API as I stand here with the "Deer in the headlights" look. They said they use LiteSpeed to handle the communications and recommended I use the https port.
Can anyone point me to a good step by step tutorial that explains the process here? API? I was also told that the API could be written in livecode.

Thanks for the help.
Tom

Simon
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am
Location: Palo Alto

Re: Connecting to a MySQL database on a Server

Post by Simon » Tue Jul 08, 2014 3:58 am

Hi Tom,
Take a look here:
http://forums.livecode.com/viewtopic.php?f=53&t=20956
Does that help?
Does your cpanel look the same?

Simon
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 928
Joined: Fri Aug 15, 2008 7:15 am

Re: Connecting to a MySQL database on a Server

Post by bangkok » Tue Jul 08, 2014 7:44 am

trags3 wrote:I just got a hosting account at HostM and they advertise the fact that they use LiveCode Server on their servers.
I am still struggling a bit trying to connect to a table with login credentials for a standalone mobile app. Before I signed up they offered me some advise on how to do what I want but without any real details. Someone who has been working with LiveCode for bore than 3 months and servers for more than a week might understand but I am at a complete loss.
They mentioned that I would need to write an API as I stand here with the "Deer in the headlights" look. They said they use LiteSpeed to handle the communications and recommended I use the https port.
Can anyone point me to a good step by step tutorial that explains the process here? API? I was also told that the API could be written in livecode.

Thanks for the help.
Tom

here is the scheme :

-on HostM , you have your MySQL server and LiveCode Server

-a script LC will act as a "layer" between any "outside" applications (mobile, desktop) and your MySQL database. This is what is called an "API".

-it will receive SQL queries (via a POST query), "sterilize" them (for security purpose, look on the web), pass them to the local MySQL DB, fetch the results, and "display" those results

-on the other hand, you have an app (made with LiveCode). It will do a POST to the remote LC script with the SQL queries, and receive the results from the remote server.

Why such a scheme ? Because it's very dangerous to leave "open" a MySQL server, allowing anyone on the internet to try to connect to it.

Quickly :

script for LiveCode Server

Code: Select all

<?lc 
put $_POST["myMessage"] into tMessage

#### start to sterilize [look on the web for more information]
replace ";" with "" in tMessage
if the length of tMessage>400 then put char 1 to 400 of tMessage into tMessage

#### we received something that looks legitimate, so we open a connexion to the DB
if tMessage is not empty then
put revOpenDatabase("mysql","127.0.0.1","nameofyourdatabase","yourlogin","yourpassword") into dbID

put revDataFromQuery(,,dbID,tMessage) into tResults

### we display the results of the query
put tResults
revCloseDatabase dbID
end if
?>

Now on your app side, in a button

Code: Select all

on mouseup
put "select * from mytable" into tSQL
 put "mymessage="&urlencode(tSQL) into tSQL
   post tSQL to url "http://yourserver.com/yourscript.lc"
answer it
end mouseup
Voilà !

After you can add security stuff : by encrypting queries, identification process, "breaking" queries (send from the app only "where" conditions, while you have hard coded in the LC script those queries (put "select * from mytable where "&tMessage into tMessage) etc.

Many, many possibilities and fun stuff. :)

MySQL + LiveCode Server + LiveCode apps = the killer app.

trags3
Posts: 363
Joined: Wed Apr 09, 2014 1:58 am
Location: Las Vegas, NV

Re: Connecting to a MySQL database on a Server

Post by trags3 » Tue Jul 08, 2014 2:21 pm

Simon and Bangkok,
I really appreciate the help.
Just a quick note regarding HostM hosting service and support.
I posted a help ticket in their Client Lounge and got back step by step instructions to include which icons to select in cPanel, where to put the api folder and file along with sample livecode scripts to incorporate the security.
While not being new to programming (i first started with Fortran on an IBM 360, punch cards etc in the late 60's) I did a lot of dBase II, III and IV in the 80's and 90's. Recently delved into microchip processor assy language and now LiveCode!! I am brand new to livecode and have never done anything with a server before.
These forums are great and I have received great support and have even as a newbie i've tried to help others where I could.
Just thought you would like to know about HostM when you run into people from MO (I'm from nearby OK) or absolute beginners regarding servers.
Here is a link.
http://www.hostm.com/
Thanks again,
Tom

Post Reply

Return to “CGIs and the Server”