Java Code Examples for org.pentaho.di.core.database.DatabaseMeta#requiresCastToVariousForIsNull()

The following examples show how to use org.pentaho.di.core.database.DatabaseMeta#requiresCastToVariousForIsNull() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example 1

Source File: InsertUpdate.java From pentaho-kettle with Apache License 2.0

4 votes

public void setLookup( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  data.lookupParameterRowMeta = new RowMeta();
  data.lookupReturnRowMeta = new RowMeta();

  DatabaseMeta databaseMeta = meta.getDatabaseMeta();

  String sql = "SELECT ";

  for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += ", ";
    }
    sql += databaseMeta.quoteField( meta.getUpdateLookup()[i] );
    data.lookupReturnRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getUpdateStream()[i] ).clone() );
  }

  sql += " FROM " + data.schemaTable + " WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += " AND ";
    }

    sql += " ( ( ";

    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else {
      if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] )
        || "IS NOT NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
        sql += " " + meta.getKeyCondition()[i] + " ";
      } else if ( "= ~NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {

        sql += " IS NULL AND ";

        if ( databaseMeta.requiresCastToVariousForIsNull() ) {
          sql += " CAST(? AS VARCHAR(256)) IS NULL ";
        } else {
          sql += " ? IS NULL ";
        }
        // null check
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
        sql += " ) OR ( " + databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ? ";
        // equality check, cloning so auto-rename because of adding same fieldname does not cause problems
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ).clone() );

      } else {
        sql += " " + meta.getKeyCondition()[i] + " ? ";
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      }
    }
    sql += " ) ) ";
  }

  try {
    if ( log.isDetailed() ) {
      logDetailed( "Setting preparedStatement to [" + sql + "]" );
    }
    data.prepStatementLookup = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
  } catch ( SQLException ex ) {
    throw new KettleDatabaseException( "Unable to prepare statement for SQL statement [" + sql + "]", ex );
  }
}

Example 2

Source File: InsertUpdate.java From pentaho-kettle with Apache License 2.0

4 votes

public void prepareUpdate( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  DatabaseMeta databaseMeta = meta.getDatabaseMeta();
  data.updateParameterRowMeta = new RowMeta();

  String sql = "UPDATE " + data.schemaTable + Const.CR;
  sql += "SET ";

  boolean comma = false;

  for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
    if ( meta.getUpdate()[i].booleanValue() ) {
      if ( comma ) {
        sql += ",   ";
      } else {
        comma = true;
      }

      sql += databaseMeta.quoteField( meta.getUpdateLookup()[i] );
      sql += " = ?" + Const.CR;
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getUpdateStream()[i] ).clone() );
    }
  }

  sql += "WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += "AND   ";
    }
    sql += " ( ( ";
    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] )
      || "IS NOT NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " " + meta.getKeyCondition()[i] + " ";
    } else if ( "= ~NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {

      sql += " IS NULL AND ";

      if ( databaseMeta.requiresCastToVariousForIsNull() ) {
        sql += "CAST(? AS VARCHAR(256)) IS NULL";
      } else {
        sql += "? IS NULL";
      }
      // null check
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      sql += " ) OR ( " + databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ?";
      // equality check, cloning so auto-rename because of adding same fieldname does not cause problems
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ).clone() );

    } else {
      sql += " " + meta.getKeyCondition()[i] + " ? ";
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ).clone() );
    }
    sql += " ) ) ";
  }

  try {
    if ( log.isDetailed() ) {
      logDetailed( "Setting update preparedStatement to [" + sql + "]" );
    }
    data.prepStatementUpdate = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
  } catch ( SQLException ex ) {
    throw new KettleDatabaseException( "Unable to prepare statement for SQL statement [" + sql + "]", ex );
  }
}

Example 3

Source File: Update.java From pentaho-kettle with Apache License 2.0

4 votes

public void setLookup( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  data.lookupParameterRowMeta = new RowMeta();
  data.lookupReturnRowMeta = new RowMeta();

  DatabaseMeta databaseMeta = meta.getDatabaseMeta();

  String sql = "SELECT ";

  for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += ", ";
    }
    sql += databaseMeta.quoteField( meta.getUpdateLookup()[i] );
    data.lookupReturnRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getUpdateStream()[i] ) );
  }

  sql += " FROM " + data.schemaTable + " WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += " AND ";
    }

    sql += " ( ( ";

    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else {
      if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] )
        || "IS NOT NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
        sql += " " + meta.getKeyCondition()[i] + " ";
      } else if ( "= ~NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {

        sql += " IS NULL AND ";

        if ( databaseMeta.requiresCastToVariousForIsNull() ) {
          sql += "CAST(? AS VARCHAR(256)) IS NULL";
        } else {
          sql += "? IS NULL";
        }
        // null check
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
        sql += " ) OR ( " + databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ?";
        // equality check, cloning so auto-rename because of adding same fieldname does not cause problems
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ).clone() );

      } else {
        sql += " " + meta.getKeyCondition()[i] + " ? ";
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      }
    }
    sql += " ) ) ";
  }

  try {
    if ( log.isDetailed() ) {
      logDetailed( "Setting preparedStatement to [" + sql + "]" );
    }
    data.prepStatementLookup = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
  } catch ( SQLException ex ) {
    throw new KettleDatabaseException( "Unable to prepare statement for SQL statement [" + sql + "]", ex );
  }

}

