Solved
Attribute _key on xtk.session.Write looks like ignored
Hello All,
- ACC 7.2.2 build 9349@e319b6d of 02/28/2022
- MSSQL is an external database
- <srcSchema> is (some values are masked/dotted):
<srcSchema ... entitySchema="xtk:srcSchema" img="xtk:schema.png" label="TestKey" ... name="TestKey" ... view="true" xtkschema="xtk:srcSchema">
<createdBy _cs="..."/>
<modifiedBy _cs="..."/>
<element dataSource="..." label="TestKey" name="TestKey"
sqltable="[dbo].[TestKey]">
<attribute advanced="false" label="col1" name="col1" sqlname="col1" type="long"/>
<attribute advanced="false" label="col2" name="col2" sqlname="col2" type="long"/>
<key clustered="true" name="PK_TestKey">
<keyfield xpath="@col1"/>
<keyfield xpath="@col2"/>
</key>
</element>
</srcSchema>
Note, there is a <key> element defined in the schema! Two columns involved. It's important for later.
- MSSQL database table structure is:
create table dbo.TestKey
(
col1 int not null,
col2 int not null,
constraint PK_TestKey Primary key(col1, col2)
)
- Test table data:
select * from dbo.TestKey;
col1 | col2
------------
10 | 1
10 | 2
20 | 1
20 | 2
- Task: I need to remove all records from dbt.TestKey where col2 = 2 (two records to delete)
- JavaScript in use:
xtk.session.Write(<TestKey xtkschema="ef:TestKey" _operation="delete" _key="@col2" col2="2" />);
Note, here attribute @_key is different from the one defined in <scrSchema> - one column vs. two columns!
Problem
When I run the code, only one record is deleted from table. But expected two records to delete (WHERE col2 = 2).
I can grab the DELETE sql statement produced by the above code (SQL Server Profiler helped me). The statement is:
declare @2784028 int
set @2784028=42
exec sp_prepexec @2784028 output,N'@P1 int,@P2 int',N'DELETE FROM [dbo].[TestKey] WHERE (col1 = @2784028 AND col2 = @65055)',10,2
select @2784028
Look at this - it uses columns col1 and col2 from the <key> defined in <scrSchema>, not the one defined in xtk.session.Write @_key attribute. Why? How to remove the records I need to remove?
Please help!