Hello folks,
First time trying to use transactions, and I can't figure out what I'm doing wrong.
First, the basics:
- perspective 8.1.17 (requirement for this project)
- mssql 2022 20.04
- I'm going mad
I tried a bunch of things, but the smallest sample I could get that raises the same error is this:
tx = system.db.beginNamedQueryTransaction('project', 'db')
system.db.closeTransaction(tx)
I do get a transaction id, I printed tx and got a reasonable value.
The error:
java.lang.Exception: Error executing system.db.closeTransaction(a9ef64fc-09ce-4dcd-8c3e-84a0506ae01b)
There's this in the trace:
Caused by: java.sql.SQLException: Transaction "a9ef64fc-09ce-4dcd-8c3e-84a0506ae01b" is closed.
So, what's going on here ? What am I missing ?
I don't know that this is causing this issue, but normally you either commit or rollback the transaction before closing it. I wonder if that is at play at all.
I also don't see where you are setting the isolationLevel though that shouldn't matter actually.
This is a bug, I think. Look for a thread with the same script call and a response from Kevin Collins.
1 Like
Yea I remember this post.
I just tried your script in 8.1.17 and it does give me the same issue just like you had @pascal.fragnoud. However, I also just tried it in 8.1.26 and had no issue. If you can upgrade that's probably your easiest solution.
Sadly, I can't.
@PGriffith Well... let's see if I understand the bug correctly:
A transaction is not actually opened when using 'beginNamedQueryTransaction', but put on a queue until a query is actually made. So, trying to close the transaction before running a query fails.
If I got that right, then there's something else wrong, because before skimming every other part of the script, it looked more like something like this:
tx = system.db.beginNamedQueryTransaction('project', 'db')
try:
system.db.runNamedQuery('insert_foo', tx=tx)
for b in bar:
system.db.runNamedQuery('insert_bar', tx=tx)
except Throwable as t:
system.db.rollbackTransaction(tx)
else:
system.db.commitTransaction(tx)
finally:
system.db.closeTransaction(tx)
This also gave me the same error.
edit: Just got back to the dev machine, gave this another shot and... no errors (with the full script).
Eh.
edit again:
Aaaand it's back ;D
I think I'm starting to understand what's going on - maybe.
I might need help with properly catching errors, especially with their types.
I'm a bit confused about something. Does using transaction make system.db.runNamedQuery run on the gateway scope ?
The calls to that function fail with "project 'path/to/namedquery' does not exist". The project name is not part of the function parameters when in perspective scope. I call it from a button onActionPerformed event.
Running the same query, with the same call, at the same place, but outside of a transaction, doesn't raise that error.
Adding the argument name explicitly (path="path/to/namedquery") makes the error disappear and the query succeed.
So I'm confused.
1 Like
I remember when I was using .17, although the path is documented as a necessary argument, I had to use a keyword argument like you said path='somePath' to make things work. I was confused by it too.
The upgrade from .17 to .26 is free if that makes any difference. If transactions are going to be a big part of this project I would really hate to have to work around a bug like this tbch. I feel like this is alone warrants the upgrade. Especially if you use work arounds now and then in the future, they do decide to upgrade...
I don't have any answer for you just that I remember needing everything to be a keyword argument to work despite it not being documented in that fashion.
I can't upgrade.
I could ask the customer to do it, but I already know they won't.
They have to send analysts and architects and maybe even a priest before they upgrade things, so it's not something they do on a regular basis.
Actually, I don't have access to the gateway machine at all.
I can't use themes because of that. And I don't have the css stylesheet because 1.17.
It's a pain in the @$$, but that's something I have to deal with.
edit:
Putting this on a button works perfectly:
a = system.db.runNamedQuery("test")
project_name = system.perspective.getProjectInfo()['name']
tx = system.db.beginNamedQueryTransaction(project_name, 'mssql')
b = system.db.runNamedQuery("test", tx=tx)
system.db.commitTransaction(tx)
system.db.closeTransaction(tx)
system.perspective.print("a: {}, b: {}".format(a, b))
The test query is a simple select 1, and I get the right results in the console.
No need for explicit path= here.
Why am I getting totally different behaviors ?
GNNNNNNN
If I add a 'real' query just after the test one... like this:
from java.lang import Throwable
project_name = system.perspective.getProjectInfo()['name']
tx = system.db.beginNamedQueryTransaction(project_name, 'mssql')
system.perspective.print("project name: {}".format(project_name))
try:
system.db.runNamedQuery("test", tx=tx)
system.perspective.print("test success")
system.db.runNamedQuery("families/create", {'name': family_name}, tx=tx)
system.perspective.print("family success")
except Throwable as t:
system.perspective.print(t)
system.db.rollbackTransaction(tx)
else:
system.db.commitTransaction(tx)
finally:
system.db.closeTransaction(tx)
I get
The test query works, but the real one doesn't.
The only difference is the real one is an update query. Is that what's causing all this ?
What if you provide the project name and path as kwargs? Or args? The error does imply that its taking your first arg as the project name project not found: families/create
Personally I would tell them to call the priest because this bug is demonic imo.
My point is, that the test query doesn't require naming parameters, but the other one does.
I can't make sense of it !
I understand your point that it does not sense hence why I say I would die on this hill for upgrading lol.
Try turning on NamedQueryManager to trace in your gateway, I am curious what it says when you run your test query what project it is running it against.