transformation target column not added to insert statement

[brad-57]

I am writing some Loader scripts from csvjdbc to microsoft sqlserver.  I have a majority of the simple and relation-column mappings done.  Then I wanted to tackle some transformations.

I am not getting any syntax errors on my transformations in form of javascript or java.

However, the target columns (VALUES only) do not appear in the insert statements.  It appears that they should be inserted at the end.  Here is an example output...

[OctopusTask]   Query 'insert into item(VENDOR_MODEL, SKU, version, VENDOR_ID, DEPT_ID, DESCRIPTION) VALUES ('50HP66','50HP66',10,6,1)' will be executed

The first two columns are value columns
The third is a constant column
The forth and fifth are relation columns
The sixth is a transformation column.

The Transformation itself is being called and I am returning data.  The output (System.err or whatever) is showing up just *after* this OctopusTask log output.  

And of course, it then fails with this... (when dataCutOff is true)

[OctopusTask]   org.webdocwf.util.loader.LoaderException: Exception:5
[OctopusTask]   at org.webdocwf.util.loader.Loader.loadSource(Unknown Source)
[OctopusTask]   at org.webdocwf.util.loader.Loader.load(Unknown Source)
[OctopusTask]   at org.webdocwf.util.loader.Loader.main(Unknown Source)
[OctopusTask] Caused by: java.lang.ArrayIndexOutOfBoundsException: 5
[OctopusTask]   at org.webdocwf.util.loader.DataCleaning.cutingDataLenght(Unknown Source)
[OctopusTask]   at org.webdocwf.util.loader.Loader.insertRow(Unknown Source)
[OctopusTask]   at org.webdocwf.util.loader.Loader.insertTargetTable(Unknown Source)
[OctopusTask]   ... 3 more

or

[OctopusTask] org.webdocwf.util.loader.LoaderException: SQLException:
[OctopusTask]   at org.webdocwf.util.loader.Loader.insertRow(Unknown Source)
[OctopusTask]   at org.webdocwf.util.loader.Loader.insertTargetTable(Unknown Source)
[OctopusTask]   at org.webdocwf.util.loader.Loader.loadSource(Unknown Source)
[OctopusTask]   at org.webdocwf.util.loader.Loader.load(Unknown Source)
[OctopusTask]   at org.webdocwf.util.loader.Loader.main(Unknown Source)
[OctopusTask] Caused by: java.sql.SQLException: Parameter #6 has not been set.
[OctopusTask]   at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareSQL(ConnectionJDBC2.java:602)
[OctopusTask]   at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:420)
[OctopusTask]   ... 5 more



