Dynamically creating db connections

[theirishmole]

I want to create jobs/transformations to handle two different types of databases in different ways. Once these are defined they will be deployed on a customer site where they should be able to configure the db connections as well as the number of each type of db without having to edit the jobs/transformations or even know about them.

There will be a configuration tool which will save the users settings in an XML file. The file will then be read by a java program which will launch the required number and types of jobs/transformations using the db connection settings specified in the xml file.

From looking at the Jave API example i can see it is possible to launch a transformation or create one. Is it possible however to launch an existing transformation but somehow pass it whatever is required to create a db connection? Is there a way in a transformation create a db connection based on input parameters or file input?

I would appreciate any input if there is a better way of doing what i propose.

[MattCasters]

That has been covered many times.
All you need to do is define a database connection and specify all the parameters with variables.

Then you can simply set the variables prior to execution. You can do that dynamically too.

[theirishmole]

Thanks for the reply, i have that working now in spoon.

I created two transformations, one which sets the db variables and another which reads from one db table and writes to another. This all works in spoon.

When i try to run these same transformations from java however, as shown here, i get a an error:
[SQLCODE: <-30>:<Table or View not found>]
[Cache Error: <<SYNTAX>errdone+3^%qaqqt>]
[Location: <Prepare>]

Its not finding the table i want to output to, even though its there and the same transformation in spoon finds the table and writes to it.

Any ideas?

[MattCasters]

Did you put these 2 transformations in a job or do you run them individually?

[theirishmole]

in spoon they are in a job running one after the other.

In java i am calling the transformations themselves one after another.

[MattCasters]

And as such, there is no job to hold the variables from one transformation to the other.
Why not simply run the job. It's less code too.

[theirishmole]

The example showed how to run a transformation.

How do i run a job?

[MattCasters]

Pretty much the same way. Use JobMeta i.s.o. TransMeta, Job i.s.o Trans, etc.

[theirishmole]

Hi.

I changed the sample code used for a transforamtion to:

public class RunningJob {

public static void main(String[] args){
String j1="D:/pdi-ce-3.2.0-stable/DataBaseTesting/TestingDynamicDBAccess/job.kjb";

runJob(j1);

}
public static void runJob(String filename) {
try {
StepLoader.init();
EnvUtil.environmentInit();
JobMeta transMeta = new JobMeta(null,filename,null);
Job j = new Job(null, null, transMeta);

j.execute(0, null); // You can pass arguments instead of null.
j.waitUntilFinished();
if ( j.getErrors() > 0 )
{
throw new RuntimeException( "There were errors during transformation execution." );
}
}
catch ( KettleException e ) {
// TODO Put your exception-handling code here.
System.out.println(e);
}
}
}

I get this error:

ERROR 20-07 11:45:24,810 - null.0 - Unable to read Job Entry copy info from XML node : org.pentaho.di.core.exception.KettleStepLoaderException:
No valid step/plugin specified (jobPlugin=null) for TRANS

ERROR 20-07 11:45:24,826 - null.0 - org.pentaho.di.core.exception.KettleStepLoaderException:
No valid step/plugin specified (jobPlugin=null) for TRANS

at org.pentaho.di.job.entry.JobEntryCopy.<init>(JobEntryCopy.java:110)
at org.pentaho.di.job.JobMeta.loadXML(JobMeta.java:928)
at org.pentaho.di.job.JobMeta.<init>(JobMeta.java:732)
at org.pentaho.di.job.JobMeta.<init>(JobMeta.java:699)
at RunningJob.runJob(RunningJob.java:22)
at RunningJob.main(RunningJob.java:15)

org.pentaho.di.core.exception.KettleXMLException:
Unable to load the job from XML file [D:/pdi-ce-3.2.0-stable/DataBaseTesting/TestingDynamicDBAccess/job.kjb]

Unable to load job info from XML node

Unable to read Job Entry copy info from XML node : org.pentaho.di.core.exception.KettleStepLoaderException:
No valid step/plugin specified (jobPlugin=null) for TRANS


No valid step/plugin specified (jobPlugin=null) for TRANS

Is there more setup required to get a job to run than a transformation?

[MattCasters]

<sigh>

add

JobEntryLoader.init();