3

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?

Marius Butuc
  • 17,781
  • 22
  • 77
  • 111
  • 1
    From the error message you're getting, it is unclear what the WHERE clause you're using is. Did you forget to put one in? – Kreg Jun 19 '12 at 15:30
  • That is a weird error, is there trigger on the table or something? Or is there some way to turn case-sensitivity on? And not accidently a `1` (one) or `l` (ell) or similar? Beyond that, I'm assuming `TIMECREATED` is a count of milli(or nano) seconds since the epoch, in UTC - to get around the fact that DB2 doesn't store the timezone information in timestamps? – Clockwork-Muse Jun 19 '12 at 15:35
  • Based on the error message, it looks like you passed a question mark to DB2 for USERIDFROM instead of a big integer. – Gilbert Le Blanc Jun 19 '12 at 16:09
  • Even from CLI, a simple `$ db2 "select * from mdl_message where USERIDFROM = 28251"` returns `SQL0206N "USERIDFROM" is not valid in the context where it is used. SQLSTATE=42703` – Marius Butuc Jun 19 '12 at 16:12
  • @X-Zero and no, I don't have any triggers on the table. – Marius Butuc Jun 19 '12 at 16:24
  • Can you try "select USERIDFROM from mdl_message fetch first 1 rows only" ? – Leo Jun 19 '12 at 17:07
  • @Leons `$ db2 "select USERIDFROM from mdl_message fetch first 1 rows only"` returns `SQL0206N "USERIDFROM" is not valid in the context where it is used. SQLSTATE=42703` – Marius Butuc Jun 19 '12 at 17:08

2 Answers2

6

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'"
Leo
  • 1,493
  • 14
  • 27
  • One quick question, when i am checking columns from "sysibm.syscolumns" it is giving me bunch of columns but i cannot see that column when i do "select* from t_name", it is subset of the columns shown in above sysibm columns. Can you pleas help me understand why there is variation in the columns? – Kulbhushan Singh Jul 28 '15 at 08:20
0

Are you sure you are connecting as the same user from command line and CLI. There might be a case where in the same table is present in different schema and you are seeing description of different table but accessing the other one.

Praveen D
  • 21
  • 1