Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: ALL SYNCHRONIZATION.kjb using pdi-ce-5.0.1-stable

  1. #1

    Default ALL SYNCHRONIZATION.kjb using pdi-ce-5.0.1-stable

    Hi,

    I am new to this pentaho data integration please help me.

    I started using pdi-ce-5.0.1-stable .....whenever i try to connect to database i get error where as pdi-ce-5.0.1-stable is working fine..database connection is successfull there...what can be the issue?




    PreScript PostgreSQL.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 - Error while running this step!
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 -
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 - Couldn't execute SQL: CREATE OR REPLACE FUNCTION exist_language(varchar)
    RETURNS bigint AS ' SELECT count(*) from pg_language where lanname = $1
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 -
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 - ERROR: unterminated quoted string at or near "' SELECT count(*) from pg_language where lanname = $1"

    Please can anyone help me with step-by-step procedures and use full information.


    Thanks,
    Ashwini

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by tejashwini99@gmail.com View Post
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 - Couldn't execute SQL: CREATE OR REPLACE FUNCTION exist_language(varchar)
    RETURNS bigint AS ' SELECT count(*) from pg_language where lanname = $1
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 -
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 - ERROR: unterminated quoted string at or near "' SELECT count(*) from pg_language where lanname = $1"
    The error message complains about the unmatched apostrophe in your SQL.

    You better test your SQL in your favored query tool before bringing it to Kettle (PDI).
    So long, and thanks for all the fish.

  3. #3

    Default

    Hi marabu,

    This is the query which was getting an error in PDI. But this query gets executed in postgresql-9.3 without any errors.


    CREATE OR REPLACE FUNCTION exist_language(varchar)
    RETURNS bigint AS '
    SELECT count(*) from pg_language where lanname = $1;
    'LANGUAGE 'sql' STABLE
    /-- END

    Please help me.

    Thanks,
    Ashwini

  4. #4

    Default

    Hi marabu,

    The query which have in PreScript PostgresSQL is as below:


    CREATE OR REPLACE FUNCTION exist_language(varchar)
    RETURNS bigint AS '
    SELECT count(*) from pg_language where lanname = $1;
    ' LANGUAGE 'sql' STABLE
    /-- END


    CREATE OR REPLACE FUNCTION insert_pg_language()
    RETURNS integer AS '
    CREATE TRUSTED PROCEDURAL LANGUAGE ''plpgsql''
    HANDLER plpgsql_call_handler
    VALIDATOR plpgsql_validator;
    SELECT 1;
    ' LANGUAGE 'sql'
    /-- END

    The error I am getting is :

    PreScript PostgreSQL.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 - Error while running this step!
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 -
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 - Couldn't execute SQL: CREATE OR REPLACE FUNCTION exist_language(varchar)
    RETURNS bigint AS ' SELECT count(*) from pg_language where lanname = $1
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 -
    2013/12/28 19:21:59 - PreScript PostgreSQL.0 - ERROR: unterminated quoted string at or near "' SELECT count(*) from pg_language where lanname = $1"



    I am not able to find the issue is with this query, can you please help me.


    Thank you,
    Ashwini

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    You should check the version numbers of the PostgreSQL server and the JDBC driver you are using - maybe they don't match.
    The functions are from OpenBravo, I guess - the script was written years ago, using old syntax elements, so there could arise compatibility errors on this side, too.
    Use a stand-alone SQL query tool (like Squirrel) to test the whole script with your driver.
    And think twice, if you must use Kettle to tell PGSQL to create 43KB worth of functions - maybe there are other ways, even more suitable ways.
    So long, and thanks for all the fish.

  6. #6

    Default

    Hi,
    I have executed the same query in squirrel SQL client version 3.3.0 and in postgresql 9.3 its running without any errors.


    Even though i executed the same query in PDI--SQL editor it also ranned successfully without any errors.


    But if i try to execute the All Synchronization.kjb getting same error as -

    ERROR: unterminated quoted string at or near "'
    PreScript PostgreSQL.0 - SELECT count(*) from pg_language where lanname = $1"

    CREATE OR REPLACE FUNCTION exist_language(varchar)
    RETURNS bigint AS '
    SELECT count(*) from pg_language where lanname = $1;
    ' LANGUAGE 'sql' STABLE;


    CREATE OR REPLACE FUNCTION insert_pg_language()
    RETURNS integer AS '
    CREATE TRUSTED PROCEDURAL LANGUAGE ''plpgsql''
    HANDLER plpgsql_call_handler
    VALIDATOR plpgsql_validator;
    SELECT 1;
    ' LANGUAGE 'sql';

    Please , can anyone help me regarding above issue with useful information.
    I am new to this, Please help me.

    Thanks,
    Ashwini

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.