[Solved] SQL Error - Unexpected end of statement

Creating and using forms
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved] SQL Error - Unexpected end of statement

Post by gkick »

Hi,

Trying to run a script to set a trigger from a button, below is the contents of the scriptfile

Code: Select all

CREATE TRIGGER "delete_tblJob" BEFORE DELETE ON "tblJob"
REFERENCING OLD AS oldrow
FOR EACH ROW WHEN (oldrow."jobid"  < 300000)
BEGIN ATOMIC
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invoices can not be deleted, only cancelled';
END;
The code works from Tools, SQL and the code to run the script also works, as it is used with another script which just clears and resets tables.
Is there anything in the code missing?
Prefer running a simple script instead of embedding the sql statement in a macro.
Thanks for your thoughts
Last edited by gkick on Mon Jul 20, 2020 9:08 pm, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: SQL Error - unexpected end of statement

Post by Sliderule »

Just looking at the code you included above . . . your last line is:

Code: Select all

END;
The final semi-colon ; should be eliminated ( not present ), put another way, it should be:

Code: Select all

END
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: SQL Error - unexpected end of statement

Post by gkick »

@Sliderule

Thank you, however removing the semicolon does not make a difference, might have to fiddle with lots of double quotes in a macro instead.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: SQL Error - unexpected end of statement

Post by F3K Total »

Hello,
this works for me, Starbasic, HSQLDB 2.5.1

Code: Select all

SUB SET_TRIGGER
    oController = thisdatabasedocument.Currentcontroller
    if not oController.isConnected then oController.Connect
    oConnection = thisdatabasedocument.Currentcontroller.ActiveConnection
    oStatement = oConnection.createStatement
    sSQL = "CREATE TRIGGER ""delete_tblJob"" BEFORE DELETE ON ""tblJob"" "&_
        "REFERENCING OLD AS OLDROW "&_
        "FOR EACH ROW WHEN (OLDROW.""jobid""  < 30) "&_
         "BEGIN ATOMIC "&_
            "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invoices can not be deleted, only cancelled'; "&_
        "END"
     print sSQL
    oStatement.executeupdate(sSQL)
end sub

SUB DROP_TRIGGER
    oController = thisdatabasedocument.Currentcontroller
    if not oController.isConnected then oController.Connect
    oConnection = thisdatabasedocument.Currentcontroller.ActiveConnection
    oStatement = oConnection.createStatement
    sSQL = "DROP TRIGGER ""delete_tblJob"""
    oStatement.executeupdate(sSQL)
end sub
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: SQL Error - unexpected end of statement

Post by gkick »

@F3K Total,

Thank you very much for this, working like a treat !
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] SQL Error - Unexpected end of statement

Post by Sliderule »

Just as an FYI ( For Your Information ) I would suggest a small modification to the StarBasic macros suggested by wise user F3K Total above.

Since, CREATING or DROPPING a Trigger might fail if it exists ( on a CREATE ), or, does not exist ( on a DROP ) . . . therefore . . . make that decision in code prior to taking the action.

The following Query will return either integer zero or one . . . so . . . incorporate the query into the macro to determine it the CREATE or DROP should be executed. :super:

Code: Select all

Select 
   COUNT(*) as NUMBER_OF_TRIGGERS 
From INFORMATION_SCHEMA.TRIGGERS 
Where INFORMATION_SCHEMA.TRIGGERS.TRIGGER_SCHEMA = CURRENT_SCHEMA 
  and INFORMATION_SCHEMA.TRIGGERS.TRIGGER_NAME = 'delete_tblJob'
Of course, I am sure you will be able to 'code' and run the above SQL Query and add the 'logic' to the code. :super:

Sliderule
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved] SQL Error - Unexpected end of statement

Post by gkick »

@Sliderule - thanks for that :super:
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Post Reply