I have tried several versions of Octopus.jar.  I have tried csv to csv, csv to jtds, csv to sqlserver (microsoft's driver).


Any ideas?

Thanks,
Brad



--
You receive this message as a subscriber of the octopus@... mailing list.
To unsubscribe: mailto:octopus-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

[brad-57]

Here is my oli file...

<definitionInclude>
    <importDefinition
        logMode="full"
        name="item"
        tableName="item">

        <!-- item.csv is TAB separated -->
        <jdbcParameters>
            <jdbcSourceParameters>
                <jdbcSourceParameter
                    name="JdbcDriver"
                    value="org.relique.jdbc.csv.CsvDriver" />
                <jdbcSourceParameter
                    name="Connection.Url"
                    value="jdbc:relique:csv:csv;separator=TAB" />
            </jdbcSourceParameters>
        </jdbcParameters>

        <valueColumns>
            <!-- valueColumn
                targetTableName="ITEM"
                sourceColumnName="DESCRIPTION"
                targetColumnName="DESCRIPTION"
                targetTableID="0"
                valueMode="Overwrite" />
                 -->
            <valueColumn
                targetTableName="VENDOR"
                targetColumnName="CODE"
                sourceColumnName="VENDOR"
                targetTableID="1"
                valueMode="Key" />
            <valueColumn
                targetTableName="DEPARTMENT"
                targetColumnName="CODE"
                sourceColumnName="DEPARTMENT"
                targetTableID="2"
                valueMode="Key" />
            <valueColumn
                targetTableName="item"
                sourceColumnName="VENDOR_MODEL"
                targetColumnName="VENDOR_MODEL"
                targetTableID="0"
                valueMode="Overwrite" />
            <valueColumn
                targetTableName="item"
                sourceColumnName="SKU2"
                targetColumnName="SKU"
                targetTableID="0"
                valueMode="Overwrite" />
        </valueColumns>

        <transformations>
            <transformation
                name="DescriptionTransformer"
                transformatorClassName="dbutil.DescriptionTransformer"
                transformatorConfig="">
                <sourceColumns>
                    <sourceColumn name="DESCRIPTION" />
                </sourceColumns>
                <targetColumns>
                    <targetColumn
                        name="description"
                        tableName="item"
                        tableID="0"
                        valueMode="Overwrite" />
                </targetColumns>
                <!--
                <javaScript>
                    retVal = new Array();
                    retVal[0] = "brad" + DESCRIPTION;
                    retVal;
                </javaScript>
                 -->
            </transformation>
        </transformations>

        <relationColumns>
            <relationColumn
                relationSourceTableName="VENDOR"
                relationSourceColumnName="VENDOR_ID"
                relationSourceTableID="1"
                relationTargetTableName="item"
                relationTargetColumnName="VENDOR_ID"
                relationTargetTableID="0"
                defaultValue="0"
                relationMode="Overwrite" />
            <relationColumn
                relationSourceTableName="DEPARTMENT"
                relationSourceColumnName="DEPT_ID"
                relationSourceTableID="2"
                relationTargetTableName="item"
                relationTargetColumnName="DEPT_ID"
                relationTargetTableID="0"
                defaultValue="0"
                relationMode="Overwrite" />
        </relationColumns>
        <constantColumns>
            <constantColumn
                targetTableName="item"
                targetColumnName="version"
                constantValue="1"
                targetTableID="0"
                valueMode="Overwrite" />
        </constantColumns>
        <tables>
            <table
                tableName="item"
                tableID="0"
                insert="true"
                tableMode="Query"
                oidLogic="false" />
            <table
                tableName="vendor"
                tableID="1"
                insert="false"
                tableMode="Query"
                oidLogic="false" />
            <table
                tableName="department"
                tableID="2"
                insert="false"
                tableMode="Query"
                oidLogic="false" />
        </tables>
    </importDefinition>
</definitionInclude>



--
You receive this message as a subscriber of the octopus@... mailing list.
To unsubscribe: mailto:octopus-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

[yuanxk]

From my experience,  you have to put the table for insertion last in the tables section to make the relationship and transformation work. The table ID for the table to be inserted needs to be the greatest.

 

In your case, try use               
tableID="0"               tableName="department"
tableID="1"               tableName="vendor"
tableID="2                tableName="item"               "
 

 

On 2/2/07, brad@... <brad@...> wrote:

Here is my oli file...

<definitionInclude>
   <importDefinition
       logMode="full"
       name="item"
       tableName="item">

       <!-- item.csv is TAB separated -->
       <jdbcParameters>
           <jdbcSourceParameters>
               <jdbcSourceParameter
                   name="JdbcDriver"
                   value="org.relique.jdbc.csv.CsvDriver" />
               <jdbcSourceParameter
                   name="Connection.Url"
                   value="jdbc:relique:csv:csv;separator=TAB" />
           </jdbcSourceParameters>
       </jdbcParameters>

       <valueColumns>
           <!-- valueColumn
               targetTableName="ITEM"
               sourceColumnName="DESCRIPTION"
               targetColumnName="DESCRIPTION"
               targetTableID="0"
               valueMode="Overwrite" />
                -->
           <valueColumn
               targetTableName="VENDOR"
               targetColumnName="CODE"
               sourceColumnName="VENDOR"
               targetTableID="1"
               valueMode="Key" />
           <valueColumn
               targetTableName="DEPARTMENT"
               targetColumnName="CODE"
               sourceColumnName="DEPARTMENT"
               targetTableID="2"
               valueMode="Key" />
           <valueColumn
               targetTableName="item"
               sourceColumnName="VENDOR_MODEL"
               targetColumnName="VENDOR_MODEL"
               targetTableID="0"
               valueMode="Overwrite" />
           <valueColumn
               targetTableName="item"
               sourceColumnName="SKU2"
               targetColumnName="SKU"
               targetTableID="0"
               valueMode="Overwrite" />
       </valueColumns>

       <transformations>
           <transformation
               name="DescriptionTransformer"
               transformatorClassName="dbutil.DescriptionTransformer"
               transformatorConfig="">
               <sourceColumns>
                   <sourceColumn name="DESCRIPTION" />
               </sourceColumns>
               <targetColumns>
                   <targetColumn
                       name="description"
                       tableName="item"
                       tableID="0"
                       valueMode="Overwrite" />
               </targetColumns>
               <!--
               <javaScript>
                   retVal = new Array();
                   retVal[0] = "brad" + DESCRIPTION;
                   retVal;
               </javaScript>
                -->
           </transformation>
       </transformations>

       <relationColumns>
           <relationColumn
               relationSourceTableName="VENDOR"
               relationSourceColumnName="VENDOR_ID"
               relationSourceTableID="1"
               relationTargetTableName="item"
               relationTargetColumnName="VENDOR_ID"
               relationTargetTableID="0"
               defaultValue="0"
               relationMode="Overwrite" />
           <relationColumn
               relationSourceTableName="DEPARTMENT"
               relationSourceColumnName="DEPT_ID"
               relationSourceTableID="2"
               relationTargetTableName="item"
               relationTargetColumnName="DEPT_ID"
               relationTargetTableID="0"
               defaultValue="0"
               relationMode="Overwrite" />
       </relationColumns>
       <constantColumns>
           <constantColumn
               targetTableName="item"
               targetColumnName="version"
               constantValue="1"
               targetTableID="0"
               valueMode="Overwrite" />
       </constantColumns>
       <tables>
           <table
               tableName="item"
               tableID="0"
               insert="true"
               tableMode="Query"
               oidLogic="false" />
           <table
               tableName="vendor"
               tableID="1"
               insert="false"
               tableMode="Query"
               oidLogic="false" />
           <table
               tableName="department"
               tableID="2"
               insert="false"
               tableMode="Query"
               oidLogic="false" />
       </tables>
   </importDefinition>
</definitionInclude>




--
You receive this message as a subscriber of the octopus@... mailing list.
To unsubscribe: mailto:octopus-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

--
You receive this message as a subscriber of the octopus@... mailing list.
To unsubscribe: mailto:octopus-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

[brad-57]

That was it... thank you very much for the response.




--
You receive this message as a subscriber of the octopus@... mailing list.
To unsubscribe: mailto:octopus-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws