Scripting Suggestion?

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Scripting Suggestion?

pgrimard-2

Yesterday I successfully implemented my first digester class and it works great,
just like I would want it to.  Now I need to take my XML file a step further by
including some form of scripting.  My XML file is basically used to map tables
and fields between 2 datasources so that my java process can pull from one and
dump to the other.  The issue however is that the columns in the target
datasource are all string columns and my source data columns aren't always
string columns and are sometimes also longer in length than the target columns.
My process will have logic to determine the source datatype and convert it to a
proper string, I just don't want to create methods for each field that might
need to be substringed before inserting into the target datasource because I
want the java process to be independent of the actual data it's processing,
hence the XML file.  It's not enough to say I can just check the length of the
target column and substring the source accordingly, because the result might not
be what I want, ie: a boolean won't automatically convert to Y or N which my
target datasource wants.  Does anybody have any suggestions?  Thanks in advance.

Here's a sample of what my XML file might look like:

<harvester>
      <datasources>
            <datasource>
                  <name>source</name>
                  <driver>some.driver</driver>
                  <url>the.url</url>
                  <username></username>
                  <password></password>
            </datasource>
            <datasource>
                  <name>destination</name>
                  <driver>some.driver</driver>
                  <url>the.url</url>
                  <username></username>
                  <password></password>
            </datasource>
      </datasources>

      <tables>
            <table>
                  <sourceTable>tableName</sourceTable>
                  <destTable>tableName</destTable>
                  <fields>
                        <field>
                              <sourceField>fieldName</sourceField>
                              <destField>fieldName</destField>
                              <transformSource>
                                    <!-- would like some kind of script here
                                    to say how to convert/trim the source value
-->
                              </transformSource>
                        </field>
                        <field>
                              <sourceField>fieldName</sourceField>
                              <destField>fieldName</destField>
                        </field>
                        <field>
                              <sourceField>fieldName</sourceField>
                              <destField>fieldName</destField>
                        </field>
                  </fields>
            </table>
      </tables>
</harvester>
(Embedded image moved to file: pic15141.jpg)

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Scripting Suggestion?

pgrimard-2
To add to this, some source fields I may not want to substring from index=0.  I
may want to substring from the middle of the source value.
(Embedded image moved to file: pic22704.jpg)



                                                                               
             Patrick.Grimard@xtl                                                
             .com                                                              
                                                                             To
             09/10/2008 07:38 AM         [hidden email]                
                                                                             cc
                                                                               
              Please respond to                                         Subject
               "Commons Users            Scripting Suggestion?                  
                    List"                                                      
             <[hidden email]                                                
                   e.org>                                                      
                                                                               
                                                                               
                                                                               





Yesterday I successfully implemented my first digester class and it works great,
just like I would want it to.  Now I need to take my XML file a step further by
including some form of scripting.  My XML file is basically used to map tables
and fields between 2 datasources so that my java process can pull from one and
dump to the other.  The issue however is that the columns in the target
datasource are all string columns and my source data columns aren't always
string columns and are sometimes also longer in length than the target columns.
My process will have logic to determine the source datatype and convert it to a
proper string, I just don't want to create methods for each field that might
need to be substringed before inserting into the target datasource because I
want the java process to be independent of the actual data it's processing,
hence the XML file.  It's not enough to say I can just check the length of the
target column and substring the source accordingly, because the result might not
be what I want, ie: a boolean won't automatically convert to Y or N which my
target datasource wants.  Does anybody have any suggestions?  Thanks in advance.

Here's a sample of what my XML file might look like:

<harvester>
      <datasources>
            <datasource>
                  <name>source</name>
                  <driver>some.driver</driver>
                  <url>the.url</url>
                  <username></username>
                  <password></password>
            </datasource>
            <datasource>
                  <name>destination</name>
                  <driver>some.driver</driver>
                  <url>the.url</url>
                  <username></username>
                  <password></password>
            </datasource>
      </datasources>

      <tables>
            <table>
                  <sourceTable>tableName</sourceTable>
                  <destTable>tableName</destTable>
                  <fields>
                        <field>
                              <sourceField>fieldName</sourceField>
                              <destField>fieldName</destField>
                              <transformSource>
                                    <!-- would like some kind of script here
