Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Exceptions while executing SQL query

  1. #1
    Join Date
    Mar 2009
    Posts
    29

    Default Exceptions while executing SQL query

    Hi Experts,

    Need some help.
    I am writing a kettle transformation in which I am querying the Sybase ASA database using ianywhere driver. The step before "Table Input" step I am selecting some values like A,B,C. A and B are strings where as C is int.

    My Table Input Query is:
    SELECT
    ? AS A_value,
    NAME as SAMPLE_NAME,
    ? as B_value
    FROM <table_name>
    WHERE id = ?

    But when executing the Table Input the error I get is:
    [Sybase][ODBC Driver][SQL Anywhere]Cannot convert 'A' to a int

    at org.pentaho.di.core.database.Database.getRow(Database.java:2861)
    at org.pentaho.di.core.database.Database.getRow(Database.java:2776)
    at org.pentaho.di.core.database.Database.getRow(Database.java:2750)
    at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:235)
    at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:127)
    at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2664)
    at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:343)
    Caused by: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Cannot convert 'A' to a int
    at ianywhere.ml.jdbcodbc.IIResultSet.next(Native Method)
    at ianywhere.ml.jdbcodbc.IResultSet.next(IResultSet.java:581)
    at org.pentaho.di.core.database.Database.getRow(Database.java:2791)
    ... 6 more

    The same job works fine when the connection is thru oracle driver.

    Let me know is something missing for the binding the variables or some special handling for ianywhere is required.


    Regards - Neuron
    Last edited by neuron; 04-07-2011 at 01:32 PM. Reason: Added some comments

  2. #2
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi Neuron,

    as a first guess, I'd suggest casting the parameter to the type you need. If you don't tell the driver it will guess, and that may go wrong. Not sure about the exact types supported by Sybase, but try something like:

    SELECT
    CAST(? AS VARCHAR) AS A_value,
    NAME as SAMPLE_NAME,
    CAST(? as VARCHAR) as B_value
    FROM <table_name>
    WHERE id = ?

    Replace VARCHAR with whatever text/varchar type your DB supports as appropriate and see if it helps.

    Cheers

    Slawo

  3. #3
    Join Date
    Mar 2009
    Posts
    29

    Default

    Hi Slawo,

    Great help! CAST did work.

    JFI, I also tried JCONNECT driver and the same kettle transformation without CAST worked.
    But with IANYWHERE driver CAST was explicitly required.

    Many Thanks!

    Neuron

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.