Hey, so I'm interested in implementing what Ignition's system.db.runPrepQuery essentially does, but as a scripting module. Does anyone know how I would begin to go about this?
From the GatewayContext you get in setup() in your GatewayHook, access the DatasourceManager.
From there, you can retrieve an SRConnection by the name of the datasource you want. SRConnection exposes the base JDBC query methods.
Doing it from the client or designer involves RPC, a different subject entirely.
Doing this from inside a "project" scope (if you're exposing a scripting function to end users) will likely require you to check ScriptContext for the implicit default datasource name.
Got it, would you happen to have any example/sample code that I could look at for reference?
Might not be 100% upto scripting spec, but as i am new at python as well here it goes....
so far i have 2 access points i have discovered into using the embedded functions
- using a Named query to execute and return a result set (Named Query Workspace - Ignition User Manual 8.1 - Ignition Documentation)
i have a simple named query with 2 parameters and i execute from script as follows:
Lets go Grab the Db Values for gStats Settings for Each Machine,Station combo
params = {"intMachineID":intMachineID, "intStationID":intStationID}
results = system.db.runNamedQuery("Oee and Stats/Get Stats Settings on Startup",params)
this returns a dataset which you will then need to parse to extract the return data if needed
[row, column]
results.getValueAt(C, X))
2. scripting direct where you can write your normal (Select Co1, Col2 from my table where my condition= x) Pass that to the same kind of container and then parse the data set the same
hope this helps..
Nope. If you can post your code publicly I can offer more concrete advice, but module dev is something you take on at your own peril; there aren't really resources beyond the public SDK examples.
Our documentation team is planning to change that, though.
So for context, our ignition project uses JDBC query methods in order to fetch data in order to render our Perspective views. I assume that would fall under "doing it from the client or designer". So from what I understand, you can check the ScriptContext in order to get the string of the datasource name, but then where do you go from there?
Um, there are numerous paths from there, based on what you are really trying to do. Note that Ignition's API for datasources gives you a generic JDBC connection on demand. (From a pool, to which it will return when you "close" it.) At that point, you would be following generic JDBC examples from StackOverflow (or wherever).
You might want to package results into a Dataset, so you might find Ignition's BasicStreamingDataset class helpful.
Anyways, describe your use case more fully:
-
What entry points into your module code will there be? Script functions? Component private implementations? Ignition Extension points of various kinds? Custom Expression functions?
-
What data, if any, will you be returning to the platform? (tags? UI stuff?)
2 Likes
Thank you for the information, I'll provide more background.
We currently have wrapper functions in the designer's Scripting library, that execute queries using ignition's system.db functions. An example would be as such:
dbName = 'someDBName'
def getSomeData(param1, param2):
query = """
SELECT
[col1]
,[col2]
FROM someTable
WHERE [col1] = ? AND [col2] = ?
"""
return system.db.runPrepQuery(query, [param1, param2], dbName)
We call functions like this in other defined functions in the scripting library, and in Perspective scripting too. My goal is to move such functions from the Scripting library to a scripting module instead in order for some encapsulation. And then I'd expect to be able to be able to call these functions the same way as before. Sorry if my language is unclear or ambiguous, let me know if I can clarify anything further.
To answer your questions:
- Scripting functions only I believe.
- Just datasets from runPrepQuery, and potentially integers from runPrepUpdate. We only use these two functions
OK. This will be relatively easy. You will implement .initializeScriptManager() in your gateway hook and will add a class that defines your functions via mgr.addScriptModule(...). This class will have the following basic structure, repeating for each function:
@KeywordArgs(names = { "dbName", "someParamName", "anotherParamName", "thirdParamName" }, types = { String.class, String.class, Integer.class, Long.class })
static public PyObject myFunctionName(PyObject[] args, String[] keywords) {
ArgParser ap = new ArgParser("myFunctionName", args, keywords, new String[] { "dbName", "someParamName", "anotherParamName", "thirdParamName" });
String dbName = ap.getString(0, null);
String someParam = ap.getString(1, null);
int anotherParam = ap.getInt(2, -1);
long thirdParam = (Long) Py.tojava(ap.getPyObject(3, new PyLong(0L)), Long.class);
/* Probably want implementations to be in separate class files for maintainability */
Connection cx = GatewayHook.getMyContext().getDatasourceManager().getDatasource(dbName).getConnection();
/* Constant query string with three question mark placeholders: */
PreparedStatement stmt = cx.prepareStatement(someConstantSqlString, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.FETCH_FORWARD);
stmt.setString(1, someParam);
stmt.setInt(2, anotherParam);
stmt.setLong(3, thirdParam);
ResultSet rset = stmt.executeQuery();
BasicStreamingDataset bsds = new BasicStreamingDataset(rset);
return Py.java2py(new PyDataSet(bsds));
}
4 Likes
This is amazing Phil, thank you for taking the time to write this. So I've done all that in the gateway scope, does there not need to be any code in any of the other scopes? Like Designer, Client, Common
Oh, yes. If you want to call from Designer or Vision Client scopes, you must implement all of the RPC model. (One of the examples has details, IIRC.)
1 Like
In the public SDK example, the RPC model is implemented in the client scope, and then it's exported to the DesignerHook, so I guess I'll try to do that too.
The SDK example has an example with a simple multiplication function here:
I'm not quite following though, particularly as to what this MathBlackBox here is, and the parameters of ModuleRPCFactory.create(). I assume these are meant to be examples of something obviously, but it's not quite clear to me, and I wasn't able to find the documentation on it.
Hey Phil, just wanted to say I took the time to study up a bit on Java project structure, and finally understood how all the scopes and packages fit together now. Thank you so much for your help, sorry for the silly questions.
Not silly at all. And less than two days to grasp the RPC model? Awfully impressive for this subject, IMHO.
1 Like
So Phil I'm in the last stretches (I hope) and was wondering if you could help me out.
So on line 18 here, I'm noticing that GatewayHook.getMyContext() is something I'm still to implement. I assume it would be a simple getter method inside the GatewayHook class. So my question is, am I to define a field inside the GatewayHook class like for example:
private final GatewayContext gatewayContext = new GatewayContext();
But according to the documentation, there's not a constructor method, so I'm a bit lost as to how to implement getMyContext().
Here's the code for the GatewayHook class:
My next best guess would be to use the setup method to set a class field to it?
@PGriffith would you have any idea as well?
Exactly that. GatewayContext is the public API of the Ignition gateway itself. You don't create an instance, you are given one in your module's setup method. It's up to you what you do with it after that.
Got it, so here's my attempt at it. Does this look about right?
No, to be accessible to other classes as GatewayHook.getMyContext(), both the field and the method need to be static. The latter needs to be public static.
1 Like
Got it, I've at least gotten it to compile now! Now I'm finally get to address runtime errors instead.
Looks like the SQL querying itself seems to be doing fine, there just seems to be type related issues at the end. I'm getting recurring errors of "BasicDataset cannot be cast to PyObject". I was wondering if you knew what might be causing this.
Not off the top of my head, no.
BTW, screenshots are much less reviewer-friendly than code blocks. (Use the "preformatted text" button in the forum comment editor after selecting any code or logfile text that has been pasted.)