hi, I want to read data from MsSql Server, it’s posible from Corona SDK… someone has some example
thks.
PD. I read that topic https://forums.coronalabs.com/topic/47084-accessing-remote-sql-server-database/ but the internal link does not work…
I’ve never attempted it and it may require Corona Enterprise, but it could be possible with LuaSQL using the ODBC or ADO driver.
I however would recommend building an API between the DB and your app and having the app connect to the API rather than directly to the database source.
Well the link you can’t get to is rather old, it doesn’t have the best practices in it and it has formatting problems due to a bad code formatting plugin. Anyway, it’s back up if you want to read it in it’s current disarray of usefulness.
But let me summarize it.
If you want Corona SDK to talk to a MySQL database you really have two options, and only one of the should you realistically consider. The first option is to have your MySQL sitting on an open port and you can use Lua’s low level socket layer support to write your own MySQL driver. This is bad for two main reasons. 1. Security. You really shouldn’t have your SQL server open to the world. You’re a password guess away with any joe getting access to your server. 2. Unless you want to write TCP/IP level code, it’s a huge cumbersome task to take on.
The other way is to use some server script layer as a middle man between your Corona App and your MySQL database. You could write some scripts that are processed by your webserver that will take input from and send output to your mobile app. And internally it connects to the database and does limited actions that you’ve programmed. You could do this in .NET if you’re more familiar with that or you could do it with PHP, Node.js or a host of other web technologies. I’m most familiar with PHP, ergo that old outdated post.
In this model, you can use any old web hosting provider since you don’t need to run your own servers as long as they offer you PHP and MySQL. You will create a PHP script that can be accessed with an HTML GET or HTML POST request. You pass some data to the script (part of the URL for GET requests, as a separate data table for POST). The PHP script will take that input, sanitize it (security is paramount for web scripting), and then execute a specific query (you never want to let the user execute arbitrary SQL commands).
When the script is done, read the results (if it’s returning any) into a PHP Associative Array, and then use the “echo” command to output a JSON encoded string:
echo json_encode( $mySQLResults);
That data will be transferred back to your app. In your app to start the process you will call our network.request() API. In a simple use case:
local results local function myscriptListener( event ) if not event.isError results = json.decode( event.response ) end end local URL = "http://myhost.com/myscript.php?key1=value1&key2=value2" network.reqeust( URL, "GET", myscriptListener )
on the server side the PHP script might be like:
$key1 = mysqli::real\_escape\_string ( $\_GET["key1"] ); $key2 = mysqli::real\_escape\_string ( $\_GET["key2"] ); $query = "INSERT INTO table (key1, key2) VALUES ('" . $key1 . "', '" . $key2 . "';";
I can’t go in to the SQL side too much. PHP is now using msqli instead of the old methods and I’ve not figured out the new system yet.
Hopefully this will get you started.
Rob
tkhs rob, i will try to adapt to Mssql (Microsoft Sql Server)
I’ve used a MS SQL db for a bunch of my projects. Pretty much I do exactly what Rob’s got up there. You’ll need to make an .aspx page that does the querying and then posts back the results… something like this
*EXAMPLE: checking for a username in the table or something
<script language=“c#” runat=“server”>
protected void Page_Load(object sender, EventArgs e) {
// CHECK FOR QUERYSTRING
if(Request.QueryString[“user”] != null){
// DO YOUR SQL STATEMENTS / CODE
// POST RESPONSE BACK TO THE USER
Response.Clear();
Response.Write(THE RESPONSE);
Response.ContentType = “text/plain”;
Response.End();
}
}
</script>
This will just post back a text string, no headers or anything. You can change ContentType to post back different things.
Steve
I’ve never attempted it and it may require Corona Enterprise, but it could be possible with LuaSQL using the ODBC or ADO driver.
I however would recommend building an API between the DB and your app and having the app connect to the API rather than directly to the database source.
Well the link you can’t get to is rather old, it doesn’t have the best practices in it and it has formatting problems due to a bad code formatting plugin. Anyway, it’s back up if you want to read it in it’s current disarray of usefulness.
But let me summarize it.
If you want Corona SDK to talk to a MySQL database you really have two options, and only one of the should you realistically consider. The first option is to have your MySQL sitting on an open port and you can use Lua’s low level socket layer support to write your own MySQL driver. This is bad for two main reasons. 1. Security. You really shouldn’t have your SQL server open to the world. You’re a password guess away with any joe getting access to your server. 2. Unless you want to write TCP/IP level code, it’s a huge cumbersome task to take on.
The other way is to use some server script layer as a middle man between your Corona App and your MySQL database. You could write some scripts that are processed by your webserver that will take input from and send output to your mobile app. And internally it connects to the database and does limited actions that you’ve programmed. You could do this in .NET if you’re more familiar with that or you could do it with PHP, Node.js or a host of other web technologies. I’m most familiar with PHP, ergo that old outdated post.
In this model, you can use any old web hosting provider since you don’t need to run your own servers as long as they offer you PHP and MySQL. You will create a PHP script that can be accessed with an HTML GET or HTML POST request. You pass some data to the script (part of the URL for GET requests, as a separate data table for POST). The PHP script will take that input, sanitize it (security is paramount for web scripting), and then execute a specific query (you never want to let the user execute arbitrary SQL commands).
When the script is done, read the results (if it’s returning any) into a PHP Associative Array, and then use the “echo” command to output a JSON encoded string:
echo json_encode( $mySQLResults);
That data will be transferred back to your app. In your app to start the process you will call our network.request() API. In a simple use case:
local results local function myscriptListener( event ) if not event.isError results = json.decode( event.response ) end end local URL = "http://myhost.com/myscript.php?key1=value1&key2=value2" network.reqeust( URL, "GET", myscriptListener )
on the server side the PHP script might be like:
$key1 = mysqli::real\_escape\_string ( $\_GET["key1"] ); $key2 = mysqli::real\_escape\_string ( $\_GET["key2"] ); $query = "INSERT INTO table (key1, key2) VALUES ('" . $key1 . "', '" . $key2 . "';";
I can’t go in to the SQL side too much. PHP is now using msqli instead of the old methods and I’ve not figured out the new system yet.
Hopefully this will get you started.
Rob
tkhs rob, i will try to adapt to Mssql (Microsoft Sql Server)
I’ve used a MS SQL db for a bunch of my projects. Pretty much I do exactly what Rob’s got up there. You’ll need to make an .aspx page that does the querying and then posts back the results… something like this
*EXAMPLE: checking for a username in the table or something
<script language=“c#” runat=“server”>
protected void Page_Load(object sender, EventArgs e) {
// CHECK FOR QUERYSTRING
if(Request.QueryString[“user”] != null){
// DO YOUR SQL STATEMENTS / CODE
// POST RESPONSE BACK TO THE USER
Response.Clear();
Response.Write(THE RESPONSE);
Response.ContentType = “text/plain”;
Response.End();
}
}
</script>
This will just post back a text string, no headers or anything. You can change ContentType to post back different things.
Steve