pythondb2pyodbcdb2-400

Retrieve IBM i column headings from SQL using pyodbc


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?


Solution

  • 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()