Use web service for mobile app and MS SQL?

[WarrenW]

Hi everyone,

I understand that I cannot connect to MS SQL from a mobile app unlike the desktop app that can use ODBC. So support had replied to me saying that a way I suggested would work which was to call a web page such as an aspx page that would could retrieve data from the MS SQL database and return it as string that can then be used however such as loading into a grid. The only problem with this is the system must have an open website to work in the middle.

Does anyone have any other answer for this? I'm just seeing what options I have for retrieving data from MS SQL from a mobile app. I can't switch to MySQL or SQLite because the client is already using MS SQL. Anything else other than a website that I can use?

Thanks!

Warren

[gpb01]

...
The only problem with this is the system must have an open website to work in the middle.
...

Hi Warren,
why "open" website ?

You can use HTTPS and authentication protocols and your connection is quite safe ! Obviously you have to write your own modules (... you can use LiveCode server ) to connect your mobile App to your MS SQL database ...

Guglielmo

[WarrenW]

Thanks Guglielmo. I guess I'm not familiar with that protocol exactly at least by the terms you used. What do I connect to then if it is not a web page I write? Or is it?

Warren

[Mark]

Hi Warren,

You need to write a PHP script, which connects to the MS SQL database. From within LiveCode, you can connect to the PHP script, using simple URL's such as

Code: Select all

get URL ("http://domain.com/db.php?cmd=upd&dat=" & urlEncode(myVar))

and

Code: Select all

put URL ("http://domain.com/db.php?cmd=get&dat=" & urlEncode(myVar)) into fld x

Is isn't always necessary to use https. You can use http if you take a few other security measures in account. You could include a secret code in the URL parameters or allow access only from specific IP numbers for instance.

If you're working for a serious client, I might help you out. Feel free to contact me privately (see below).

Kind regards,

Mark

[mwieder]

...but in any event, you need a server to talk to. It can be an http interface, in which case you can use php, a RESTful interface, or something similar; or you can control the server yourself and use a socket interface. For a mobile device without external library support you'll be stuck with http, so you'll want to have a web server on the backend. Since you're talking to an MSSQL database my guess is that youll be using a Windows server with asp.net. My condolences.

[bangkok]

Since you're talking to an MSSQL database my guess is that youll be using a Windows server with asp.net. My condolences.

That's harsh.

But so true.

Or :
-set up a small HTTP server (with Abyss Web Server for instance, easy and free) on the windows network, i guess a company, where is located the MS SQL server.
Coupled with a LiveCode Server (for windows) and a "hole" in the firewall to let in incoming requests... coming from a "regular" web server, in order to allow in only one IP address (for security), etc.
The mobile app would send the query to the regular web server, via HTTP POST, then a PHP or a Livecode server script would "forward" the query to the local company network, wait for the result, put the results, allowing display in the mobile app. etc.
Complicated but it's working (i use such solution)

Or :
-dont bother... Just set up a regular web server (cheap) with a MySQL database.
And, on the local network, create a livecode desktop app that will launch periodically a script that will read the data you need from the local MS SQL database... and then write the data to the MySQL database, hosted on the regular web server. Then, like previous point, the mobile app would talk to this server, via regular HTTP POST queries, etc.

Afterall, maybe "real reatime" is not necessary.

[WarrenW]

Thanks for the replies. Basically I'll need a web server which is what I was planning in the first place with aspx pages and using data controls to access the ms sql database. I was just wondering if there was any other methods. I don't have a problem with this at all as long as the client I do work for has a web server to use for this. If they do not, I can always host the web pages on my server and access their database as long as it is setup for remote connections. Because of how popular MS SQL is I just had to have a solution for this. Plus, I'm working on some of my own websites that I want to have a mobile app for and work with my database.

Thanks!

Warren

[mwieder]

Warren-

If you have that kind of control over the environment (lucky you), I'd seriously recommend that you look into the LiveCode server product. You can use it to serve web pages or set up RESTful services on the server and do things that way. The biggest advantage is that you can skip all the aspx, iis, and other microsoft garbage and write xtalk on the server to talk to the database.

[steveharman]

This is precisely why we need native (or library) support for serious back-end services such as Stackmob, Parse, Cocoafish, Kinvey et.al.

There's a reason Parse has just been acquired by FaceBook and Cocoafish by mobile development giants Appcelerator - the sort of functionality you're describing is vital to have in any modern IDE. The world has gone mobile & cloud centric - LiveCode seems to have mobile covered very nicely, but 'could try harder' in support for enterprise grade backend-as-a-service offerings from the likes of those listed above.

Steve