to say how to convert/trim the source value
-->
                              </transformSource>
                        </field>
                        <field>
                              <sourceField>fieldName</sourceField>
                              <destField>fieldName</destField>
                        </field>
                        <field>
                              <sourceField>fieldName</sourceField>
                              <destField>fieldName</destField>
                        </field>
                  </fields>
            </table>
      </tables>
</harvester>
(Embedded image moved to file: pic15141.jpg)
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Scripting Suggestion?

pgrimard-2
I figured out an alternative.  Primarily, all source data needs to get truncated
to fit the target columns.  The only source data I might need to do something
tricky with appears to be java.sql.Timestamp values.  One particular column in
my target table that is a String data type that holds a date value wants to see
the dates as YYYY-MM-DD-HH-MM-SS.  So when I'm getting values from my source
data ResultSet, rather than just using the getString() method, I'm using the
getObject() method instead.

Doing it that way, I'm able to test the returned Object to see what class it's
an instance of.  This allows me to convert the data to a string and do any other
conversions that are specific to the source data type.  In the case of my date
above, I'm replacing the " " (space) character between the data and time with a
"-" (dash) and dropping the nanos.  That gives me the converted value I need.
So no fancy scripting needed in my XML.
(Embedded image moved to file: pic04107.jpg)



                                                                               
             Patrick.Grimard@xtl                                                
             .com                                                              
                                                                             To
             09/10/2008 07:41 AM         "Commons Users List"                  
                                         <[hidden email]>              
                                                                             cc
              Please respond to                                                
               "Commons Users                                           Subject
                    List"                Re: Scripting Suggestion?              
             <[hidden email]                                                
                   e.org>                                                      
                                                                               
                                                                               
                                                                               
                                                                               




To add to this, some source fields I may not want to substring from index=0.  I
may want to substring from the middle of the source value.
(Embedded image moved to file: pic22704.jpg)




             Patrick.Grimard@xtl
             .com
                                                                             To
             09/10/2008 07:38 AM         [hidden email]
                                                                             cc

              Please respond to                                         Subject
               "Commons Users            Scripting Suggestion?
                    List"
             <[hidden email]
                   e.org>








Yesterday I successfully implemented my first digester class and it works great,
just like I would want it to.  Now I need to take my XML file a step further by
including some form of scripting.  My XML file is basically used to map tables
and fields between 2 datasources so that my java process can pull from one and
dump to the other.  The issue however is that the columns in the target
datasource are all string columns and my source data columns aren't always
string columns and are sometimes also longer in length than the target columns.
My process will have logic to determine the source datatype and convert it to a
proper string, I just don't want to create methods for each field that might
need to be substringed before inserting into the target datasource because I
want the java process to be independent of the actual data it's processing,
hence the XML file.  It's not enough to say I can just check the length of the
target column and substring the source accordingly, because the result might not
be what I want, ie: a boolean won't automatically convert to Y or N which my
target datasource wants.  Does anybody have any suggestions?  Thanks in advance.

Here's a sample of what my XML file might look like:

<harvester>
      <datasources>
            <datasource>
                  <name>source</name>
                  <driver>some.driver</driver>
                  <url>the.url</url>
                  <username></username>
                  <password></password>
            </datasource>
            <datasource>
                  <name>destination</name>
                  <driver>some.driver</driver>
                  <url>the.url</url>
                  <username></username>
                  <password></password>
            </datasource>
      </datasources>

      <tables>
            <table>
                  <sourceTable>tableName</sourceTable>
                  <destTable>tableName</destTable>
                  <fields>
                        <field>
                              <sourceField>fieldName</sourceField>
                              <destField>fieldName</destField>
                              <transformSource>
                                    <!-- would like some kind of script here
to
say how to convert/trim the source value
-->
                              </transformSource>
                        </field>
                        <field>
                              <sourceField>fieldName</sourceField>
                              <destField>fieldName</destField>
                        </field>
                        <field>
                              <sourceField>fieldName</sourceField>
                              <destField>fieldName</destField>
                        </field>
                  </fields>
            </table>
      </tables>
