bohag_bihu bohag_bihu - 6 months ago 12
PHP Question

How to get the data type of db2 variables in php?

From a PHP code,I am trying to insert a new row to a DB2 table where I have the column names but not the corresponding data types.

Insert into <table_name> (column1,column2,column3,.....) values ('value1','value2','value3',....)


So, if any column is of date or timestamp data type, the single quotes around
'value'
makes db2_exec function to throw an error:

Warning: db2_exec(): Statement Execute Failed in C:\Program Files (x86)\insight_db.php on line 68
[IBM][CLI Driver][DB2/NT64] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007 SQLCODE=-180


to avoid placing
''
around such values, I need to get the data type of each column of the table and thereby checking a condition whether to place the quotes or not.

Answer

You can query the catalog view SYSCAT.COLUMNS to obtain the information:

select
  colname, typename
from 
  syscat.columns
where
  tabschema = 'YOURSCHEMA' and
  tabname = 'YOURTABLE'

However, your approach is not going to work, because datetime literals must be enclosed in single quotes, and removing them will render your insert statement invalid. To avoid the SQL0180N error you can supply datetime values in the ISO format, e.g. YYYY-MM-DD-HH.MM.SS for TIMESTAMP.

Comments