How to override schema use on ibm_db.exec_immediate(connect, sql) in Python

1
votes

I am trying to run a run select count(*) from 'ActualSchemaUser.TABLE_NAME' where 'DATE_CREATE' >= 2019-10-17 and I get the following error:

** Exception: [IBM][CLI Driver][DB2/LINUXZ64] SQL0204N "DatabaseUSer.ActualSchemaUser.TABLE_NAME" is an undefined name. SQLSTATE=42704 SQLCODE=-204 **

It looks like ibm_db.exec_immediate(connect, sql) adds the database connecting user to the query from ibm_db module perspective.

Is there a way I can prevent set or override the schema for ibm_db.exec_immediate() function.

Python code snippet:

import ibm_db
connect  = ibm_db.connect("DATABASE=DatabaseName;HOSTNAME=DatabaseHostname;PORT=DatabaseName;PROTOCOL=TCPIP;UID=DatabaseUSer; PWD=DataBasePassword;", "", "")

schemaName = 'ActualSchemaUser'
sql = "select count(*) from '{}.TABLE_NAME' where 'DATE_CREATE' >= 2019-10-17".format(schemaName)
statement = ibm_db.exec_immediate(connect, sql)
result = ibm_db.fetch_assoc(statement)

print(result)
2
pythondb2

2 Answers

2
votes

Not sure it is a Python issue, but wrong quoting around SQL identifiers. You need to use

select count(*) from "ActualSchemaUser"."TABLE_NAME"
where 'DATE_CREATE' >= 2019-10-17

There should be double quotes for SQL identifiers, single quotes for string values. The two part name consists of the schema and the table name and both a separate identifiers.

1
votes

You need to quote separately schema and table name:

schemaName = 'ActualSchemaUser'
sql = "select count(*) from \"{}\".\"TABLE_NAME\" where DATE_CREATE >= 2019-10-17".format(schemaName)
# or sql = 'select count(*) from "{}"."TABLE_NAME" where DATE_CREATE >= 2019-10-17'.format(schemaName)
statement = ibm_db.exec_immediate(conn, sql)
result = ibm_db.fetch_assoc(statement)

Exception: [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  "ActualSchemaUser.TABLE_NAME" is an undefined name.  SQLSTATE=42704 SQLCODE=-204

(of course I don't the the table)