Updating SQL Table through QlikView

[niegel]

is this possible?

I use QlikView to identfy record owner conflict and would like to update the SQL DB based on the query result.

[#2]

Hi Niegel,

No this is not possible since QlikView per definition accesses databases in read-only mode.

regards Mark

[Anonymous]

Mark,
It is possible to use read/write mode. I used it to cerate and to drop index, but it could be used for any SQL statement.
(Or maybe it was changed recently? I did it long ago, probably in QV 5...)

[#4]

It can be done using a Macro

Here is an example.

Hope that helps

Philippe

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

sub Connection

set conn=CreateObject("ADODB.Connection")
conn.Open "CONQ"
set rs=CreateObject("ADODB.recordset")
risque = getVariable("Change")

set inventory = ActiveDocument.Fields("codeinv").GetPossibleValues

for i = 0 to inventory.Count-1

' Database Update

sql="UPDATE invd3 SET "
sql=sql & "risqueqte='" & risque & "'"
sql=sql & " WHERE codeinv='" & inventory.item(i).Text & "'"

on error resume next
conn.Execute sql
if err <> 0 then
msgbox("No update permissions! " & err )
end if
'End of the Update
Next
conn.close
end sub

function getVariable(varName)
set v = ActiveDocument.Variables(varName)
getVariable = v.GetContent.String
end function

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

[niegel]

Philippe,

This is interesting. I'll try it out and give my feedback about this process.

Niegel

[#6]

Very interesting. Can somebody confirm that you can update data sources from a reload script in the current QlikView version (8.5 or 9)?

[#7]

it's in 9

[#8]

Hi,

It is possible to write back to database through macros irrespective of script privilages in the edit script section.

Using vb script I am writing back to the database(MYSQL) and selecting the data from database(MYSQL) with reloading of QVW file. Macros are triggered while opening the sheet.

[#9]

Using a restful api it makes it simple too.