</harvester>
(Embedded image moved to file: pic15141.jpg)
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Scripting Suggestion?

Simon Kitching
[hidden email] schrieb:

> I figured out an alternative.  Primarily, all source data needs to get truncated
> to fit the target columns.  The only source data I might need to do something
> tricky with appears to be java.sql.Timestamp values.  One particular column in
> my target table that is a String data type that holds a date value wants to see
> the dates as YYYY-MM-DD-HH-MM-SS.  So when I'm getting values from my source
> data ResultSet, rather than just using the getString() method, I'm using the
> getObject() method instead.
>
> Doing it that way, I'm able to test the returned Object to see what class it's
> an instance of.  This allows me to convert the data to a string and do any other
> conversions that are specific to the source data type.  In the case of my date
> above, I'm replacing the " " (space) character between the data and time with a
> "-" (dash) and dropping the nanos.  That gives me the converted value I need.
> So no fancy scripting needed in my XML.
>  

Ok, thanks for letting us know. And good luck with your project.

And by the way, if you have any future questions for this list then
please put the name of the commons project in the subject line, eg
  [digester] scripting suggestion

Regards, Simon


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Scripting Suggestion?

pgrimard-2
Thanks Simon.
(Embedded image moved to file: pic05447.jpg)



                                                                               
             Simon Kitching                                                    
             <[hidden email]                                                
             rg>                                                             To
                                         Commons Users List                    
             09/10/2008 10:49 AM         <[hidden email]>              
                                                                             cc
                                                                               
              Please respond to                                         Subject
               "Commons Users            Re: Scripting Suggestion?              
                    List"                                                      
             <[hidden email]                                                
                   e.org>                                                      
                                                                               
                                                                               
                                                                               




[hidden email] schrieb:
> I figured out an alternative.  Primarily, all source data needs to get
truncated
> to fit the target columns.  The only source data I might need to do something
> tricky with appears to be java.sql.Timestamp values.  One particular column in
> my target table that is a String data type that holds a date value wants to
see
> the dates as YYYY-MM-DD-HH-MM-SS.  So when I'm getting values from my source
> data ResultSet, rather than just using the getString() method, I'm using the
> getObject() method instead.
>
> Doing it that way, I'm able to test the returned Object to see what class it's
> an instance of.  This allows me to convert the data to a string and do any
other
> conversions that are specific to the source data type.  In the case of my date
> above, I'm replacing the " " (space) character between the data and time with
a
> "-" (dash) and dropping the nanos.  That gives me the converted value I need.
> So no fancy scripting needed in my XML.
>

Ok, thanks for letting us know. And good luck with your project.

And by the way, if you have any future questions for this list then
please put the name of the commons project in the subject line, eg
  [digester] scripting suggestion

Regards, Simon


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

[beanutils] RowSetDynaClass

pgrimard-2
In reply to this post by Simon Kitching
I'm having a ConversionException issue when creating a RowSetDynaClass from a
ResultSet.  Apparently I'm not the only person's who's experienced this.  The
exception is saying "Cannot assign value of type 'java.lang.Integer' to property
'vsize' of type 'java.lang.Short'.  Does somebody have a solution to this
problem?  I'm using Beanutils 1.8.
(Embedded image moved to file: pic25667.jpg)

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: [beanutils] RowSetDynaClass

Niall Pemberton
On Wed, Sep 10, 2008 at 8:35 PM,  <[hidden email]> wrote:
> I'm having a ConversionException issue when creating a RowSetDynaClass from a
> ResultSet.  Apparently I'm not the only person's who's experienced this.  The
> exception is saying "Cannot assign value of type 'java.lang.Integer' to property
> 'vsize' of type 'java.lang.Short'.  Does somebody have a solution to this
> problem?  I'm using Beanutils 1.8.

You could override the getObject(ResultSet, String) method and use
ConvertUtils to convert the value, something like:

protected Object getObject(ResultSet resultSet, String name) throws
SQLException {
    Object value = super.getObject(resultSet, name);
    DynaProperty property = getDynaProperty(name);
    if (property != null) {
        value = ConvertUtils.convert(value, property.getType());
    }
    return value;
}

