Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: DB procedure NullPointerException

  1. #1
    Join Date
    Sep 2007
    Posts
    3

    Default DB procedure NullPointerException

    Hello, I'm having a terrible time trying to figure out this problem and would appreciate any ideas. I wrote a stored procedure in MySQL with 2 IN args and 1 OUT arg. I got it running smoothly in Spoon on my Windows Vista dev machine. But now I'm trying to run it in a Unix production environment, and the transformation fails as soon as the first row of data reaches the DB procedure. It's a nullPointerException. I know for a fact the data going in is not null, so I'm guessing the OUT arg is throwing the error.

    The procedure looks like this. I tested from the mysql command line, and it appears to work fine:
    create procedure findSessionEndDate(IN user INT, IN start DATETIME, OUT p_out DATETIME)

    I do notice that if I click Verify in Spoon on my local machine, I do get an error for the procedure step, saying that the OUT arg is not found in input from previous steps. However, it doesn't prevent the transformation from running correctly on my dev machine.

    The log output and stack trace:
    2007/09/13 16:19:56 - procedure values.0 - Got row from previous step: [userId=138, platformType=3, logTime=2007/09/01 00:00:11.000, id=118946420, year=2007, month=9, day_of_month=1, hour=0, minute=0, type=viewImpressions, subType=ids=598758 598719 598718 598714 598713 598698 598695 , log=, uri=/pictures.html, partnerId=0, userAgent=Mozilla/5.0 (Macintosh; U; Intel Mac OS X; en-US; rv:1.8.1.6) Gecko/20070725 Firefox/2.0.0.6, action_tally=0]
    2007/09/13 16:19:56 - procedure values.0 - Wrote row to next step: [user=138, platformType=3, start=2007/09/01 00:00:11.000, id=118946420, year=2007, month=9, day_of_month=1, hour=0, minute=0, type=viewImpressions, subType=ids=598758 598719 598718 598714 598713 598698 598695 , log=, uri=/pictures.html, partnerId=0, userAgent=Mozilla/5.0 (Macintosh; U; Intel Mac OS X; en-US; rv:1.8.1.6) Gecko/20070725 Firefox/2.0.0.6, action_tally=0]
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Unexpected error in ' : java.lang.NullPointerException
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : java.lang.NullPointerException
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.StringUtils.indexOfIgnoreCaseRespectQuotes(StringUtils.java:948)
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1253)
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3628)
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:628)
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:513)
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4147)
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.Connection.prepareCall(Connection.java:4221)
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.Connection.prepareCall(Connection.java:4195)
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.core.database.Database.setProcLookup(Database.java:3357)
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dbproc.DBProc.runProc(DBProc.java:84)
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dbproc.DBProc.processRow(DBProc.java:122)
    2007/09/13 16:19:56 - proc get session duration.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dbproc.DBProc.run(DBProc.java:205)
    2007/09/13 16:19:56 - radar_mart - Statement canceled!
    Last edited by thujone0; 09-13-2007 at 05:52 PM.

  2. #2
    Join Date
    Sep 2007
    Posts
    3

    Default

    If someone could just tell me where I can see some examples of DB Proc being used, I can probably figure out what's going on. I notice people saying you have to use a row generator, but I don't know where I can find out more about that. Is that row just to trigger the procedure, or am I actually passing an OUT arg into the procedure?

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

    Default

    A row generator is just to trigger. If you look at the stack trace you will see the problem doesn't originate from Kettle... So I did some searching, I found this:

    #1 Much better error message, you now can drop my Feature Request:

    java.sql.SQLException: User does not have access to metadata required to determine stored
    procedure parameter types. If rights can not be granted, configure connection with
    "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT
    strings irregardless of actual parameter types.

    #2
    mysql-connector-java-5.0-nightly-20070208-bin.jar fixed it.

    Thanks for your prompt response.

    (I hope the security model for StoredProcedures will be refined in 5.1.
    As it is now its OK for us but not in general)
    Are you having access rights problems?

    Regards,
    Sven

  4. #4
    Join Date
    Sep 2007
    Posts
    3

    Default

    Ah, you were right, the kettle user didn't have enough permissions! The DBA had given the kettle user select-only access. All's good now -- thanks so much!

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.