Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Database Writeback Help

I've read quite a number of posts in regard to writing back to the database and had it working when I hard-coded the values but when I changed to use variables, it does not work.  When I try to run the code below using the variables, I get a "No update permissions!" error (exactly as the code says - just not sure what, exactly, I am doing wrong.

Details:

Desktop client only (no web deployment planned), write back to database (initially insert, but later edit & delete options as well).

The Macro is as follows (the real code has an actual server listed in the Data Source= portion, left it out here):

sub DataInsert

     set conn=CreateObject("ADODB.Connection")

     conn.Open "Provider='sqloledb';Data Source=<MyServer>;Initial Catalog='Rally';Integrated Security='SSPI';"

     set rs=CreateObject("ADODB.recordset")

' Database Update

     sql="INSERT INTO mw_TestTable VALUES ('" & vTestChar & "', " & vTestInt & ", '" & vTestDate & "')"

     on error resume next

     conn.Execute sql

     if err <> 0 then

          msgbox("No update permissions! " & err )

     end if

     conn.close

end sub

Thanks for any help you can provide.

Mark.

20 Replies
qlikviewwizard
Master II
Master II

Hi Mark,

Please check this link.

Re: SQL Writeback

evan_kurowski
Specialist
Specialist

Mark Worthen wrote:

' Database Update

    

    sql="INSERT INTO mw_TestTable VALUES ('" & vTestChar & "', " & vTestInt & ", '" & vTestDate & "')"

    

  

    

Mark.

 

Hello Mark,

 

One thing you may want to look into is whether the statement requires a listing of field names the values are being inserted into (QV VBA macros use this syntax)

 

    sql="INSERT INTO mw_TestTable (Field1, Field2, Field3) VALUES (‘$(vTestChar)', ‘$(vTestInt)’,’$(vTestDate)’”

 

jonathandienst
Partner - Champion III
Partner - Champion III

Where the variables defined? You cannot use QV variables directly in a macro. You need syntax like this:

set v = ActiveDocument.Variables("vTestChar ")

vTestChar  = v.GetContent.String

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thanks Evan,

I tried adding the fields and still got the error.  I also noticed your syntax was a bit different on the Values section so matched that as well and still get the No Permissions error.

Thanks.

Mark.

Not applicable
Author

Thanks for your help Jonathan.  That did the trick.  Used your code to define all three variables and it works as expected/desired.

Much appreciated.

Mark.

evan_kurowski
Specialist
Specialist

Hello Mark,

That syntax is used in conjunction with DynamicUpdateCommand, which can be launched from the VBA module and doesn't require the intermediate steps of assigning a QlikView variable into a VB variable. 

Not applicable
Author

Hi Evan,

That syntax is used in conjunction with DynamicUpdateCommand, which can be launched from the VBA module and doesn't require the intermediate steps of assigning a QlikView variable into a VB variable.

That is where I thought I was.  Tools; Edit Module.  Entirely likely my ignorance is showing through again.  Where is the VBA module as compared to where I was?

Thanks.

evan_kurowski
Specialist
Specialist

Mark Worthen wrote:


Details:

Desktop client only (no web deployment planned), write back to database (initially insert, but later edit & delete options as well).

Hi Evan,

That syntax is used in conjunction with DynamicUpdateCommand, which can be launched from the VBA module and doesn't require the intermediate steps of assigning a QlikView variable into a VB variable.

That is where I thought I was.  Tools; Edit Module.  Entirely likely my ignorance is showing through again.  Where is the VBA module as compared to where I was?

Thanks.

Hello Mark,

Indeed it sounds like you're in the right place.  If you use DynamicUpdateCommand, the VBA code can reference Qlikview variables without having to convert to VB variables first, but this is only writing back to the internal QVW.  For an external DB connection, syntax converting each QV variable to a VB variable is the way to go when using the VB module.

However, if your goal is to writeback many QV variables into an external database, why are you using the VBA module as compared to a partial reload?  For writeback, it seems in-script replacement of QV variables in conjunction with native DDL in non- SQL SELECT statements would be easier to form than building in the VB module.

For example, why is something like this off the table?


IF IsPartialReload() AND '$(vDBAction)' = 'INSERT' THEN
          
OLEDB CONNECT TO [Data Source=$(MyServer);Initial Catalog='Rally';Integrated Security='SSPI'];
         
SQL INSERT INTO mw_TestTable VALUES ('$(vQVVariable1)','$(vQVVariable2)', '$(vQVVariable3)');
END IF

Not applicable
Author

Evan Kurowski wrote:

However, if your goal is to writeback many QV variables into an external database, why are you using the VBA module as compared to a partial reload?  For writeback, it seems in-script replacement of QV variables in conjunction with native DDL in non- SQL SELECT statements would be easier to form than building in the VB module.

For example, why is something like this off the table?


IF IsPartialReload() AND '$(vDBAction)' = 'INSERT' THEN
          
OLEDB CONNECT TO [Data Source=$(MyServer);Initial Catalog='Rally';Integrated Security='SSPI'];
         
SQL INSERT INTO mw_TestTable VALUES ('$(vQVVariable1)','$(vQVVariable2)', '$(vQVVariable3)');
END IF

I google'd how to write back to the database and never found this as an option.  More learning to do.  That looks like a better option.

I'll have to study up on the IsPartialReload() function and go from there.

Thanks for the pointer.

evan_kurowski
Specialist
Specialist

Mark Worthen wrote:

I google'd how to write back to the database and never found this as an option.  More learning to do.  That looks like a better option.

I'll have to study up on the IsPartialReload() function and go from there.

Thanks for the pointer.

The IsPartialReload() part is related to how much data you already have loaded within the same application as your DB update client.

Lets say you were working with an empty or near empty shell application with very little data, a .QVW designed to execute DDL only, your writeback statement is going to take about the same execution time for Full vs. Partial reload.

But in a scenario where you've loaded a large volume of data, and are connecting your update variables to field selections made against this large dataset, the extra time between each full reload transaction would start to feel cumbersome. (Full reload dumps out all the data, and starts anew, vs. Partial which doesn't erase any data, only performs supplementary actions).

Then as I saw mentioned earlier, a switch statement can be useful to organize and expand into a variety of DDL actions in an orderly manner.

 

OLEDB Connect[ToSomething];

SWITCH '$(vDBAction)'

CASE 'INSERT'
//INSERT SQL
     CASE 'UPDATE'
//UPDATE SQL
     CASE 'CREATE'
//CREATE SQL
     CASE 'DROP'
//DROP SQL
          
END SWITCH