Connect to MS SQL Server

[jagiron]

Its posible from iOS to connect to SQL Server and work like we can do in sqlite or mysql ?
Thanks

[FireWorx]

Hi,
I asked the sdame question a few months back in this post. About half way down a member named Bankok explains that he does it. He describes the method as well. He helped me write an iRev file and I connected to a MySQL server but I never got as far as connecting to SQL SERVER. If you are succesfull please write about it here because that is a project that is on the back burner for me. I currently have a desktop runrev product routing data through ODBC to a Microsoft SQL SERVER and have entertained accessing it wireless. Bankok is active on the forum and a great resource.

http://forums.runrev.com/phpBB2/viewtop ... SQL+SERVER

Dave

[JosepM]

Hi,

I'm also interested in...

Salut,
Josep M

[peter786723]

Yes you can connect with a iOS device to a mysql server, works perfect

Peter

[FireWorx]

The question was how to use IOS to connect to a "Microsoft SQL SERVER" Which on the windows desktop is done with live code via the ODBC interface in the windows admin control pannel. But on IOS? The only person that has said he does it is Bangkok, and he hasn't posted exactly how, although he says he uses a socket server. I am not sure how that works.
Dave

[bangkok]

But on IOS? The only person that has said he does it is Bangkok, and he hasn't posted exactly how, although he says he uses a socket server. I am not sure how that works.
Dave

Watch out : i didn't say it was working with iOS, because i don't use it.

But let's assume LiveCode iOS doesn't have ODBC driver or native MS SQL driver capabilities... therefore can not directly connect to a MS SQL database, then what would be your options ?

Let's assume, you need something working on a local network (your company)

You have 2 solutions :
-webservice
-socket server (if LiveCode iOS handles socket, which i don't know)

-webservice
-set up a web server (with Apache, or the very good and simple Abyss on Windows, Linux)
-install LiveCode Server (that will allow you to write "cgi" like regular LiveCode script)
-install on the web server machine, the drivers to connect to the MS SQL database

The scheme is easy :
-iOs sends http queries to the local webserver. Something like :

Code: Select all

put URL "http://192.9.202.1/mycgi.irev?"&myparameters into myresult

-the cgi, takes the parameters, connects to the MS SQL db, sends the SQL query, takes the results, and "display" (just a put) the results on the webpage
-iOS gets the results

-socket server
-in this solution, no need LiveCode Server
-easy : you replace http queries by data sent directly to some socket, to a server
-set up a server (Windows, Linux) with a special LiveCode application on it
-set up the drivers for MS SQL
-this server, will "listen" to a special port number

Code: Select all

on mouseUp
   accept connections on 8192 with message "connection"
end mouseUp

on Connection theIP
   read from socket theIP until return with message "incoming"
end Connection

on contacted theIP,theMessage
    
if char 1 of theMessage ="1" then
--- here connects to DB
--- sends the query
write myResult&"#" to socket TheIP with message "finish"
exit to top
end if
end contacted

-the iOS application opens connexion on this port number, sends the query, waits for result, and close the connexion

Code: Select all

 
on mouseup
open socket to "192.9.202.1:8192" with message "iamconnected"
end mouseup

on iamconnected theSocket
   write "1"&myparameters&return to socket theSocket 
   read from socket theSocket  until "#" with message "displayResult"
end iamconnected

on displayResult theIP theMessage
close socket theIP
  answer theMessage
end displayResult

In both solutions, you need a server. It will do the dirty work. iOS applications will be only "client". it's good for security : no need to share DB passwords for instance on many applications. You have only one. And it's easier to maintain. If you need to modify a SQL query... you do it only once, on the server. The clients do not need to be uptated/changed.

[FireWorx]

Bank thanks for the detailed response and also for helping me out with the slick n easy route hooking up to MySql server on On-Rev. I will forward your idea to a friend who is an IT guy on the MS SQL SERVER enterprise that I contribute to. For now side stepping around that issue and working on some other projects and thinking of trying to use my on-rev MySql database keep track of calendar entries and share calendars with others. But... thats another topic.

Thanks Again,
Dave

[FireWorx]

This looks interesting....

http://ODBCrouter.com/ipad

Dave

[KrystalRe]

Yes you can connect with a iOS device to a mysql server, works perfect

Peter

Does it depend on the iOS version?

[FireWorx]

I ended up downloading an IOS app called DBViewer. It cost like $6.99. There is a desktop database interface where you set up filters and sorts on your data and then you enter you 40 digit UDID # of your phone which creates a link between your IOS devise and the desk top interface. Then you can open your IOS app and execute the stored query and return the data to your phone. It works ok but what I want is to be able to do all that from within my Livecode app.

So...

I am back to writing iRev scripts (queries, inserts and updates) and loading them onto my web site to be executed from my phone via Livecode in order to access the MySQL database on my On-Rev server. This is working ok but url encoding the query parameters and passing them to the iRev scripts via the post command can be tricky and hard to debug.

Then the second part of that is to automate getting the data out of the MS SQL SERVER database and into MySQL web server database on say a monthly interval.

It's all a big headache.

[programmerBUSLVMD]

I'm currently running a program that I wrote that monitors a port and looks for parameters and works as a direct intermediary to the MS Sql server.

The calling command is put url("yourwebpage:port/?param1;userid;hashed password?") into rtable

The parameter in my case is a function number but could just as easily be a query. I return the data tab separated and end it with a chr(10) and throw it right into a grid. (Don't know how they can call that a return character when it's really a line feed. Old school ascii line printer codes.) This would only work on a windows box as it's a Delphi program and I use OLEDB to talk to the sql server for max performance. The code doesn't belong to me so I can't share it. But I used a wizard in Delphi XE2 (uses pascal) to generate web server base code and had a working version in less than a day. Works really well on IOS and Android.

[Mark]

Hi,

If you're willing to set up a web server as an intermediary between LC and MS SQL, then I'd recommend PHP. The PHP commands for MS SQL work almost exactly the same way as the PHP commands for MySQL. E.g. mysql_connect becomes mssql_connect and mysql_query becomes mssql_query.

Kind regards,

Mark