database connect question

[FireWorx]

My goal is to trap emergency services dispatch information that is currently routed to a tear off printer via ethernet and funnel that dispatch information into tables in a SQL database. I have identified a method of doing this. Then users in the field that are dispatched by radio can query the database to get a hard copy on the screen of their iPads of that dispatch data.

A majority of the information is public information that you could get off a 911 scanner. However some of the information is not. It involves names, phone numbers, type of emergency, etc and that data needs to be reasonably protected. The data needs to be updated and available to the end user within seconds of the dispatch and the print out. The users will be alerted however by radio that new data is waiting to be viewed.

One possible solution I am considering is to find away to load the dispatch data into a google fusion database table. Pay for a premium membership in order to secure the privy part of the data. Then query the data via javascript and display the geocoded information on a google map in a 3" x 5" pop up info window.

However I am interested in making the IOS iPad to SQL SERVER connect as well for future projects. Thanks so much for your help!
Dave

[bangkok]

My goal is to trap emergency services dispatch information that is currently routed to a tear off printer via ethernet and funnel that dispatch information into tables in a SQL database. I have identified a method of doing this. Then users in the field that are dispatched by radio can query the database to get a hard copy on the screen of their iPads of that dispatch data.

So real time is indeed very important.

So If I understand you correctly :

-you've found a way to "funnel that dispatch information into tables in a SQL database", right ?

-in this case, you have everything at your hand ! Why bother with google fusion ?

-funnel the dispatch informations to a MySQL hosted on your OnRev account.

-write an irev script (easy since you already use LiveCode) that will receive queries from a POST (launched by your Ipad app), executes the queries, and display the results
Your Ipad will accesss this irev script like :

Code: Select all

      put "theQuery=urlencode(myQuery) into myQuery
      post myQuery to url "http://www.on-rev.com/mySite/backoffice.irev"
      put it into myResult

On the irev script side, you'll have something like :

Code: Select all

<?rev
put $_POST["theQuery"] into myQuery
--sterilize myQuery (to avoid SQL injection)
--open DB
--execute the query
--put the result
--close DB
?>

-then on your Ipad app, you can add goodies such as google map

Piece of cake.
Do you want more detailed irev scripts as examples ?

[FireWorx]

Thanks so much for giving me enough information to get started!

I am dusting off my IREV thinking cap and I will create a new MySQL DB table on my On-Rev site and populate it with some test data. Then I will work with the Irev example scripts available at On-Rev.com to get started.

After I am able to connect to the database via live code and the IREV interface and display a data query I will then try it in IOS on my iPad. If at that point all systems are go I will have the confidence to make a purchase request to acquire the technology needed to intercept the dispatch information that is currently routed to the printer and get it into our MySQl database. At that point most of the puzzle pieces will be in place.

Thanks Again.
Dave

[FireWorx]

I am pretty excited. Thanks to the help I got here in this forum I was able to create a MySQL table on my On-Rev Site. Write an $_POST script in a .irev file that contains my database connect string, and query info. Then write a few lines of code in a live code stack to execute the query and display the information. My connect string, password, database and table names, etc are all hidden from the user. The data posts with lightening speed. I couldn't be much happier. This opens up some doors for me.

Thanks for paying it forward guys. I will do my best to pay it back to who ever else I can help in turn.

Here is the stack that is the result of our work.

The data is a bit jumbled still but I need to go get a workout in so thats a task for later.

Dave

[bangkok]

Congratulations.

I told you it would be piece of cake (and fun).

To go further :

-you MUST put an "index.irev" file into your directory !
Right now, everybody can see the content of your directory

http://aptosfire.on-rev.com/
DispatchQuery.irev
DispatchQueryVar.irev
Query test.irev
etc..

-don't forget to "sanitize" what you send as query, in order to avoid "SQL injections" threat : remove quote, space, single quote, = etc.

-you can give a more esoteric name to your backoffice.irev file, in order to avoid a name too obvious

-you could add an encryption system in order to scramble the data you post.

[mwieder]

Dave-

Yes, congratulations on getting this working. The n-tier approach is most definitely the way to go. Now, as bangkok said, it's time to put the bells and whistles in place to lock this down to prevent idiots from playing with the setup. But you've taken that first hard conceptual step and the rest should be easy.

[FireWorx]

