Direct Discovery - can QlikView delete row in database?

[#1]

Dear community,

Is it possible for QlikView to delete row in datamart?

Requirement:

1. select few rows in list box

2. create a delete button, once press/trigger, activate delete in the script:

     TableBlaBla:

     DIRECT DELETE

          ABC

     FROM TableBlaBla

     WHERE ABC = 'haha';

Anyone try this out and it is working?

Thanks and best regards,

Chanel

[amars]

Hi Chanel,

From my past experiences we can use to insert, delete from a SQL Server but on Oracle it fails.

To Achieve the above you can write a Macro and can execute it on the button click.

Function DeleteData(ValuesToDelete)

dim sConn, oConn

Set oConn = CreateObject("ADODB.Connection")

sConn = "Provider=MSDAORA;User ID="yourlogin";Password="yourpassword";Data Source="your data source";OLEDB.NET=true;SPPrmsLOB=true;Persist Security Info=False;"

oConn.Open sConn

sSQL="Delete from Tablename Where FieldName in " & ValuesToDelete

oConn.Execute(sSQL)

oConn.Close

Set oConn = Nothing

End Function

You will need to enable the "Script (Allow Database Write and Execute Statements)" property inside User Preferences -> Security.

Hope this helps.

Thanks

[#3]

Hi

upto my knowledge it is not possible in qlikview

[Anonymous]

Chanel

I would be very wary of using QlikView to write back to the database.  Direct Discovery was not designed for this, indeed I have seen QlikTech advise using a read only database connection.

Notwithstanding I get asked about this kind thing every now & then, and if you give it a try I'd be interested in your experiences.

Best Regards,     Bill

[#5]

I don`t believe it`s possible, since the qlikview is a tool for data analysis.

[#6]

Thanks pretty much! I will test this out once my datamart is ready.

[#7]

Dear Michael,

I got this error : Invalid connection string attribute, do you have any idea on this?

Thanks and best regards,

Chanel

[#8]

Hi Chanel,

Did you resolve this ? I too had issues to delete table in MS access, but tried using excel vba to do the same and used the same connection string in QV and it worked.

see the example below

fnm=ActiveDocument.Variables("vfiles").GetContent().String

AppPath = "S:\Phase I\After Run1\Database v1.1.accdb"
Set objADO = CreateObject("ADODB.Connection")

objADO.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & AppPath & ";"

objADO.Execute("delete * from Basedata where Filename="&chr(34)& fnm &chr(34))

objADO.Close

Basically test the connection in Excel or other application and bring it to the QV. It should work.

kamal

[hugmarcel]

Another example:

Sub DeleteFrom_Oracle_DB
  Dim objADO
  Dim strSQL
 
  'Create ADO Object
  Set objADO = CreateObject("ADODB.Connection")

  'Establish a connection: See https://www.connectionstrings.com/oracle/ for valid connection types
  objADO.Open "Provider=OraOLEDB.Oracle;Data Source=<service_name>;User Id=<user>[<schema>];Password=<password>"

  'Set SQL to e.g. delete rows
  strSQL="DELETE FROM <schema>.<table> WHERE <field> LIKE '<your_value>'"

  'Begin Transaction
  objADO.BeginTrans

  'Execute Transaction
  objADO.Execute strSQL, RecordsAffected
  objADO.CommitTrans
  objADO.Close

  Msgbox RecordsAffected
End Sub