In Search of the Elusive Middleware Example

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

lrieber
Posts: 37
Joined: Sat Sep 24, 2011 6:30 pm
Location: Athens, Georgia USA
Contact:

In Search of the Elusive Middleware Example

Post by lrieber » Sat Mar 14, 2015 2:43 pm

Actually, the long title of this post is: "In search of a simple example of the elusive PHP middleware example that will allow one to connect LiveCode to a mySQL database that does not allow direct connections"

Almost three years ago I submitted a request for help in figuring how to connect LiveCode to a mySQL database that I use through a commercial web hosting company. I was following this tutorial:

http://lessons.runrev.com/m/4071/l/7003 ... l-database

I submitted my request when I hit my brick wall; here's a link to the thread:

http://forums.livecode.com/viewtopic.ph ... 45&p=57525

I learned from the responses that LiveCode requires a direct connection to the database, but unfortunately this poses a severe security risk, such that virtually all web hosting companies do not allow it. I was assured that it was still possible to do it, but I would need to create what others have referred to as "middleware" - some code written in PHP, for example, that would live on the server with the database that LiveCode could first connect to.

I know a little PHP, but not much. Every now and then, I come back to this problem and spend time trying to figure it out. But, I never get very far. I've done searches in this forum and on google and have found others who seem to be in the same boat - and the same advice is given:

http://forums.livecode.com/viewtopic.ph ... re#p116334

http://forums.livecode.com/viewtopic.ph ... re#p100711

http://forums.livecode.com/viewtopic.ph ... are#p91789

In the last thread, I think "twseid58" expressed my feelings well:
"I searched the forum for 'middleware' and couldn't find much information about how others deal with this. Surely this is something developers work with if they create and deploy a stand-alone LiveCode application that makes use of a MySQL database."

Yes, surely this is something that loads of people have figured out.

My request here is for someone to provide a simple, concrete example of the PHP middleware file and also to show a simple example of how to write the needed scripts in LiveCode to connect to the mySQL database via the middleware file. (The request is really targeted to Kevin Miller and the good folks at LiveCode to create an "official" tutorial on this.)

My motivation for sending this semi-emotional posting is that I've been teaching LiveCode here at the University of Georgia and I now have students who want to use it to connect to their mySQL databases.

And, to be clear, I'm loving every minute I spend with LiveCode. It's just now time for me (and others) to take their LiveCode to the next level to connect to full-fledged databases on the web. (And who are already paying a good chunk of money each month to one - or more - web hosting companies.)

Thanks!

Lloyd Rieber

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7441
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: In Search of the Elusive Middleware Example

Post by FourthWorld » Sat Mar 14, 2015 3:48 pm

Hello LLoyd, good to see you here.

The "middleware" can be any program that can accept HTTP requests, communicate with the DB server program, and then return results from the DB back to the client over HTTP.

PHP is a common choice for that, and Python is growing in popularity, but LiveCode Server works well for that too.

You could conceptualize it like this:

Desktop:
LiveCode <-> DB externals <-> MySQL

Client-Server:
Client (could be a Web browser, could be LiveCode) <- HTTP over the Internet via GET or POST-> LiveCode Server <-> DB Externals <-> MySQL

If you use PHP, Python, or other middleware, it would take the place of LiveCode Server in that second setup. But since you seem to prefer LiveCode (good choice, IMO <g>), I think you'll find it enjoyable to use once you set it up. In broad terms, LiveCode Server can be used in most workflows wherever you might consider PHP.
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 326
Joined: Tue Apr 10, 2012 9:18 am

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Sat Mar 14, 2015 5:29 pm

I am also interested in this topic, and have had the same experience as Lloyd: I am pointed in the direction of using Livecode Server as the intermediary between a standalone app and a MySQL server, but have not found any clear examples of how to do that. Basically the posts I read amount to:

Yeah, just use Livecode Server as the middle-man. You will simply send your queries prjhkjhreh ehherekr hdhfdhfyouaretoodumbto understandthiskjasdjfdkj

