In Search of the Elusive Middleware Example

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

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

Re: In Search of the Elusive Middleware Example

Post by lrieber » Thu Mar 19, 2015 9:40 pm

First, I want to thank everyone for their very thoughtful responses. I continue to be impressed with how supportive the LiveCode community is.

I've been carefully studying all the replies to my initial post and I've come to the conclusion that I had a misconception based on following this tutorial:
http://lessons.runrev.com/m/4071/l/7003 ... l-database

This tutorial's main parts are as follows:
  • -Connecting to the Database
    -Getting Data from the Database
    -Adding a New Record to the Database
    -Editing or Deleting an Existing Record
I thought the "middleware" PHP code I needed was only related to "Connecting to the Database." I thought that once that problem was solved, I could then continue using this tutorial to build my program. That is, I thought I could embed SQL statements into my standalone LiveCode app to insert, update, or delete data from the mySQL database on the server. Put another way, I thought the middleware code I needed was only to give me a workaround for using the revOpenDatabase command.

My understanding now is that the "middleware" PHP code residing on the server must do ALL of these CRUD* functions. Consequently, I see I need to program my LiveCode app to communicate with this PHP code through POST/GET statements (or sockets) to accomplish all of the CRUD tasks.

If my current understanding is on target, that is actually a big help because I now see at last how to approach the problem. (So folks, please do not be shy in correcting me if my understanding is still off target.) I've already played around successfully with "posting" data to my database from LiveCode via PHP. However, I'm still fuzzy on how to GET data from a SQL query into my standalone LiveCode app. So, any help or suggestions with that would be appreciated.

The other take-away I've learned is that there are key advantages if I go with LiveCode Server instead of PHP. I don't think LiveCode Server is free (although I searched the RunRev site for pricing and couldn't find it - they seem to have updated their web site and this information is not readily available). Assuming that LiveCode Server is not free and given I have a budget of $0, I think I'll continue to explore PHP for middleware.

Thanks again for all the help. I think this an important topic that will continue to need a lot of explanation as the LiveCode community grows. If I get something working that I think is worth sharing, I'll come back and post a report.

Best wishes to all - Lloyd

*CRUD=Create,Read,Update,Delete

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 » Thu Mar 19, 2015 9:53 pm

LiveCode Community server is free 8) , but you will need to install it on your own server. If that is an option for you, it can be downloaded here:

http://downloads.livecode.com/livecode/

--Sefro

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

Re: In Search of the Elusive Middleware Example

Post by lrieber » Thu Apr 09, 2015 1:18 am

I have had some success in creating some PHP files to act as middleware between LiveCode and a mySQL database. I created a very simple example and wrote up how it works on my blog - here's a link to the post:

http://learninglivecode.blogspot.com/20 ... eware.html

Lloyd

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

Re: In Search of the Elusive Middleware Example

Post by FourthWorld » Thu Apr 09, 2015 5:30 am

Another good blog post - thanks, Lloyd.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

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

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Thu Apr 09, 2015 4:25 pm

I have been kinda of trailing along on a similar plane, but I am using Livecode Server rather than PHP.

My latest efforts have been encrypting the data sent to the server, and decrypting on the server. This post sort of helped me:

http://forums.livecode.com/viewtopic.php?f=8&t=11733

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

Re: In Search of the Elusive Middleware Example

Post by FourthWorld » Sat Apr 18, 2015 9:06 pm

ghettocottage wrote:I down-graded my version of Livecode Server from 7.03 to 6.7 Community Edition and now everything works fine.
Did you file a bug report on that?
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: In Search of the Elusive Middleware Example

Post by sphere » Sat Apr 18, 2015 10:05 pm

And after all those years after your first post, there is still nearly nothing to find on this matter.

I'm glad Ghettocottage pointed me to this thread :)

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

Re: In Search of the Elusive Middleware Example

Post by FourthWorld » Sat Apr 18, 2015 11:42 pm

sphere wrote:And after all those years after your first post, there is still nearly nothing to find on this matter.
In all fairness, the LAMP setup described there is very widely used, and since none of those elements (Linux, Apache, MySQL, and PHP) involve LiveCode I can't really blame the RunRev team for not adding yet another tutorial to the many already available on the Web.

There's certainly more we as a community can do to pull together more tutorials on integrating LiveCode with such services, but to the team's credit they do offer tutorials like this one that Sefro linked to that explains how to write LiveCode clients that access such web services:
http://lessons.runrev.com/m/4070/l/4093 ... g-livecode

And for those who want to replace the PHP part of that with LiveCode, the tutorials on working with databases and using Server are helpful:
http://lessons.runrev.com/m/4071/l/7003 ... l-database
http://lessons.runrev.com/m/4070

As I wrote very early in this thread, protecting the database with a Web server only requires a little more learning than exposing it directly:
http://forums.livecode.com/viewtopic.ph ... 14#p122233
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

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

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Sun Apr 19, 2015 12:02 am

FourthWorld wrote:
ghettocottage wrote:I down-graded my version of Livecode Server from 7.03 to 6.7 Community Edition and now everything works fine.
Did you file a bug report on that?
I have not. I could. It will take me another session of playing to word things properly.

