Connect to MSSQL Database using Redwood Script

[Former Member]

Hi guys, I'm trying to connect mssql database using job created inside redwood.

The idea behind this is to send the job information to external database so that it can be used for analytical report.

Currently, I've managed to pull the job information in redwood but stuck on connecting to respective database.

Have anyone tried this before? Thanks..

{

String a = RaiseJobID; //get jobID using event trigger

Long jobID = Long.decode(a);

Job job = jcsSession.getJobByJobId(jobID);

JobDefinition jobDef = job.getJobDefinition();

String startRun = job.getRunStart().toFormattedString("yyyy-MM-dd HH:mm:ss").toString();

String endRun = job.getRunEnd().toString();

String jobName= jobDef.getName();

String jobState= job.getStatus().getState().toString();

String jobStatus= job.getStatus().toString();

String endRunF = job.getRunEnd().toFormattedString("yyyy-MM-dd HH:mm:ss").toString();

String host = "";

String uName = "";

String uPass = "";

String query =  "INSERT INTO rw_tableau ('"+jobName+"','"+jobState+"','"+jobStatus+"','"+startRun+"','"+endRunF+"')";

jcsOut.println(query);

//Connection con = DriverManager.getConnection(host, uName, uPass); //connection for mssqldatabase

}

[nanda_kumar21]

What error are you getting?

Generally you will need JDBC license to create a process server that connects to a MSSQL DB.

thanks

Nanda

[Former Member]

Hi, it says that object Connection cannot be found. But nevermind I've manage to find the solution,

Thanks for the help Nanda..

package com.redwood.scheduler.custom;

import java.math.BigDecimal;

import java.util.Iterator;

import com.redwood.scheduler.api.date.DateTimeZone;

import com.redwood.scheduler.api.model.*;

import com.redwood.scheduler.api.model.enumeration.*;

import java.sql.*;

public class name

extends nameStub

{

  public void execute()

  throws Exception

  {

    String a = RaiseJobID;

    Long jobID = Long.decode(a);

    Job job = jcsSession.getJobByJobId(jobID);

    JobDefinition jobDef = job.getJobDefinition();

    String startRun = job.getRunStart().toFormattedString("yyyy-MM-dd HH:mm:ss").toString();

    String endRun = job.getRunEnd().toString();

    String jobName= jobDef.getName();

    String jobState= job.getStatus().getState().toString();

    String jobStatus= job.getStatus().toString();

    String endRunF = job.getRunEnd().toFormattedString("yyyy-MM-dd HH:mm:ss").toString();

    String host = "jdbc:sqlserver://localhost:1433"+";databaseName=EDW_ETL";

    String uName = "EDW_ETL_CNTRL";

    String uPass = "EDW_ETL_CNTRL";

    String query =  "INSERT INTO RWTABLEAU VALUES('a','"+jobName+"','"+jobState+"','"+jobStatus+"','"+startRun+"','"+endRunF+"')";

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        Connection con = DriverManager.getConnection(host, uName, uPass);

        System.out.println("Connected database successfully...");

        //STEP 4: Execute a query

        System.out.println("Inserting records into the table...");

        jcsOut.println(query);

        Statement stmt = con.createStatement();

        stmt.executeUpdate(query);

}

}