Also, similar to Lloyd, I am really enjoying learning to use Livecode, so I am not ranting, just curious to see examples.
I do have Livecode server running on the same VPS that has MySQL, so I did manage to get that far.


Currently, my funky solution is an external script that launches an SSH tunnel. This script is launched from Livecode (I have it on a button at the moment), and there is another script that closes the SSH tunnel when I am done.
Once the SSH tunnel is running, I can simply connect to the database as if it was running directly on that computer.

This solution works fine on Ubuntu and OS X. I have SSH keys set up on the machines that are connection, so no passwords need to be sent.

I am not sure if this solution would work on Windows, but in college we used Putty to make SSH tunnels, so maybe the Windows script would need to have a Putty executable next to it. I am pretty sure you can send arguments to Putty from a cmd line.


At any rate: in summary, I would also like to see some example stacks that send queries to Livecode Server that send back the MySQL data.
at the moment, using SSH tunnel is working fine, and for my use-cases is an acceptable work-around, since the apps I am creating are custom made for small businesses who will be using them to access their own servers.
This would not be a good solution for a widely distributed app, since it would be like handing out access to your server to random people.

Lloyd, if you would like to see an example of what I have, I will gladly post it.

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7441
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: In Search of the Elusive Middleware Example

Post by FourthWorld » Sat Mar 14, 2015 6:16 pm

Do you have LiveCode Server setup and running a simple test script successfully?
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

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

Re: In Search of the Elusive Middleware Example

Post by AxWald » Sat Mar 14, 2015 6:21 pm

Hi,

I'll not be able to help you much with the dreaded middleware - I'm no PhP coder, and use a helper to do the server side code. But I may be able to give some hints. Let's try.

At first, Richard already gave a lot of good advice! If I'd have to do it all for myself, I'd use LC server, or, preferably for me, a hand-tailored LC standalone on the server.
Unfortunately many "commercial web hosting companies" don't allow/ support this. Like one of those I have to work with. So I have to use PhP - and thanks heaven, I have one that does that job :)

