Hitachi Vantara Pentaho Community Forums
Results 1 to 16 of 16

Thread: Invalid column name error

  1. #1
    Join Date
    Sep 2011
    Posts
    171

    Default Invalid column name error

    Hello Everyone,

    I attached very simple transformation which gives the following error. Could anybody help to figure out what does "invalid column name" mean?

    Regard,
    Alex



    2011/10/06 15:12:22 - Spoon - Starting job...
    2011/10/06 15:12:22 - MainJob - Start of job execution
    2011/10/06 15:12:22 - MainJob - Starting entry [GetDateSetVar]
    2011/10/06 15:12:22 - GetDateSetVar - Loading transformation from XML file [N:\Daily Positions\Manager\Pentaho\GS Gamma\GetDateSetVar.ktr]
    2011/10/06 15:12:22 - GetDateSetVar - Dispatching started for transformation [GetDateSetVar]
    2011/10/06 15:12:22 - Date Excel Input 2.0 - Finished processing (I=1, O=0, R=0, W=1, U=0, E=0)
    2011/10/06 15:12:22 - Set Variables.0 - Setting environment variables...
    2011/10/06 15:12:22 - Set Variables.0 - Set variable DATEOF to value [2011/09/26 00:00:00.000]
    2011/10/06 15:12:22 - Set Variables.0 - Finished after 1 rows.
    2011/10/06 15:12:22 - Set Variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2011/10/06 15:12:22 - MainJob - Starting entry [Transformation 2]
    2011/10/06 15:12:22 - Transformation 2 - Loading transformation from XML file [N:\Daily Positions\Manager\Pentaho\GS Gamma\SetDatePutEverythingToExcel.ktr]
    2011/10/06 15:12:22 - SetDatePutEverythingToExcel - Dispatching started for transformation [SetDatePutEverythingToExcel]
    2011/10/06 15:12:22 - Table output.0 - Connected to database [TestIRISStagingTables] (commit=1000)
    2011/10/06 15:12:22 - MainRows Excel Input.0 - Finished processing (I=2, O=0, R=0, W=2, U=0, E=0)
    2011/10/06 15:12:22 - Get Variables.0 - Finished processing (I=0, O=0, R=2, W=2, U=0, E=0)
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Unexpected batch update error committing the database connection.
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException:
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error updating batch
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Invalid column name 'Instrument Type'.
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1494)
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:647)
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:69)
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: java.sql.BatchUpdateException: Invalid column name 'Instrument Type'.
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:947)
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1477)
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 3 more
    2011/10/06 15:12:22 - SetDatePutEverythingToExcel - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2011/10/06 15:12:22 - SetDatePutEverythingToExcel - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2011/10/06 15:12:22 - Table output.0 - Finished processing (I=0, O=0, R=2, W=0, U=0, E=1)
    2011/10/06 15:12:22 - SetDatePutEverythingToExcel - SetDatePutEverythingToExcel
    2011/10/06 15:12:22 - SetDatePutEverythingToExcel - SetDatePutEverythingToExcel
    2011/10/06 15:12:22 - MainJob - Finished job entry [Transformation 2] (result=[false])
    2011/10/06 15:12:22 - MainJob - Finished job entry [GetDateSetVar] (result=[false])
    2011/10/06 15:12:22 - MainJob - Job execution finished
    2011/10/06 15:12:22 - Spoon - Job has ended.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2011
    Posts
    171

    Default

    I figured out that if remove "Use batch update for inserts" then everything flows smoothly.

  3. #3
    Join Date
    Sep 2011
    Posts
    171

    Default

    Let me take it back. unchecking "Use batch update for inserts" did not help.

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    Quote Originally Posted by august_month View Post
    2011/10/06 15:12:22 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Invalid column name 'Instrument Type'.
    It's probably not liking the space in the middle of the column name. Try deleting the space or replacing it with an underscore.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Do you have a column 'Instrument Type' in your DB table?

  6. #6
    Join Date
    Sep 2011
    Posts
    171

    Default

    You right it worked! Do you think it is a bug?

  7. #7
    Join Date
    Sep 2011
    Posts
    171

    Default

    Do you have a column 'Instrument Type' in your DB table?
    No, in DB I have colunm name "InstrumentCode".

    Funny. I deleted space (in Excel) in "Security Name" and it went through. I deleted space in "Intrument Type" and it still does not work.
    Last edited by august_month; 10-06-2011 at 05:38 PM.

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

    Default

    Once you use a space in a column we need to properly quote.
    Once that happens the columns become case depending. So perhaps in the database it's called "instrument type" or "INSTRUMENT TYPE" or something totally different.

    Only you can tell.

  9. #9
    Join Date
    Sep 2011
    Posts
    171

    Default

    I tried deleting space from "Instrument type" and from "Estimated Value", but not luck so far. I double checked there is no "Instrument Type" or "Estimated Value" columns in my database.

    I am going to create another table in DB with few columns just to troubleshoot.

    Regards,
    Alex

  10. #10
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    The fact that there's no "Instrument Type" in your DB indicates the problem.
    You are trying to load a column "Instrument Type" from your stream to your table, and the column doesn't exist.

    ie. you are issuing a statement INSERT into MYTABLE("Instrument Type",OtherColumn) VALUES ("SomeValue", OtherValue) and your DB is complaining that you're asking it to do something that it has no idea how to do.

    Solutions:
    1) Turn on mapping in the Table Output Step
    --OR--
    2) Use a select values step to name all the columns in your stream exactly as they are in your DB.

  11. #11
    Join Date
    Sep 2011
    Posts
    171

    Default

    Solutions:
    1) Turn on mapping in the Table Output Step
    --OR--
    2) Use a select values step to name all the columns in your stream exactly as they are in your DB.
    In table output component I have a mapping "Intrument Type" is mapped to "InstrumentCode"

    I tried renaming "Instrument Type" to "InstrumentType" in Select value component, it did not help.

    Maybe because some cells are hidden and protected, I am getting the error. I need to experiment more.

  12. #12
    Join Date
    Sep 2011
    Posts
    171

    Default

    Update,

    In Table output component, I checked "Specify database fields" and everything went through smoothly.

    Could anybody let me know what this field ("Specify database fields") is and why it is not checked by default?

    Regards,
    Alex

  13. #13
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    That is what turns on the field mapping...

    In olden times, this did not exist in the table output, one had to use a select fields step before the table mapping to name all the columns **EXACTLY** as they are in the database (in your case, "Instrument Type" to "InstrumentCode" )

    When the option to do this mapping in the same step was added, it was set to default to off, since that was the behaviour of the table step before.

    Perhaps you should file a JIRA to default it to be on....

  14. #14
    Join Date
    Sep 2011
    Posts
    171

    Default

    Thank you, gutlez!

    I do not know about JIRA. If legacy user already got used to this filed being off, it will cause them some major headache. I just have to keep it in mind for the future.

  15. #15
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    So... File a Jira to add a preference option.
    Default it to On, but let a user change their preference to Off.

    Newer users (like yourself) would expect the mapping feature (labelled Specify Database fields) to work just by entering the fields.
    If an older user doesn't want that option to be on by default, let them specify that.

    Worst that can happen is that they say no.
    Best case, we get a more intuitive software.

  16. #16
    Join Date
    Sep 2011
    Posts
    171

    Default

    Filed Jira.

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.