Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Tuesday, August 23, 2011

A List of DDL Statements with tFixedFlowInput

If you have a list of several DDL statements that you'd like to run as part of a Talend Open Studio job, use  tFixedFlowInput to define a list and feed the list into a database row component like tMysqlRow.

Sometimes, it's more convenient to call a sequence of SQL statements from within Talend Open Studio instead of through a command line tool like sqlplus.  In a previous post, I wrote about using the tFileInputDelimited component to drive the processing.  This post suggests using the tFixedFlowInput component with the statements embedded in the Component View.

Basic Job

This job uses an open/process/commit/close pattern on a datasource defined in the Metadata panel.  tFixedFlowInput defines a list and uses each iteration to produce a flow record.  Each flow record is a valid DDL String -- in this case a DELETE -- which is executed by a tMysqlRow.  Once the flow is finished, the results are committed and the connection closed.

Job with tFixedFlowInput
Structured and Unstructured Lists

The list of SQL statements can be defined using a structured editor (Inline Table) or as a block of text (Inline Content).  In the Inline Table, each UI row renders an element.  In this example, there is only a single field containing the SQL statement, but there could be more than one field.

Inline Table Config of tFixedFlowInput
An alternative is to use a block of text with each line representing a row.  More than one field is defined using a delimiter.

Inline Content Config of tFixedFlowInput
Executing the SQL

To execute the SQL, output the sqlStatement field of row1.  The tFixedFlowInput schema defines a single "sqlStatement" field that is of type String.

tMysqlRow Executing Statement from row1
 Most database tools have a command line utility or a GUI for executing a text file of DDL statements.  However, it may be more convenient to maintain these statements within Talend Open Studio.  That way, you have all of your data loading and maintenance processing in one place.
Posted by at

4 comments:

  1. UnknownJune 13, 2012 at 8:38 PM

    Hi Carl, How can I capture the error that a particular sql statement generates?

    I know I can use logcatcher with tsendmail, but I want to make sure that I capture the error sql statement and throw that in the email.

    Right now I have a tFixedFlowInput -> tMysqlRow -> tJavaRow

    tMysqlRow executes the sql statement.
    The tJavaRow reads
    String progress = (String)globalMap.get("storedProcedureProgress");
    if(progress == null) {
    progress = "";
    }
    globalMap.put("storedProcedureProgress", progress + "\nCompleted Step: " + row12.sqlcommand);
    (row12 connects tMysqlRow -> tJavaRow)

    I'm using the tMySqlRow to call a bunch of stored procedures one after the other.

    Even with this approach, I get the last successfully executed sql statement but not the one that errored out. Any ideas?

    ReplyDelete
  2. CarlJune 13, 2012 at 9:04 PM

    Take a look at a recent post titled "Handling Database Warnings with Talend Open Studio". See if you can use a Run If after the Row components.

    ReplyDelete
  3. UnknownJanuary 19, 2014 at 3:04 AM

    Sry to be a smartass, but Selete is DML (Data Manipulation) not DDL (Data Definition). As i was searching for DDL possibilities i got stuck here :)

    ReplyDelete
    Replies
    1. CarlJanuary 19, 2014 at 2:46 PM

      Yep. This is Part 2 of a post that does feature a DDL ALTER TABLE statement (and more DML). http://bekwam.blogspot.com/2011/02/running-file-of-sql-statements-with.html

      You might find this DDL-oriented post interesting:

      http://bekwam.blogspot.com/2011/10/controlling-ddl-behind-talend-open.html

      Delete

Subscribe to: Post Comments (Atom)