Not able to delete row in MSSQL via :delete parameter

General Discussion
1

HI all,

I am trying to manage a best practice for my system design, and one thought was to have this Named Query.

MERGE INTO company AS Target
USING (VALUES (:name, :parentId))
      AS Source (name, parentId)
ON Target.name = Source.name
WHEN NOT MATCHED THEN
    INSERT (name, parentId)
    VALUES (Source.name, Source.parentId)
WHEN MATCHED AND :delete = TRUE THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET
        name = Source.name,
        parentId = Source.parentId;

my thought was to have a merge/update/insert and delete handled by one query.
The issue coming up is, the insert isn't working, the delete throws an error:

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'TRUE'

Any thoughts are greatly appreciated.

2

You can't use a Value type parameter as a column name. You have to use a QueryString type - but you need to be aware of the risks.

1 Like
3

Also, MS SQL Server doesn't implement the SQL Standard TRUE and FALSE literals--it doesn't support the BOOLEAN data type at all. When passing through JDBC to SQL Server, booleans are coerced to tinyint zero or one.

{Just say NO to MS SQL Server!}

4

I wish I could... Thats what we get access too. Unfortunately, I can't control that. I have to work with what my company provides.
I get the sentiment.

5

Thank you. I am not allowing any user input to that parameter. It is 100% hard code.

6

Noted, but you can complain about it when MS stupidity puts you in a bind. (BTW, SQL Server's MERGE statement is racy--it can give errors by chance on non-conflicting inserts--where PostgreSQL's ON CONFLICT is deterministic.)

If you don't complain, your company will never get the hint that MS SQL Server is [expletive]. There is a reason that all of the other major players use PostgreSQL as the foundation of their big data offerings.

1 Like
7

Well, let me see if I can get a PostgreSQL server for this project. :slight_smile:

1 Like