20 Replies Latest reply: Sep 29, 2015 10:32 AM by Mark Worthen RSS

    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.

        • Re: Database Writeback Help
          Sinan Ozdemir

          Hi Mark,

           

          Try it by writing your variables like the below:

           

          '$(vTestChar)' and follow the same format for other variables.

           

          Thanks

          • Re: Database Writeback Help
            arjun rao

            Hi Mark,

            Please check this link.

            Re: SQL Writeback

            • Re: Database Writeback Help
              Evan Kurowski

              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)’”

               

                • Re: Database Writeback Help

                  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.

                    • Re: Database Writeback Help
                      Evan Kurowski

                      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. 

                        • Re: Database Writeback Help

                          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.

                            • Re: Database Writeback Help
                              Evan Kurowski

                              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

                                • Re: Database Writeback Help

                                  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.

                                    • Re: Database Writeback Help
                                      Evan Kurowski

                                      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