A MERGE statement must be terminated with a semicolon

[skostrewa]

The Merge-Statmentent written in my .sql-file can not be executed due to a missing semicolon, even though the semicolon is specified just like the examples all over the internet require. The query also runs fine in ssms if you alter the part where the values are grabbed from SQLPage.

index.sql:

select
    'form'                       as component,
    'Edit user'                 as title,
    'insert_user.sql' || COALESCE('?id=' || $id, '') as action;

select
    'firstname'                   as name,
    (select firstname from "Ressource" where Ressource_ID = cast($id as int)) as value;

select
    'lastname'                  as name,
    (select lastname from "Ressource" where Ressource_ID = cast($id as int)) as value;

insert_user.sql:

MERGE Ressource as target
USING  (select CAST($id AS INT) as id, :firstname as new_firstname, :lastname as new_lastname) as source
ON (target.Ressource_ID = source.id)
WHEN MATCHED
    THEN UPDATE SET
        target.Vorname = source.new_firstname,
        target.Nachname = source.new_lastname
WHEN NOT MATCHED
    THEN INSERT (RessourceTyp_ID, Vorname, Nachname)
    VALUES (1, source.new_firstname, source.new_lastname)
;

There is an error-message being displayed when you execute the presented code

error returned from database: Eine MERGE-Anweisung muss durch ein Semikolon (;) abgeschlossen werden. (translates to "A MERGE statement must be terminated with a semicolon (;)")

i expect the database-table to be updated or inserted according to the data submitted by the form

• OS: Windows 11
• Database: MSSQL
• SQLPage Version: 0.20.4

[lovasoa]

Hello and welcome to SQLPage! Thank you for reporting this, I'll look into it. In the meantime you should be able to work around this by putting the merge into a stored procedure and just calling it from SQLPage.

[skostrewa]

Hi, Thank you for you fast reply! Unfortunately i am failing to call any sp from SQLPage and i am not able to find any information about that topic. Can you elaborate on how to call a sp from SQLPage?

Furthermore i noticed that the link to your blog on the sqlpage-website is not working as intended: E.g.: im currently browsing https://sql.ophir.dev/examples/tabs.sql and select blog from the menu. The url will be https://sql.ophir.dev/examples/blog.sql instead of https://sql.ophir.dev/blog.sql and you will get a 404 not found. I thought i just mention it here instead of opening a new issue.

[skostrewa]

I'm now temporarily got it running using a upsert statement. I am still looking forward to your answer 😄.

[lovasoa]

E.g.: im currently browsing https://sql.ophir.dev/examples/tabs.sql and select blog from the menu. The url will be https://sql.ophir.dev/examples/blog.sql instead of https://sql.ophir.dev/blog.sql and you will get a 404 not found.

It sounds like you have an old version of the page in cache ? Can you hard-refresh the page (with ctrl-shift-R) ?

[skostrewa]

I am glad i did not open an issue for that. Hard refreshing the page fixed this issue. Thanks!

[lovasoa]

I'll change SQLPage's behavior to include trailing semicolons in what we send to the database in the next release. Currently, the easiest is probably to do an update followed by an insert with a condition.

SELECT 'text' as component, @@VERSION as contents;

--drop table Ressource;
--create table Ressource (Ressource_ID INTEGER PRIMARY KEY, firstname varchar(50), lastname varchar(50));

-- In the next sqlpage, you will be able to run:
-- merge into Ressource as target
-- using (select $firstname as firstname, $lastname as lastname) as source
-- on target.Ressource_ID = $id
-- when matched then
--     update set firstname = source.firstname, lastname = source.lastname
-- when not matched then
--     insert (Ressource_ID, firstname, lastname) values ($id, source.firstname, source.lastname);

-- Currently, we do it manually. Update and then insert if necessary.
update Ressource set firstname = $firstname, lastname = $lastname where Ressource_ID = $id;
insert into Ressource (Ressource_ID, firstname, lastname) select $id, $firstname, $lastname where @@ROWCOUNT = 0;

select 'list' as component;
select CONCAT(firstname, ' ', lastname) as title, Ressource_ID as description
from Ressource;

[skostrewa]

Thank you very much!

[lovasoa]

Let's keep this opened until the semicolon fix is pushed

[lovasoa]

0.21 is now live, with the fix