Cannot use field name with special characters

CloverDX Platform
1

Hi everyone and happy new year.

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

Then:


public DataFieldMetadata(String name, char fieldType, String delimiter) {
	setName(name);

	this.type = fieldType;
	this.delimiter = delimiter;

	if (isNumeric() || fieldType == DATE_FIELD || fieldType == DATETIME_FIELD || fieldType == BOOLEAN_FIELD) {
		this.trim = true;
	}

	setFieldProperties(new Properties());
}

then in the setName() method :


public void setName(String name) {
	if (!StringUtils.isValidObjectName(name)) {
		throw new InvalidGraphObjectNameException(name, "FIELD");
	}

	this.name = name;
}


public static boolean isValidObjectName(CharSequence seq) {
	if (seq == null) {
		return false;
	}

	return seq.toString().matches(OBJECT_NAME_PATTERN);

}

And finally we have :


private final static String OBJECT_NAME_PATTERN = "[_A-Za-z]+[_A-Za-z0-9]*";

That’s why I’ve got the error :

parseRecordMetadata method call: Graph object FIELD named “U##CODE_POSTE” violates naming pattern [A-Za-z0-9_] !

So my question is: Is this intentional? Why can’t we use special characters in our field names?

Plus standard JDBC allow special characters.

Can Clover Staff give me an answer? Do you think you’re gonna fix this in a next release or not?

Thanks a lot.

2

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.

3

Thank you for the answer.

But I’m afraid I don’t get it.

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?

Thank you.

4

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.

5

Hi.

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?

I think it would me better.

6

Hi,
AnalyzeDB is obsolete and not maintained. I hope it is replaced with better version in next release.