I have some recorded data in Mssql, I’m sending these data over ignition to mssql. I need get a table of sql from ignition to excel now but there isn’t any output when I run the script as manual.
if str(currentValue.quality).upper() == 'GOOD' and str(previousValue.quality).upper() == 'GOOD':
if (currentValue.value > 0):
results = system.db.runQuery("SELECT * FROM MyTable", 'MyServer')
spreadsheet = system.dataset.toExcel(True, [results])
filePath = "C:\\Users\\test1\\Desktop\\results.xls"
system.file.writeFile(filePath, spreadsheet)
I set memory tag as 1 and check the diagnostic result, I can’t not any problem. But there isn’t any created file when I check the destination path. What could be the problem? What am I doing wrong?
Context is important. Are you running this on a gateway script? Then you should be checking this on the gateway server at that file location. If you’re running it on a client script, then check the client computer. So first make sure you are checking the right place. Always a good idea to think about your execution context when debugging.
If this isn’t working at all you should be getting some error somewhere. This looks like you’re running it in on the gateway so you should check your server logs and see what error message you are getting since nothing is happening.
My guess would be that it’s a type issue. system.db.runNamedQuery returns a basic dataset while system.db.runQuery like you’re using returns a PyDataSet.
In the documentation for system.dataset.toExcel they use a named query so they feed it a basic dataset. So you may want to try turning your runQuery into a namedQuery or do a system.dataset.toDataSet() around your runQuery statement - toExcel might be able to handle PyDataSets, but I can’t tell that from the documentation, and this would eliminate one possibility.
Thanks a lot for your detail answer. I don’t use gateway or specific client scripts. I just created a memory tag named “export_excel” in “Tag Browser” and wrote the script in my previous message into it. While trying the script, I manually set the following memory tag to 1.
But when I check the manual and Ignition University, all excel examples are running on the perspective. I’m not sure this method running just perspective side.
You may not be writing these scripts with a context in mind but they are executing in a specific context always.
Since you’re dealing with files/folders its actually quite important as well as with queries. Here’s the differences -
Gateway:
Files/Folder Operations - You are creating/reading/updating files/folders on the computer that the gateway is hosted on.
Database queries - You must specify which database connection you are using (or if using a named query which project it comes from)
Vision Client:
Files/Folder Operations - You are creating/reading/updating files/folders on the computer that the client is using the project on. Computer A is hosting the gateway and computer B opens a Vision project where a button does creates an excel file in a script, that folder is being created on computer B’s file system.
Database queries - You can set the default database connection for the project and omit these.
All jython code in perspective is run in the gateway. So if on a perspective button you say "make this excel sheet on "C:\\Users\\test1\\Desktop\\results.xls"" - this is being made on the computer that is hosting the Igniton gateway, not the computer of the person visiting the perspective application.
From you’re reply I am assuming that you are running this script from a perspective project is that correct? If so I would expect the excel file to be on the gateway computer.
Not quite sure what you mean with that memory tag - are you just using it to debug? Post your updated script.
@bkarabinchak.psi is right on top of it. Tag Event scripts execute in the Gateway Context. So assuming the script produces no errors the file will be located on the computer hosting the Gateway Server.
perspective has no acces to your local computer, you can trigger a download of the file though.
In which case you dont even need to create a file on your gateway.
Didn’t know it was any different. Thought it was just a ‘shortcut’.
Thanks for the info, I’ll use this now. Maybe. I kind of prefer the meaning isGood brings, even if I dislike the case.
It is a shortcut. Like .quality above is short for .getQuality(). Jython automatically recognizes Java getters and setters that follow the NetBeans pattern and treats them as jython object attributes.
object.getSomething() can be replaced with object.something.
object.setSomething(newValue) can become object.something = newValue.
For booleans, object.isSomething() becomes object.something.
This is a standard across the Java ecosystem (JSP, Struts, et cetera).
If it’s just a shortcut, then I’d rather stick with .isGood. Even if using .quality.
It doesn’t get any clearer than .quality.isGood(), that’s basically an english sentence !
.isGood() is attribute lookup (java) to obtain the isGood method, followed by the parentheses operator (jython interpreter) to invoke the method (back into java).
.good is attribute lookup that includes the method call, entirely in java.
Always use the Netbeans shortcuts if performance matters to you.
