Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

Is anyone familiar with mashups? (InputBox to store into MySql db?Then get the stored column?) Php? Javascript?Jquery?Node.js?

Is there anyone with a high web affinity to guide through me this scenario PLEASE?

I want to have a mashup that contains:

Some Qlik Sense charts +

2 input Boxes:

to enter:

- commentaries

-and their userName

Once they submit; the commentaries and entered UserName must be stored in a column within a mysql table.

Then we'll have a list of the userNames, if we select a name, we'll have a table of all their commentaries.

The operation must be in "real-time"; the post and get process.

This is the idea, but I DON'T have ANY CLUE on how to do this.

Can ANYONE please help me?

I'm a complete newbie when It comes to web technologies

I've always come to help , now I need yours

1 Solution

Accepted Solutions
m_s
Partner - Creator II
Partner - Creator II

Hello omar,

I wanted to try out node.js anyways so I created a small POC for your issue. I appended a ZIP archive containing a small rest-api web-application with 3 resource-urls:

GET /listUsers

GET /listComments/{username}

POST /addComment

As I'm also new to node, the following links helped in building this app:

https://www.tutorialspoint.com/nodejs/nodejs_restful_api.htm

https://www.w3schools.com/nodejs/nodejs_mysql.asp

Setup

I created a new folder inside the "C:\Program Files\Qlik\Sense" directory called "CustomWebservices" (because I'll probably write more services like that in the future) and copied the "commenter-mysql" directory from the zip archive into it.


Then you need to export your Qlik Sense Server certificate using QMC. I used 1234 as password but you can enter anything you want (if you do you need to modify the "certPass" property inside the config.json file).

You can also change the webserver port (atm its 2222) and mysql config (hostname, user, database etc.) here.


I created a database named "test_db" with a table "comments" containing two columns: "username" and "comment" on my local mysql server. As your table most likely has a different schema you should open the app.js file and modify the SQL statements.


After that just double click the start.bat file which will use the "\Qlik\Sense\ServiceDispatcher\Node\node.exe" to start the app.js application. If this is successfull you should see "server started" in the console.


Then navigate to https:\\QLIKSENSESERVER:2222/listUsers and if everything went right you should see an empty json array []. Otherwise you'll see an error in the browser and the console.


Mashup

I also built a small mashup using only jquery for adding html elements:

var webserviceUrl = 'https://' + window.location.hostname + ':2222';

require( ["js/qlik" ], function ( qlik ) {

var container = $('<div class="row" style="border: 1px solid lightgray; padding: 4px"/>')

var userInput = $('<input type="text" class="lui-input" placeholder="Username..."/>');

var commentInput = $('<input type="text" class="lui-input" placeholder="Comment..."/>');

var sendButton = $('<button class="lui-button">Send</button>');

var userList = $('<select class="lui-select"/>');

var resultTable = $('<table></table>');

$.get(webserviceUrl + '/listUsers').done(function(result){

console.log('users:', result);

userList.append('<option>Please select user...</option>');

userList.append(result.map(u => $('<option>')

.val(u.username)

.text(u.username)));

});

userList.change(function(){

var selectedUser = userList.val();

$.get(webserviceUrl + '/listComments/' + selectedUser).done(function(result){

console.log('comments for ' + selectedUser, result);

resultTable.empty();

resultTable.append(result.map(c => $('<tr><td>' + c.comment  +'</td></tr>')));

});

})

sendButton.click(function(){

console.log('sending...');

var postBody = JSON.stringify({

user: userInput.val(),

comment: commentInput.val()

});

$.ajax({

url: webserviceUrl + '/addComment',

method: 'POST',

data: postBody,

contentType: 'application/json'

}).done(function(result){

console.log('inserted!');

userList.change();

})

});

// style and prepen to site-container

[userInput, commentInput, sendButton, userList, resultTable ].forEach(e => {

e.css('margin-top', '10px');

container.append(e);

});

$('div.container').prepend(container);

});

Here you can see it in action: https://i.gyazo.com/56894ffa7ecd6f717dfefe2a1cbf6634.mp4

I hope this example helps you solve your problem,

Mathias

View solution in original post

8 Replies
m_s
Partner - Creator II
Partner - Creator II

Hello omar,

I wanted to try out node.js anyways so I created a small POC for your issue. I appended a ZIP archive containing a small rest-api web-application with 3 resource-urls:

GET /listUsers

GET /listComments/{username}

POST /addComment

As I'm also new to node, the following links helped in building this app:

https://www.tutorialspoint.com/nodejs/nodejs_restful_api.htm

https://www.w3schools.com/nodejs/nodejs_mysql.asp

Setup

I created a new folder inside the "C:\Program Files\Qlik\Sense" directory called "CustomWebservices" (because I'll probably write more services like that in the future) and copied the "commenter-mysql" directory from the zip archive into it.


Then you need to export your Qlik Sense Server certificate using QMC. I used 1234 as password but you can enter anything you want (if you do you need to modify the "certPass" property inside the config.json file).

You can also change the webserver port (atm its 2222) and mysql config (hostname, user, database etc.) here.


I created a database named "test_db" with a table "comments" containing two columns: "username" and "comment" on my local mysql server. As your table most likely has a different schema you should open the app.js file and modify the SQL statements.


After that just double click the start.bat file which will use the "\Qlik\Sense\ServiceDispatcher\Node\node.exe" to start the app.js application. If this is successfull you should see "server started" in the console.


Then navigate to https:\\QLIKSENSESERVER:2222/listUsers and if everything went right you should see an empty json array []. Otherwise you'll see an error in the browser and the console.


Mashup

I also built a small mashup using only jquery for adding html elements:

var webserviceUrl = 'https://' + window.location.hostname + ':2222';

require( ["js/qlik" ], function ( qlik ) {

var container = $('<div class="row" style="border: 1px solid lightgray; padding: 4px"/>')

var userInput = $('<input type="text" class="lui-input" placeholder="Username..."/>');

var commentInput = $('<input type="text" class="lui-input" placeholder="Comment..."/>');

var sendButton = $('<button class="lui-button">Send</button>');

var userList = $('<select class="lui-select"/>');

var resultTable = $('<table></table>');

$.get(webserviceUrl + '/listUsers').done(function(result){

console.log('users:', result);

userList.append('<option>Please select user...</option>');

userList.append(result.map(u => $('<option>')

.val(u.username)

.text(u.username)));

});

userList.change(function(){

var selectedUser = userList.val();

$.get(webserviceUrl + '/listComments/' + selectedUser).done(function(result){

console.log('comments for ' + selectedUser, result);

resultTable.empty();

resultTable.append(result.map(c => $('<tr><td>' + c.comment  +'</td></tr>')));

});

})

sendButton.click(function(){

console.log('sending...');

var postBody = JSON.stringify({

user: userInput.val(),

comment: commentInput.val()

});

$.ajax({

url: webserviceUrl + '/addComment',

method: 'POST',

data: postBody,

contentType: 'application/json'

}).done(function(result){

console.log('inserted!');

userList.change();

})

});

// style and prepen to site-container

[userInput, commentInput, sendButton, userList, resultTable ].forEach(e => {

e.css('margin-top', '10px');

container.append(e);

});

$('div.container').prepend(container);

});

Here you can see it in action: https://i.gyazo.com/56894ffa7ecd6f717dfefe2a1cbf6634.mp4

I hope this example helps you solve your problem,

Mathias

OmarBenSalem
Author

I LOVE YOU

Thank you very much for your time !!!

OmarBenSalem
Author

Hi Mathias and thanks again for your great contribution:

I have some questions though :

you said:

I created a database named "test_db" with a table "comments" containing two columns: "username" and "comment" on my local mysql server. As your table most likely has a different schema you should open the app.js file and modify the SQL statements.

1) What's your database? can you please give a download link? I tried to install Mysql but didn't know what exactly to install..

2) I'm using DB Browser for SqlLite (which is in my local machine) while the Qlik Sense is on a server machine:

