The Insert script works flawlessly on SSMS.
But using perspective Insert script, the same SQL code will lockup the database, preventing other queries to proceed.
I tried runNamedQuery, runPrepUpdate same behaviour.
From observation, lock happends (usually) when I change argument from say 'A' to 'B'.
What is happening here, is there a way to make script run in perspective as if it were run on SSMS?
When lock happens, I need to run a rollback transaction on namedQuery.
The lock issue will be fix.
This command also returns error saying missmatch of begin and commit statement.
But the script does not use begin transaction and commit transaction.
Also the table I am inserting too, was created by below code:
CREATE TABLE [dbo].[Request](
[RequestId] [int] IDENTITY(1,1) NOT NULL,
[Department] [nvarchar](50) NULL
CONSTRAINT [PK_Request] PRIMARY KEY CLUSTERED
(
[RequestId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I did not write the above code.
But still the insert script when run on SSMS will work just fine.
On ignition, it will lock the database.
SSMS doesn't use JDBC. It makes a native connection to the database. But Microsoft also supplies the JDBC driver involved. Which means this is really a question for Microsoft, not IA.
short answer, I add "if @@trancount > 1 commit tran"
long answer, the code looks like below:
ignition begin statement
runQuery statement
ignition commit statement
concept:
for every begin, there should be one commit.
somehow the runQuery knows, if arg have change from previous run.
when argument of runQuery has changed. there will be two begin statement.
one from igntion begin statement (explicitly written),
the other one, idk where it came from.
so I add the code "if @@trancount > 1 commit tran" on the start of the SQL.
and it work flawlessly this time.
You haven't posted code showing how you are beginning and committing transactions surrounding your runQuery. If you aren't using Ignition's own system.db.beginTransaction() and passing the txid to subsequent calls, you cannot expect it to work. (Ignition uses connection pooling, so trying to use BEGIN and COMMIT separately won't necessarily happen on the same connection that your queries use. ( Boom! )
tx = system.db.beginTransaction(isolationLevel = 2, timeout=5000)
query1 = \
"""
if @@trancount > 1 commit tran
DECLARE
@req int = NULL,
@datetime datetime = GETUTCDATE()
SET NOCOUNT ON;
INSERT INTO [table1]
([column1],
[columnN]
)VALUES(
NULL,
?
);
SELECT @req = @@IDENTITY
INSERT INTO [dbo].[table2]
([col1],
[colN])
VALUES
(@req,
@dateTime,
?,
?);
INSERT INTO [dbo].[table3]
([col1],
[col2])
SELECT @req, value FROM [table4];
SELECT @@TRANCOUNT
"""
args = []
args.append(arg1)
args.append(argN)
result1 = system.db.runPrepQuery(query=query1, args=args, tx=tx)
system.db.commitTransaction(tx)
system.db.closeTransaction(tx)
system.perspective.print(result1[0][0])
What is happening is,
if I keep sending query with the same argument. @@TRANCOUNT will be equal to 1. (result1[0][0])
but when you change value of argument. @@TRANCOUNT will be equal to 2. and data will not be written to the table.
I use dropdown to changed data of argument.
I am still curious if this is because of how database setup, or other than that.
Your query1 has multiple statements. This is not supported by JDBC, though Microsoft's driver allows it. This will cause you grief. In particular, each operation will produce either a result set or a return code, and Ignition cannot handle multiple responses.
Break query1 into separate operations, one SELECT, UPDATE, or INSERT per operation. Don't use @var syntax anywhere.
Let me rephrase a comment above: SSMS and JDBC are different technologies. Do not expect things that work in SSMS to necessarily work in JDBC.
Yes, I was there. BUT, I google search, Insert and Select @@IDENTITY cannot be separated.
and in DB world, probably its usual to Mashup Multiple Insert and Select.
All the tables above are related in some way. So I figure, probably JDBC may support it?
Another example is on the last query. The values for the insert statement came from select statement. (on second thought, this can be split)
I will try to split them some more as possible.. See if one of those select is adding Begin statement.
When you perform an INSERT operation via system.db.*Update() or a named query, use the getKey boolean argument to ask Ignition to get that identity value for you in a JDBC-compliant way. When getKey is true, the return value is the identity value instead of the number of affected rows (the normal return from an update).
Doesn't work for mssql though, or I'm missing something.
I had to add select scope_identity() after my inserts in named queries for getKey to have any effect.
JDBC expects single statements. One SELECT, one UPDATE, one INSERT, one whatever per call. If you execute a single INSERT, the getkey option should work. If it doesn't, report the bug.
Not all databases support automatic retrieval of generated keys.
I seem to recall I did some research when I needed that feature and found out mssql is one of those.
If you say it should work, I'll do some tests and contact support if it doesn't work.
But in the meantime, adding a select scope_identity() after the insert does work.
I should also say that I'm not very familiar with databases and their drivers.
The OP is using multi-statement SQL scripts instead of single statements per call. That would certainly explain the failure of getKey, as SQL scripts are not supported by generic JDBC.
Yeah, I agree. I was specifically responding to @pascal.fragnoud needing to use select scope_identity() after an insert. I've edited my response to make that a bit clearer.
Both successfully return the auto generated ID for the row. Notice that I am not providing a value for the ID column.
You do not need to request the Identity in the query, just run the insert and provide a True value for the getKey parameter. I have never had to do anything different for this to work as expected, regardless of if this was being run inside or outside of a transaction.
Also, since you are running this in perspective, remember that it is being executed in a Gateway Scope, and so for system.db.runPrepQuery() you should really provide the database name that you want to use, and for system.db.runNamedQuery() you must provide your project name.
I see, returning newly generated key in the manual refers to the identity being return.
Thanks for the info about, gateway and project scope, easier to understand why we need those input declared on the arguments. Makes more sense now.
Edit: There is actually a runNamedQuery(), with project scope. - no need to specify project.
I rather use runNamedQuery(), I can keep the script in one place, cleaner code as you don't have to write the SQL on the runAction Script, for the arguments, using dictionary is better for me than list.
Do you know if there would be a problem using runNamedQuery instead of runPrepQuery?