SQL datatype not support: KeyError -360

[PepperJo]

I'm tying to run a simple join with a where, group by and order by clause on DB2WoC:

db..ida_query('select sys."type",sys."compressed",perf."label",AVG(perf."maxvalue"),perf."units" ' +
                                                          'from PERFORMANCE_DATA as perf join SYSTEM as sys on (sys."id"=perf."id") ' +
                                                          'where perf."metricid"=819 group by sys."type",sys."compressed",perf."label",perf."units" order by sys."type"')

and get the following error:

/opt/conda/envs/DSX-Python35/lib/python3.5/site-packages/ibmdbpy/base.py in ida_query(self, query, silent, first_row_only, autocommit)
    760         """
    761         self._check_connection()
--> 762         return sql.ida_query(self, query, silent, first_row_only, autocommit)
    763 
    764     def ida_scalar_query(self, query, silent=False, autocommit = False):

/opt/conda/envs/DSX-Python35/lib/python3.5/site-packages/ibmdbpy/sql.py in ida_query(idadb, query, silent, first_row_only, autocommit)
     79     """
     80     if idadb._con_type == 'odbc':
---> 81         return _ida_query_ODBC(idadb, query, silent, first_row_only, autocommit)
     82     else:
     83         return _ida_query_JDBC(idadb, query, silent, first_row_only, autocommit)

/opt/conda/envs/DSX-Python35/lib/python3.5/site-packages/ibmdbpy/sql.py in _ida_query_ODBC(idadb, query, silent, first_row_only, autocommit)
     92     try:
     93         query = _prepare_query(query, silent)
---> 94         cursor.execute(query)
     95 
     96         if autocommit is True:

/opt/conda/envs/DSX-Python35/lib/python3.5/site-packages/pypyodbc.py in execute(self, query_string, params, many_mode, call_mode)
   1624 
   1625         else:
-> 1626             self.execdirect(query_string)
   1627         return self
   1628 

/opt/conda/envs/DSX-Python35/lib/python3.5/site-packages/pypyodbc.py in execdirect(self, query_string)
   1652         check_success(self, ret)
   1653         self._NumOfRows()
-> 1654         self._UpdateDesc()
   1655         #self._BindCols()
   1656         return self

/opt/conda/envs/DSX-Python35/lib/python3.5/site-packages/pypyodbc.py in _UpdateDesc(self)
   1804         else:
   1805             self.description = None
-> 1806         self._CreateColBuf()
   1807 
   1808 

/opt/conda/envs/DSX-Python35/lib/python3.5/site-packages/pypyodbc.py in _CreateColBuf(self)
   1710             col_sql_data_type = self._ColTypeCodeList[col_num]
   1711 
-> 1712             target_type = SQL_data_type_dict[col_sql_data_type][2]
   1713             dynamic_length = SQL_data_type_dict[col_sql_data_type][5]
   1714             # set default size base on the column's sql data type

KeyError: -360

From the error message it looks like one of the datatypes in one of the columns is not supported?

[PepperJo]

Also opened an issue here: jiangwen365/pypyodbc#95

[ToniBollinger]

What are the data types of the columns of the tables in this query?
Can you give their "create table" statements?

[PepperJo]

SYSTEM:

• id: INTEGER
• type: VARCHAR
• compressed: DECIMAL

PERFORMANCE_DATA:

• id: INTEGER
• label: VARCHAR
• maxvalue: DECFLOAT
• units: VARCHAR
• metricid: SMALLINT

[ToniBollinger]

It looks as if pypyodbc does not support the DECFLOAT data type. As a workaround you can cast the value of the perf."maxvalue" column to DOUBLE with double(perf."maxvalue").

[PepperJo]

Toni, thanks for your quick answer and workaround. The query runs through if I cast it DOUBLE. Happy Easter!