Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
kkoyyalamudi
Contributor

Database connection testing Script

Hello Community Folks,

I am trying to create a script that tests the database connection .I want variable value to be '0' when database connection fails so that variable value can be used to send email alerts about the connection issue.And I also want to create a qlik table with database name and sysdate as NULL if the db connection fails.Here is the script I created but when I give incorrect db creds in connection string, script is not entering IF block.Can some one correct my script.

Set ErrorMode=0;

Set NullInterpret = '';

LET vDBConnection = 1;


Trace 'Now Testing DBConnection';

OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxxxx;Data Source=xxxx;Extended Properties=""] (XPassword is xxxxxx);

StreamHist:

SQL SELECT  'Databse Name' as DBNAME,SYSDATE FROM DUAL;

LET DBconnection_DATE =PEEK('SYSDATE',0,'StreamHist');

IF DBconnection_DATE = '' THEN

LET vDBConnection = 0;

StreamHist:

LOAD * INLINE [

    DBNAME, SYSDATE

    'Databse Name'

];

ENDIF

exit script;

1 Solution

Accepted Solutions
MVP
MVP

Re: Database connection testing Script

maybe

Set ErrorMode=0;

Set NullInterpret = '';

LET vDBConnection = 1;

Trace 'Now Testing DBConnection';

OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxxxx;Data Source=xxxx;Extended Properties=""] (XPassword is xxxxxx);

StreamHist:

SQL SELECT  'Databse Name' as DBNAME, SYSDATE, 'OK' as Status FROM DUAL;

LET DBconnection_DATE =PEEK('SYSDATE',0,'StreamHist');

IF len(trim(DBconnection_DATE)) = 0 THEN

  LET vDBConnection = 0;

  StreamHist:

  LOAD 'Database Name' as DBNAME, null() as SYSDATE, 'KO' as Status AutoGenerate 1;

ENDIF;

exit script;

3 Replies
MVP
MVP

Re: Database connection testing Script

maybe

Set ErrorMode=0;

Set NullInterpret = '';

LET vDBConnection = 1;

Trace 'Now Testing DBConnection';

OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxxxx;Data Source=xxxx;Extended Properties=""] (XPassword is xxxxxx);

StreamHist:

SQL SELECT  'Databse Name' as DBNAME, SYSDATE, 'OK' as Status FROM DUAL;

LET DBconnection_DATE =PEEK('SYSDATE',0,'StreamHist');

IF len(trim(DBconnection_DATE)) = 0 THEN

  LET vDBConnection = 0;

  StreamHist:

  LOAD 'Database Name' as DBNAME, null() as SYSDATE, 'KO' as Status AutoGenerate 1;

ENDIF;

exit script;

kkoyyalamudi
Contributor

Re: Database connection testing Script

maxgro‌: Thank you.It worked

Not applicable

Re: Database connection testing Script

Hi, you could also use ScriptError variable.

It takes values different from 0, when an error occures.

Combining it with if condition you can set the value for your variable:


let ScriptError=Null();     //clear ScriptError variable in case some other error occured few lines above


command causing failure


if ScriptError>0 then

  let vDBConnection =0;

ELSE

  let vDBConnection =1;

ENDIF

Community Browser