Hitachi Vantara Pentaho Community Forums
Results 1 to 32 of 32

Thread: Generic URL and Driver Class

  1. #1

    Default Generic URL and Driver Class

    Is there a plan to allow the Generic URL and Driver Class parameters the ability to be populated via environment variables?

    I believe this single feature will open up many new possibilities and solve many existing questions.

    In my research on this forum regarding this issue, I have come across numerous problems/topics that I believe could be solved by this one little feature. If anyone cares, here is that list:I've been looking at the code and I do not think it would be too difficult. If there is not a feature request currently for this, I would be glad to open one and possibly provide a patch for it...

    Regards,
    JM

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    A change request is already created at http://www.javaforge.com/proj/tracke...e&task_id=4443

    The URL could be opened up, the driver class most likely not... there's too much dependent on driver class.

    Regards,
    Sven
    Last edited by sboden; 01-31-2007 at 03:30 AM.

  3. #3

    Default

    Sven, thanks for the link. However, I do not believe that is asking for the same thing that I am asking about.

    In that enhancement request, the 'connection type' (e.g. Oracle, MySQL ... etc.) is the target.

    My question/request deals specifically within the realm of a 'Generic' connection type. I would expect the URL and Driver Class attributes to only be parameterizable (a real word?) when the 'connection type' is 'Generic'. This essentially leaves the configuration of the connection up to the end user (by setting env vars) or possibly an external custom JAVA based launcher.

    In this scenario, the transformations and jobs really only need to care about a 'connection name' ... all of the details are filled in at runtime. IMHO (tee hee) this would lead to a significantly more 'runtime friendly' setup.

    I think the 'nuts and bolts' of this could be implemented by making small tweaks to the getDriverClass() and getURL(...) methods of GenericDatabaseMeta class.
    Additionally, there would have to be tweaks to the GUI changing the URL and Driver Class fields from class Text to class TextVar...
    EDIT: after looking at it a bit more ... it won't be this easy

    I truly understand the concern about 'over parameterizing' things. Trying to match the vast array of GUI config options without making it confusing and messy to maintain could be a problem.

    This brings me to an interesting thought. How difficult would it be to create a 'generic jdbc dB connection' input (and output) step? Basically some kind of interface that allows parsing of the connection info from an external configuration point (.properties, env vars ... etc)


    I am in particular need of this Generic connection parameterization feature because I am dealing with 500+ databases on different servers, all with distinct connection info. Because of the particular database's JDBC driver, I am forced to use the Generic connection type. More importantly, the ETL process that I wish to run will be identical for all of these databases.

    Thanks for reading...

    Regards,
    JM
    Last edited by JMOSSER_NISC; 01-31-2007 at 01:37 PM. Reason: I'm too optimistic...

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    Quote Originally Posted by JMOSSER_NISC View Post
    Sven, thanks for the link. However, I do not believe that is asking for the same thing that I am asking about.

    In that enhancement request, the 'connection type' (e.g. Oracle, MySQL ... etc.) is the target.
    Well... the URLs in your original post also aren't on the same thing as you want specifically. Currently the important fields of the regular database connections can be configured by environment variables (host, userid, password, port), the connection type isn't.

    Personally I want to know which is the database a job/transformation is written for. 99% of the ETL will only ever run against 1 type of database, and most SQLs (real world SQL) are only usable one 1 type of database anyway.

    For generic type the driver is the connection type to me, the URL the "parameters". I have no problem making the URL variable.


    Quote Originally Posted by JMOSSER_NISC View Post
    This brings me to an interesting thought. How difficult would it be to create a 'generic jdbc dB connection' input (and output) step? Basically some kind of interface that allows parsing of the connection info from an external configuration point (.properties, env vars ... etc)
    Depending what you want in it, aren't you making it yourself too difficult? I don't see it happening as a change to the current input/output steps. And as seperate steps you get code duplication, ...

    Quote Originally Posted by JMOSSER_NISC View Post
    I am in particular need of this Generic connection parameterization feature because I am dealing with 500+ databases on different servers, all with distinct connection info. Because of the particular database's JDBC driver, I am forced to use the Generic connection type. More importantly, the ETL process that I wish to run will be identical for all of these databases.
    What's actually your use case, which different types of databases do you use? Which database don't we support yet?

    Regards,
    Sven
    Last edited by sboden; 01-31-2007 at 03:19 PM.

  5. #5

    Default I can see clearly now...

    Quote Originally Posted by sboden View Post
    Well... the URLs in your original post also aren't on the same thing as you want specifically.
    Good point. At the time, many of them seemed similar, but now that I go back and review ... many of them are not.

    Quote Originally Posted by sboden View Post
    Currently the important fields of the regular database connections can be configured by environment variables (host, userid, password, port), the connection type isn't.
    Very true. However since my particular source dB is not currently in the supported list (and I would doubt there is adequate demand ... see below for my use-case info) I tried to use existing functionality but ran into this tiny roadblock.

    Quote Originally Posted by sboden View Post
    Personally I want to know which is the database a job/transformation is written for. 99% of the ETL will only ever run against 1 type of database, and most SQLs (real world SQL) are only usable one 1 type of database anyway.
    Now I see why KETTLE has such highly custom, database specific connection support. I guess I never looked at it that way, but it does make perfect sense. Too bad SQL isn't more generic. I tend to be all for 'generic' interfacing when possible but I also understand the need for ease of configuration.


    Quote Originally Posted by sboden View Post
    For generic type the driver is the connection type to me, the URL the "parameters".
    Ahh, now I see. Makes sense to me.

    Quote Originally Posted by sboden View Post
    I have no problem making the URL variable, if SVN is back in order that takes 10 minutes including GUI changes (something for tomorrow evening probably).
    Excellent. What kind of beer (or other tasty beverage) do you enjoy?

    Quote Originally Posted by sboden View Post
    Depending what you want in it, aren't you making it yourself too difficult? I don't see it happening as a change to the current input/output steps. And as seperate steps you get code duplication, ...
    Yep, I think it would be too difficult and redundant. With your comments I understand your point of view much better. I think parameterizing the URL would be an optimal solution.


    Quote Originally Posted by sboden View Post
    What's actually your use case, which different types of databases do you use? Which database don't we support yet?
    My source dB is Progress and the target is Oracle.

    I am guessing you guys do not have Progress in the 'connection type' list due to their JDBC driver (and .dll/.so) licensing, correct? I was able to configure Spoon to work with Progress fairly easily using the Generic connection type. I really don't think they have any wiz-bang features that would need customized support so I don't foresee them getting on the list any time soon.

    Anyway, if the URL parameter for Generic connection type is made to be 'soft' I will be ever so grateful.

    Thanks for the response and explanation. Keep up the great work.

    Regards,
    JM

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default Progress

    Actually, I asked for integration support from Progress several time, they "would get back to me".
    Perhaps we can ask one of the JDBC vendors to include a decent demo driver or something.
    So, it's not of lack of interest or will that there is no support in Kettle at this time.

    Matt

  7. #7

    Default Progress, how I love thee...

    Quote Originally Posted by MattCasters View Post
    Actually, I asked for integration support from Progress several time, they "would get back to me".
    PLEASE don't hold your breath!

    Quote Originally Posted by MattCasters View Post
    So, it's not of lack of interest or will that there is no support in Kettle at this time.
    That is good to know. At least they are consistently difficult to work with...

    Are the KETTLE developers aware of what it takes to get Progjress's JDBC driver working with KETTLE? If not, I could write up a quick 'how to' for those that would be interested.

    Regards,
    JM

  8. #8
    Join Date
    May 2006
    Posts
    4,882

    Default

    In the mean time the URL of the generic database is "environment variable" sensitive... grab a new kettle.jar in a few minutes and give it spin.

    Regards,
    Sven

  9. #9

    Default

    Sven, it works perfectly. Thanks for changing this so quickly!

    Regards,
    JM

  10. #10
    Join Date
    May 2006
    Posts
    4,882

    Default

    No problem... If it makes sense, it makes sense ;-)

    Best Regards
    Sven

  11. #11

    Default

    We're having our Progress database opened up on friday for o/jdbc I waas wondering if anyone had any good tips to get it working, I've seen that you need to add $DLC/bin to my path, is that really true? Also do you need to get some other JDBC drivers or are the files in $dlc/java and $dlc/lib enough to get connect(I certainly hope so)..

    Cheers

    Tom

  12. #12

    Default

    Quote Originally Posted by bugg_tb View Post
    We're having our Progress database opened up on friday for o/jdbc...
    What version of Progress is it? That makes a difference on the JDBC driver configuration. Specifically, the configuration process changed with Progress 10.X versus Progress 9.X.

    Regards,
    JM

  13. #13

    Default

    Rock on,

    I've seen that pointed out as well, it would appear to be 9.1C if thats of any use.

    Cheers

    Tom

  14. #14

    Default

    Below are some snips from my custom spoon.bat. The same concepts apply if you want to run Spoon (or any of the other KETTLE tools) on Windows or Linux. In the environment where I run KETTLE I do not rely on the $dlc/java or $dlc/lib folders. I have placed the driver and system libs under KETTLE_HOME/libext ... so that is what this info will reflect.

    The files needed for a connection to a Progress 9.X database are:
    • jdbc.jar
    • JdbcProgress.dll (Windows clients only)
    • procli92.dll (Windows clients only)
    • libJdbcProgress.so (*NIX clients only)
    • libprocli92.so (*NIX clients only)
    The files needed for a connection to a Progress 10.X database are:
    • base.jar
    • openedge.jar
    • pool.jar
    • spy.jar
    • util.jar
    To the best of my knowledge, these JDBC driver components are all proprietary, closed source, not redistributable and must be obtained from Progress Software directly.

    I placed the Progress 9.X JDBC drivers and external libs (.dll and .so files) in %KETTLE_HOME%/libext/JDBC/progress folder. I also am running KETTLE against Progress 10.X ... for that I created a %KETTLE_HOME%/libext/JDBC/progress/10.X folder structure.

    There is no 'progress' folder by default so if you follow this exactly, you will need to create that folder structure.

    Now for some batch file tweaks...

    The following loops should go just before the 'goto extlibe' line in the Spoon.bat distributed with KETTLE. (you could exclude the 10.X one if you don't envision ever needing it)
    Code:
    REM *****************
    REM   add Progress 9.X jar files
    REM *****************
    FOR %%F IN (%KETTLE_HOME%\libext\JDBC\progress\*.jar) DO call :addcp %%F
    
    REM *****************
    REM   add Progress 10.X jar files
    REM *****************
    FOR %%F IN (%KETTLE_HOME%\libext\JDBC\progress\10.X\*.jar) DO call :addcp %%F
    This next bit should occur somewhere before the final 'set OPT' line. Note that this is
    only needed for Progress 9.X drivers to gain access to the native libraries
    (.dll or .so files). Progress 10.X does not depend on external libraries to
    operate, it is totally implemented in JAVA.
    Code:
    REM *****************
    REM   java.library.path
    REM *****************
    set JAVA_LIB_PATH=%KETTLE_HOME%\libswt\win32\
    set JAVA_LIB_PATH=%JAVA_LIB_PATH%;%KETTLE_HOME%\libext\JDBC\progress\
    
    REM *****************
    REM   prefix system path to ensure Progress 9.X external libs (.dll or .so) are found
    REM *****************
    set PATH=%JAVA_LIB_PATH%;%PATH%
    And lastly, this is my custom version of the 'set OPT' line, the important change is the
    tweak made for '-Djava.library.path=%JAVA_LIB_PATH%'
    Code:
    REM *****************
    REM  the OPT variable is used to collect together all options passed to the JVM
    REM *****************
    set OPT=%OPT% -Xms64m -Xmx768m -cp %CLASSPATH% -Djava.library.path=%JAVA_LIB_PATH% -DKETTLE_HOME="%KETTLE_HOME%"
    In addition to this configuration change, getting the connection configured in Spoon is a bit of fun too.

    As you may have guessed, using the 'Generic' connection type is the best option. This allows you to parameterize everything with variables that can be set by internal ('set variable' logic within Spoon) or external means (i.e. -D params to the JVM).

    It is important to know the JDBC driver name and JDBC url structure.

    For Progress 9.X:
    • JDBC Driver Name = com.progress.sql.jdbc.JdbcProgressDriver
    • JDBC URL Structure = jdbc:jdbcprogress:T:<HOST>:<PORT>:<DB_NAME>
    For Progress 10.X
    • JDBC Driver Name = com.ddtek.jdbc.openedge.OpenEdgeDriver
    • JDBC URL Structure = jdbc:datadirect:openedge://<HOST>:<PORT>;databaseName=<DB_NAME>;
    The 'Generic' connection type will also have a user/password parameter, and those
    will of course vary base on your setup.

    Hopefully that is more than enough to get you up and running against Progress. :)

    Regards,
    JM

  15. #15

    Default

    Yeah the drivers I was checking the ones in the progress dirs were ok and didn't have to buy the ones from openlink or anyone.
    You are an abolute legend, I will give this ago on Friday and all being well it will go according to plan, if not you'll be hearing from me again

    Can someone add this to the wiki so other people in similar situations can benefit from the information?

    Cheers

    Tom
    Last edited by bugg_tb; 10-10-2007 at 03:13 PM.

  16. #16

    Default

    Hi JM

    I've been having a poke around in the $dlc/lib directory there isn't a libJdbcProgress.so library, just a libJdbcProgress.a is that going to cause a problem or not?
    Infact thats very odd theres loads of .so libs that appears to be the only one thats missing. Thats going to be a right pain in the ass, looks like I may have to use my ODBC driver instead.
    Cheers

    Tom
    Last edited by bugg_tb; 10-11-2007 at 08:57 AM.

  17. #17

    Default

    Hi JM

    I copied and pasted the lines out of your howto on my windows box but when I enter the driver type is says it can't find com.progress.sql.jdbc.JdbcProgressDriver, any tips or pointers??

    Cheers

    Tom

    Code:
     
    Exception while loading class
    com.progress.sql.jdbc.JdbcProgressDriver 
     
     at be.ibridge.kettle.core.database.Database.normalConnect(Database.java:297)
     at be.ibridge.kettle.core.database.Database.connect(Database.java:246)
     at be.ibridge.kettle.core.database.Database.connect(Database.java:208)
     at be.ibridge.kettle.core.database.Database.connect(Database.java:198)
     at be.ibridge.kettle.core.dialog.DatabaseDialog.test(DatabaseDialog.java:1821)
     at be.ibridge.kettle.core.dialog.DatabaseDialog.test(DatabaseDialog.java:1756)
     at be.ibridge.kettle.core.dialog.DatabaseDialog$4.handleEvent(DatabaseDialog.java:304)
     at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
     at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:938)
     at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3673)
     at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3284)
     at be.ibridge.kettle.core.dialog.DatabaseDialog.open(DatabaseDialog.java:379)
     at be.ibridge.kettle.trans.step.BaseStepDialog$3.widgetSelected(BaseStepDialog.java:328)
     at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:227)
     at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
     at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:938)
     at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3673)
     at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3284)
     at be.ibridge.kettle.trans.step.tableinput.TableInputDialog.open(TableInputDialog.java:311)
     at be.ibridge.kettle.spoon.Spoon.editStep(Spoon.java:3099)
     at be.ibridge.kettle.spoon.SpoonGraph.editStep(SpoonGraph.java:2063)
     at be.ibridge.kettle.spoon.SpoonGraph.access$100(SpoonGraph.java:96)
     at be.ibridge.kettle.spoon.SpoonGraph$4.mouseDoubleClick(SpoonGraph.java:246)
     at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:182)
     at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
     at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:938)
     at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3673)
     at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3284)
     at be.ibridge.kettle.spoon.Spoon.readAndDispatch(Spoon.java:964)
     at be.ibridge.kettle.spoon.Spoon.main(Spoon.java:7325)
    Caused by: be.ibridge.kettle.core.exception.KettleDatabaseException: 
    Exception while loading class
    com.progress.sql.jdbc.JdbcProgressDriver 
     at be.ibridge.kettle.core.database.Database.connectUsingClass(Database.java:344)
     at be.ibridge.kettle.core.database.Database.normalConnect(Database.java:281)
     ... 29 more
    Caused by: java.lang.ClassNotFoundException: com.progress.sql.jdbc.JdbcProgressDriver 
     at java.net.URLClassLoader$1.run(Unknown Source)
     at java.security.AccessController.doPrivileged(Native Method)
     at java.net.URLClassLoader.findClass(Unknown Source)
     at java.lang.ClassLoader.loadClass(Unknown Source)
     at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
     at java.lang.ClassLoader.loadClass(Unknown Source)
     at java.lang.ClassLoader.loadClassInternal(Unknown Source)
     at java.lang.Class.forName0(Native Method)
     at java.lang.Class.forName(Unknown Source)
     at be.ibridge.kettle.core.database.Database.connectUsingClass(Database.java:336)
     ... 30 more
    Last edited by bugg_tb; 10-22-2007 at 11:57 AM.

  18. #18
    Join Date
    Nov 1999
    Posts
    459

    Default Special database issues and experiences

    Quote Originally Posted by JMOSSER_NISC View Post
    Are the KETTLE developers aware of what it takes to get Progjress's JDBC driver working with KETTLE? If not, I could write up a quick 'how to' for those that would be interested.
    Hey JM,

    I would appreciate that (and can imagine your hard way), so I prepared a hotbed for you: http://wiki.pentaho.org/display/EAI/...nd+experiences

    Best Regards,
    Jens

  19. #19

    Default Change in message

    My error message has now changed after playing around with some files....
    I now get

    Code:
    Error connecting to database [OAJdbc] :
    be.ibridge.kettle.core.exception.KettleDatabaseException:
    Error occured while trying to connect to the database
    
    Error connecting to database: (using class
    com.progress.sql.jdbc.JdbcProgressDriver)
    No suitable driver found for jdbc:jdbcprogress:T:encon2:2501:OpenAcc
    
    
    be.ibridge.kettle.core.exception.KettleDatabaseException:
    Error occured while trying to connect to the database
    
    Error connecting to database: (using class
    com.progress.sql.jdbc.JdbcProgressDriver)
    No suitable driver found for jdbc:jdbcprogress:T:encon2:2501:OpenAcc
    
    
            at
    be.ibridge.kettle.core.database.Database.normalConnect(Database.java:297)
            at
    be.ibridge.kettle.core.database.Database.connect(Database.java:246)
            at
    be.ibridge.kettle.core.database.Database.connect(Database.java:208)
            at
    be.ibridge.kettle.core.database.Database.connect(Database.java:198)
            at
    be.ibridge.kettle.core.dialog.DatabaseDialog.test(DatabaseDialog.java:1821)
            at
    be.ibridge.kettle.core.dialog.DatabaseDialog.test(DatabaseDialog.java:1756)
            at
    be.ibridge.kettle.core.dialog.DatabaseDialog$4.handleEvent(DatabaseDialog.java:304)
            at
    org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
            at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:938)
            at
    org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3673)
            at 
    org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3284)
            at
    be.ibridge.kettle.core.dialog.DatabaseDialog.open(DatabaseDialog.java:379)
            at
    be.ibridge.kettle.trans.step.BaseStepDialog$3.widgetSelected(BaseStepDialog.java:328)
            at
    org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:227)
            at
    org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
            at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:938)
            at
    org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3673)
            at
    org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3673)
            at
    org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3284)
            at
    be.ibridge.kettle.trans.step.tableinput.TableInputDialog.open(TableInputDialog.java:311)
            at be.ibridge.kettle.spoon.Spoon.editStep(Spoon.java:3099)
            at
    be.ibridge.kettle.spoon.SpoonGraph.editStep(SpoonGraph.java:2063)
            at
    be.ibridge.kettle.spoon.SpoonGraph.access$100(SpoonGraph.java:96)
            at
    be.ibridge.kettle.spoon.SpoonGraph$4.mouseDoubleClick(SpoonGraph.java:246)
            at
    org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:182)
            at
    org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
            at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:938)
            at
    org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3673)
            at
    org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3284)
            at be.ibridge.kettle.spoon.Spoon.readAndDispatch(Spoon.java:964)
            at be.ibridge.kettle.spoon.Spoon.main(Spoon.java:7325)
    Caused by: be.ibridge.kettle.core.exception.KettleDatabaseException:
    Error connecting to database: (using class
    com.progress.sql.jdbc.JdbcProgressDriver)
    No suitable driver found for jdbc:jdbcprogress:T:encon2:2501:OpenAcc
    
            at
    be.ibridge.kettle.core.database.Database.connectUsingClass(Database.java:414)
            at
    be.ibridge.kettle.core.database.Database.normalConnect(Database.java:281)
            ... 29 more
    Caused by: java.sql.SQLException: No suitable driver found for
    jdbc:jdbcprogress:T:encon2:2501:OpenAcc
            at java.sql.DriverManager.getConnection(Unknown Source)
            at java.sql.DriverManager.getConnection(Unknown Source)
            at
    be.ibridge.kettle.core.database.Database.connectUsingClass(Database.java:395)
            ... 30 more

  20. #20
    Join Date
    Nov 1999
    Posts
    459

    Default

    I'm not sure about the URL, but most URLS have two // before the host.

    JDBC URL Structure = jdbc:jdbcprogress:T://<HOST>:<PORT>:<DB_NAME>

    Good luck,
    Jens

  21. #21

    Default

    Thanks for the tip jens,

    Sadly Progress like to be different

    Judging by the error this is a problem with my configuration but I'm not sure what

    Tom

  22. #22
    Join Date
    Nov 1999
    Posts
    459

    Default

    I see.... I had similar issues with a SAP connection:
    Try to copy the dll files to your windows/system32 directory and check if there might be other dll versions somewhere that can get in conflict.

    Just my 2 cents ... Jens

  23. #23
    Join Date
    Nov 1999
    Posts
    459

    Default

    Ahhhh... now I remember what the problem was: you have to copy some dlls in the Kettle directory \libswt\win32 and beside this you have to make sure that no other dll versions of this dll are somewhere on your system.

    The rule is: the first dll that is called from the jar has to be in \libswt\win32
    if this dll in \libswt\win32 calls another dll, this must be on windows\system32

    I cross my fingers - Jens

  24. #24

    Default

    Yeah tried that too thanks Jens, I do think that its loading the jdbc.jar file but struggling with the dll's for some reason. I could be way off the mark there though.

    Anyway, thanks a lot Jens

    Tom

  25. #25
    Join Date
    Feb 2008
    Posts
    216

    Red face JDBC driver for Progress/ODBC error

    Quote Originally Posted by MattCasters View Post
    Actually, I asked for integration support from Progress several time, they "would get back to me".
    Perhaps we can ask one of the JDBC vendors to include a decent demo driver or something.
    So, it's not of lack of interest or will that there is no support in Kettle at this time.

    Matt
    So, I'm currently using the ODBC driver for Progress, because I haven't been able to obtain a JDBC driver for it yet, and I stumbled across this post. Is there any update on this? Or are they still stalling in getting JDBC access to you? I DO have a request in to our vendor to see if I can get a JDBC driver to use because I am now running into the following error using the ODBC driver on a particular table.

    I don't get this error when I run the same SQL via other tools such as Embarcadero's RapidSQL, which is using the same ODBC driver. I'm getting the following when I try to select data out of one of our tables. There's nothing special about the SQL itself and I've tried using select * and selecting specific column names. Both have the same result.
    Failed to execute runnable (java.lang.NullPointerException)

    Details:
    org.eclipse.swt.SWTException: Failed to execute runnable (java.lang.NullPointerException)
    at org.eclipse.swt.SWT.error(Unknown Source)
    at org.eclipse.swt.SWT.error(Unknown Source)
    at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Unknown Source)
    at org.eclipse.swt.widgets.Display.runAsyncMessages(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.core.dialog.PreviewRowsDialog.open(PreviewRowsDialog.java:229)
    at org.pentaho.di.ui.trans.steps.tableinput.TableInputDialog.preview(TableInputDialog.java:568)
    at org.pentaho.di.ui.trans.steps.tableinput.TableInputDialog.access$300(TableInputDialog.java:62)
    at org.pentaho.di.ui.trans.steps.tableinput.TableInputDialog$6.handleEvent(TableInputDialog.java:303)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.trans.steps.tableinput.TableInputDialog.open(TableInputDialog.java:335)
    at org.pentaho.di.ui.spoon.delegates.SpoonStepsDelegate.editStep(SpoonStepsDelegate.java:124)
    at org.pentaho.di.ui.spoon.Spoon.editStep(Spoon.java:6420)
    at org.pentaho.di.ui.spoon.trans.TransGraph.editStep(TransGraph.java:2153)
    at org.pentaho.di.ui.spoon.trans.TransGraph.access$100(TransGraph.java:116)
    at org.pentaho.di.ui.spoon.trans.TransGraph$4.mouseDoubleClick(TransGraph.java:312)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:873)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:5626)
    at org.pentaho.di.ui.spoon.Spoon.run(Spoon.java:5722)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:371)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.pentaho.commons.launcher.Launcher.main(Launcher.java:116)
    Caused by: java.lang.NullPointerException
    at org.pentaho.di.core.row.ValueMeta.convertBooleanToString(ValueMeta.java:907)
    at org.pentaho.di.core.row.ValueMeta.getString(ValueMeta.java:1251)
    at org.pentaho.di.ui.core.dialog.PreviewRowsDialog$6.run(PreviewRowsDialog.java:276)
    at org.eclipse.swt.widgets.RunnableLock.run(Unknown Source)
    ... 31 more
    Here's the SQL in question:

    SELECT *
    FROM PUB.notes

    Has anyone else experienced this type of error accessing data from Progress? I'm not getting this problem accessing any other tables so far.

  26. #26

    Default

    JDBC does work, if you have ODBC drivers you should have the jdbc ones as well, at least thats our case with the Progress Client. I haven't got any further as ODBC seems to be holding up ok and the spead increase wouldn't make much difference to me.

    Tom
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  27. #27
    Join Date
    Feb 2008
    Posts
    216

    Default Progress Client...

    Quote Originally Posted by bugg_tb View Post
    JDBC does work, if you have ODBC drivers you should have the jdbc ones as well, at least thats our case with the Progress Client. I haven't got any further as ODBC seems to be holding up ok and the spead increase wouldn't make much difference to me.

    Tom
    Hi Tom -
    We're using a Syteline ERP system and I don't know if JDBC drivers are included with that or not. If they are, they're installed on some server somewhere and I'll have to see if I can get access to it...

    Thanks for the tip!

  28. #28
    Join Date
    Nov 1999
    Posts
    459

    Default

    The null pointer exception is due to a conversions from a boolean that is NULL. This is fixed right now by http://jira.pentaho.org/browse/PDI-833
    A circumvention until 3.0.3 is out, is to change the SQL with a NULLIF, a COALESCE or whatever this is in Progress to return a string "" (with length 0).

    BTW: It would be great when you (Tom?) share your experiences over here:
    http://wiki.pentaho.org/display/EAI/Progress

    Cheers,
    Jens

  29. #29
    Join Date
    Feb 2008
    Posts
    216

    Default Null Booleans... Ah.

    Quote Originally Posted by jbleuel View Post
    The null pointer exception is due to a conversions from a boolean that is NULL. This is fixed right now by http://jira.pentaho.org/browse/PDI-833
    A circumvention until 3.0.3 is out, is to change the SQL with a NULLIF, a COALESCE or whatever this is in Progress to return a string "" (with length 0).

    BTW: It would be great when you (Tom?) share your experiences over here:
    http://wiki.pentaho.org/display/EAI/Progress

    Cheers,
    Jens
    That is interesting. I did notice that even though it doesn't let me preview the rows correctly (getting the null pointer exception), when I run the transformation it seems to run okay anyway (this is still using the ODBC driver).

    I have not been able to get the JDBC driver to work yet... even though I added in the appropriate dll files to the Pentaho directory as mentioned earlier in the thread. I guess I'll stick with ODBC on this until I hit some critical issue.

  30. #30
    Join Date
    Nov 1999
    Posts
    459

    Default

    Some background: Preview converts the boolean into a (human readable) string and fails when it hits a NULL.
    When your transformation does not convert your boolean all works fine ;-)

    Cheers,
    Jens

  31. #31

    Default

    Added some instructions to the progress page on the wiki for you Jens

    Tom
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  32. #32
    Join Date
    Nov 1999
    Posts
    459

    Default

    Thanks - and I believe the community will appreciate this, too!

    Cheers,
    Jens

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.