Wanted example SQL data driven project

[alan_b_4]

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.

Do

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

Al

[PaulF_IntelCorp]

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.

[alan_b_4]

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

Al

[Dan_K_]

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        

function agree_clicked()
        {
            var customerCode = document.getElementById("input-field").value;
            
            if(customerCode == "")
            {
                alert("Please enter a customerCode");
            }
            
            var url = "http://myServer.com/apiDB.php?find_customer_code=" + encodeURIComponent(customerCode);
            
            var request;
            if(window.XMLHttpRequest)
            {
              request = new XMLHttpRequest();
            } 
            else if(window.ActiveXObject) 
            {
              try 
              {
                request = new ActiveXObject('Msxml2.XMLHTTP');
              } 
              catch (e) 
              {
                try 
                {
                  request = new ActiveXObject('Microsoft.XMLHTTP');
                } 
                catch (e) {}
              }
            }
            
            request.open('GET', url);

            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>