Connecting SAPUI5 with MySQL
Hello Readers,
Here I am going to show how to connect an SAPUI5 application to a MySQL database using PHP.
Prerequisites –
- Install WAMP server / XAMPP server.
- Install PHP.Usually comes in built with the server.
Step 1 – Create a database and a table,Insert content.
In my case I am using PHPMyAdmin to create a database (Employee).In it I am creating a table (user) and within it I am inserting some records.
Step 2- Create a SAPUI5 application.
Here,I am creating an SAPUI5 application,wherein I am creating an empty JSON model and calling an ajax function with source to my php file.On the success function,to the JSON model,I set the data recieved from my PHP file.
Afterwards I am showing the data in a table(ui.table.Table), by setting the JSON model.
As this is an basic program,all the code is implemented in the index.html file itself.
My index.html file goes like this :-
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta http-equiv='Content-Type' content='text/html;charset=UTF-8'/>
<script src="https://sapui5.hana.ondemand.com/resources/sap-ui-core.js"
id="sap-ui-bootstrap"
data-sap-ui-libs="sap.m,sap.ui.table,sap.ui.commons"
data-sap-ui-theme="sap_bluecrystal">
</script>
<!-- only load the mobile lib "sap.m" and the "sap_bluecrystal" theme -->
<script>
var json = new sap.ui.model.json.JSONModel();
$.ajax({
url: 'tableData.php',
async:false,
success: function(data) //on recieve of reply
{
json.setData(JSON.parse(data));
},
error: function(err)
{
console.log(err);
}
});
var tableo = new sap.ui.table.Table({
title : "Arjun's First Table",
visibleRowCount : 5
});
tableo.addColumn(new sap.ui.table.Column({
label : new sap.ui.commons.Label({text:"Userid"}),
template : new sap.ui.commons.TextField().bindProperty("value","Userid"),
width : "140px"
}));
tableo.addColumn(new sap.ui.table.Column({
label : new sap.ui.commons.Label({text:"Name"}),
template : new sap.ui.commons.TextField().bindProperty("value","Name"),
width : "140px"
}));
tableo.addColumn(new sap.ui.table.Column({
label : new sap.ui.commons.Label({text:"Age"}),
template : new sap.ui.commons.TextField().bindProperty("value","Age"),
width : "140px"
}));
tableo.addColumn(new sap.ui.table.Column({
label : new sap.ui.commons.Label({text:"Email"}),
template : new sap.ui.commons.TextField().bindProperty("value","Email"),
width : "140px"
}));
tableo.setModel(json)
tableo.bindRows("/result");
tableo.placeAt("content");
</script>
</head>
<body class="sapUiBody" role="application">
<div id="content"></div>
</body>
</html>
Step 3- Write the PHP file
In my PHP file tableData.php, I am writing a query to the database and sending the data to front end in JSON format.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "employee";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "select * from user";
$res = mysqli_query($conn,$sql);
$result = array();
while($row = mysqli_fetch_array($res)){
array_push($result,
array('Userid'=>$row[0],'Name'=>$row[1],'Age'=>$row[2],'Email'=>$row[3]));
}
echo json_encode(array('result'=>$result));
?>
Step 4 – Now execute the index.html –
Thus, we have successfully displayed MySQL table data into an SAPUI5 table.
Hi Arjun,
Thanks for your example. Using your code for example, the data of my database don't appear in the browser.
Where do you put your "tableData.php" file? At the root of your application?
It would seem I don't succeed to access to this file.
The error message FireFox sent to me, below :
responseText: "<html>\n<head>\n<meta http-equiv=\"Content-Type\" content=\"text/html;charset=utf-8\"/>\n<title>Error 404 Not Found</title>\n</head>\n<body><h2>HTTP ERROR 404</h2>\n<p>Problem accessing /Hello/tableData.php. Reason:\n<pre> Not Found</pre></p><hr><a href=\"http://eclipse.org/jetty\">Powered by Jetty:// 9.3.9.v20160517</a><hr/>\n\n</body>\n</html>\n"
Could you help me, please?
Thanks and Regards.
Ewen.
Hi Former Member,
Thank you for trying out the example.
Yes you have to place the tableData.php file at your root folder i.e. the webContent folder of your application with the index.html file.
The folder structure should look something like this :
Within your tableData.php, you can write php code to fetch your data from the database and send it to your java script in JSON format.
Hope this helps,
Regards.
Hi Arjun,
Thank you for your response.
For me the php file appears like in the image attached.
Now, I have this error message :
________________________________________________________________
Erreur d’analyse XML : aucun élément trouvé
Emplacement : http://localhost:60003/Hello/tableData.php
Numéro de ligne 27, Colonne 3 :
tableData.php:27:3
SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data
_________________________________________________________________
Can you help me again?
Thanks and Regards.
Ewen.
Hi Former Member,
Sure I can try to help you with that.
I too faced a similar issue a week ago. As per my experience, the problem is in your PHP file i.e. the data being sent from your PHP file to your AJAX is not in JSON format. Could you log the data in your success call-back function and check once. In my case the SELECT query in my PHP was creating some problems, due to which the "echo" from PHP was not in JSON format and when I was parsing that in my AJAX, it was throwing the same error.
Hope this helps,
Regards.
Hi Arjun,
Really, I think the problem is in my php file in my application.
If I launch directly my php file in browser without use Eclipse, the code sends me "true". Then, the code is good.
But in Eclipse, I don't see how to parse the "echo" of my php file. I do different tests but without success.
Thanks and Regards.
Ewen.
Hi Former Member,
If you could send me your application in mail or share your application screen, then I can try to debug the error for you.
Regards.
Hi Arjun,
The code of my index.html file, below :
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge"/>
<meta charset="UTF-8"/>
<title>Hello</title>
<script id="sap-ui-bootstrap" src="https://sapui5.hana.ondemand.com/resources/sap-ui-core.js" data-sap-ui-theme="sap_bluecrystal" data-sap-ui-libs="sap.m,sap.ui.table,sap.ui.commons"> </script>
<script> var json = new sap.ui.model.json.JSONModel(); $.ajax({
url: 'tableData.php',
async:false,
success: function(data)
{
json.setData(JSON.parse(data));
},
error: function(err)
{
console.log(err);
} });
var oTable = new sap.ui.table.Table({
title : "hellotable",
visibleRowCount : 5 });
oTable.addColumn(new sap.ui.table.Column({ l
abel : new sap.ui.commons.Label({text:"Identifiant"}),
template : new sap.ui.commons.TextField().bindProperty("value","Identifiant"), width : "150px"
}));
oTable.addColumn(new sap.ui.table.Column({
label : new sap.ui.commons.Label({text:"Bonjour"}),
template : new sap.ui.commons.TextField().bindProperty("value","Bonjour"), width : "150px"
}));
oTable.setModel(json);
oTable.bindRows("/result");
oTable.placeAt("content");
</script>
</head>
<body class="sapUiBody" role="application">
<div id="content"></div>
</body>
</html>
The code of my tableData.php file, below :
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "hello";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "select * from hellotable";
$res = mysqli_query($conn,$sql);
$result = array();
while($row = mysqli_fetch_array($res)){
array_push($result,
array('Identifiant'=>$row[0],'Bonjour'=>$row[1]));
}
echo json_encode($result);
?>
When I launch the application, I have 2 error messages :
The first :
Erreur d’analyse XML : aucun élément trouvé
Emplacement : http://localhost:52375/Hello/tableData.php
Numéro de ligne 27, Colonne 3 :
tableData.php:27:3
The second :
SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data
It would seem the sapui5 application doesn't succeed to access to the tableData.php file
I have tried to sent the application.
Thanks and Regards.
Hi Former Member,
If your SAPUI5 application would not have succeeded to access the tableData.php file, you would have got the 404 file not found error, so I guess the application can access the file. By analysing your PHP code, I would suggest you to echo the $result and $row variables and check the output, the problem is that your select query is not returning an JSON parsable string. Do you have any BLOB data in your table.
Regards.
Hi Arjun,
Thanks for your response.
I don't have BLOB data in my table, just an integer and a char. I have forgotten to update my tableDtata.php in wamp (C:\wamp64\www\demo\tableData.php). With the same tableData.php code present in my application, I have a 404 file not found error.
abort: function abort()
always: function always()
complete: function add()
done: function add()
error: function add()
fail: function add()
getAllResponseHeaders: function getAllResponseHeaders()
getResponseHeader: function getResponseHeader()
overrideMimeType: function overrideMimeType()
pipe: function then()
progress: function add()
promise: function promise()
readyState: 4
responseText: "<html>\n<head>\n<meta http-equiv=\"Content-Type\" content=\"text/html;charset=utf-8\"/>\n<title>Error 404 Not Found</title>\n</head>\n<body><h2>HTTP ERROR 404</h2>\n<p>Problem accessing /Hello/table.php. Reason:\n<pre> Not Found</pre></p><hr><a href=\"http://eclipse.org/jetty\">Powered by Jetty:// 9.3.9.v20160517</a><hr/>\n\n</body>\n</html>\n"
setRequestHeader: function setRequestHeader()
state: function state()
status: 404
statusCode: function statusCode()
statusText: "Not Found"
success: function add()
then: function then()
__proto__: Object { … }
index.html:36:10
It would seem the application doesn't succeed to access. Would you like I sent my application to you?
Thanks and Regards.
Ewen.
Hi Former Member,
Sure, you could send your application to me, my mail id : arjunbiswas0999@gmail.com
Also if possible send your sql data base, by exporting it to an .sql file, It would make my debugging process easier.
Regards,
Arjun Biswas
Hi Arjun,
Thank you for your email, but my message with my SAPUI application code and SQL database code has been blocked.
Regards.
Ewen.
Hi Arjun,
Like mx.google has reject the email I have sent to you with my code in attachement, I have sent to you a link via WeTransfer you can download my code.
Good receipt.
Regards.
Ewen.
Hi Former Member,
I got your error. You might be executing the application from your eclipse. But your eclipse cannot access the MySql. So you have to execute the application from the wamp server itself. Open your browser, in the address bar, enter "localhost/your_project_name", and press enter. In the below screenshot, I have highlighted, how your application address bar should look like.
Hope this helps,
Regards.
Hi Arjun,
I have copied the hello application folder in wamp64/www and opened my browser entering "localhost/hello/WebContent". No data appear at screen.
Hi Former Member,
I think you have not started your wamp server. Go to the directory where you have installed your wamp server and double click on the wampmanager.exe file as shown below.
Then wait for 2-3 min for all the services to get started and then try again.
Hope this helps,
Regards,
Arjun Biswas.
Hi Arjun,
Thanks for your response, but I have always launched wampserver to execute my application.
If I enter "http://localhost/hello/WenContent/tableData.php", my data appear at screen. But if I just enter "http://localhost/hello/WenContent/", any data appear.
With the same code to me, data appear in your browser?
Best regards.
Ewen.
Hi Former Member,
By providing the same link, data appears on my browser, check here. Maybe, we could have a chat on team viewer sometime, then I could take a look into the working of the code in your system.
Regards,
Arjun Biswas
Hi Arjun,
I have modified my windows hosts file and wamp vhosts files, but in vain.
Here is my windows hosts file :
127.0.0.1 localhost
Here is my wamp httpd-vhosts.cong file :
<VirtualHost *:80>
ServerName localhost
ServerAlias localhost
DocumentRoot "C:/wamp64/www/Hello/"
ErrorLog "logs/localhost-error.log"
Customlog "logs/localhost-access.log" common
</VirtualHost>
Here is my wamp httpd.conf file :
Include conf/extra/httpd-vhosts.conf
Are you the same code in your files?
Thanks in advance for your responses.
Best regards.
Ewen.
Can you help me with the process to solve this issue ???
My Error ---
Uncaught SyntaxError: Unexpected token < in JSON at position 0
at JSON.parse (<anonymous>)
at Object.success (index.html:38)
at p (sap-ui-core.js:2098)
at Object.fireWith [as resolveWith] (sap-ui-core.js:2098)
at h3 (sap-ui-core.js:2098)
at XMLHttpRequest.<anonymous> (sap-ui-core.js:2098)
at Object.send (sap-ui-core.js:2098)
at Function.ajax (sap-ui-core.js:2098)
at index.html:33
i guess its what you are referring to in this comment ...
Hi, I'm developing an APP in Ui5 and I have a problem when I work with Worklist-Object. I have no problem to obtain data in Worklist but I don't know how replace "expand" and metadata.xml to pass data and charge on Object view.
My program is based in this example (https://sapui5.hana.ondemand.com/#topic/6a6a621c978145ad9eef2b221d2cf21d)
Best regards,
The tutorial looks like it is pretty useful.
I am working on a UI5 project for the Autism at work team.
I was wondering is it possible to link up Sqlanywhere database with UI5 using this method?