I have a sneaking suspicion that there is a character is in the wrong place or my syntax is off somewhere that 6.7 was forgiving of and 7 and up is more strict.

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

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Sun Apr 19, 2015 12:42 am

It is a hefty topic to take on, since there are so many dimensions to it:
  • setting up a server
  • getting mysql, php and apache working properly
  • figuring out what permissions mean and what chown and chmod are
  • securing your server
  • setting up a database in mysql
  • figuring out yet another set of permissions with your mysql users
then, after all that, if you have made it this far, you must now figure out how to get Livecode to reach out and talk to your server.

the concepts that need to be grasped are understandably overwhelming. The upside is that Livecode is incredibly user-friendly and intuitive (in my opinion) and can make the process much easier than say, learning PHP or some other programming language.

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: In Search of the Elusive Middleware Example

Post by sphere » Sun Apr 19, 2015 12:45 pm

Dear Livecode lovers, it is totally not my intention to blame anyone, especially not the Runrev team. I'm sorry if it did look like that.

I love what they do and yes the tutorials that are here are very helpful. How could i even got this far where i am now.

With the tutorial from Lloyd added i could manage to get it run: Android-->PHP--->Mysql, just to read some things from the database.

If you know the PHP piece, then it is actually quit simple. But...you have to know that part. Also on stackflow there are many usefull tips.

I knew a little PHP just to manage and change some scripts & css from the Joomla site i manage.
So i knew a little bit about Mysql (importing/exporting) but know i know even more :) Now using PHP and MYSQL.

All credit must go to the people here on the forum and the runrev team and other forums for what i have accomplished now, even if what i create looks simple for the diehard coders.

So thank you all !

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: In Search of the Elusive Middleware Example

Post by sphere » Sun Apr 19, 2015 12:56 pm

Hello,

i have a questions of which i don't understand where the characters come from.

If i run the PHP script on the website to retrieve data from the database i get:
Welcome

When i get it in Livecode and put it in a field i get:
Welcome

Anyone knows where they come from and/or what they mean. They appear with a single table but also with multiple tables and always on the first line.

the php part:

Code: Select all

<?php
//1. Create a database connection
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_connLivecode = "localhost";
$database_connLivecode = "databasename";
$username_connLivecode = "username";
$password_connLivecode = "password";
$connLivecode = mysql_pconnect($hostname_connLivecode, $username_connLivecode, $password_connLivecode) or trigger_error(mysql_error(),E_USER_ERROR); 

//2. Perform database query

mysql_select_db($database_connLivecode, $connLivecode);
$query_rsUser = ("SELECT flashnews FROM table");

$rsUser = mysql_query($query_rsUser, $connLivecode) or die(mysql_error());
$row_rsUser = mysql_fetch_assoc($rsUser);
$totalRows_rsUser = mysql_num_rows($rsUser);
 
if ($totalRows_rsUser == 0) {
    echo "Geen FlashNews";
} 


do {
  echo $row_rsUser["flashnews"]."\n";
   } while ($row_rsUser = mysql_fetch_assoc($rsUser)); 
  
?>
Note that also on the live website i use localhost as i noticed that using the ip address or the website url did not work.

piece of the livecode code:

Code: Select all

on mouseUp
     
   put URL "http://websitename.com/appscript/flashnews.php" into varResults
      put varResults into field "Data" of card 2
  
end mouseUp
It works ok, but only those weird characters.

Thanks a lot for any help on this.

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

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Sun Apr 19, 2015 6:31 pm

Someone else with more knowledge can chime in here, but I think that is unicode. You might try:

Code: Select all

on mouseUp
     
   put URL "http://websitename.com/appscript/flashnews.php" into varResults
      put uniDecode(varResults, UTF8) into field "Data" of card 2
 
end mouseUp

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: In Search of the Elusive Middleware Example

Post by sphere » Sun Apr 19, 2015 6:40 pm

Thanks.

But unfortunately it's not the solution, it gives this:
믯垿汥潫楢敤渠敩睵愠灰瘠湡嘠牥湥杩湩⁧畏ⵤ敌敤⠱䱎
乕匠杩慮慂瑴污潩੮

FYI all tables and the database are UTF8, so i don't think they have to be decoded?

If i do a google search then hte only topics i get are about getting data from multiple columns.
But i already can do that. And also then the first line contains the 3 strange characters.

i will post it at stackoverflow, maybe someone with more PHP knowledge knows what's going on.

I will report here if i have a solution. edit---ok i allready found some things about it, it seems to do with PHP script and using utf8 and BOM or something, have to read some more---

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

Re: In Search of the Elusive Middleware Example

Post by ghettocottage » Sun Apr 19, 2015 7:05 pm

I was on the right track:

Code: Select all


on mouseUp
   put URL "http://websitename.com/appscript/flashnews.php" into varResults
   put uniencode(varResults, "utf8") into varResults
   set the unicodetext of field  "Data" of card 2
end mouseUp


Post Reply

Return to “Databases”