Over the years JavaScript has become an integral part in every web developers life. The Dojo Toolkit is one of many JavaScript frameworks offering language goodies and interface voodo. It may not be the most used framework but it is a certain beauty to it – once you get used to it. The steep learning curve may be a bit in its way to success. However, to demo how to connect from browser-side JavaScript to MySQL it is about perfect as all its bells and whistles show what can be done, given enough time…
Feel free to skip all the recap sections, if you are familiar with the very basics of AJAX/JSONP.
Recap: JavaScript in a LAMP application
If your web site is driven by MySQL, a mix of a script language (PHP, Python, Perl, Ruby, …) and you plan to have quite some JavaScript running in the Browser you are confronted with a challenge. JavaScript run as part of an HTML page cannot connect to MySQL. JavaScript is limited to making HTTP requests but MySQL does not speak HTTP. Thus, JavaScript cannot connect to MySQL directly. It takes a proxy to connect them.
| JavaScript | <- HTTP Protocol -> | Proxy, e.g. Apache/PHP |
| ^ | ||
| MySQL Protocol (binary) | ||
| v | ||
| MySQL Server | ||
There are two tasks for you as a developer: issue an HTTP request from JavaScript, create a proxy to handle the requests.
Recap: A proxy to handle requests
In an ideal world you would sit down and design a service interface for your JavaScript application that is then implemented by the proxy and offered as a RESTful service. You would not offer a single call that is not needed by your application to create a minimal service. The less, the less bugs can there be and the lower the risk of abuse. You would map a SQL SELECT to an HTTP GET request and so forth. Do that for production use!
During development, a simple PHP script accepting a SQL command as a GET parameter, for example, like http://127.0.0.1/index.php?sql=SELECT%201, may do the trick. It also does the trick, if all you want it demo how JavaScript and MySQL can be glued together ;-). WARNING: do not use in production, unless you are fully aware of the security implications.
<?php
function reply($msg) {
$reply = json_encode($msg);
if (isset($_REQUEST['jsonp']) && $_REQUEST['jsonp']) {
$reply = sprintf("%s(%s);", $_REQUEST['jsonp'], $reply);
}
header("Content-type: application/json");
die($reply);
}
if (isset($_REQUEST['sql'])) {
$mysqli = new mysqli("127.0.0.1", "root", "root", "test", 3307);
if ($mysqli->connect_errno) {
reply(array(
"errno" => $mysqli->connect_errno,
"error" => $mysqli->connect_error,
"sqlstate" => "HY000"));
}
$stmt = $mysqli->prepare($_REQUEST['sql']);
if ($mysqli->errno) {
reply(array(
"errno" => $mysqli->errno,
"error" => $mysqli->error,
"sqlstate" => $mysqli->sqlstate));
}
if (!$stmt->execute()) {
reply(array(
"errno" => $stmt->errno,
"error" => $stmt->error,
"sqlstate" => $stmt->sqlstate));
}
$result = array();
$resultset_idx = 0;
do {
if ($res = $stmt->get_result()) {
/* this could be a stored procedure call returning different result sets */
$result[$resultset_idx] = array(
"data" => array(),
"meta" => array(),
"status" => array(),
);
while ($row = $res->fetch_row()) {
$result[$resultset_idx]["data"][] = $row;
}
$res_meta = $stmt->result_metadata();
$fields = $res_meta->fetch_fields();
foreach ($fields as $field) {
$result[$resultset_idx]["meta"][] = array(
"type" => $field->type,
"database" => $field->catalog,
"table" => $field->table,
"column" => $field->name
);
}
if ($mysqli->insert_id) {
$result[$resultset_idx]["status"]["last_insert_id"] = $mysqli->insert_id;
}
$result[$resultset_idx]["status"]["warning_count"] = $mysqli->warning_count;
} else {
/* Either an error or a statement which has returned no results */
if ($stmt->errno) {
reply(array(
"errno" => $stmt->errno,
"error" => $stmt->error,
"sqlstate" => $stmt->sqlstate));
} else {
reply(array(
"warning_count" => $mysqli->warning_count,
"affected_rows" => $mysqli->affected_rows,
"last_insert_id" => $mysqli->insert_id,
));
}
}
$resultset_idx++;
} while ($stmt->more_results() && $stmt->next_result());
reply($result);
} else {
reply(array(
"errno" => 1065,
"error" => "Query was empty",
"sqlstate" => "42000"
));
}
Above you can find a sample PHP proxy script which takes an arbitrary SQL command as a GET parameter, executes it and returns the result as a JSON object. The easiest way to test it, may be using the PHP built-in webserver. Copy the script, save it named as index.php, start the PHP built-in webserver with the location of the script as the document root.
> php -S 0.0.0.0:8080 -t /home/nixnutz/src/php-src/ 1>2 2>>/tmp/php.log &
> curl "http://127.0.0.1:8080/?sql=SELECT%201"
[{"data":[[1]],"meta":[{"type":8,"database":"def","table":"","column":"1"}],"status":{"warning_count":0}}]
Feel free to change the format of the JSON returned in reply to SELECT 1. I am not aware of any format standards to follow. The example script is just good enough to handle simple queries and calls to stored procedures returning multiple result sets. It should do the trick for prototyping work. However, you probably know your script language but are more curious to see the JavaScript snippets.
[
{
"data": [
[
1
]
],
"meta": [
{
"type": 8,
"database": "def",
"table": "",
"column": "1"
}
],
"status": {
"warning_count": 0
}
}
]
Recap: JavaScript HTTP request
The easiest way of doing an HTTP request with JavaScript may be using the dedicated XMLHttpRequest object. Together with a proxy, such as the one sketched above, it is the most simple way of connecting from JavaScript to MySQL.
<script language="JavaScript">
xmlHttp = new XMLHttpRequest();
xmlHttp.open('GET', 'http://127.0.0.1:8080/index.php?sql=SELECT%20\'Greetings!\'', true);
xmlHttp.onreadystatechange = function () {
document.write("Response: " + xmlHttp.responseText);
};
xmlHttp.send(null);
</script>
Let me cite Wikipedia on a major limitation:
XMLHttpRequest is subject to the browser’s same origin policy in that, for security reasons, requests will only succeed if they are made to the same server that served the original web page.
A common solution to the same origin restriction is using JSONP: JSON with padding. The idea is simple. It takes two steps to understand. First, instead of returning a JSON document, the proxy return a JSON document padded with a function call. Try calling the sample proxy with index.php?sql=SELECT%201&jsonp=callback:
callback(
[
{
"data": [
[
1
]
],
"meta": [
{
"type": 8,
"database": "def",
"table": "",
"column": "1"
}
],
"status": {
"warning_count": 0
}
}
]
);
Second, put a script block in your HTML document which implements a function callback(). Put another script tag in the HTML document which refers to the proxy using the src attribute.
<script language="JavaScript">
function callback(data) {
document.wrtite("<pre>" + data + "</pre>");
}
</script>
<script language="JavaScript" src="http://127.0.0.1:8080/index.php?sql=SELECT%1&jsonp=callback">
A script tag can load code from an arbitrary URL. What happens is that the second script tag loads the JavaScript script from your proxy, executes it and callback() gets called.
dojo talks to MySQL
As usual, frameworks hide details such as cross browser compatibility, adding HTML elements to the DOM on the fly, URL encoding and error handling to some degree. Below is a complete dojo example utilizing the PHP proxy from above. Upon click on a button some SQL queries are run against MySQL to create a table, populate it and fetch results from it. The complete example first, a step by step discussion is below.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
</head>
<body>
<script src="http://ajax.googleapis.com/ajax/libs/dojo/1.8.4/dojo/dojo.js"
data-dojo-config="async: true"></script>
<script>
require(
["dojo/dom", "dojo/on", "dojo/request/script", "dojo/request/notify", "dojo/json", "dojo/domReady!"],
function(dom, on, script, notify, JSON) {
// Results will be displayed in resultDiv
function mysql(queries) {
if (0 == queries.length)
return;
var query = queries[0];
queries.shift();
alert("Running " + query);;
var url = "http://127.0.0.1:8080/index.php?sql=";
url += encodeURIComponent(query);
var promise = script.get(url, {jsonp: "jsonp", query: {jsonp_escape: 1}});
promise.then(
function (data) {
alert("MySQL reply: " + JSON.stringify(data));
mysql(queries);
},
function (err) {
alert("(Error) " + JSON.stringify(err));
mysql(queries);
}
);
}
// Attach the onclick event handler to the makeRequest button
on(dom.byId('makeRequest'), "click", function (evt) {
queries = new Array(
"DROP TABLE IF EXISTS test",
"CREATE TABLE test(id INT)",
"INSERT INTO test(id) VALUES (1), (2), (3)",
"SELECT id FROM test ORDER BY id DESC"
);
mysql(queries);
});
}
);
</script>
<form><input type="button" id="makeRequest" value="Click to query MySQL" /></form>
</body>
</html>
As a dojo beginner you will find the usual bits in there to load dojo and to import some modules:
[...]
<script src="http://ajax.googleapis.com/ajax/libs/dojo/1.8.4/dojo/dojo.js"
data-dojo-config="async: true"></script>
<script>
[...]
require(
["dojo/dom", "dojo/on", "dojo/request/script", "dojo/request/notify", "dojo/json", "dojo/domReady!"],
function(dom, on, script, notify, JSON) {
You can either download dojo, put into your local file system and use a directory path to reference it or use a CDN. For toying around, a CDN does the trick. The require() function is dojo’s current way of loading modules and importing them into whatever function you provide. If this is completely new to you, think of function(dom, on, script, notify, JSON) as your main() function, your programs main body. The parameter you get correspond to the modules that you require.
The last element in the list of required modules is a special one: it delays the start of your "main()" function until the DOM is loaded and you can start manipulating it. For example, you may want to associate a click event handler with a button from the DOM, as its done towards the end of the script.
[...]
// Attach the onclick event handler to the makeRequest button
on(dom.byId('makeRequest'), "click", function (evt) {
queries = new Array(
"DROP TABLE IF EXISTS test",
"CREATE TABLE test(id INT)",
"INSERT INTO test(id) VALUES (1), (2), (3)",
"SELECT id FROM test ORDER BY id DESC"
);
mysql(queries);
});
[...]
<form>
<input type="button" id="makeRequest"
value="Click to query MySQL" />
</form>
When the button is clicked, dojo calls our mysql() function which performs a JSONP HTTP request to query MySQL.
Culture clash: this world is asynchronous!
A PHP developer, who is used to synchronous function calls, will have to learn a couple of new language "phrases". Many dojo function calls operate in an asynchronous way. Calling function does not block
execution until the function returns. The main execution flow continues as events arrive asynchronously.
A JSONP HTTP request can be made by the means of the dojo script module. It can be used to query the proxy for running a SQL statement. script.get() is a non-blocking, asynchronous call. A call to script.get(proxy_url, [options]) does not block, neither does it return the result of HTTP request it performs. Instead it returns a dojo promise object.
var promise = script.get(url, {jsonp: "jsonp", query: {jsonp_escape: 1}});
You can use the promise object to install callback functions that are called when results arrive from the HTTP request or an error occurs.
promise.then(
function (reply) { },
function (error) { }
);
A promise is one of those "phrases" that you have to know to master this non-PHP world. And, a promise it as the hearth of the mysql() function that makes dojo query MySQL.
[...]
var url = "http://127.0.0.1:8080/index.php?sql=";
url += encodeURIComponent(query);
var promise = script.get(url, {jsonp: "jsonp", query: {jsonp_escape: 1}});
promise.then(
function (data) {
alert("MySQL reply: " + JSON.stringify(data));
mysql(queries);
},
function (err) {
alert("(Error) " + JSON.stringify(err));
mysql(queries);
}
);
[...]
script.get() hides all the details of the HTTP request from you. You pass in the URL and tell it whether it shall do a JSONP request or something else, it does the rest for you including DOM manipulation for inserting the script tags and code shown above in the recap paragraphs.
JSON.stringify serves no other purpose but turning the object you get in reply to your JSONP request into a string that you can easily display, for example, using alert().
Seriously, it is asynchronous…
Upon closer inspection you may notice that the mysql() function uses a recursive approach to run queries. The function takes an array of SQL queries as an input parameter.
[...]
queries = new Array(
"DROP TABLE IF EXISTS test",
"CREATE TABLE test(id INT)",
"INSERT INTO test(id) VALUES (1), (2), (3)",
"SELECT id FROM test ORDER BY id DESC"
);
mysql(queries);
[...]
It shifts the first element off the list, sends a JSONP request through script.get() and registers callbacks for the promise. When the promise executed the callbacks, the result it processed and
the mysql() is called with the list of remaining queries.
function mysql(queries) {
if (0 == queries.length)
return;
var query = queries[0];
queries.shift();
[...]
url += encodeURIComponent(query);
var promise = script.get(url, {jsonp: "jsonp", query: {jsonp_escape: 1}});
promise.then(
function (data) {
alert("MySQL reply: " + JSON.stringify(data));
mysql(queries);
},
[...]
Assume you would not wait for the promise to invoke the callbacks and fire off all SQL queries at once. You could do that: script.get() is non-blocking!
url += encodeURIComponent("DROP TABLE IF EXISTS test");
var promise1 = script.get(url, {jsonp: "jsonp", query: {jsonp_escape: 1}});
url += encodeURIComponent("CREATE TABLE test(id INT)");
var promise2 = script.get(url, {jsonp: "jsonp", query: {jsonp_escape: 1}});
Unfortunately, that is not going to work. The HTTP request for the DROP may arrive at your proxy after the CREATE table one. Thus, the CREATE could fail. The recursive approach makes sure that all SQL statements are processed in the order given.
Next post: a MySQL dojo store
Armed with this, you should be able to glue pieces together to get started. Keep in mind that SQL over HTTP bares a risk. The URL of your proxy script is in your HTML document. Your proxy script will accept any SQL – any. For prototyping or in secured environments this may still be very much acceptable. However, it should not be difficult to derive a simple REST interface given the above cut&paste pieces to get started.
The next post is on creating a dojo data store for MySQL. Many dojo/dijitt widgets, for example, the data grid (spreadsheet) accept stores as data sources. A sortable spreadsheet with dojo and MySQL? Doable…
Happy hacking!
Pingback: Connecting MySQL and the Dojo Toolkit: MySQL data store | Ulf WendelUlf Wendel