topic Re: Database Writeback Help in QlikView

Database Writeback Help

Thu, 24 Sep 2015 20:07:58 GMT

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

sinanozdemir — Thu, 24 Sep 2015 20:40:39 GMT

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

Thu, 24 Sep 2015 20:47:31 GMT

Thanks Sinan.

When I tried using that format I can no longer test the code and get an error, it says "Invalid character".

Mark.

Re: Database Writeback Help

sinanozdemir — Thu, 24 Sep 2015 20:48:51 GMT

You may need to use chr(39) instead of single quote.

Re: Database Writeback Help

Thu, 24 Sep 2015 20:52:33 GMT

Changed to use chr(39):

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

Same error as when I used the single quote.

Re: Database Writeback Help

sinanozdemir — Thu, 24 Sep 2015 20:57:01 GMT

Sorry Mark. I am on a computer, but can you just avoid using double quotes as well?

Can you try the below:

sql_text = 'INSERT INTO mw_TestTable VALUES (' & chr(39) & '$(vTestChar)' & ',' & chr(39) & '$(vTestInt)' & chr(39) & ',' & chr(39) & '$(vTestDate)' & chr(30) & ')';

Thanks

Re: Database Writeback Help

Thu, 24 Sep 2015 21:00:10 GMT

Thank you for your continued help. The line returns a syntax error.

Re: Database Writeback Help

Thu, 24 Sep 2015 21:01:59 GMT

I believe the error is because a single quote designates commented out code and so by using single quotes, I've effectively commented out the line.

Re: Database Writeback Help

sinanozdemir — Thu, 24 Sep 2015 21:33:49 GMT

Let's try one more time:

sql_text = "INSERT INTO mw_TestTable VALUES (" & vTestChar & "," & vTestInt & "," & vTestDate & ")";

didn't realize you were writing this in VB.

This is not going to work either. I will get back to you.

Re: Database Writeback Help

Thu, 24 Sep 2015 21:37:32 GMT

Still getting the same error.

Re: Database Writeback Help

qlikviewwizard — Fri, 25 Sep 2015 01:25:07 GMT

Hi Mark,

Please check this link.

Re: SQL Writeback

Re: Database Writeback Help

evan_kurowski — Fri, 25 Sep 2015 03:45:21 GMT

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

jonathandienst — Fri, 25 Sep 2015 04:50:23 GMT

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

Re: Database Writeback Help

Fri, 25 Sep 2015 14:15:17 GMT

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

Fri, 25 Sep 2015 14:25:51 GMT

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.

Re: Database Writeback Help

evan_kurowski — Fri, 25 Sep 2015 17:00:13 GMT

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

Fri, 25 Sep 2015 17:06:54 GMT

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 — Fri, 25 Sep 2015 17:18:30 GMT

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

Mon, 28 Sep 2015 15:00:06 GMT

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 — Tue, 29 Sep 2015 01:41:07 GMT

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