Wanted example SQL data driven project

alan_b_4 — Mon, 07 Sep 2015 23:25:38 GMT

HI Guys

Please excuse me if this has been covered, I couldn't find it. I'm new to XDK and I'm looking to build an SQL data driven app for Android / IOS.

any of you have any examples or tutorials that cover these areas

Connecting to an SQL database (cloud hosted SSL certificate based server)
Logging on (user name and password stored in SQL)
Pushing data back to the SQL server database
Retrieving data from the SQL server database

I have a reasonably good grasp of retrieving record sets in Java using Rest and I'm Ok with SQL queries, not so good with connection strings and a day 1 user with the XDK.

To speed things up, I'm willing to pay a nominal donation if someone can put a template together for that I can work from.

Many Thanks

I recommend you check out W3C

PaulF_IntelCorp — Tue, 08 Sep 2015 23:12:00 GMT

I recommend you explore the general HTML5 programming sites on the web (such as W3C Schools, StackOverflow and HTML5 Rocks). We are only staffed to provide you with Cordova/XDK-specific issues, we don't have the resources to answer general HTML5 questions.

Thanks Paul, At least now I

alan_b_4 — Tue, 08 Sep 2015 23:30:03 GMT

Thanks Paul, At least now I have a starting place of where to look :)

Alan,

Dan_K_ — Wed, 09 Sep 2015 12:32:14 GMT

Alan,

I struggled with this same problem. Turns out both HTML5 and Javascript cannot talk directly with an SQL database (who knew? I didn't). Anyway, PHP is the language of choice to talk with SQL.

Here are the steps to use a MySQL database with XDK.
1) create your database on a server.
2) write the php code that talks with the MySQL DB and put it on the same server where you created the database.
3) add a Javascript function to you HTML code to access the database and return data.

Here's a sample php file, apiDB.php followed by the Javascript function. When the data is returned, I save it localStorage.

BTW, I'm not a php or javascript expert. I actually had this code written by someone off shore.

**************************
PHP code to access MySQL
**************************
<?php

   error_reporting(E_ALL);
   ini_set('display_errors', 1);
   header("Access-Control-Allow-Origin: *");

   $username = "myUser";
   $password = "User$db";
   $hostname = "localhost";

// Create connection
$con=mysqli_connect($hostname,$username,$password);

   // Check connection
   if (mysqli_connect_errno()) {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
   die();
   }

   //create database dgkwebco_AXIsql if not exists
   $sql_1 = "CREATE DATABASE IF NOT EXISTS mySQL";
   if (mysqli_query($con,$sql_1))
   {}
   else
   {
   echo "Error creating database: " . mysqli_error($con);
   die();
   }

   //select the database for further operations.
   $sql_2 = "USE mySQL";
   if(mysqli_query($con, $sql_2))
   {}
   else
   {
       echo "Error selcting database mySQL: " . mysqli_error($con);
       die();
   }

   $sql_3 = "CREATE TABLE IF NOT EXISTS customers (
           customerName varchar(23) DEFAULT NULL,
           customerCode varchar(13) DEFAULT NULL,
           emergencyPhone varchar(13) DEFAULT NULL,
       )";
   if(mysqli_query($con, $sql_3))
   {}
   else
   {
       echo "Error creating table customers: " . mysqli_errno($con);
       die();
   }

   if(isset($_GET["findCustomerCode"]))
   {
       $code = $con->real_escape_string($_GET["findCustomerCode"]);

$sql_8 = "SELECT * FROM customers WHERE customerCode='$code'";
$result_1 = mysqli_query($con, $sql_8);

       if($result_1->num_rows > 0)
       {
           echo json_encode(mysqli_fetch_array($result_1));
       }
       else
       {
           echo "false";
       }
   }

****************************************
javascript code to get data from MySQL

****************************************
<script>
// this function is called when a button is clicked. The user enters a code and clicks an "agree" button

request.onreadystatechange = function() {
if(request.readyState === 4)
{
if(request.status === 200)
{
if(request.responseText == "false")
{
alert("Please enter a valid password");
}
else
{
var obj = JSON.parse(request.responseText);
localStorage.setItem("customerName", obj.customerName);
localStorage.setItem("customerCode", obj.customerCode);
localStorage.setItem("emergencyPhone", obj.emergencyPhone);
}
}
}
};

request.send(null);

}
</script>

topic Alan, in Software Archive

Wanted example SQL data driven project

I recommend you check out W3C

Thanks Paul, At least now I

Alan,