Existing DB2 column reported as not being a column of the table

Marius Butuc picture Marius Butuc · Jun 19, 2012 · Viewed 23.1k times · Source

In a Moodle 2 install running on DB2, deleting a user is unsuccessful, returning an error reading from database:

Debug info: [IBM][CLI Driver][DB2/LINUXX8664] SQL0206N "USERIDFROM" is not valid in the context where it is used. SQLSTATE=42703 SQLCODE=-206 SELECT * FROM mdl_message WHERE useridfrom = ? ORDER BY timecreated
[array ( 0 => '28521', )]

The error description for SQL0206N is clear, but useridfrom is a column of mdl_message:

$ db2 describe table mdl_message

                          Data type                     Column
Column name               schema    Data type name      Length     Scale Nulls
------------------------- --------- ------------------- ---------- ----- ------
 ID                       SYSIBM    BIGINT                       8     0 No    
 USERIDFROM               SYSIBM    BIGINT                       8     0 No    
 USERIDTO                 SYSIBM    BIGINT                       8     0 No    
 SUBJECT                  SYSIBM    VARCHAR                    200     0 Yes   
 FULLMESSAGE              SYSIBM    VARCHAR                    200     0 Yes   
 FULLMESSAGEFORMAT        SYSIBM    SMALLINT                     2     0 Yes   
 FULLMESSAGEHTML          SYSIBM    VARCHAR                    100     0 Yes   
 SMALLMESSAGE             SYSIBM    VARCHAR                    200     0 Yes   
 NOTIFICATION             SYSIBM    SMALLINT                     2     0 Yes   
 CONTEXTURL               SYSIBM    VARCHAR                    200     0 Yes   
 CONTEXTURLNAME           SYSIBM    VARCHAR                    200     0 Yes   
 TIMECREATED              SYSIBM    BIGINT                       8     0 No    

  12 record(s) selected.

What else could cause this error?

Answer

Leo picture Leo · Jun 19, 2012

It's possible that the column name somehow got a space in it. You can use this query to find out if there's any trailing blank space:

db2 "select '<' || name || '>' from sysibm.syscolumns where tbname = 'MDL_MESSAGE'"