I want to launch SQL querys from a PC to a DB2-database on IBM i. The files were created with the old method (source file, 10-character's file and field name, and detailled column-heading) In SQL from the emulator, I have an option to retrieve the column headings with the long name (Preferences / Results / column headings), so I think the jdbc driver can export them (I think it is allowed when checking Edition / JDBC Configuration/ Other / Extended metadata).
I can't retrieve this long name column heading when using pyodbc from python, using the driver "iSeries Access ODBC Driver". I don't see where tohave it, I searched in https://www.ibm.com/docs/en/i/7.1?topic=apis-connection-string-keywords the right option for the connection string, but did'nt find anything. The 'description' cursor attribute in pyodbc does retrieve the column name and length, but no extended attribute
Is this not possible at all to retrieve the long name? Is this option accessible in the jdbc driver?
Here is a simple working example of what I needed, based on nfgl's comments
# With JayDeBeApi :
# pip install JayDeBeApi
# And jdbc Driver :
# JTOPEN in https://sourceforge.net/projects/jt400/
# Copy the file jt400.jar in this directory
import os
import jaydebeapi
def run_sql(sql):
# Create a jdbc connexion
# Connexion string https://www.ibm.com/docs/en/i/7.5?topic=details-connection-string-keywords
system = 'MYAS400'
username = 'MYNAME'
password = 'MYPSW'
connection_string='jdbc:as400://' + system
driver_file_path = os.path.join(os.path.dirname(__file__) , "jt400.jar")
cnxn = jaydebeapi.connect("com.ibm.as400.access.AS400JDBCDriver",
connection_string,
{'user':username, 'password':password, 'extended metadata': 'true'},
driver_file_path )
with cnxn:
# Create a cursor
cursor = cnxn.cursor()
cursor.execute(sql)
if cursor.description != None:
header = tuple((cursor._meta.getColumnLabel(i+1)) for i in range(len(cursor.description)))
print(header)
print ('*' * 100)
result = cursor.fetchone()
while result != None:
print(result)
result = cursor.fetchone()
def main():
run_sql("SELECT x, y, z, 'A' as \"New Column\" FROM mylib.mytable WHERE thefield = 'OK'" )
if __name__ == "__main__":
main()