topic Re: Macro to update database in QlikView

Macro to update database

sunny_talwar — Fri, 15 Jun 2018 14:52:26 GMT

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‌

Re: Macro to update database

wdchristensen — Fri, 15 Jun 2018 15:10:50 GMT

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.

Re: Macro to update database

sunny_talwar — Fri, 15 Jun 2018 15:13:02 GMT

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

Re: Macro to update database

wdchristensen — Fri, 15 Jun 2018 15:35:05 GMT

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

snpData.Update strSQL, dbMyDBConnection

Re: Macro to update database

sunny_talwar — Fri, 15 Jun 2018 15:36:36 GMT

Nope, that did not work

Re: Macro to update database

wdchristensen — Fri, 15 Jun 2018 15:40:01 GMT

snpData.Execute strSQL, dbMyDBConnection

???

Re: Macro to update database

sunny_talwar — Fri, 15 Jun 2018 15:41:36 GMT

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.

Re: Macro to update database

wdchristensen — Fri, 15 Jun 2018 15:49:14 GMT

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.

Re: Macro to update database

sunny_talwar — Fri, 15 Jun 2018 15:55:10 GMT

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?

Re: Macro to update database

wdchristensen — Fri, 15 Jun 2018 16:12:49 GMT

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?

Re: Macro to update database

sunny_talwar — Fri, 15 Jun 2018 16:33:25 GMT

I just made that up for sharing, but I am using the database name for DataSource....

Re: Macro to update database

wdchristensen — Fri, 15 Jun 2018 16:57:23 GMT

I think issue is that you are using the database name where the DSN would go. Maybe something like:

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

or create a DSN with the name db.

Re: Macro to update database

sunny_talwar — Fri, 15 Jun 2018 17:03:50 GMT

I am not sure what the ServerName would be.... but I did create DSN and that did not help. But having done some more test, I think the connection string might be working... because if I change db to something else... it doesn't go past the connection string. So, seems like something else is a problem. Checking with another query

Re: Macro to update database

sunny_talwar — Fri, 15 Jun 2018 17:06:35 GMT

Just saw that I was using the semi-colon which might have been causing some issue for the query itself... now that SELECT works... moving to update next

Re: Macro to update database

sunny_talwar — Fri, 15 Jun 2018 18:06:10 GMT

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