0

I am trying to execute the following query using 'ExecuteSQL' processor in Apache nifi.

INSERT INTO SampleDB VALUES (${rno}, '${tno}', '${tval}', '${lotno}', '${datval}') WHERE ${rno} NOT IN (SELECT rno FROM SampleDB);

Here the ${rno} is obtained as flow file attribute.

The following error is obtained on execution:

ExecuteSQL[id=01781107-63a4-1204-8110-6b19db3d5ffc] Unable to execute SQL select query INSERT INTO LimsOnCloud VALUES (1, 'CTG123 ', 'ITM123 ', '123 ', '5 ') WHERE 1 NOT IN (SELECT rno FROM SampleDB); for StandardFlowFileRecord[uuid=93db20b2-5f9f-4521-ac42-11239abb94c2,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1615281573937-132, container=default, section=132], offset=168254, length=152],offset=0,name=098a8ad2-0dc9-4564-a242-8b4855b619b2,size=152] due to Incorrect syntax near the keyword 'WHERE'.; routing to failure: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'.

Not able to figure out where the error lies or what is the better way to achieve this.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Rakshitha Vasishta
  • 69
  • 1
  • 7
  • 2
    The query is simply invalid. Apart from that, DON'T use string interpolation to construct SQL queries. That's how SQL injection attacks happen. What if one of those fields contained `'); Drop table Users;--` ? Use parameterized queries instead – Panagiotis Kanavos Mar 09 '21 at 10:12
  • The syntax you are looking for is `INSERT SampleDB (col1, col2, col3...) SELECT @param1, @param2, @param3.... WHERE ...` and **use parameters** – Charlieface Mar 09 '21 at 10:30
  • 1
    https://xkcd.com/327/ – Stu Mar 09 '21 at 10:55
  • You have a **table** named `SampleDB`?? – SMor Mar 09 '21 at 12:43

2 Answers2

0

  • You should be using proper parameterized queries.

  • To use a WHERE like that, you need SELECT.

  • You should also specify the column names to insert into explicitly.

  • NOT IN falls down in the face of nullables, so favour NOT EXISTS instead.

INSERT INTO SampleDB
    (rno, tno, tval, lotno, datval)
SELECT @rno, @tno, @tval, @lotno, @datval
WHERE NOT EXISTS (SELECT 1
    FROM SampleDB
    WHERE rno = @rno
);
Charlieface
  • 52,284
  • 6
  • 19
  • 43
-1

The Error is being thrown because it tries for find a column named 1 in your table, so Instead of the above, try this

INSERT INTO SampleDB SELECT ${rno}, '${tno}', '${tval}', '${lotno}', '${datval}' 
WHERE  NOT EXISTS (SELECT 1 FROM SampleDB WHERE rno = ${rno});
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39