Instead of using the classic http GET/ POST approach I use sockets (only because I'm not firm in this GET/ POST stuff ...). They're fast, reliable and I'm working in heavy production with it since I took the project over, more than 1 year ago.
It's not exactly what you need, but I can offer some code snippets that may be helpful.

A basic PhP function to work with sockets, stolen from IDontKnowWhere:

Code: Select all

    <?php

    /*
    * PHP Sockets - How to create a TCP/IP socket client
    */

    echo "<h2>TCP/IP Connection</h2><br />";

    /* Get the port for the WWW service. */
    $service_port = '50535';//getservbyname('www', 'tcp');

    /* Get the IP address for the target host. */
    $address = gethostbyname('my.server.dyndns.org');

    /* Create a TCP/IP socket. */
    $socket = socket_create(AF_INET, SOCK_STREAM, SOL_TCP);
    if ($socket === false) {
        echo "socket_create() failed: reason: " . socket_strerror(socket_last_error()) . "<br />";
    } else {
        echo "OK.<br />";
    }

    echo "Attempting to connect to '$address' on port '$service_port'...";
    $result = socket_connect($socket, $address, $service_port);
    if ($result === false) {
        echo "socket_connect() failed.<br />Reason: ($result) " . socket_strerror(socket_last_error($socket)) . "<br />";
    } else {
        echo "OK.<br />";
    }

    $in = "Hello, Server!\r";
    $out = '';

    echo "Sending HTTP HEAD request...";
    socket_write($socket, $in, strlen($in));
    echo "OK.<br />";

    echo "Reading response:<br /><br />";
    while ($out = socket_read($socket, 2048)) {
        echo $out."<br /><br />";
    }

    echo "Closing socket...";
    socket_close($socket);
    echo "OK.<br /><br />";
    ?>
(This may be demo code, never tested!)

Now some real PhP code, stolen from my production server (slightly altered & commented):

Code: Select all

    // user hovers the mouse above a link on a website, this is called:
    // [...]
    $codice = $_REQUEST["html"];
    $lenCodice = strlen($codice) + 1 + strlen($_REQUEST["priceTag"]);
    // setting up some variables ...

    if (!($client = fsockopen("tcp://my.server.dyndns.org", 50535, $errno, $errorMessage, 15))) die("Could not connect to host. err. n:".$errno." - ".$errorMessage);
    if (!stream_set_timeout($client, 1)) die("Could not set timeout");
    if (!stream_set_blocking($client, 1)) die("Could not set stream_set_blocking");
    fread($client,26);
    $msg = "ART,$lenCodice"."\r\n"."$codice,".$_REQUEST["priceTag"]."\r\n";
    // preparing a multiline message that should look like:
    //   ART,8         -- a command (ART) & the length of the next line
    //   08067,13       -- an product number & a pricelist number

    fwrite($client,$msg);
    // sending the request to the LC socket server, waiting for reply
    $ret = fread($client,2048);
    // socket server replies:
    //   ART OK,23      -- again a code first(ART OK), then the length
    //   3,10.47,550,6,1,Fl,Krt   -- information about the product (availability, price, ID for web-database, other stuff)

    $arr = explode(chr(10),$ret);
    $arr = explode(",",$arr[1]);
    if(count($arr)<3)
    {
    // a popup is created at the mouseLoc, containing customer specific product information
    // [...]
    }

    fclose($client);
    // [...]
(This is real code, but I may have shot it when editing ...)

The web server here uses PhP to query a LC standalone ("LC socket server") running as a service (=faceless) for real time, customer specific article data to display them in a popup on a website, on mouseover.

I assume you'd like it the other way 'round, and you'd like to send parameters to the web server to create a SQL statement that's than sent to your MySQL. It shouldn't be this hard to change the direction, right?

I'd never send full SQL statements this way for execution. Too easy to fool your PhP into something that's not desired. That's why here's this structure used:

Code: Select all

[CmdName], [lenght(whatFollows)]
[All, my, data
that, I want, processed]
CmdName should correlate to a specific SQL string stored in a MySQL table that your PhP fetches, and should maybe resolve to:
ART = "INSERT INTO 't_Articles' VALUES "
and "whatFollows" would be the values, nicely formatted. Maybe you need to add brackets or an ";" at the end ;-)

I hope you get the idea. Think the PhP part wouldn't be this hard to create, basically nothing more is used but:
  1. Waiting for a socket connection,
  2. checking incoming connections if "sober",
  3. if yes, say "Hi".
  4. Getting the proper SQL string according to the "CmdName" sent,
  5. adding the "whatFollows" part and do some formatting,
  6. and send the ready SQL command to MySQL.
  7. Maybe return a result, same way.
There may be PhP code out there that already that does this ...


Now for the LC part:

One of these days (weeks, months, years, decades ...) I might find the time to clean up the current code of the socket server. Too bad there's still a lot of hard-coded data in it that I must not reveal. And you know how it is, once the baby is able to walk by itself you get thrown two others ...

But maybe this helps a bit:
A post of me regarding this socket server, with code
Or this:
A thread made by JGonz, the initiator of the project, from whom I took over, with even more code


Hope I was able to give you something to think of, and maybe to provide an idea or three. ;-)

