database connect question

[jrioux]

I remember reading on this forum that one shouldn't "hard code" urls and passwords into apps intended for public use. On the other hand, I need to connect to an external database for the app to function. Is there some special method for achieving this that I'm missing?

[bangkok]

I remember reading on this forum that one shouldn't "hard code" urls and passwords into apps intended for public use. On the other hand, I need to connect to an external database for the app to function. Is there some special method for achieving this that I'm missing?

Yes because 2 issues :

-login and password are "out there". Someone could look into your app.

-worse : this scheme forces you to "open" your database (MySQL Server for instance), leading to increased danger.

Therefore, the idea is :
-the app communicates with a webserver
-it sends the SQL queries to this webserver, through a basic POST command (like any web form)
-the webserver (thanks to a CGI in PHP or in LiveServer) is going to check the validity of the queries, send them to the MySQL server, and then displays the results
-the app "catches" and then processes the results

In the process, you can use a key to encrypt the communication back and forth, reducing the risks.

And other big advantage of this method : if your app is used on a corporate network... you'll avoid the issue of blocked ports (firewall) ! To communicate with MySQL server, you need to use the port 3306. Many companies allow only web trafic (regular port 80). Therefore, your app will work, because it communicates with the MySQL server, through regular web trafic.

[jrioux]

Thanks for the reply. I'm still a bit at sea, though. Are you saying doesn't there have to be, somewhere in my app, the name of the external database, the username, and the password?

[jrioux]

OK. I got it, now. The php script on the server contains all the "hard-coded" info, and the app calls that with the user's values.

There still remain "hard-coded" values, though, right (i.e., the "http://www.myserver.com/connect.php" files and such-like)?

Many thanks.

[FireWorx]

That is some very interesting information. I am a little rusty at SQL. I wrote a desktop revolution application that loads information in and out of an MS SQL SERVER database years ago. Now I am developing IOS applications and would like to pull information in and out of a database via the net.

Can you include a snippet of code that would help us understand a typical call to a database located on a web server? Is the connect string different etc? Sometimes a guy just needs a platform to spring off.

Dave

[mwieder]

Dave-

As mentioned in this and some other threads, I wouldn't do it that way. Do you have control over the server end of things as well? If so, I'd put the database access code on the server and have an interface to the web-facing stuff that you could talk to in order to manipulate the database. I wouldn't put the database directly out on a web interface.

If you don't have access to the web server, then you're stuck with things the way they are. And the webmaster should be properly thrashed. But in that case your database access *shouldn't* be much different from local database access, depending on the type of database.

[FireWorx]

Thanks Mark,
The other RunRev application I wrote connected Windows Pc's to a MS SQL SERVER database via ODBC over T1 lines to a database server a few miles away. I was never able to get a direct connection from RunRev to a MS SQL SERVER database without going through ODBC. Which I believe was identified in the documetation. Therefore I had to set up the ODBC in the control panel of each PC to get it to work. It continues to work fine however.

I do know the DB & network admin and will be speaking to him soon. As far as LiveCode IOS databse connections over the internet. Would it be your guess that I may have to switch to MySql server or another db (other than MS SQL SERVER) in order to connect via IOS over the internet?

Dave

[mwieder]

Well, without knowing a thing about this (why should that stop me?), my guess is that even in a post-Steve Jobs world you won't find iOS cuddling up with anything as Microsofty as ODBC. And I don't know of any way to get to SQLServer without ODBC. I haven't been following the iOS stuff here, so somebody else should probably chime in with which of the LC database externals, if any, work on that platform.

[bangkok]

Therefore I had to set up the ODBC in the control panel of each PC to get it to work. It continues to work fine however.
Dave

Well... there is another way.

Use a socket server !

This is what I did, to do exactly the same : allowing many PC within my internal network to access a MS SQL server, without setting ODBC drivers and connections on each PC.

An individual app, on individual PC, opens a socket connection to a (livecode) server, sends a query, the server accesses the MS SQL Server (through 1 ODBC connection), and then sends back the result.
Works perfectly.

