Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Quotes needed for Postgresql values

  1. #1
    Join Date
    Sep 2007
    Posts
    10

    Default Quotes needed for Postgresql values

    The transformation for the time dimension insert fails due to a lack of quotes around the string values. It interprets the string value as a column name. I ran the insert statement after manually putting in the quotes ('Sunday') and it ran. I have been looking for a transformation step which will quote the string values. I can't find it. What next?

    2007/11/08 10:51:04 - Table output.0 - ERROR (version 3.0.0-RC2, build 299036 from 2007/10/31 23:59:46) : Because of an error, this step can't continue:
    2007/11/08 10:51:04 - Table output.0 - ERROR (version 3.0.0-RC2, build 299036 from 2007/10/31 23:59:46) : Error batch inserting rows into table [time_dimension].
    2007/11/08 10:51:04 - Table output.0 - ERROR (version 3.0.0-RC2, build 299036 from 2007/10/31 23:59:46) : Errors encountered (first 10):
    2007/11/08 10:51:04 - Table output.0 - ERROR (version 3.0.0-RC2, build 299036 from 2007/10/31 23:59:46) : Batch entry 0 INSERT INTO "public".time_dimension(date_key, date_value, year_value, month_value, day_of_year, day_of_month, day_of_week, week_of_year, day_of_week_desc, day_of_week_short_desc, month_desc, month_short_desc, quarter) VALUES ( 1, 1950-01-01, 1950, 1, 1, 1, 1, 1, Sunday, SUN, January, JAN, 1) was aborted. Call getNextException to see the cause.

    ERROR: column "sunday" does not exist
    SQL state: 42703
    Character: 272

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

    Default

    ERROR: column "sunday" does not exist
    Chalk up one more PostgreSQL bug.
    The values are actually passed with ? parameters to the JDBC driver. If the driver makes a mistake passing the values to Postgres, then I guess it's time to try a new JDBC driver or to file a bug report over at our PGSQL friends.

    All the best,

    Matt

  3. #3
    Join Date
    Sep 2007
    Posts
    10

    Default JDBC Connection

    That may be true, but it works for the repository which has to be inserting and updating the Postgresql on a regular basis.

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

    Default

    Maybe, maybe not. There are many things that can influence the behavior.
    I don't have all the details either. I assume you are using a Table Output step?
    Do you get the same error when you are using batch inserts or not?
    What version of Postgres are you using? Did you try to upgrade the driver?

    All I can tell you is that we never pass values to SQL statements.
    We always use a parameterized approach.

    All the best,
    Matt

  5. #5
    Join Date
    Sep 2007
    Posts
    10

    Default

    As you recommended, I just posted a message to the Pg forum. For laughs and giggles this morning, I ran an unquoted insert value via pgAdmin III v1.6 and Pg 8.2. I wrongly assumed that the problem was yours given the success with the repository. Guess what? It failed. The problem is on the Pg side. I am using batch inserts not knowing why I should uncheck the selection. Keep in mind I am a newbie with Pentaho. However, I have been writing sql on other db's without this type of problem for 15 years. FYI, I used both the native and ODBC drivers.

    I don't want to go to MYSQL on account of their licensing(my preference). According to them they are the preferred db. Do a search for Pentaho on their website and you will find:

    Pentaho is a recommended component of a MySQL DataWarehouse Scale-Out Solution Set.

    The Pentaho BI Project provides enterprise-class reporting, analysis, dashboard, data mining and workflow capabilities that help organizations operate more efficiently and effectively. The software offers flexible deployment options that enable use as embeddable components, customized BI application solutions, and as a complete out-of-the-box, integrated BI platform.

    Quote Originally Posted by MattCasters View Post
    Maybe, maybe not. There are many things that can influence the behavior.
    I don't have all the details either. I assume you are using a Table Output step?
    Do you get the same error when you are using batch inserts or not?
    What version of Postgres are you using? Did you try to upgrade the driver?

    All I can tell you is that we never pass values to SQL statements.
    We always use a parameterized approach.

    All the best,
    Matt

  6. #6
    Join Date
    Jul 2007
    Posts
    2,498

    Default

    Keep us posted, please. I'm also a psql user.

    Thanks
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

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

    Default

    Switch off your batching and try again. Batching eats a lot of error messages.

    Regards,
    Sven

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.