I am evaluating CloverETL and one of the major things we need to be able to do is to take images from a source and import into a BLOB field in a Firebird database. So I have my graph setup. Very simple. A SQL Server table as input and my Firebird table as output. When I run the graph, it fails.
ERROR [WatchDog] - Graph execution finished with error
ERROR [WatchDog] - Node DB_INPUT_TABLE0 finished with status: ERROR caused by: The size of data buffer is only 49152. Set appropriate parameter in defaultProperties file.
ERROR [WatchDog] - Node DB_INPUT_TABLE0 error details:
java.lang.RuntimeException: The size of data buffer is only 49152. Set appropriate parameter in defaultProperties file.
I googled the error and found a post in this forum that says to change the parameters inside this file which is supposed to be located in the “org/jetel/data/” subdirectory. Well, I don’t have such a subdirectory. I am running CloverETL Designer Desktop edition trial version 3.12. I cannot find any file called defaultProperties either.
The other issue I noticed is when I tried to create the metadata file for this. I pointed it at my SQL Server source table and the images are stored in a text field that is size 2147483647. In the metadata edit screen, I get an error saying that 2147483647 is not a number which I know is false. That is a number. I have to truncate the size down to 29999 which is way smaller. Too small.
Is it not possible to do what I am trying to do with Clover? Please help as I think we really want to use this tool instead of others we are evaluating.
regarding your troubles with changing the parameters of CloverETL, please see the documentation here: http://www.cloveretl.com/documentation/ … tings.html . In short - there’s basically two ways:
- modifying the properties file. It can be found in the CLOVER_INSTALLATION/plugins/com.cloveretl.gui/lib/lib/cloveretl.engine.jar file inside the org/jetel/data subdirectory.
- create a small property file of your own, and point the executed graph to it. This is also described in the documentation, but the point is that you need to pass the “-config path_to_your_config_file” to the executed graph. Please take care to pass the correct path, e.g. an absolute path. The file can contain just the one property you want to change, which is Record.MAX_RECORD_SIZE in your case.
Additionally, we’re releasing CloverETL version 3.2 early next week, which has greatly improved handling or big records - it’s edges can dynamically grow as needed. So in the new version you will almost never need to change the property.
Regarding your issue with string size - this was already encountered by someone else, please see forum post: viewtopic.php?f=4&t=4930 . In short, you can set the field size to empty value - this will not harm you.
Fixing the data buffer size is prety easy as you can do it by overriding default engine properties as described in documentation. In your case, the engineProperties file should contain the following four entries:
All the sizes should be kept at the same value. You may need to incrementally increase the sizes in case the transfered data is larger. However, please do not set them to unnecessarily huge number as you may run into memory allocation issues.
If you have not done that yet, I recommend using byte or cbyte data type for BLOB data. Please leave the field size in metadata empty. It is used only for reading from/writing to fixed-length files.
Configure all edges transferring large data as “direct fast propagate” (right click on the edge and select Edge type → Direct fast propagate
As a side note, handling large data is going to be significantly improved in CloverETL 3.2 which should relief you from performing the above configuration of overriding default engine properties.
Thanks for the information. However, I don’t have a org/jetel/data subdirectory. That is why I submitted my request. I am just evaluating ETL tools, so they are all very new to me. I am an Oracle PL/SQL Developer and can do conversions in that. But my company wants to use a Graphical ETL tool and asked me to research. So I downloaded Clover and others and just started playing around in them. I figured I would tackle the hardest issue first.
I will try my hand at a local configuration. I’ll post a followup to let you know how I make out.
I was able to create a local defaultProperties file with the code provided by Jan. I had to up the numbers to get my simple transformation to work. But it did work. And now I hear version 3.2 is out and I will be downloading it and trying it out.
Thanks very much. I downloaded v3.2 and changed my defaultProperties to what you suggested and that worked great. And after our Webex session today, I now know why I was having trouble getting the image transformations to work. Thanks for the info about the picture data being stored as TEXT is not supported.
That leads me to a question, though. Will it be possible to get CloverETL to support pictures stored as data type TEXT? This is quite common in our line of work.
Storing any binary data in TEXT column is not a good idea because of the encoding conversions applied to the TEXT column. Using some binary data type such as IMAGE or VARBINARY is the way to go.
If you configure all readers and writers to the same encoding, then you should be able to read/write binary data into TEXT column. It must be the same encoding originally used to load data into database. Because of the binary nature of picture data, there still may have been some one-way conversions when data was written to database which you won’t be able to convert back when reading. I strongly recommend against doing this. Storing purely binary data in non-binary data type is simply not a good idea.
I totally agree with what you say about storing binary picture data in the TEXT data type. But it was supported by Microsoft until now. And in our business, there are lots of companies using old versions of databases (e.g. SQL Server 2000, Access97, Btrieve 5, etc) and we cannot change them to use another format.
But just to inform you, IMAGE data type is being deprecated by Microsoft and won’t be in future versions (beyond 2008 R2) of SQL Server. They suggest using varbinary for binary data.
It’s not uncommon in our industry to be using old and/or deprecated databases. Even we still use Oracle 9i.
So is there a way to request a change in CloverETL to support images stored in TEXT fields?
We are facing similar issue for one of our graphs.We want to change default property file just for one graph, not for all graphs. How to make clover server to understand that it need to access this file for engine properties just for one particular graph.We are running the graphs through clover server. We followed following instruction-
we used -Dclover.engine.config.file=defaultProperties.file but clover is still using same properties and did not make any change in its behavior. Can you please help us over there to make clover understand that it need to access this file for this particular graph.
I am attaching screenshots of file location and vm . But it is not affecting when I launch graph from clover server.
Unfortunately, its not possible to change the default properties file on a given server graph. If you which to change default properties file it will take effect on a Server level (all your graphs on the server will be affected). You can accomplish this by adding -Dclover.engine.config.file=defaultProperties.file in the sentv.sh or sent.bat (depending on the OS).
