Attribute _key on xtk.session.Write looks like ignored | Community
Skip to main content
I
igorku
Level 2
December 2, 2022
Solved

Attribute _key on xtk.session.Write looks like ignored

  • December 2, 2022
  • 1 reply
  • 1614 views

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!

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by david--garcia

Another approach in JS. https://experienceleague.adobe.com/developer/campaign-api/api/f-sqlExec.html

 var sql= "DELETE FROM TESTKEY WHERE COL2 = 2;
sqlExec(sql);

Features

Available in:

  • Content management
  • Delivery properties
  • Typology rule
  • JSSP
  • SOAP Method
  • WebApp
  • Workflow

1 reply

david--garcia
david--garciaAccepted solution
Level 10
December 4, 2022

Another approach in JS. https://experienceleague.adobe.com/developer/campaign-api/api/f-sqlExec.html

 var sql= "DELETE FROM TESTKEY WHERE COL2 = 2;
sqlExec(sql);

Features

Available in:

  • Content management
  • Delivery properties
  • Typology rule
  • JSSP
  • SOAP Method
  • WebApp
  • Workflow
    I
    igorkuAuthor
    Level 2
    December 5, 2022

    @david--garcia, Thinking more I see that sqlExec is better than xtk.session.Write approach.

    However, Is there a way to parametrize the DELETE query?

    If I try to delete like this:

    var sql = "DELETE FROM Marketing.dbo.TestKey WHERE col2 = $(l);"
sqlExec(sql, 2000);

    I get this exception:

    JST-310000 Error while compiling script '_webApp_APP164__preview' line 156: missing ; before statement (line='var sql = "DELETE FROM Marketing.dbo.TestKey WHERE col2 = serverForm.getLocalizableString("l");"
' token='l");"
').

    Per docs (sqlExec) It looks like it's impossible to parametrize DELETE statement. Params work only for SELECT and UPDATE.

    Q: How to avoid sql-injection issue for DELETE statement? Am I missing something?

    Thank you!

      david--garcia
      david--garcia
      Level 10
      December 7, 2022

      Hi @igorku im not sure if is not available in the jsapi docs then I wouldnt know. On the other hand, if you want to find out the current database and schema name so that your code can work seamlessly in other environments, you can check with the following script (note is unfinished). but you can focus on the database environment you are working on and get the current db and current schema and use that to construct your database.schema base of your sql syntax.

       

      var oracle = false;
var postgresql = false;
var sqlserver = false;

if (application.getConnection().isPostgreSQL) {
  postgresql = true;
  var sql = sqlGetString('SELECT current_database()');
} else if (application.getConnection().isOracle) {
  oracle = true;
  var sql = sqlGetString('SELECT * FROM v$database');
} else {
  var isSQLServer = sqlGetString('SELECT @@VERSION');

  if (isSQLServer.indexOf('Microsoft') != -1) {
    var sqlserver = true
    var sql = sqlGetString("SELECT DB_NAME()+'.'+SCHEMA_NAME()"); //returns database.schema --> i.e. adobe.dbo
  }

}

logInfo(sql);
logInfo("postgresql: " + postgresql + " oracle: " + oracle + " sqlServer: " + sqlserver)

       

        Terms & ConditionsAccessibility statement

        Loading footer...