Written by

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

Comments

Eduard Lebedyuk · Feb 27, 2020

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?

0
Kevin McGinn  Feb 27, 2020 to Eduard Lebedyuk

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.

0
Eduard Lebedyuk  Feb 27, 2020 to Kevin McGinn

Yes, looks like no exception is thrown.

Try this query:

sql = "SELECT 1"  
0
Kevin McGinn  Feb 27, 2020 to Eduard Lebedyuk

"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.

0
Kevin McGinn  Feb 27, 2020 to Kevin McGinn

I am running Python 3.7.4 if that makes a difference.

0
Eduard Lebedyuk  Feb 27, 2020 to Kevin McGinn

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

0
Kevin McGinn  Feb 27, 2020 to Eduard Lebedyuk

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

0
Sinon Galvin  Mar 4, 2020 to 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!!!!! 

0