topic Re: Writing to SQL DB from Macro in QlikView

Writing to SQL DB from Macro

Wed, 20 Nov 2013 16:54:30 GMT

Hi, Im successfully updatin an SQL db from Macro. However, when I try to add a datestamp the write fails.

This is my write command:

sSQL="insert into qlikview_comments (id,user_id,indicator_number, comment,time_added) values (newid(),'" & vuser & "',12345,'" & vcomment & "','" & vtime & "')"

Any help appreciated.

Tom

Re: Writing to SQL DB from Macro

Anonymous — Wed, 20 Nov 2013 18:26:49 GMT

Hi,

Could you give a sample of the result-string eSQL and also a correct sample you expect it to be ?

Paul

Re: Writing to SQL DB from Macro

jonasheisterkam — Wed, 20 Nov 2013 18:32:32 GMT

Test it with the normal SQL tool to check the exact syntax of the type. Then show var in the macro via massagebox and modify the string until it match.

Re: Writing to SQL DB from Macro

Thu, 21 Nov 2013 10:11:23 GMT

Hi,

This is my test SQL string :

insert into qlikview_comments (id,user_id,indicator_number, comment,time_added)

values (newid(), 'User1',12345,'this is a test comment',getdate())

and this is my resultset :

id	user_id	indicator_number	time_added	comment
83CF78C7-EE27-4C25-940E-A3B5B5F4DFB6	User1	12345	2013-11-21 09:40:52.560	this is a test comment

The macro insert from the document works without the datetime .....

Hope this info helps,

Tom

Re: Writing to SQL DB from Macro

Anonymous — Thu, 21 Nov 2013 11:32:06 GMT

Hi,

Thanks for the info, but could you also add the result you expedted ? I mean the string which will work with SQL.

I don't know how to use the SQL-tool to test (like Jonas mentioned), but perhaps you could test with QV and change the string vtime with a fixed string like f.ex. '2013-11-21 09:40:52.560' or '11-21-2013 09:40:52.560' or '11-21-2013 09:40' or .....

There are many possibilities, but I think it depends on the field 'time_added' and the time/date settings on the server.

Paul

Re: Writing to SQL DB from Macro

Thu, 21 Nov 2013 14:36:53 GMT

Hi Paul,

thanks for your reply. I've amended my Macro with these lines before the write statement:

vdate = "2013-11-21 09:40:52"

MsgBox(vdate)

which is showing the correct date,

My write statement now looks like this :

sSQL="insert into qlikview_comments (id,user_id,indicator_number, comment,time_added) values (newid(),'" & vuser & "','" & vdate & "',12345,'" & vcomment & "')"

which is where the macro fails. Im guessing that I need to convert vdate to a datetime variable but not sure how to do this....

Hope you can help,

Tom

Re: Writing to SQL DB from Macro

Anonymous — Thu, 21 Nov 2013 16:00:43 GMT

Hi,

I suppose next lines will do :

vdate = (FormatDateTime(date(),2) & " " & FormatDateTime(time() ,4) )

Msgbox vdate

But you have to know (test with trial) how the date and time has to look in the SQL statement.

In our SQL-DB I did not work with 'writing to SQL' but I used 'Stored Procedures' thru QV and I had to format the date as DD/MM/YYYY HH:NN

Paul

Re: Writing to SQL DB from Macro

Thu, 21 Nov 2013 16:33:28 GMT

Hi,

Your suggested lines did work....

do you have any advice/examples of calling stored procedures from QV?

Thanks again,

Tom

Re: Writing to SQL DB from Macro

Anonymous — Thu, 21 Nov 2013 16:56:55 GMT

Hi,

Until now I only used raporting-procedures, no procedures who change data.

But here is a sample of what I used.

ODBC CONNECT32 TO SQL_ERP;

/* StockList */

StockList:

SQL EXEC [sql_DB_masterdata].[dbo].[pr_reporting_stocklist]

@id_bu = 1,

@id_warehouse = -1,

@id_language = 2

;

DISCONNECT;

Hope you can use it, it gave me a boost when I found this in the forum.

PS: Could you share your SQL-writing-macro with us.

Paul

Re: Writing to SQL DB from Macro

Fri, 22 Nov 2013 09:13:48 GMT

Hi Thanks for this.

This is my macro for inserting to SQL DB :

Sub Update_Comment

dim sServer, sConn, oConn,oRS

sServer="myserver"

sConn="provider=sqloledb;data source=" & sServer & ";initial catalog=db_name"

Set oConn = CreateObject("ADODB.Connection")

oConn.Open sConn, "username", "password"

Set oRS =CreateObject("ADODB.Recordset")

sSQL="insert into db_table (field1) values ("12345")"

ors.open sSQL, oconn

end sub

Many thanks,

Tom