Should the code of the socket server (above what's contained in the linked posts) be of interest, tell me. It's still ugly and full of dirty tricks, but it's GPL V3. If someone wants it in the current state, I should be able to deliver.

Have a good time!

PS: I want to emphasize that:
  1. I use sockets just by personal preference. GET/POST may work as well.
  2. I don't use LC on the web server only because the provider doesn't allow.
  3. One of these days I'll learn how to smuggle an LC StandAlone into a LAMP - I hope!
Livecode programming until the cat hits the fan ...

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 326
Joined: Tue Apr 10, 2012 9:18 am

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Sat Mar 14, 2015 7:05 pm

FourthWorld wrote:Do you have LiveCode Server setup and running a simple test script successfully?

I do. A $5 per month droplet on DigitalOcean running Ubuntu 14.04. Was really quite easy to set up Livecode Server on a VPS...especially after muddling around with it on various web hosting plans with no success: https://firefli.de/?quote=says%20hello

What about you Lloyd?

sefrojones
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 447
Joined: Mon Jan 23, 2012 12:46 pm

Re: In Search of the Elusive Middleware Example

Post by sefrojones » Sat Mar 14, 2015 7:18 pm

Here is a VERY simple example of using a LiveCode server script to insert information into a database. This script assumes a table named "sampledata" with three columns: name,age, and email. Once you have created a MySQL database, and added a table named "sampledata" with those collumns, save this script as "middleware.lc" . Then add your database information where needed in the script, and upload it to a web accessible location on your server that has LiveCode server installed.

Code: Select all

<?lc
put $_POST["name"] into tName
put $_POST["age"] into tAge
put $_POST["email"] into tEmail
 put revOpenDatabase("mysql","yourhost.com","yourdatabasename","yourdatabaseuser","youdatabasepassword") into gConnectionID
if gConnectionID is a number then
	put "name, age, email" into tfields
	put "sampledata" into tTableName
	put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3)" into tSQL
   revExecuteSQL gConnectionID, tSQL, "tName","tAge","tEmail"
if the result is a number then
        put "Successfully added a record!"
        revclosedatabase gConnectionID
    else
        put the result
    end if
else
put the result
end if
?>
Then from your application, you can enter data into the database like this:

Code: Select all

on mouseUp
   put "SampleUser" into tName
   put "99" into tAge
   put "sampleuser@usertown.usa" into tEmail
  put "name=" & tName & "&age=" & tAge & "&email=" & tEmail into tStuffToPost
  post tStuffToPost to URL "http://yourhost.com/middlware.lc"
  put it into tResponse
  answer tResponse
end mouseUp
This is a very simple example, and I'm sure there are other considerations, but I just tested this and it works without having to go out to another language or hard coding your sensitive info into the application itself.

Hope this helps you on your way!

--Sefro

edit: added "revclosedatabase gConnectionID" to server script

edit 2: edited out incorrect urlencode lines

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 326
Joined: Tue Apr 10, 2012 9:18 am

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Sun Mar 15, 2015 12:09 am

Thank you for posting that Sefro. That is helpful.

It is not working for me, but I see what you are doing.

I beleive it is working fine on the Server page, since if I refresh the page it adds a blank record to my database, so all good there.

if I run your code from my desktop app, nothing seems to happen. The response pops up, but is blank.

so I am guessing it is failing on: post tStuffToPost to URL "https://firefli.de/middlware.lc"


****experimented around and I found that NOT using SSL seems to let it go through, although now I am getting an error:

row 3, col 1: put: error in expression


**interesting...if I wait about 10 minutes and hit send again, it works.

sefrojones
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 447
Joined: Mon Jan 23, 2012 12:46 pm

Re: In Search of the Elusive Middleware Example

Post by sefrojones » Sun Mar 15, 2015 12:42 am

No problem at all. Is it working consistently for you now? As I said, it's a pretty simple script, it probably has some errors. I am not a pro, but I thought this would show that it is possible for LiveCode server to interact with a database without hard coding the database credentials into your app or going out to PHP to handle it. 8)

--sefro

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 326
Joined: Tue Apr 10, 2012 9:18 am

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Sun Mar 15, 2015 12:43 am

I get the basic idea.

Any examples of how to retrieve data?

sefrojones
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 447
Joined: Mon Jan 23, 2012 12:46 pm

Re: In Search of the Elusive Middleware Example

Post by sefrojones » Sun Mar 15, 2015 1:28 am