Example 4

Source File: Update.java From pentaho-kettle with Apache License 2.0

4 votes

public void prepareUpdate( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  DatabaseMeta databaseMeta = meta.getDatabaseMeta();
  data.updateParameterRowMeta = new RowMeta();

  String sql = "UPDATE " + data.schemaTable + Const.CR;
  sql += "SET ";

  for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += ",   ";
    }
    sql += databaseMeta.quoteField( meta.getUpdateLookup()[i] );
    sql += " = ?" + Const.CR;
    data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getUpdateStream()[i] ) );
  }

  sql += "WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += "AND   ";
    }
    sql += " ( ( ";
    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] )
      || "IS NOT NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " " + meta.getKeyCondition()[i] + " ";
    } else if ( "= ~NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {

      sql += " IS NULL AND ";

      if ( databaseMeta.requiresCastToVariousForIsNull() ) {
        sql += "CAST(? AS VARCHAR(256)) IS NULL";
      } else {
        sql += "? IS NULL";
      }
      // null check
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      sql += " ) OR ( " + databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ?";
      // equality check, cloning so auto-rename because of adding same fieldname does not cause problems
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ).clone() );

    } else {
      sql += " " + meta.getKeyCondition()[i] + " ? ";
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
    }
    sql += " ) ) ";
  }
  try {
    if ( log.isDetailed() ) {
      logDetailed( "Setting update preparedStatement to [" + sql + "]" );
    }
    data.prepStatementUpdate = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
  } catch ( SQLException ex ) {
    throw new KettleDatabaseException( "Unable to prepare statement for SQL statement [" + sql + "]", ex );
  }
}

Example 5

Source File: CombinationLookup.java From pentaho-kettle with Apache License 2.0

4 votes

/**
 * CombinationLookup table: dimension table keys[]: which dim-fields do we use to look up key? retval: name of the key
 * to return
 */
public void setCombiLookup( RowMetaInterface inputRowMeta ) throws KettleDatabaseException {
  DatabaseMeta databaseMeta = meta.getDatabaseMeta();

  String sql = "";
  boolean comma;
  data.lookupRowMeta = new RowMeta();

  /*
   * SELECT <retval> FROM <table> WHERE ( ( <key1> = ? ) OR ( <key1> IS NULL AND ? IS NULL ) ) AND ( ( <key2> = ? ) OR
   * ( <key1> IS NULL AND ? IS NULL ) ) ... ;
   *
   * OR
   *
   * SELECT <retval> FROM <table> WHERE <crcfield> = ? AND ( ( <key1> = ? ) OR ( <key1> IS NULL AND ? IS NULL ) ) AND
   * ( ( <key2> = ? ) OR ( <key1> IS NULL AND ? IS NULL ) ) ... ;
   */

  sql += "SELECT " + databaseMeta.quoteField( meta.getTechnicalKeyField() ) + Const.CR;
  sql += "FROM " + data.schemaTable + Const.CR;
  sql += "WHERE ";
  comma = false;

  if ( meta.useHash() ) {
    sql += databaseMeta.quoteField( meta.getHashField() ) + " = ? " + Const.CR;
    comma = true;
    data.lookupRowMeta.addValueMeta( new ValueMetaInteger( meta.getHashField() ) );
  } else {
    sql += "( ( ";
  }

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( comma ) {
      sql += " AND ( ( ";
    } else {
      comma = true;
    }
    sql +=
      databaseMeta.quoteField( meta.getKeyLookup()[ i ] )
        + " = ? ) OR ( " + databaseMeta.quoteField( meta.getKeyLookup()[ i ] );
    data.lookupRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[ i ] ) );

    sql += " IS NULL AND ";
    if ( databaseMeta.requiresCastToVariousForIsNull() ) {
      sql += "CAST(? AS VARCHAR(256)) IS NULL";
    } else {
      sql += "? IS NULL";
    }
    // Add the ValueMeta for the null check, BUT cloning needed.
    // Otherwise the field gets renamed and gives problems when referenced by previous steps.
    data.lookupRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[ i ] ).clone() );

    sql += " ) )";
    sql += Const.CR;
  }

  try {
    if ( log.isDebug() ) {
      logDebug( "preparing combi-lookup statement:" + Const.CR + sql );
    }
    data.prepStatementLookup = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
    if ( databaseMeta.supportsSetMaxRows() ) {
      data.prepStatementLookup.setMaxRows( 1 ); // alywas get only 1 line back!
    }
  } catch ( SQLException ex ) {
    throw new KettleDatabaseException( "Unable to prepare combi-lookup statement", ex );
  }
}