topic Re: Direct Discovery - can QlikView delete row in database? in QlikView

Direct Discovery - can QlikView delete row in database?

Thu, 26 Sep 2013 06:41:06 GMT

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

Re: Direct Discovery - can QlikView delete row in database?

amars — Thu, 26 Sep 2013 07:34:30 GMT

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

Re: Direct Discovery - can QlikView delete row in database?

Thu, 26 Sep 2013 09:37:39 GMT

upto my knowledge it is not possible in qlikview

Re: Direct Discovery - can QlikView delete row in database?

Anonymous — Thu, 26 Sep 2013 09:57:05 GMT

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

Re: Direct Discovery - can QlikView delete row in database?

Thu, 26 Sep 2013 12:25:53 GMT

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

Re: Direct Discovery - can QlikView delete row in database?

Thu, 26 Sep 2013 12:55:09 GMT

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

Re: Direct Discovery - can QlikView delete row in database?

Thu, 03 Oct 2013 06:41:35 GMT

Dear Michael,

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

Thanks and best regards,

Chanel

Re: Direct Discovery - can QlikView delete row in database?

Fri, 22 Nov 2013 12:10:06 GMT

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

Re: Direct Discovery - can QlikView delete row in database?

hugmarcel — Mon, 09 Apr 2018 13:38:24 GMT

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