Issue regarding ibm db sa + sqlalchemy functions

[frbelotto]

Hello, I am using sqlalchemy over a DB2 database but I am facing some weird bugs over the dialect. I was in touch with SQLA team, but the issue seems related to the dialect, that is not their support.

https://github.com/sqlalchemy/sqlalchemy/issues/8291

Using IBM DB

sql = '''select count(emp_id), to_char(b_date,'YYYY') from employees group by to_char(b_date,'YYYY')'''      

stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_tuple(stmt)
while dictionary != False:
    print(dictionary)
    dictionary = ibm_db.fetch_tuple(stmt)
>>>(1, '1972')
>>>(1, '1975')
>>>(1, '1976')

Using SQLA text

stmt = text('''select count(emp_id), to_char(b_date,'YYYY') from employees group by to_char(b_date,'YYYY')''')        
print (stmt)
with engine.begin() as conn:
    result = conn.execute(stmt)
    for row in result:
        print (row)
>>>(1, '1972')
>>>(1, '1975')
>>>(1, '1976')

using SQLA core select

stmt = select(func.count(employees.c.emp_id), func.to_char(employees.c.b_date,('YYYY'))).group_by(func.to_char(employees.c.b_date,('YYYY')))
print(stmt.compile(compile_kwargs={"literal_binds": True}))
with engine.begin() as conn:
    result = conn.execute(stmt)
    for row in result:
        print (row)

SELECT count(employees.emp_id) AS count_1, to_char(employees.b_date, 'YYYY') AS to_char_1 
FROM employees GROUP BY to_char(employees.b_date, 'YYYY')

ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0119N  An expression starting with "B_DATE" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.  SQLSTATE=42803  SQLCODE=-119
[SQL: SELECT count(employees.emp_id) AS count_1, to_char(employees.b_date, ?) AS to_char_1 
FROM employees GROUP BY to_char(employees.b_date, ?)]
[parameters: (b'YYYY', b'YYYY')]
(Background on this error at: https://sqlalche.me/e/14/f405)

In this last example the issue "disappear" if I remove the to_char function in the group by section.

[praveen-db2]

@frbelotto Error is thrown from DB2. Query formed using SQLC core select is correct. So there is no issue with Alchemy ibm_db adapter. why are you sending parameters in bytes ? I think that's causing issue.

[frbelotto]

I am not passing bytes. Sqlalchemy and/or the dialect is. That part is generated by them. I am setting just the 'yyyy'

[frbelotto]

And the issue regarding passing variables is bigger than I thought. I am trying the "round" func and happens the same.

select_stmt_resumoguias = select(guiaspagas.c.convenio, guiaspagas.c.convenente, func.round(guiaspagas.c.vlr_rec,2).label('sum_rec')).group_by(guiaspagas.c.convenio,guiaspagas.c.convenente)

SQLA compile : SELECT "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".convenio, "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".convenente, round("DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".vlr_rec, 2) AS sum_rec 
FROM "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO" GROUP BY "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".convenio, "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".convenente

Error exeception : ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2] SQL0171N  The statement was not processed because the data type, length or value of the argument for the parameter in position "2" of routine "ROUND" is incorrect. Parameter name: "ROUND".  SQLSTATE=42815  SQLCODE=-171
[SQL: SELECT "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".convenio, "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".convenente, round("DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".vlr_rec, ?) AS sum_rec 
FROM "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO" GROUP BY "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".convenio, "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".convenente]
[parameters: (2,)]

[frbelotto]

Any feedback?

[bchoudhary6415]

@frbelotto Please use ibm-db-sa 0.3.9 and verify the issue. Thanks.

[frbelotto]

Same issue Running on pure SQLA over IBM_DB_SA sql = select(guiaspagas.c.convenio, guiaspagas.c.convenente, func.round(guiaspagas.c.vlr_rec,2)).limit(10) with engine.connect() as conn: data = pd.read_sql(sql,conn)

Output :

ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2] SQL0171N  The statement was not processed because the data type, length or value of the argument for the parameter in position "2" of routine "ROUND" is incorrect. Parameter name: "ROUND".  SQLSTATE=42815  SQLCODE=-171
[SQL: SELECT "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".convenio, "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".convenente, round("DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO".vlr_rec, ?) AS round_1 
FROM "DB2I023A"."ANL_GUIAS_PAGAS_PREPAGO" FETCH FIRST 10 ROWS ONLY]
[parameters: (2,)]

Now just compiling the sql before executing

sql = select(guiaspagas.c.convenio, guiaspagas.c.convenente, func.round(guiaspagas.c.vlr_rec,2)).limit(10)
with engine.connect() as conn:
    sql2 = sql.compile(compile_kwargs={"literal_binds": True})
    data = pd.read_sql(sql2,conn)