[FireWorx]

Thats interesting stuff. I wish I knew more about how it all worked but... There is just so much stuff still to learn!

As well as having PC's access the DB via ODBC I have the same live code application running on a Cloud Server that supports multiple users via the internet and accesses the same MS-SQL SERVER database. A company called RASS takes care of technical stuff and we can have up to 5 concurrent users for a monthly fee. As of a few months ago they did not support mobile browsers but I will check back.

My current goal however is to access and insert data to and from a MS SQL Server DB via a G3 enabled iPad in the field. If I am forced to switch to a MYSQL database to get around the ODBC requirement and do a direct DB connect via live code then that might not be such a problem.

Any sample code or examples to help me get started would be appreciated. I will be having a meeting with the DB and network admin person soon who know very little about live code. Together we will look over out options.

Dave

[mwieder]

You may want to check out this thread regarding sockets:

http://forums.runrev.com/viewtopic.php? ... 428#p46428

...and still, for security reasons, I wouldn't put public-facing interface directly on the web without running a web service or something on the server end.

[mwieder]

bangkok - good to know you can get around the ODBC pain this way. Mind sharing the client-server interaction? If it can be extracted without too much trouble that would be really useful, now that you've already blazed the trail.

[bangkok]

My current goal however is to access and insert data to and from a MS SQL Server DB via a G3 enabled iPad in the field. If I am forced to switch to a MYSQL database to get around the ODBC requirement and do a direct DB connect via live code then that might not be such a problem.

Where is located the MS SQL server ?

On a private network, behind a firewall ?

You want to insert and pull out data from it, from Ipad G3, outside the private network ?

Right ?

I would therefore suggest the following steps :

-Ipad : a LiveCode app that connects to a .irev page hosted on your On Rev site.
The app "posts" to the irev page (see beginning of this thread) a query

-the irev page then send the query to the private network, also with the GET method.

Code: Select all

put URL "http://www.privatenetwork.com/cgi/runrev.rev?query='test'" into myResult
put myResult

-the firewall on the private network has a rule to allow incoming trafic on this port but only from the IP address of your On Rev site.
The firewall makes a port forward... to a small server on the private network that runs a small RunRev CGI (you'll find info about this -old- method on this forum) or a small LiveCode Server with Apache.

-this CGI opens the connection to the MS SQL server, through the ODBC driver, sends the query, takes the result

-results are going back to the irev page

-and thefore on the Ipad

Sounds complicated ?

It's real. I do it, altought not from Ipad, but directly from my OnRev site.

It allows me to consult from the internet my stock inventory, in real time, with the data coming from the MS SQL server of my ERP, on my network.

[FireWorx]

Thanks for that detail and time you spent on that message.

You are correct in your assumptions regarding my network. I do in fact have a lifetime on-rev account and have the ability to write and implement irev pages althoughI have done so only experimently. Because I have an On-Rev account I do have access to the MySQL DB on there OnRev web server there as well.

I will be creating new tables for my mobile project as this project has very little to do with the tables in the other MS SQL project i mentioned. I am thinking that hooking up to the on site enterprise DB server via ODBC as you suggest would be the preferred method in that keeping all of the organizations data in one place and backed up by a admined fire walled etc is a benefit although assume on rev has a secure system.

However would it be much easier to just build the tables on the on-rev MySQL database in order to communicate with the irev pages at the same location?

Thanks again for the great info!
Dave

[bangkok]

However would it be much easier to just build the tables on the on-rev MySQL database in order to communicate with the irev pages at the same location?

Yes of course.

But in any case, it depends of your needs and/or constrains.

-is "real time" data important for your users ?

-would it be okay to have let's say one update per day ? In this case, create an app on your private network that will copy relevant data from MS SQL to a MySQL on your OnRev. Security will be improved too (no access whatsoever from the outside to your private network)

-is security a top concern ? In this case, you should not think about my solution of a cgi on your private network, allowing incoming connections.

etc. etc.

What I know for sure : LiveCode + LiveCode Server + MySQL + web = almost everything is possible.