7 Replies Latest reply: May 25, 2012 4:43 AM by Deepak Vadithala RSS

    Connecting to web services and writing back to database

      Hi,

       

      I have requirement where users should have the option to select certain KPIs from a list box and input some field values. So everytime based on whatever changes they do to the selection and input fields, QlikView should send this as a parameter to SPSS or SAS application and the application them manipulates the values and writes back to the database. We should then be able to get the updated values from the database.

       

      So while all this happens in background at the QV user end it should be seamless as the usual selection criteria submission and data refresh.

       

      As I mentioned this should be dynamic and not just ones during the load.

       

      Appreaciate any help. Let me know if I am not clear on the question.

       

      Thanks

        • Connecting to web services and writing back to database
          Alexander Karlsson

          Extensions are by default client side actions executed in the context of the users browser so you would need to deal with authentication in some way.

           

          Anyhow i would probably build an object extension so you would require some user feedback before calling your webservice. Since we do fire update events, which is good, but with our associative nature everything in your application will be considered an update in the application.

           

          You clicked a list box on a totally different sheet? The underlaying data model updates which also causes your selections to update which in turn fires our update event and your extension code is executed.

           

          So worst case you would be sending tons of requests against your webservice with the same data over and over again.

           

          Hook up your own event handler to your object and send data on user interaction instead, just my 2 cents

          • Re: Connecting to web services and writing back to database
            Deepak Vadithala

            Hi,

             

            I have done something similar where QV writes back to SQL Server. I have used System Meta Data functions() in QlikView to capture the current selection states based on in-built events. And these values are stored in QV Variables and I have create abstraction layer to transform these variables. My abstraction layer contains QV Variables which generates SQL Insert Statments. And finally, I have used VB Script to open & close OLEDB connection which writes to SQL Server Table. I have also written constraints to validate the selection state by using QV Variables for error handling. Also implemented constraints in SQL Server for data integrity purposes.

             

            I hope this makes sense.

             

            Good luck.

             

            Cheers,

            DV

            www.QlikShare.com

              • Re: Connecting to web services and writing back to database

                Hi Deepak,

                 

                Would you mind sharing the code or any support files? Appreciate your response.

                 

                Thanks,

                Archana

                  • Re: Connecting to web services and writing back to database
                    Deepak Vadithala

                    Hi Archana,

                     

                    Sorry, I can't share the entire application but I'm sharing the VB Script which writes to SQL Server by using OLEDB connection. As mentioned above this will be last step where I'm storing the values in variables and writing these variables to SQL Server.

                     

                    I hope this helps.

                     

                    Sub WriteToSQLServer()

                    Dim MyServer, MyConn, MyConnOpen, MyRecordSet, MySQLString

                            '===========================================================================

                            MyServer = "100.100.100.100"

                            MyConn = "Provider=SQLOLEDB.1; Initial Catalog=DB_Maintenance; Data Source=" & MyServer

                            Set MyConnOpen = CreateObject("ADODB.Connection")

                            MyConnOpen.Open MyConn, "Test", "test"

                            Set MyRecordSet =CreateObject("ADODB.Recordset")

                     

                            Set MyVariable =  ActiveDocument.GetVariable("vMySQLStatement")

                            MySQLString = MyVariable.GetContent.String       

                     

                            MyRecordSet.Open MySQLString, MyConnOpen

                            MsgBox "You have updated the data for " & MyDateString

                     

                            '===========================================================================

                    End Sub

                     

                    Good luck!

                     

                    Cheers,

                    DV

                    www.QlikShare.com

                • Re: Connecting to web services and writing back to database
                  Robert Svebeck

                  This can be solved without extensions, by using a macro that is either triggered by a variable-trigger, or a button. The macro can either createa textfile that is imported into SPSS, or it could problably also write directly into SPSS (I have done something similar against an SQL database where my macro executed a stored procedure).


                  Best Regards Robert Svebeck

                  • Re: Connecting to web services and writing back to database
                    Lee Matthews

                    Hi Archana

                     

                    Try this example http://community.qlik.com/docs/DOC-2784

                    It uses an ASP page to process the XML submitted, but the concepts are all the same.

                     

                    Regards

                    Lee