As a back-end database I usually (preferably) use MySql.
It is a free, SQL-compliant and high performance relational database, even with lots of data.
This post is about connecting to server-side MySql databases from client-based code bases, using Object Pascal / Javascript and PHP.
Step 1
Create a MySQL database, define its tables, indexes and relationships and populate it with data.
MySql has various tools to do this.
Assuming we have a 'demo' database with a 'users' table which stores users and passwords, the SQL-DDL looks something like this
-- -- Database: `demo` -- CREATE TABLE `Users` ( `id` int(11) NOT NULL PRIMARY KEY, `user` text, `password` text ); INSERT INTO `Users` (`id`, `user`, `password`) VALUES (1, 'admin', 'admin');
Step 2
Accessing a back-end database requires a server component. These can be constructed using various development tools, for example NodeJS, websockets or otherwise - it all works.
This post however is based on using PHP.
There are many servers around based on the LAMP-stack, which have PHP installed by default. And the big advantage is that PHP servers do not have to be started or stopped explicitely. They start by being referenced and stop when done, which is great for incidental database access.
PHP servers are very simple text-files saved on the server somewhere ('demoserver.php') :
*** php 7
*** sql select statements
<?php
header("Access-Control-Allow-Origin: *");
$servername = "...server domain...";
$username = "...user...";
$password = "...password...";
$dbname = "...database...";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$user = $_POST['user'];
$sql = 'select * from Users where user="'.$user.'";';
$arr = array();
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
$arr[] = $row;
}
echo '{"rows":'.json_encode($arr).'}';
} else {
echo "0 results";
}
mysqli_close($conn);
?>
Step 3
The code above connects to the database, executes a SQL statement which is provided as a POST-parameter and returns the result as a json-encoded array.
The server is fired up using XMLHTTPREQUEST and the resulting array can be accessed client-side in it's onload event handler.
There are multiple ways of coding this :
Step 3A --- using pure javascript (in an asm block)
asm var xhttp = new XMLHttpRequest(); xhttp.open("POST", "https://...server domain.../demoserver.php"); xhttp.setRequestHeader("Content-type","application/x-www-form-urlencoded"); var encodedstr1 = window.encodeURIComponent('...user...'); sql_statement = "user=" + encodedstr1; var smscursor=''; xhttp.onload = function(e) { smscursor = JSON.parse(xhttp.responseText); rows=smscursor.rows.length; if (xhttp.responseText === '0 results') { window.alert('no such user'); } else { smscursor = JSON.parse(xhttp.responseText); rows = smscursor.rows.length; for (i = 0; i < rows; i++) { console.log(smscursor.rows[i].user + ' / ' + smscursor.rows[i].password); }; }; }; xhttp.send(sql_statement); end;
Step 3B --- in native object pascal using external references
type { Event management } JEvent = class external 'Event'; TEventHandler = function (event: JEvent): Variant; JGlobalEventHandlers = class external 'GlobalEventHandlers' public onload: TEventHandler; end; { Ajax } JXMLHttpRequest = class external 'XMLHttpRequest' public responseText: String; constructor Create; procedure open(&method: String; url: String); overload; procedure setRequestHeader(&name: String; value: String); procedure send(data: Variant); overload; end; { JSON } MyJSON = class external 'JSON' public function Parse(Text: String): Variant; overload; external 'parse'; function Stringify(const Value: Variant): String; overload; external 'stringify'; end; var JSON external 'JSON': MyJSON; var window external 'window': variant; var console external 'console': variant;
and then implement the call in Object Pascal
var xhttp := JXMLHttpRequest.Create; xhttp.open("POST", "https://...server domain.../demoserver.php"); xhttp.setRequestHeader("Content-type","application/x-www-form-urlencoded"); var encodedstr1 := window.encodeURIComponent('...user...'); sql_statement := 'user=' + encodedstr1; var smscursor := ''; JGlobalEventHandlers(xHttp).onLoad := lambda(e:JEvent) smscursor := JSON.parse(xhttp.responseText); var rows := smscursor.rows.length; if (xhttp.responseText = '0 results') then begin window.alert('no such user'); end else begin smscursor := JSON.parse(xhttp.responseText); rows := smscursor.rows.length; for var i := 0 to dbrows -1 do begin writeln(smscursor.rows[i].user + ' / ' + smscursor.rows[i].password); end; end; end; xhttp.send(sql_statement);
Step 3C --- using the QTX RTL
qtx.dom.http encapsulates XMLHTTPREQUEST (and more) and can be used as in the included xhttp project
Note 1 :
The examples are coded in PHP7. Previous versions (PHP5) have a different syntax.
Note 2 :
The alternative to XMLHTTPREQUEST is to use the FETCH api
In pure javascript this codes more densely as it makes use of promises
The Fetch api is not implemented in the QTX RTL (yet);
Note 3 :
The examples above are for SQL statements which return a cursor (SQL 'select' statements)
For statements which do not return such a result array, for instance Insert, Update, Delete, Drop etc, the code is slightly different as there is no array of rows to be exported :
*** php 7
*** sql non-select statements (insert, update, delete, drop etc)
<?php
header("Access-Control-Allow-Origin: *");
$servername = "...server domain...";
$username = "...user...";
$password = "...password...";
$dbname = "...database...";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Attempt non-select execution
$sql_statement = $_POST['sql_statement'];
if(mysqli_query($conn, $sql_statement)){
echo "SQL handled successfully.";
} else{
echo "ERROR: Not able to execute $sql. " . mysqli_error($conn);
}
// Close connection
mysqli_close($conn);
?>
****** usage : (insert etc)
var FHttp := JXMLHttpRequest.Create; FHttp.open("POST",'https://...server domain.../demoserver.php'); FHttp.setRequestHeader("Content-type","application/x-www-form-urlencoded"); var FSelect := 'INSERT INTO `test`(`id`, `webjson`) VALUES (3,"c")'; var encodedstr1 := window.encodeURIComponent(FSelect); var sql_statement := 'sql_statement=' + encodedstr1; FHttp.send(sql_statement);
If you look at how I wrapped SQLite with TDataset etc, you could do a nice wrapper for this that targets MySQL 🙂 AWESOME POST btw!!
Have you tried using firebird via node.js btw? not sure it has a JSON / REST API