bohag_bihu bohag_bihu - 5 months ago 7x
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
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.


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

  colname, typename
  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.