Cool. I added an index.irev file to the directory with a simple script in it's body to show the time. It seems to act as a gate keeper.

I am taking a look at http://revigniter.com/ It looks like some great database query examples and such. Not sure if I am going to load the folders up on to my server or not but considering it.

Thanks again
Dave

[greaper76]

Hi guys

I am new to Livecode and IOS development and I have run into a brick wall with the app I am creating.

I need to be able to pull data from a Mysql database and display the data on the phone. I have read the previous threads and understand that IOS does not support database connections. There was also mention of another method which I was trying to get my head around but cant. Can someone please post some code or maybe a link to a more detailed explanation, this would be much appreciated.

If anyone has a better method, I am all ears.

thanks
Donovan

[greaper76]

I have just seen that we could use an on-rev site and create an irev script file to query the database for you. I will need to query the database based on values that are entered by the user within the app, can this be done using an irev script file.

[FireWorx]

It's been awhile since I worked on this topic. But I was planning on re visiting it soon. I currently have been using an Sqlite database within my app. The way it was explained to me you can actualy connect to a MySQL database with connect string and password but it is not recomeded because you have to display your password in your code which can be picked up and you put your data at risk.

The prefered method as explained to me is to write your connect string and SQL query in either PHP or as discussed in this thread using an iRev file on the Revolution Server called On-Rev. The iRev file or PHP file whichever you chose sits on your server with the database connect and query code sitting on it. Then all you have to do is connect to that url and your query will be automaticly executed and return the data that you seek. Why don't you look into On-Rev? I got it free for life when I upgraded to version LC 4.8 but not sure what it cost now.
Then the code is just:

on mouseUp
  put "theQuery=urlencode(myQuery)" into myQuery
post myQuery to url "http://aptosfire.on-rev.com/backoffice.irev" ## the simple text style iRev file sitting on my server with the SQL on it
      put it into tMyResult
answer tMyResult ## the data that you seek
end mouseUp
if you would like a look at the iRev file code I can post it later from my other PC. I will need to access my site via the c-pannel to get it for you. Its one page of very basic livecode and SQL
Dave

[greaper76]

Thanks Dave, I am going to try the php method first as I have a host a already and would prefer staying with them.

I do have one more question though, Can I send parameters for the query from my app to the php script and if so do you know what the code will look like. I am not very proficient in php.

Yes I would like to see your code for irev file that you mentioned earlier.

thanks again

[FireWorx]

Hi Greeper,
Sorry it took so long here is the iRev code that takes car of the query. It delivers the data back to live code with the little stack attached in my earlier post. Note I changed the connect string omitting my personal info.

<?rev set the errorMode to "inline" ?>
<?rev put revOpenDatabase("mysql", "localhost", "YourDbName", "YourUserName", "YourPassword") into tConId
put $_POST["theQuery"] into myQuery
put "SELECT * FROM YourSQLTableName" into tSQL
if tConId is a number then
put revDataFromQuery(tab, return, tConID, tSQL) into tList
put tList
revCloseDatabase tConID
end if
?>

Dave

[snm]

I don't understand why you are sending the SQL query to the server, then put it from $_POST into myQuery - and finally don't use it at all?

<?rev set the errorMode to "inline" ?>
<?rev put revOpenDatabase("mysql", "localhost", "YourDbName", "YourUserName", "YourPassword") into tConId
put $_POST["theQuery"] into myQuery
put "SELECT * FROM YourSQLTableName" into tSQL
if tConId is a number then
put revDataFromQuery(tab, return, tConID, tSQL) into tList

[FireWorx]

Here is the code in the button on the card that calls the iRev script above into action. If you still think I have extra uneccesary code then let me know what that is. I'm kind of new at this as well. Download the little stack above if you like and click the button and you will see that the button does send a query to the MySql database at my on-rev site. The iRev file with the code above is activated the query takes place and the data is returned to the user.
Dave

Button Script

on mouseUp
  put "theQuery=urlencode(myQuery)" into myQuery
post myQuery to url "http://myonrevsite.on-rev.com/backoffice.irev"
      put it into tMyResult
answer tMyResult
end mouseUp

[snm]

I think the line:
Put $_POST["theQuery"] into myQuery
is for nothing. You are not using myQuery variable then anywhere anymore.

Marek