So again, these are very simple examples, but should get you on your way:

Server script name this simpleretrieve.lc :

Code: Select all

<?lc

put revOpenDatabase("mysql","yourDBhost","yourDBname","yourDBusername","yourDBpassword") into gConnectionID
if gConnectionID is a number then
put "SELECT * FROM " & "sampledata" into tSQL
    put revDataFromQuery(tab, cr, gConnectionID, tSQL) into TableData
repeat with x=1 to the number of lines in TableData
put line x of TableData & comma after OutputData
end repeat
repeat with x=1 to the number of lines in tOutputData
repeat with y= 1 to the number of words in line x of tOutputData
put tab after item y of line x of touputdata
end repeat
end repeat
put toutputdata
else
put "error"
end if


?>

application script:

Code: Select all

on mouseUp
   put url "yourDBhost/simpleretrieve.lc" into tData
   set the itemdelimiter to comma
   repeat with x = 1 to the number of items in tData
      replace space with tab in tData
      put item x of tData into line x of  tOutputData
   end repeat
   put tOutputData
   
end mouseUp
This is just an example to show it is possible, there are probably better ways to parse the data, etc. But this shows us that it is possible. :D

--sefro

edit: slightly edited server script to spit out tab delimited data, which can then be put into a simple table field

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 326
Joined: Tue Apr 10, 2012 9:18 am

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Sun Mar 15, 2015 10:24 pm

I have been playing around with $_post to send data to a Livecode server from a desktop app (following the examples in this thread) and am having a strange issue:

Send post data, everything works
Try again..might work/ or might get an error
Try again, I get an error:

<pre>
file "/path/to/middleware.lc"
row 3, col 1: put: error in expression
</pre>

if I wait a while, it will work again eventually. I have been wracking my brain trying to figure out what is going on. Does anyone have a clue why it works sometimes and then fails, and then works again after a while?

**

the connection to the database works fine and is not timing out.I can fill the variables with some dummy data and it will work every time
the problem seems to be that the info getting sent to the server is accepted the first time, but not immediatly after that...but then it will 10 minutes or so later.
Attachments
server_database.livecode.zip
(1.54 KiB) Downloaded 98 times

sefrojones
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 447
Joined: Mon Jan 23, 2012 12:46 pm

Re: In Search of the Elusive Middleware Example

Post by sefrojones » Sun Mar 15, 2015 11:28 pm

I'm not really sure, but you could try adding a 4th auto increment column named ID to the table. I haven't played around with LiveCode server too much, nor am I a MySQL expert, but I think that might help.


--Sefro

edit: I also think that this example might throw errors if the table contains blank entries

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 326
Joined: Tue Apr 10, 2012 9:18 am

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Mon Mar 16, 2015 2:26 am

I down-graded my version of Livecode Server from 7.03 to 6.7 Community Edition and now everything works fine.

I also tried version 8, but had the same results (post not working after second send)

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 326
Joined: Tue Apr 10, 2012 9:18 am

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Mon Mar 16, 2015 5:26 pm

First off, Thank you Sefro for the great examples. With a bit if dinkering I was able to make a working database CRUD that uses Livecode Server as an intermediary to MySQL.
I had some issues, like having to down-grade my server version to 6.7, and I simplified the query to go into a datagrid, but once I worked things out I can see the advantages to using Livecode Server as the middle-man:

no need to configure each computer the app willl run on
no need to worry about database drivers
if you need to update the db connection, you can do it on the server rather than updating the application

I have attached the livecode stack, with files that will go on the server, and a simple sql dump with some example files.


This has been a very helpful discussion.


My next question, which I will ask on a different thread, is how to secure the files on the server side? Should we send a password from the application? Anyway, at least I am on the right track now.
Attachments
server_example.zip
example stack with server files that act as intermediary to mysql database
(9.82 KiB) Downloaded 124 times

Post Reply

Return to “Databases”