Skip to Content
avatar image
Former Member

Connect to MSSQL Database using Redwood Script

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

}

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 13, 2015 at 07:10 AM

    What error are you getting?

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

    thanks

    Nanda

    Add comment
    10|10000 characters needed characters exceeded

    • 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);

         

      }

      }