I did something like this for BEANUTILS-142[1] - but then backed it
out - because it didn't solve that particular issue - and used another
solution. But perhaps I should have left that in as well.

Niall


[1] http://issues.apache.org/jira/browse/BEANUTILS-142

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [beanutils] RowSetDynaClass

pgrimard-2
Thanks Niall,

I took your suggestion and just had to modify it a bit.  At first when I tried
your suggestion, my process crashed when it came across a java.sql.Timestamp
property.  What I did in the end was look at the source of JDBCDynaClass and I
copied some of the logic from it's getObject() method into my class that extends
RowSetDynaClass.  The code I copied basically handles Timestamp, Date and Time
properties just like JDBCDynaClass, except I'm handling them like "if else
if..." instead of just a normal if statement for each.  That allowed me to add
your suggested code as the last option in my if else if tree.

Thanks again.
Patrick



                                                                               
             "Niall Pemberton"                                                  
             <niall.pemberton@gm                                                
             ail.com>                                                        To
                                         "Commons Users List"                  
             09/10/2008 05:29 PM         <[hidden email]>              
                                                                             cc
                                                                               
              Please respond to                                         Subject
               "Commons Users            Re: [beanutils] RowSetDynaClass        
                    List"                                                      
             <[hidden email]                                                
                   e.org>                                                      
                                                                               
                                                                               
                                                                               




On Wed, Sep 10, 2008 at 8:35 PM,  <[hidden email]> wrote:
> I'm having a ConversionException issue when creating a RowSetDynaClass from a
> ResultSet.  Apparently I'm not the only person's who's experienced this.  The
> exception is saying "Cannot assign value of type 'java.lang.Integer' to
property
> 'vsize' of type 'java.lang.Short'.  Does somebody have a solution to this
> problem?  I'm using Beanutils 1.8.

You could override the getObject(ResultSet, String) method and use
ConvertUtils to convert the value, something like:

protected Object getObject(ResultSet resultSet, String name) throws
SQLException {
    Object value = super.getObject(resultSet, name);
    DynaProperty property = getDynaProperty(name);
    if (property != null) {
        value = ConvertUtils.convert(value, property.getType());
    }
    return value;
}

I did something like this for BEANUTILS-142[1] - but then backed it
out - because it didn't solve that particular issue - and used another
solution. But perhaps I should have left that in as well.

Niall


[1] http://issues.apache.org/jira/browse/BEANUTILS-142

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]





---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [beanutils] RowSetDynaClass

pgrimard-2
I'm running into a new issue that seems to be intermittent.  The process I
created selects data from tables in one database and inserts it into another
database.  At the beginning of my process, I initialize an array of
HarvestRowSetDynaClass (this is my custom class that extends RowSetDynaClass)
with an initial array length equivalent to the number of tables I need to
harvest in my source database.  The source database is constantly being updated
over the web.  The query to the first table returns 20000 records (not sure why
it's such a round number), but that changes as the database gets updated from
the web.  I create a new HarvestRowSetDynaClass object in my array for each
ResultSet.  Later in my process, I iterate through my array, get the rows from
each HarvestRowSetDynaClass object, then I iterate over it's DynaBeans to get
the data and insert it into my target database.  Every once in a while though,
my process gets through the 20000 records from the first HarvestRowSetDynaClass
object, moves on to the next one in the array and throws a NullPointerException.
This behaviour seems intermittent because the process runs fine at other times.

Could this be due to the garbage collector clearing my HarvestRowSetDynaClass
array before I have the chance to read from it again?  Would I be better off
storing my HarvestRowSetDynaClass objects in a list rather than just a simple
array?

Thanks,
Patrick



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [beanutils] RowSetDynaClass

pgrimard-2
When a RowSetDynaClass object is created from a ResultSet, how does it handle
deleted records in the ResultSet?  Does each object in the DynaBean representing
a deleted record just get a null value?  I looked at the source of
JDBCDynaClass, specifically the introspect method.  I don't see that it handles
deleted records.  In my class when I'm retrieving data from the DynaBean, should
I have a check to see if the value of an object is null to avoid a
NullPointerException?
(Embedded image moved to file: pic29599.jpg)

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]