Could DBExecute create preparedStatements later?

CloverDX Platform
1

In DBExecute.java::init, I see that the split SQLStatement is iterated through to instantiate preparedStatements for later execution.

I am having issues because (by default and/or as far as I know), DB/2 only allows 1500 or so preparedStatements per connection. My DDL load SQL file has several thousand sql statements which all get initialized in the init() method.

Could the connection.prepareStatement be executed prior to the executeUpdate inside of execute() instead?

2

DBExecutes executes all prepared statements for all input records, so it is the cause they are instntiated in init method.
You can easy realise your transformation by Reformat component:

<?xml version="1.0" encoding="UTF-8"?>
<Graph created="Thu Apr 24 08:58:24 CEST 2008" guiVersion="1.9" id="1209020374328" licenseType="Evaluation license." modified="Thu Apr 24 09:25:44 CEST 2008" name="test" revision="1.7">
<Global>
<Metadata id="Metadata0">
<Record fieldDelimiter="|" name="query" recordDelimiter="\n" type="delimited">
<Field name="query" type="string"/>
</Record>
</Metadata>
<Metadata id="Metadata1">
<Record fieldDelimiter="|" name="result" recordDelimiter="\n" type="delimited">
<Field name="query" type="string"/>
<Field name="result" type="string"/>
</Record>
</Metadata>
<Connection dbConfig="conn/postgre.cfg" id="Connection0" type="JDBC"/>
<Property fileURL="C:/javlin/eclipse/workspace/test/workspace.prm" id="GraphParameter0"/>
</Global>
<Phase number="0">
<Node enabled="enabled" fileURL="data-in/queries.txt" guiHeight="0" guiName="Universal Data Reader" guiWidth="0" guiX="20" guiY="22" id="DATA_READER0" type="DATA_READER"/>
<Node enabled="enabled" guiHeight="0" guiName="Reformat" guiWidth="0" guiX="183" guiY="22" id="REFORMAT0" transformClass="DBExecute" type="REFORMAT"/>
<Node debugPrint="true" enabled="enabled" guiHeight="0" guiName="Trash" guiWidth="0" guiX="356" guiY="22" id="TRASH0" type="TRASH"/>
<Edge fromNode="DATA_READER0:0" guiBendpoints="" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (output)" toNode="REFORMAT0:0"/>
<Edge fromNode="REFORMAT0:0" guiBendpoints="" id="Edge1" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (out)" toNode="TRASH0:0"/>
</Phase>
</Graph>

with transformation:

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import org.jetel.component.DataRecordTransform;
import org.jetel.connection.DBConnection;
import org.jetel.data.DataRecord;
import org.jetel.exception.ComponentNotReadyException;
import org.jetel.exception.TransformException;


public class DBExecute extends DataRecordTransform {
	
	Connection connection;
	Statement statement;
	String query;
	
	@Override
	public boolean init() throws ComponentNotReadyException {
		DBConnection dbConnection = ((DBConnection)getGraph().getConnection("Connection0"));
		dbConnection.init();
		connection = dbConnection.getConnection("REFORMAT0");
		try {
			statement = connection.createStatement();
		} catch (SQLException e) {
			throw new ComponentNotReadyException(e);
		}
		return super.init();
	}

	@Override
	public boolean transform(DataRecord[] arg0, DataRecord[] arg1)
			throws TransformException {
		query = arg0[0].getField(0).getValue().toString();
		arg1[0].getField(0).setValue(query);
		try {
			statement.executeUpdate(query);
			arg1[0].getField(1).setValue("OK");
		} catch (SQLException e) {
			arg1[0].getField(1).setValue(e.getMessage());
		}
		return true;
	}

}
3

Ok but…

  1. Prepared statements are for operations that are repeated, whereas each statement in DBExecute is executed only once.

  2. All those prepared statements created by DBExecute internally are never closed.

  3. This means that the default DBExecute implementation is inherently not scalable. If I pass in a SQL file with 100k statements (which is not that extreme), it is kinda silly to create 100k prepared statements in one shot.

These are pretty basic JDBC concepts that are being overlooked IMHO.

The implementation of DBExecute is quite weak compared with ExecSQL from Kettle:
-- Kettle uses Statement.execute() for non-parameterized statements
-- Kettle uses a streaming parser, rather than reading the entire SQL file into memory in one shot
-- Kettle closes its statements properly

I don’t mean to slam DBExecute, but executing SQL files with Clover is a natural place to start, isn’t it?

4

Hi,
DBExecute component will be rewrote to be able to execute not only PreparedStatements. In new version it will have some new features.