Class Queries via PYODBC or Python Native

Question

Question

Sinon Galvin · Jan 4, 2021

Under the previous python binding method (Cache) it was possible to pass in parameters, execute a class query, and fetch the result sets. I have been unable to find a way to do this via PYODBC or the Native API (perhaps I am just missing something).

My work around is to create a SQLquery string and execute it through a cursor. I would much prefer to simply execute the debugged and more secure class queries that already exist and and have been precompiled.

Any pointers would be greatly appreciated

Thanks,

Sinon

Product version: IRIS 2020.1

Discussion (10)0

Log in or sign up to continue

Sinon Galvin · Jan 4, 2021

Figured it out.

From PYODBC, call in the standard ODBC format (duh). The projected procedure name was causing the issue. If using the the above example code, then:
cursor.execute('{CALL exp.MD5NoMatch(?,?,?)}',('exchange', 'cplx','channel'))
(I was previously trying to call with the classname in the file..... exp.DayFiles_MD5NoMatch

The correct projected SP name is in the management portal:Home->System Explorer->SQL->Procedures

There I could see that the class name is not included in this case, presumably because the SP name is unique in my "exp" schema. (FYI "exp" schema is just an example schema name I used when creating the class, you won't find it in your listings) Also note that if more than one "." is used in defining the calling procedure name, only the leftmost will be a "." and the others will be replaced by an underscore "_" which is why I was originally trying to call the procedure as schema.classname_spName

0 0

score 0 · Answer 1 · 2021-01-04T13:43:29-05:00

JDBC (docs):

import pandas as pd
import jaydebeapi
cnxn=jaydebeapi.connect("com.intersystems.jdbc.IRISDriver","jdbc:IRIS://localhost:51773/Python",  ["dev", "123"], "/InterSystems/IRIS/dev/java/lib/JDK18/intersystems-jdbc-3.0.0.jar")
Data=pd.read_sql('SELECT 1',cnxn)
cnxn.close()

ODBC (docs):

import pandas as pd
import pyodbc
cnxn=pyodbc.connect(('DSN=ENSEMBLE(PYTHON);UID=dev;PWD=123'),autocommit=True)
Data=pd.read_sql('SELECT 1',cnxn)
cnxn.close()

I would much prefer to simply execute the debugged and more secure class queries that already exist and and have been precompiled.

Sure, this works too:

SELECT * FROM Class.Query

score 0 · Answer 2 · 2021-01-04T16:56:38-05:00

Thanks, but perhaps I wasn't clear Eduard or I am still too dense (likely). I have a class that I defined to have properties, methods and most importantly for this example a query. It expects to receive Exchange, Complex and Channel as parameter inputs and then returns a resultset. Under old methods I would call as shown below. I'm just missing the way to do that via PYODBC or Direct.

import intersys.pythonbind3 as ip
...
database = ip.database(conn)
...
cq2 = ip.query(database)
sqlcode=0
cq2.prepare_class("exp.DayFiles", "MD5NoMatch");
cq2.set_par(1,exchange)
cq2.set_par(2,cplex)
cq2.set_par(3,channel)
cq2.execute()

and then begin fetching rows from the recordset.

The particular query definition from my exp.DayFiles class

/// MD5 discrepency w/ Amazon, not necessiarly a busted file
Query MD5NoMatch(Exchange As %String, Complex As %String, Channel As %String) As %SQLQuery(ROWSPEC = "Exchange:%String,Cplx:%String,Channel:%String,DateStd:%String,FileName:%String,FPathRemote:%String,FPathLocal:%String,MD5Cloud:%String,MD5Local:%String") [ SqlName = MD5NoMatch, SqlProc ]
{
SELECT Exchange, Complex, Channel, DateStd,ZipFileName,{ fn concat(ZipPathCloud,ZipFileName)} as CloudPath,
{ fn concat(ZipPathLocal,ZipFileName)} as LocalPath, ZipMD5Cloud, ZipMD5Local from exp.DayFiles
WHERE Exchange = :Exchange AND Complex = :Complex AND Channel = :Channel
AND ZipMD5Agree = 'False' ORDER BY DateStd
}

score 0 · Answer 3 · 2021-01-05T03:35:51-05:00

Great. This should also work:

cursor.execute('SELECT * FROM exp.MD5NoMatch(?,?,?)',('exchange', 'cplx','channel'))

score 0 · Answer 4 · 2021-01-05T10:11:36-05:00

Sinon Galvin · Jan 5, 2021

Thanks Eduard. Going to give that a try. I actually prefer it to the "call" approach.

0 0

score 1 · Answer 5 · 2021-01-05T10:03:20-05:00

Glad to hear this is resolved, but let me add that we are currently working on full support for DB-API:

https://www.python.org/dev/peps/pep-0249/

The idea is to have a one stop shop (Python SDK) where you'll be able to access IRIS from Python using the standard DB-API (relational), XEP/Binding (for objects) or Native API (globals), as well as having full access to Python from IRIS (via Python Gateway/PEX).

score 0 · Answer 6 · 2021-01-05T10:10:21-05:00

Sinon Galvin · Jan 5, 2021

So glad to hear that Aleksandar. Fantastic. Of course it begs the question when to expect? :-)

0 0

score 0 · Answer 7 · 2021-01-05T10:20:19-05:00

As I am sure you are aware, some of the above components are already available (Native API + reentrant Python Gateway). Python PEX will become available with 2021.2, and DB-API and object access should follow soon, hopefully later this year.

score 1 · Answer 8 · 2021-01-05T10:45:02-05:00

Along those "currently available" lines Aleksandar: Is it possible to return / read a resultset with the native API? All I've seen are simple vectors for return values.

score 0 · Answer 9 · 2021-01-05T14:12:54-05:00

Aleksandar Djakovic · Jan 5, 2021

No, that functionality will be provided via DB-API

0 0