Question
Kevin McGinn · Feb 26, 2020

Using python to select from persistent class INFORMATION.SCHEMA.TABLES fails

 

I have a simple python script to pull table information from cache 2018.1.2 which is on a windows 10 machine. I get no results though the same query in the management portal works correctly:

 

import os
import sys
import intersys.pythonbind3 as ipyb
import numpy as np

# Connect to the Cache' database
url = "localhost[1972]:%SYS"
user = "xxxx"
password = "zzzzz"

accessKey = (url, user, password)

def main(accessKey):

    tbList        = []
        
    # Get the connection and database handles
    conn = ipyb.connection()
    conn.connect_now(accessKey[0],accessKey[1],accessKey[2], None)
    database = ipyb.database(conn)

    tHdl = ipyb.query(database)
    sql = "SELECT Table_Type,Table_Schema,Table_Name,Owner FROM INFORMATION_SCHEMA.TABLES"
    tHdl.prepare(sql)
    tHdl.execute()
            
    # grab 10 rows
    for x in range(0,10):
         print(tHdl.fetch([None]))
  

if __name__ == '__main__':
    main( accessKey)

 

Trying to get the first 10 rows, I get 10 empty lists. Not sure what I am missing since the query executes correctly in the management portal. There are no errors

0
0 171
Discussion (8)1
Log in or sign up to continue

Any exceptions on stack?

import sys, traceback
traceback.format_exception(sys.last_type, sys.last_value, sys.last_traceback)

Can you run SELECT 1 query?

I added the import of the traceback lib as you suggested. Putting the traceback  statement after the execute or fetch caused an error stating that "last_type" does not exist which would I should see if no errors were found (as I understand it) . I changed the select to "select * ....." and that produced the error:

  File "getTables.py", line 45, in main
    tHdl.prepare(sql)
intersys.pythonbind3.cache_exception: file=intersys/pythonbind3.c line=3355 err=-1 message=cbind_prepare_gen_query()

an even more confusing error for which I have not found any info on the error condition.

I changed the the statement to "select count(*)...." that was a mistake. The python script never returned.

Yes, looks like no exception is thrown.

Try this query:

sql = "SELECT 1"  

"select 1" generates the same error:

    tHdl.prepare(sql)
intersys.pythonbind3.cache_exception: file=intersys/pythonbind3.c line=3355 err=-1 message=cbind_prepare_gen_query()

What is more perplexing is if I create a python script using an ODBC connection instead of pythonbind3,  the select does work. But I prefer not to use ODBC.

I am running Python 3.7.4 if that makes a difference.

That's a fairly recent version of Python. Not sure if old Python Binding works with it. I can recommend you to:

Eduard Lebedyuk; thank-you for your help.

Unfortunately, switching to IRIS is not an option as we are preparing to support clients who are not prepared to upgrade to IRIS; at least not yet. I will, though, look at PythonGateway.

Again, thank-you for the help.

Kevin McGinn

Hi Kevin,

Python 3.6.7 is the latest version that I have been able to successfully target for Cache. Listed below are my personal doc notes from the last time I reinstalled Cache and built the python binding (almost a year ago). All paths are specific to my cache install and my personal choices in how I configure / name Anaconda environments.

  • Add cache and cache/bin to path
  • Set up python binding
    • This was a struggle….complicated by placing path additions for CACHE after the Conda environemts were set in .bashrc.  Looked like they were there, but they weren't in the notebook. Place them before.
    • When rebuilding python for Cache be in the /cache/dev/python directory sudo run full anaconda path python to setup python3  /home/xxx/anaconda3/envs/py367/bin/python setup3.py install

Note: no higher than 3.6.7 python at the moment so make sure anaconda env py367 is created: conda create -n py367 python=3.6.7 anaconda

TESTED SUCCESSFULLY!!!!!