topic Macro to insert data in database in QlikView

Macro to insert data in database

Wed, 20 Jul 2011 19:27:43 GMT

Hi guys,

I need to use a macro to insert just one value in a SQL Server Database.

I'm trying, but I think that my code is something wrong...

What's the best way to do that?

Exemple: insert into test (name) values ('qlikview')

Which code I have to use?

Could you send me a exemple or a qlik file that explain what to do?

Thank you so much.

Regards,

Rodrigo Almeida.

Macro to insert data in database

Thu, 21 Jul 2011 10:20:48 GMT

Hi guys,

Anybody knows about?

Thanks,

Rodrigo.

Macro to insert data in database

Fri, 22 Jul 2011 12:30:42 GMT

hi,

try with this code in VBscript

dim sConn, oConn

Set oConn = CreateObject("ADODB.Connection")

sConn = "Provider=MSDAORA;User ID="yourlogin";Password="yourpassword";Data Source="your data source";OLEDB.NET=true;SPPrmsLOB=true;Persist Security Info=False;"

oConn.Open sConn

sSQL="your request"

oConn.Execute(sSQL)

oConn.Close

Set oConn = Nothing

Macro to insert data in database

Tue, 26 Jul 2011 10:17:32 GMT

Thanks my friend!

Macro to insert data in database

Thu, 18 Aug 2011 15:12:49 GMT

Hi, I have used this code to do the opposite,to load new data from the Database in qlikview using a Macro (cos I want to use variables in the conditions), and I get no errors but my dimension fng_timestamp is not updated with the new data.

You can find the code Im using as follows, can you please help me?Thanks a lot!

sub con3

dim sConn, oConn

Set oConn = CreateObject("ADODB.Connection")

sConn = "Provider=OraOLEDB.Oracle.1;User ID=Moneta_usage;Password=Moneta_usage;Data Source=orcl;OLEDB.NET=true;SPPrmsLOB=true;Persist Security Info=False;"

oConn.Open sConn

sSQL="Select fng_timestamp as time FROM ""MONETA_USAGE"".""SW_NATIONAL2011"""

oConn.Execute(sSQL)

oConn.Close

Set oConn = Nothing

end sub

Thanks a lot!

Macro to insert data in database

Thu, 18 Aug 2011 15:17:35 GMT

Thanks Marcos!!

Macro to insert data in database

Thu, 18 Aug 2011 15:25:53 GMT

Hi Rodrigo, could you help me to solve my problem? Thanks a lot! Gracias tio!

Re: Macro to insert data in database

Thu, 18 Aug 2011 17:48:45 GMT

Hola Marcos,

Desculpame, yo no he mirado su pregunta.

Yo estoy usando el mismo codigo pero con la string del SQL en una variable.

Sigue mi codigo:

sub Gravar_DB

'BASE DE DADOS

set conn = CreateObject("ADODB.Connection")

conn.open "Provider=sqloledb.1;Data Source=YOURSERVER;Initial Catalog=YOURDATABASE;User Id=USER;Password=PASS"

operacaoInsertOrUpdate = ActiveDocument.Variables("vIns_Or_Upd").GetContent().String

if operacaoInsertOrUpdate = "UPDATE" then

StatementSqlToDatabase = ActiveDocument.Variables("vUpdate_SQL").GetContent().String

else

if operacaoInsertOrUpdate = "INSERT" then

StatementSqlToDatabase = ActiveDocument.Variables("vInsert_SQL").GetContent().String

end if

'msgbox("StatementSqlToDatabase: " & StatementSqlToDatabase)

if StatementSqlToDatabase <> "" then

on error resume next

conn.Execute StatementSqlToDatabase

if err <> 0 then

msgbox("Ocorreu um erro na actualização do registo! " & err )

ActiveDocument.Variables("vInsertUpdate_Activo").SetContent "1", true

ActiveDocument.Variables("vGravarBD").SetContent "Erro", true

else

msgbox("Registo efectuado com sucesso!")

ActiveDocument.Variables("vIns_Or_Upd").SetContent "", true

ActiveDocument.Variables("vGravarBD").SetContent "Sucesso", true

'call PCComercial_Inicializa_Valores

'Reload

'ActiveDocument.Reload

end if

conn.close

else

msgbox("Ocorreu um erro imprevisto!")

ActiveDocument.Variables("vGravarBD").SetContent "Erro", true

ActiveDocument.Variables("vInsertUpdate_Activo").SetContent "1", true

end if

'BASE DE DADOS

end sub

Las variables vUpdate_SQL o vInsert_SQL contiene un scrip para SQL Server.

Uso uno y otra para actualizar o insertar registros.

No lo sé como anda solamente un SELECT...

Pero eso codigo anda perfectamente in mi aplicacion.

Abrazo,

Rodrigo.

Macro to insert data in database

Fri, 19 Aug 2011 09:26:52 GMT

Hi Rodrigo, the problem is that when I execute the select query I get no error but my dimension fng_timestamp is not updated in Qlikview with the new data from the database.

Thanks.

Obrigado.

Macro to insert data in database

Fri, 19 Aug 2011 11:56:27 GMT

Hi Marcos,

Ok... you want to load new data from database in qlikview......

I think that is possible... and I think that I have a code for this.

I'll try to find it in my projects at weekend and next week I'll post a new comment.

Abraço,

Rodrigo.

Macro to insert data in database

Mon, 16 Apr 2012 21:22:23 GMT

Hi,

Is it possible to help me on this.

I have a report where my source is a directory of log files and it loads the files incrementally to a qvd.

and I also have a ODBC connection as follow:

ODBC CONNECT TO EQUIPMENT (XUserId is fYRJFKC, XPassword is VMCHLXL);

is it possible to send the data from the qvd to SQL table

the table name is PhoneReport and it has the same fields as the qvd.

Thxs

Re: Macro to insert data in database

Anonymous — Tue, 15 Jul 2014 07:15:20 GMT

Did it worked for you?

Thanks

Javier

Re: Macro to insert data in database

Anonymous — Tue, 15 Jul 2014 07:17:08 GMT

Hello,

I am having an issue with this code. It is working using the QV Desktop but when I publish it in the QV Server and try to do the same (QV 11R2 - IE Plugil - QV Web Server).. it raise a security error. I have checkd in the server, and the option to run unsafe macro is checked.

Any clue?

Thnxs

Javier