An this does work.

[bchoudhary6415]

@frbelotto I created below sample program to check func.round()

from sqlalchemy import create_engine, Column, Integer, String, func, select from sqlalchemy.orm import sessionmaker from sqlalchemy.orm import declarative_base import pandas as pd

db2_credentials = { 'username': 'user_name', 'password': 'Password', 'host': 'host', 'port': 'port_number', 'database': 'database_name' }

db2_conn_str = ( 'ibm_db_sa://{username}:{password}@{host}:{port}/{database}' .format(**db2_credentials) )

engine = create_engine(db2_conn_str) Base = declarative_base()

class MyTable(Base): tablename = 'my_table' id = Column(Integer, primary_key=True) name = Column(String(50)) value = Column(Integer)

MyTable.table.create(bind=engine, checkfirst=True) Session = sessionmaker(bind=engine) session = Session() data = [ {'name': 'Alice', 'value': 5}, {'name': 'Bob', 'value': 6}, {'name': 'Charlie', 'value': 7}, {'name': 'David', 'value': 8}, ] for item in data: row = MyTable(**item) session.add(row) session.commit()

query = select(MyTable.name, func.round(MyTable.value / 2)).limit(2) conn = engine.connect() result = pd.read_sql(query, conn) print(result)

I got below result :- name round_1 0 Alice 2 1 Bob 3

Did get any error. Can you please give me an update that it's giving error for particular datatype, Or anything I'm missing to reproduce the issue?

[frbelotto]

On your code, replace this : query = select(MyTable.name, func.round(MyTable.value / 2)).limit(2)

to this query = select(MyTable.name, func.round(MyTable.value, 2)).limit(2)

and test please. I´ve teste here and if I supress the parameter "2" on the func.round it does work, but I understand that such parameter should be informed as it does compose the IBM ROUND FUNC (documentation)

[bchoudhary6415]

@frbelotto I changed the query to your one - query = select(MyTable.name, func.round(MyTable.value, 2)).limit(2) And getting the output - name round_1 0 Alice 5 1 Bob 6 I think it's working.

[frbelotto]

If not that, what would be causing such exception? I don't think is the data type because I had the same error using "to_char" function.

I don't know how to replicate the same version of DB2 I have on my work ( I will edit this comment later to insert details of it ) , to try replicating some data

[bchoudhary6415]

@frbelotto Please use the latest version of ibm_db_sa that is v0.4.0 I checked below sample program for the "to_char" function -

from sqlalchemy import create_engine, Table, Column, Integer, Date, MetaData, text conn_str = 'ibm_db_sa://{username}:{password}@{host}:{port}/{database}' engine = create_engine(conn_str) metadata = MetaData() orders = Table('orders', metadata, Column('order_id', Integer, primary_key=True), Column('order_date', Date) ) metadata.create_all(engine) with engine.connect() as conn: conn.execute(orders.insert().values(order_id = 1, order_date = '2023-04-24')) conn.execute(orders.insert().values(order_id= 2, order_date = '2023-04-25')) conn.execute(orders.insert().values(order_id= 3, order_date = '2023-04-26')) conn.commit()

query = text("SELECT order_id, to_char(order_date, 'DD-MON-YYYY') AS formatted_date FROM orders") with engine.connect() as conn: results = conn.execute(query).fetchall()

for row in results: print(f"Order ID: {row.order_id}, Formatted Date: {row.formatted_date}")

And getting output as : - Order ID: 1, Formatted Date: 24-APR-2023 Order ID: 2, Formatted Date: 25-APR-2023 Order ID: 3, Formatted Date: 26-APR-2023

It's also working fine.

[frbelotto]

This is the DB2 server that I am connected. It's a DB2 for z/os. I don't know how I could replicate / create another instance for testing

Connected to database
DBMS_NAME:  DB2
DBMS_VER:   12.01.0005
DB_NAME:    XXXXXXX
DRIVER_NAME:           DB2CLI.DLL
DRIVER_VER:            11.05.0600
DATA_SOURCE_NAME:      XXXXXXX
DRIVER_ODBC_VER:       03.51
ODBC_VER:              03.01.0000
ODBC_SQL_CONFORMANCE:  EXTENDED
APPL_CODEPAGE:         1252
CONN_CODEPAGE:         1208

[bchoudhary6415]

@frbelotto I'm able to reproduce the issue on DB2 for z/os for func.round(), but for to_char it's working fine. I will work on reproduced issue and will update you. Thank you

[bchoudhary6415]

@frbelotto The fix for this issue is given. Please verify through the latest PR - https://github.com/ibmdb/python-ibmdbsa/pull/130 I'm closing this issue now. Thank you