You can use system.db.runNamedQuery() it will work.
Is there a way to programmatically get the Project Name and Default database name?
You can use system.util.getProjectName() to get the project name, I don't know of an easy way to get the Default Database name, if there is even one set. I always set the database name explicitly.
You shouldn't need to get the default database name for use with runPrepUpdate and friends. Anywhere you can't omit it, there is no default database to get.
By contrast, IA royally screwed up the order of arguments to runNamedQuery such that you cannot omit the project name in gateway scope even when in a scope where it could have been implied, so you should use .getProjectName() for those cases.
{Typo spotted by Paul.}
3 Likes
runPrepQuery, or runNamedQuery?
2 Likes
um... on the beginNamedQueryTransaction, Project Name and Database are required, as it threw an error when omitted.
I am using the whole runNamedQuery gang, but only beginNamedQueryTransaction needs Project Name and Database Name.
To my knowledge, there is no supported way to get the default data source. I'm sure if you went through the trouble of getting a GatewayContext, there is a way to get to it. But that is a lot more trouble then just typing in the database name. Why is just using a constant database name unacceptable?
1 Like
Thank's a lot for the input.
I have since split multiple queries into one query per NamedQuery. That said, previously, I have two inserts, and one select in Named Query, and it work just fine so far, including the Begin and Commit statement contained in the NamedQuery..
I have my try-except, commit-rollback logic in place.
do you guys know how to get the error message from (JDBC and Python) the try block?
I will pass this message to Popup parameter.
If you are not using Ignition to create the transaction, then Ignition will not be able to rollback the transaction. Don't do this. Create the transaction in ignition with the rollback/commit logic in Ignition, or do it in a stored procedure if you want it to be purely handled by the DB. Don't do it in both places.
You said you are using system.db.beginNamedQueryTransaction() if that is the case, then you are effectively wrapping a transaction with a transaction (not good).
Multiple queries inside of a named query is just asking for trouble (Even if it works currently). On top of that it makes it difficult to troubleshoot should it come to that. Split them out completely. If for some reason you insist on multiple statements being executed in one call then you should use a stored procedure.
Use java.lang.Throwable to catch errors.
1 Like
Sorry for confusion.
I have since then split the multiple query into individual, one query per runNamed function.
Below is my script:
numQuery = 4
result = [None]*numQuery
queryPath = [None]*numQuery
param = [None]*numQuery
queryPath[0]="Insert_Into_table1"
queryPath[1]="Insert_Into_table2"
queryPath[2]="Insert_Into_table3"
queryPath[3]="Select_From_table4"
tx = system.db.beginNamedQueryTransaction(system.util.getProjectName(), Project.database, timeout=5000)
try:
#Edit This
result[0] = system.db.runNamedQuery(path=queryPath[0], parameters=param[0], tx=tx, getKey=1)
Id = result[0]
param[1]['Id'] = Id
result[1] = system.db.runNamedQuery(path=queryPath[1], parameters=param[1], tx=tx, getKey=0)
param[2]['Id'] = Id
result[2] = system.db.runNamedQuery(path=queryPath[2], parameters=param[2], tx=tx, getKey=0)
param[3]['Id'] = Id
result[3] = system.db.runNamedQuery(path=queryPath[3], parameters=param[3], tx=tx, getKey=0)
except:
isError = True
system.db.rollbackTransaction(tx)
else:
system.db.commitTransaction(tx)
finally:
system.db.closeTransaction(tx)
I will be working with production database.
I have a development database.
I copy codes from development script, into production script.
I might forget to change the database path..
-> since there is no way to get default database programatically, so I used python variable instead.
Okay, I think you want a script like this.
Note: I don't think that the script as you have posted it will run, as param[1] is not defined anywhwere as a dictionary.
results = []
queryPaths = ['Insert_Into_Table1','Insert_Into_Table2','Insert_Into_Table3','Select_From_Table4']
tx = system.db.beginNamedQueryTransaction(system.util.getProjectName(),Project.database, timeout=5000)
try:
id = system.db.runNamedQuery(path=queryPaths[0], tx=tx,getKey=True)
results = [system.db.runNamedQuery(path=path,parameters={'Id':id}, tx=tx) for path in queryPaths[1:]]
results.insert(0,id)
except Exception e:
isError = True
system.db.rollbackTransaction(tx)
except Throwable t:
isError = True
system.db.rollbackTransaction(tx)
else:
system.db.commitTransaction(tx)
finally:
system.db.closeTransaction(tx)
This is a really bad practice. Use one database connection name for both development and production environments. In your development environment, it would point at a development DB instead of the production DB.
1 Like
yes, you are right. good point.
What is Exeception e?
What is Throwable t?
how do I get the error message?
does avove covers all possibe error?
is there a general catch where it catch any error, and capture the error message?
Thanks.
Exception are python's errors.
Throwable are java errors, which are raised by database operations.
Catching both should cover pretty much everything, but you could add a bare except: and get the error from the stacktrace... But frankly it's not necessary.
What's not caught will end up in the logs anyway.
By the way, I usually reraise errors at some point to make sure they're logged:
except Exception as e:
handle_exception_whichever_way_you_like
raise
You can do this:
except (Expection, Throwable) as e:
# error handling
I don't recommend this. Exception is Jython's type, which is unrelated to java's Throwable type. Java's Exception is a subclass of Throwable but Jython's Exception is not. In the above, you would have to test the type of e for anything more complex then stringifying its message.
Catching them separately gives you more options for logging with complete backtraces, particularly if you use PythonAsJavaException from later.py.
Well yes, if you handle the errors differently.
But if the handling is exactly the same, I don't see a reason to duplicate the handling code.
Hi,
I searched but did not find.
error: global name 'Throwable' is not defined.
how do you fix the above error.
from java.lang import Throwable
1 Like