The solution is too use kettle() and save the script below in a importimage.ktr file, open it with kettle, change the db settings. Create your image field in the bdd as a charvarying type and an other one with a binary type. So we import in the charvarying field and copy in the binary field with casting.(This is needed because the binary type in kettle is not directly compatible with postgresql.
At the end, once all of your images are imported you need to cast the type with the postresql command: UPDATE table_media SET picture = picture_char::bytea And to save disk space execute this command to clear the non-used field UPDATE table_media SET picture_char = '' <xml> <transformation> <info> <name>Test script reading files into BLOBs</name> <description> <extended_description> <trans_version> <directory>/</directory> <log> <read> <write> <input> <output> <update> <rejected> <connection> <table> <use_batchid>Y</use_batchid> <use_logfield>N</use_logfield> </log> <maxdate> <connection> <table> <field> <offset>0.0</offset> <maxdiff>0.0</maxdiff> </maxdate> <size_rowset>1000</size_rowset> <sleep_time_empty>1</sleep_time_empty> <sleep_time_full>1</sleep_time_full> <unique_connections>N</unique_connections> <feedback_shown>Y</feedback_shown> <feedback_size>5000</feedback_size> <using_thread_priorities>Y</using_thread_priorities> <shared_objects_file> <dependencies> </dependencies> <partitionschemas> </partitionschemas> <slaveservers> <slaveserver><name>localhost:8080:Master</name><hostname>localhost</hostname><port>8080</port><username>cluster</username><password>Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8</password><proxy_hostname><proxy_port><non_proxy_hosts><master>Y</master></slaveserver> <slaveserver><name>localhost:8082</name><hostname>localhost</hostname><port>8082</port><username>cluster</username><password>Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8</password><proxy_hostname><proxy_port><non_proxy_hosts><master>N</master></slaveserver> <slaveserver><name>localhost:8081</name><hostname>localhost</hostname><port>8081</port><username>cluster</username><password>Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8</password><proxy_hostname><proxy_port><non_proxy_hosts><master>N</master></slaveserver> <slaveserver><name>localhost:8080</name><hostname>localhost</hostname><port>8080</port><username>cluster</username><password>Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8</password><proxy_hostname><proxy_port><non_proxy_hosts><master>N</master></slaveserver> <slaveserver><name>localhost:8084</name><hostname>localhost</hostname><port>8084</port><username>cluster</username><password>Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8</password><proxy_hostname><proxy_port><non_proxy_hosts><master>N</master></slaveserver> <slaveserver><name>localhost:8083</name><hostname>localhost</hostname><port>8083</port><username>cluster</username><password>Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8</password><proxy_hostname><proxy_port><non_proxy_hosts><master>N</master></slaveserver> </slaveservers> <clusterschemas> <clusterschema> <name>local schema</name> <base_port>40000</base_port> <sockets_buffer_size>2000</sockets_buffer_size> <sockets_flush_interval>5000</sockets_flush_interval> <sockets_compressed>N</sockets_compressed> <slaveservers> <name>localhost:8080:Master</name> <name>localhost:8081</name> <name>localhost:8082</name> </slaveservers> </clusterschema> </clusterschemas> <modified_user> <modified_date>2007/09/11 21:34:50.919</modified_date> </info> <notepads> </notepads> <connection> <name>MySQL localhost test</name> <server>localhost</server> <type>MYSQL</type> <access>Native</access> <database>test</database> <port>3306</port> <username>matt</username> <password>Encrypted 2be98afc86aa7f2e4cb79ce10df90acde</password> <servername> <data_tablespace> <index_tablespace> <attributes> <attribute><code>CUSTOM_DRIVER_CLASS</code><attribute>com.ibm.u2.jdbc.UniJDBCDriver</attribute></attribute> <attribute><code>CUSTOM_URL</code><attribute>jdbc:universe://localhost/database</attribute></attribute> <attribute><code>EXTRA_OPTION_MYSQL.defaultFetchSize</code><attribute>500</attribute></attribute> <attribute><code>EXTRA_OPTION_MYSQL.rewriteBatchedStatements</code><attribute>false</attribute></attribute> <attribute><code>EXTRA_OPTION_MYSQL.useCursorFetch</code><attribute>true</attribute></attribute> <attribute><code>EXTRA_OPTION_MYSQL.zeroDateTimeBehavior</code><attribute>convertToNull</attribute></attribute> <attribute><code>EXTRA_OPTION_SYBASE.SQLINITSTRING</code><attribute>SET CHAINED OFF</attribute></attribute> <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute> <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute> <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute> <attribute><code>MAXIMUM_POOL_SIZE</code><attribute>10</attribute></attribute> <attribute><code>MSSQL_DOUBLE_DECIMAL_SEPARATOR</code><attribute>N</attribute></attribute> <attribute><code>POOLING_defaultCatalog</code><attribute>catalog</attribute></attribute> <attribute><code>POOLING_removeAbandoned</code><attribute>true</attribute></attribute> <attribute><code>POOLING_testOnReturn</code><attribute>false</attribute></attribute> <attribute><code>PORT_NUMBER</code><attribute>3306</attribute></attribute> <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute> <attribute><code>STREAM_RESULTS</code><attribute>Y</attribute></attribute> <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute> </attributes> </connection> <connection> <name>ORCL 8104</name> <server>OracleServer</server> <type>ORACLE</type> <access>Native</access> <database>ORCL</database> <port>1521</port> <username>scott</username> <password>Encrypted 2be98afc86aa7f2e4cb79ce64d795aac8</password> <servername> <data_tablespace> <index_tablespace> <attributes> <attribute><code>EXTRA_OPTION_MYSQL.defaultFetchSize</code><attribute>500</attribute></attribute> <attribute><code>EXTRA_OPTION_MYSQL.useCursorFetch</code><attribute>true</attribute></attribute> <attribute><code>PORT_NUMBER</code><attribute>1521</attribute></attribute> </attributes> </connection> <connection> <name>192.168.0.41</name> <server>192.168.0.41</server> <type>POSTGRESQL</type> <access>Native</access> <database>test2</database> <port>5432</port> <username>axe</username> <password>Encrypted 2be98afc86aa7f2e4cb79b6688cc4fb88</password> <servername> <data_tablespace> <index_tablespace> <attributes> <attribute><code>EXTRA_OPTION_MYSQL.defaultFetchSize</code><attribute>500</attribute></attribute> <attribute><code>EXTRA_OPTION_MYSQL.useCursorFetch</code><attribute>true</attribute></attribute> <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute> <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute> <attribute><code>INITIAL_POOL_SIZE</code><attribute>5</attribute></attribute> <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute> <attribute><code>MAXIMUM_POOL_SIZE</code><attribute>10</attribute></attribute> <attribute><code>MSSQL_DOUBLE_DECIMAL_SEPARATOR</code><attribute>N</attribute></attribute> <attribute><code>PORT_NUMBER</code><attribute>5432</attribute></attribute> <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute> <attribute><code>STREAM_RESULTS</code><attribute>Y</attribute></attribute> <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute> </attributes> </connection> <order> <hop> <from>Get File Names</from><to>Java Script Value</to><enabled>Y</enabled> </hop> <hop> <from>Java Script Value</from><to>Ajout constantes</to><enabled>Y</enabled> </hop> <hop> <from>Ajout constantes</from><to>Insertion / Mise à jour table</to><enabled>Y</enabled> </hop> </order> <step> <name>Ajout constantes</name> <type>Constant</type> <description> <distribute>Y</distribute> <copies>1</copies> <partitioning> <method>none</method> <schema_name> </partitioning> <fields> <field> <name>idconst</name> <type>Integer</type> <format> <currency> <decimal> <group> <nullif>5</nullif> <length>-1</length> <precision>-1</precision> </field> <field> <name>product_id</name> <type>Integer</type> <format> <currency> <decimal> <group> <nullif>1</nullif> <length>-1</length> <precision>-1</precision> </field> </fields> <cluster_schema> <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI> <xloc>312</xloc> <yloc>213</yloc> <draw>Y</draw> </GUI> </step> <step> <name>Get File Names</name> <type>GetFileNames</type> <description> <distribute>Y</distribute> <copies>1</copies> <partitioning> <method>none</method> <schema_name> </partitioning> <filter> <filterfiletype>all_files</filterfiletype> </filter> <file> <name>${Internal.Transformation.Filename.Directory}/images/</name> <filemask>.*\.png$</filemask> <file_required> </file> <cluster_schema> <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI> <xloc>96</xloc> <yloc>86</yloc> <draw>Y</draw> </GUI> </step> <step> <name>Insertion / Mise à jour table</name> <type>InsertUpdate</type> <description> <distribute>Y</distribute> <copies>1</copies> <partitioning> <method>none</method> <schema_name> </partitioning> <connection>192.168.0.41</connection> <commit>100</commit> <update_bypassed>N</update_bypassed> <lookup> <schema> <table>table_media</table> <key> <name>idconst</name> <field>id</field> <condition>=</condition> <name2> </key> <value> <name>product_id</name> <rename>product_id</rename> <update>Y</update> </value> <value> <name>picture_char</name> <rename>encString</rename> <update>Y</update> </value> </lookup> <cluster_schema> <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI> <xloc>504</xloc> <yloc>213</yloc> <draw>Y</draw> </GUI> </step> <step> <name>Java Script Value</name> <type>ScriptValueMod</type> <description> <distribute>Y</distribute> <copies>1</copies> <partitioning> <method>none</method> <schema_name> </partitioning> <compatible>Y</compatible> <jsScripts> <jsScript> <jsScript_type>0</jsScript_type> <jsScript_name>ScriptValue</jsScript_name> <jsScript_script>file = new Packages.java.io.File(filename.getString()); fileInputStream = new Packages.java.io.FileInputStream(file); var content = Packages.org.pentaho.di.core.Const.createByteArray(file.length()); fileInputStream.read(content, 0, file.length()); var encString = new Packages.java.lang.String( Packages.org.apache.commons.codec.binary.Base64.encodeBase64( content) ); //var encString2 = new Packages.org.apache.commons.codec.binary.Base64.encodeBase64( content ); fileInputStream.close(); </jsScript_script> </jsScript> </jsScripts> <fields> <field> <name>content</name> <rename>content</rename> <type>Binary</type> <length>-1</length> <precision>-1</precision> </field> <field> <name>encString</name> <rename>encString</rename> <type>String</type> <length>-1</length> <precision>-1</precision> </field> </fields> <cluster_schema> <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI> <xloc>295</xloc> <yloc>84</yloc> <draw>Y</draw> </GUI> </step> <step_error_handling> </step_error_handling> <slave> </slave> <slave_transformation>N</slave_transformation> </transformation> _______________________________________________ Tinyerp-users mailing list http://tiny.be/mailman/listinfo/tinyerp-users
