Solved: Re: Macro to update database - Qlik Community

sunny_talwar · ‎2018-06-15

Hey Guys - I am trying to run this... but it doesn't do anything... Any hints on what I might be doing wrong

SUB UpdateRecord


 'Save as ConnectDB.vbs
 Dim strSQL
 Dim strUsername
 Dim strPassword
 Dim snpData
 Dim dbMyDBConnection
 Dim Dimension        

 Set snpData = CreateObject("ADODB.Recordset")
 Set dbMyDBConnection = CreateObject("ADODB.Connection")
 strUsername = "User"
 strPassword = "Pass"

 dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=db;User ID=" & strUsername & ";Password=" & strPassword & ";"
 dbMyDBConnection.Open

 Dimension = "A"
 strSQL = "UPDATE TABLE_NAME SET FIELD1 = 'YES' WHERE DIM = '" & Dimension & "';"
 strSQLCommit = "COMMIT;"

 snpData.Open strSQL, dbMyDBConnection
 snpData.Open strSQLCommit, dbMyDBConnection

 snpData.Close
 dbMyDBConnection.Close

 Set snpData = Nothing
 Set dbMyDBConnection = Nothing

END SUB

marcus_sommer‌, tamilarasu‌

sunny_talwar · ‎2018-06-15

So this is what finally worked for me

'Save as ConnectDB.vbs
 Dim strSQL
 Dim snpData
 Dim dbMyDBConnection
 Dim vSelectionDim
 Dim vSelectionTradeDate


SUB UpdateRecord

 Set snpData = CreateObject("ADODB.Recordset")
 Set dbMyDBConnection = CreateObject("ADODB.Connection")

 dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=Db;User Id=username;Password=pass;"
 dbMyDBConnection.Open

 vSelectionDim = ActiveDocument.Evaluate("DIM")
 vSelectionTradeDate = ActiveDocument.Evaluate("Date(Date, 'MM/DD/YYYY')")

 strSQL = "UPDATE APPPNLADMIN.TABLE_NAME SET FIELD1 = 'Yes' WHERE DIM = '" & vSelectionDim & "' and DATE = TO_DATE('" & vSelectionTradeDate & "', 'MM/DD/YYYY')"
 strSQLCommit = "COMMIT"

 snpData.Open strSQL, dbMyDBConnection
 snpData.Open strSQLCommit, dbMyDBConnection



 'snpData.Close

 dbMyDBConnection.Close

 Set snpData = Nothing
 Set dbMyDBConnection = Nothing



END SUB

View solution in original post

wdchristensen · ‎2018-06-15

Hi Sunny,

The code looks good but the database could be locked by another transaction. You might try to execute the update code outside of a script just to verify that it isn't something on the database side.

sunny_talwar · ‎2018-06-15

This is a dummy table I created just for testing this out. One thing I did find was that all the Dims need to come before the Sub...

Also, I think the problem is something related to the connection string itself because this is where it have issues

snpData.Open strSQL, dbMyDBConnection

wdchristensen · ‎2018-06-15

snpData.Open I believe is only for selecting data. Maybe try:

snpData.Update strSQL, dbMyDBConnection

sunny_talwar · ‎2018-06-15

Nope, that did not work

wdchristensen · ‎2018-06-15

snpData.Execute strSQL, dbMyDBConnection

???

sunny_talwar · ‎2018-06-15

That did not work as well. It there a way to know if we at least had a connection? I am not sure if we even got connected or not.

wdchristensen · ‎2018-06-15

If you change strSQL from an “update” to a “select”, maybe you can count the number of items in the recordset.

Dim cntRecords

strSQL = "SELECT * FROM TABLE_NAME WHERE DIM =’A’"

snpData.Open strSQL, dbMyDBConnection

cntRecords =snpData.RecordCount

If cntRecords is greater than zero we know that the connection is working.

sunny_talwar · ‎2018-06-15

So, this is what I got...

msgbox 5

snpData.Open strSQL, dbMyDBConnection

cntRecords = snpData.RecordCount

msgbox cntRecords

msgbox 6

I get the popup 5, but it never goes to 6 or cntRecords. So, it has to be the connection, right?

wdchristensen · ‎2018-06-15

I agree, something is up with the connection.

dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=db;User ID=" & strUsername & ";Password=" & strPassword & ";"

in your example you used "db" for the data source, is that valid DSN for the connection?