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!