Php + unixODBC + DB2 + DESCRIBE = token not valid

db2db2-400ibm-midrangePHPunixodbc

Code I am trying to run:

$query = "DESCRIBE TABLE TABLENAME";
$result = odbc_exec($h, $query);

The result:

PHP Warning: odbc_exec(): SQL error: [unixODBC][IBM][iSeries Access
ODBC Driver][DB2 UDB]SQL0104 – Token TABLENAME was not valid. Valid
tokens: INTO., SQL state 37000 in SQLExecDirect in …

There were no other problems with SELECT, INSERT, UPDATE or DELETE queries on the same connection. Is this a syntax error?

Best Answer

The iSeries flavor of DB2 does not support the SQL DESCRIBE statement. Instead, you have to query the system table:

select * from qsys2.columns where table_schema = 'my_schema' and table_name = 'my_table'

Related Solutions

Sql – Not able to run SQL queries in AS400, run into Invalid Token errors

TABLE is a reserved word. SQL Reference: Reserved schema names and reserved words.

Single quotes escape a string literal
Double quotes escape a reserved word (similar to brackets in TSQL)

SQL Reference: Identifiers

The queries could be re-written as:

SELECT ID, SUM(FIELD1 + FIELD2) AS TOTAL FROM "TABLE" GROUP BY ID
SELECT COUNT(*) FROM (SELECT DISTINCT FIELD1 FROM "TABLE" WHERE FIELD2 = 'ABC')

UPDATE

DB/2 for i does not support your method of numeric to character conversion or the type of character comparison used in your LIKE query.

The query can be re-written as:

SELECT eds, SUM(INT(sds)) AS totalh 
FROM tbl1 
WHERE eds BETWEEN 20130500 AND 20130599 
AND siteds IN (
    SELECT DISTINCT site 
    FROM tbl2 
    WHERE H_04 IN ('1234') AND PERIOD = 201305
) 
GROUP BY eds
ORDER BY eds

You may need to use DEC instead of INT depending upon the definition of field sds.

SQL Reference: - INT - DEC - BETWEEN

Sql – 4200 – SQL0104 – Token was not valid

try to modify your query like this

  select * from PTMIFLIB.PYLEAVE

or like this

  select * from PTMIFLIB/PYLEAVE

Best Answer

Related Solutions

Sql – Not able to run SQL queries in AS400, run into Invalid Token errors

Sql – 4200 – SQL0104 – Token was not valid

Related Topic