6 Replies Latest reply: Sep 26, 2017 9:33 AM by Mathias Schindler RSS

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

    omar bensalem

      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

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

          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