To start 2011 I wanna talk about a case I’ve to deal with.
Indeed I have to load data from a table to another same one. The problem is that this table contains fields with special characters in their name.
For instance there are “#” characters. I know it’s pretty weird but I’ve to deal with that.
So in the DataRecordMetadataXMLReaderWriter class we have:
field = new DataFieldMetadata(name, fieldType, delimiter);
Hello Maxani,
this is intentional. Using special characters in field names could cause many problems starting from coding problems to not properly handling component’s attributes.
You should use org.jetel.util.string.StringUtils.normalizeName(CharSequence) method before creating the metadata.
I generate my .fmt files with AnalyzeDB.main(parameters) method. When do I have to use org.jetel.util.string.StringUtils.normalizeName(CharSequence) method exactly?
Hi,
I’ve created an issue for this problem (http://bug.cloveretl.org/view.php?id=5684) in our bug tracking system.
As a workaround you can use following class I’ve prepared for you:
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.Properties;
import org.jetel.connection.jdbc.DBConnection;
import org.jetel.exception.ComponentNotReadyException;
import org.jetel.exception.JetelException;
import org.jetel.graph.runtime.EngineInitializer;
import org.jetel.metadata.DataRecordMetadata;
import org.jetel.metadata.DataRecordMetadataXMLReaderWriter;
import org.jetel.plugin.Plugins;
public class CreateMetadata {
private final static int BUFFER_SIZE = 2048;
/**
* @param args
*/
public static void main(String[] args) {
Properties config = new Properties();
int optionSwitch = 0;
String engineConfig = null;
String pluginsRootDirectory = null;
String filename = null;
if (args.length == 0) {
printInfo();
System.exit(-1);
}
String queryFilename = null;
String query = null;
for (int i = 0; i < args.length; i++) {
if (args[i].equalsIgnoreCase("-cfg")){
engineConfig = args[++i];
}else if (args[i].equalsIgnoreCase("-dbDriver")) {
config.setProperty("dbDriver",args[++i]);
optionSwitch |= 0x01;
} else if (args[i].equalsIgnoreCase("-dbURL")) {
config.setProperty("dbURL",args[++i]);
optionSwitch |= 0x02;
} else if (args[i].equalsIgnoreCase("-driverLibrary")) {
config.setProperty("driverLibrary", args[++i]);
} else if (args[i].equalsIgnoreCase("-jdbcSpecific")) {
config.setProperty("jdbcSpecific", args[++i]);
} else if (args[i].equalsIgnoreCase("-database")) {
config.setProperty("database", args[++i]);
} else if (args[i].equalsIgnoreCase("-o")) {
filename = args[++i];
} else if (args[i].equalsIgnoreCase("-f")) {
queryFilename = args[++i];
optionSwitch |= 0x04;
} else if (args[i].equalsIgnoreCase("-q")) {
query = args[++i];
optionSwitch |= 0x04;
} else if (args[i].equalsIgnoreCase("-user")) {
config.setProperty("user",args[++i]);
} else if (args[i].equalsIgnoreCase("-password")) {
config.setProperty("password",args[++i]);
} else if (args[i].equalsIgnoreCase("-config")) {
try{
InputStream stream = new BufferedInputStream(new FileInputStream(args[++i]));
config.load(stream);
stream.close();
if (config.getProperty("dbDriver") != null) optionSwitch |= 0x01;
if (config.getProperty("dbURL") != null) optionSwitch |= 0x02;
}catch(Exception ex){
System.err.println("[Error] "+ex.getMessage());
System.exit(-1);
}
} else if (args[i].equalsIgnoreCase("-plugins")) {
i++;
pluginsRootDirectory = args[i];
} else {
System.err.println("[Error] Unknown option: " + args[i] + "\n");
printInfo();
System.exit(-1);
}
}
if (queryFilename != null) {
int length;
int offset = 0;
char[] buffer = new char[BUFFER_SIZE];
FileReader reader = null;
StringBuffer stringBuf = new StringBuffer();
try {
reader = new FileReader(queryFilename);
while ((length = reader.read(buffer)) > 0) {
stringBuf.append(buffer, offset, length);
offset += length;
}
reader.close();
}
catch (FileNotFoundException ex) {
System.err.println("[Error] " + ex.getMessage());
System.exit(-1);
}
catch (IOException ex) {
System.err.println("[Error] " + ex.getMessage());
System.exit(-1);
}
query = stringBuf.toString();
}
config.put(DBConnection.SQL_QUERY_PROPERTY, query);
EngineInitializer.initEngine(pluginsRootDirectory, engineConfig, null);
Plugins.activateAllPlugins();
DataRecordMetadata metadata = null;
try {
metadata = createMetadata(config);
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
}
OutputStream outStream = null;
try {
outStream = filename != null ?
new FileOutputStream(filename) : System.out;
} catch (FileNotFoundException e) {
e.printStackTrace();
System.exit(1);
}
DataRecordMetadataXMLReaderWriter.write(metadata, outStream);
}
private static DataRecordMetadata createMetadata(Properties config) throws IOException, JetelException, SQLException {
DBConnection connection;
// load in Database Driver & try to connect to database
connection=new DBConnection("", config);
try {
connection.init();
} catch (ComponentNotReadyException e) {
throw new IOException(e.getMessage());
}
return connection.createMetadata(config);
}
private static void printInfo() {
System.out.println("Usage:");
System.out.println("-dbDriver JDBC driver to use");
System.out.println("-dbURL Database name (URL)");
System.out.println("-driverLibrary *Library containing a JDBC driver to be loaded");
System.out.println("-jdbcSpecific *Specific JDBC dialect to be used");
System.out.println("-database *ID of a built-in JDBC library");
System.out.println("-config *Config/Property file containing parameters");
System.out.println("-user *User name");
System.out.println("-password *User's password");
System.out.println("-o *Output file to use (standard is stdout)");
System.out.println("-f *Read SQL query from filename");
System.out.println("-q *SQL query on command line");
System.out.println("-info *Displays list of driver's properties");
System.out.println("-plugins *directory where to look for plugins/components");
System.out.println("\nParameters marked [*] are optional. Either -f or -q parameter must be present.");
System.out.println("If -config option is specified, mandatory parameters are loaded from property file.");
System.out.println("When output is directed to file (-o option used), UTF-8 encoding is used - this should");
System.out.println("be the preffered way as some format characters can't be represented as pure ASCII.\n");
}
}
It works similarly as AnalyzeDB, but normalizes record’s and fields’ names.
It works far better with your class. Indeed the result looks like the result we have with CloverETL Designer. It manages correctly the jdbcSpecific parameter on the contrary of AnalyzeDB. Now my decimal fields are well defined.
Why are there so many differences between AnalyzeDB.main() and connection.createMetadata() (the class you gave me)?
Do you plan to replace AnalyzeDB with something like the class you gave me?