Capture.PNG

so what should I change EXACTLY in the app.js?

ps: I'd prefer to have the same database 'Mysql) you're using so that I won't have nothing to change but still wont to know where to change things when my db is on another machine (local)

as for the mashup, you created it using sense dev-hub? or with a notepad for exp? and if so the language I choose is html?

I know that are some dump questions.. I already said am a noob

m_s
Partner - Creator II
Partner - Creator II

Ok I thought you already had an existing mysql-database that is used for storing comments.

I use XAMPP-portable as my local development environment: https://sourceforge.net/projects/xampp/files/XAMPP%20Windows/5.6.31/xampp-portable-win32-5.6.31-0-VC...

Just download the .7z-file, extract it to C:\xampp, run setup_xampp.bat then start xampp-control.exe. From there you can start Apache (Webserver) and MySql (Database). By default xampp will use the port 80 which means you can enter http://localhost/phpmyadmin into the browser and you should see the mySql-Administration page.

If you already have an IIS webserver or another application listening to port 80 you need to modify the C:\xampp\apache\conf\httpd.conf file:

Just search for "Listen" (should be around line 58/60) and replace :80 with e.g. :888. Then enter http://localhost:888/phpmyadmin into the browser and you should be good to go.

My database can be recreated by executing the following SQL:

CREATE DATABASE IF NOT EXISTS `test_db` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

USE `test_db`;

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `comments`

--

CREATE TABLE `comments` (

  `username` varchar(100) NOT NULL,

  `comment` varchar(1024) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

A word of caution: XAMPP is not meant to be used as a production enviroment, so if you use this for a customer be aware of possible security risks!

You can also use sqlite if you want to dig deeper into node js (NPM-package: sqlite3‌).

@Mashup

I appended my mashup files but yes, it's just a basic mashup template created from dev-hub. I stripped most of the generated html and generate every element inside the .js-file.

Regards,

Mathias

OmarBenSalem
Author

Mathias, is there a way to test this diretly with QLIK SENSE DESKTOP?

m_s
Partner - Creator II
Partner - Creator II

I don't use Qlik Sense Desktop but I don't see why not (if you are allowed to create mashups in Qlik Sense Desktop).

Mathias

stevenkoppens
Partner - Contributor III
Partner - Contributor III

Hi Mathias,

Great post! This works for me when I RDP to the server, however it doesn't if I try to reach it from the web.

Since we use Auth0 to authenticate (SAML), we use an url like: https://servername/auth0/hub. I think in order to redirect to a port, I need to have something like https://servername/comments, any ideas on this?

Thanks!

Steven

m_s
Partner - Creator II
Partner - Creator II

Hello Steven,

did you implement Auth0 in the express-webservice or are you just using it for Qlik Sense?

The express-webservice should be available under the port you specified in the config.json (by default 2222). Maybe the port needs to be forwarded in your firewall / security group?

Mathias