Connecting to web services and writing back to database

[#1]

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

[Alexander_Thor]

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

[IAMDV]

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

[RSvebeck]

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

[#5]

Hi Deepak,

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

Thanks,

Archana

[IAMDV]

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

[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

[IAMDV]

Hi Lee,

Thanks for sharing. Very useful.

Cheers,

DV