Dbeaver :- Editor – Customization – “Statement Delimiter”

Background

Got around to developing programmable objects, Stored Procedures and Functions, with DBeaver.

Error

Ran into an error as the routine, specifically function, got a bit more complex.

Image

endofStatement_error_01_20180912_0257AM.PNG

Textual


 SQL Error [42601]: An unexpected token "END-OF-STATEMENT" was found following "
        end if".  Expected tokens may include:  "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.21.29<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Trouble Shooting

The error seems to be related to statements that span multiple lines such as :

  1. if/then/end if
  2. while do / end while

 

Remediation

Outline

  1. Configuration
    • Access Menu
      • Window > Preferences > Editors/SQL Editor/SQL Processing
        • Statement Delimiter
          • Change Statements delimiter: from “;” to something peculiar
            • We are using //
        • Ignore Native Delimiter
          • Enabled/Checked
        • Blank line is statement delimiter
          • Enabled/Checked
  2. SQL Editor
    • Within the text pane
      • Please end your programmable object code definition with the delimiter

 

Configuration

Image

Origin

endofStatement_customize_01_20180912_0302AM.PNG

Revised

endofStatement_customize_02_20180912_0329AM

 

SQL Editor

Image

Revised

codeEditor_20180912_0336AM

 

Dedicated

Anyone who knows me, know I do not keep opinions to myself.

It took a long time, too much.

Sorry, but not in any mood to waste even more time.

I just spent 30 minutes listening in to Wendy Williams talk about people throwing shoes at each other; knowing we are better than this.

So here we go.

Dedicating to people worth talking about, people what applauding.

Serge Rider, the great Sergent!

Conversations Tracked

MySQL DELIMITER statement
Link

  1. erseco
    • In English
      • It would be interesting support the MySQL DELIMITER statement to expedite the development and testing of stored procedures and functions.Thanks!
    • In spanish
      • Sería interesante soportar el DELIMITER de MySQL para agilizar el desarrollo y testeo de funciones y procedimientos almacenados.
  2. Serge
    • Hello, Yes, we are going to add this feature (not only for MySQL). Generally it’s quite not easy feature (as we need to make complex analysis of SQL script) but hopefully it’ll be implemented soon.  Thanks!
  3. Endo
    • Any update?
  4. carlosspohr » Fri Mar 27, 2015 3:37 am
    • It will beautiful when implemented on DBeaver!
      @Serge, I can test this feature for mysql db if you want…every day I need to use cmd or terminal to run my procedures coded in DBeaver 🙂
  5. guni82
    • Hi!
      Setting the delimiter would be a great and essential feature!
      I’m using triggers and procedures in Firebird a lot and can’t import them from a script at the moment.
      I could even live with an option to change the standard delimiter from ; to something else.
      There is an option called Script Delimiter in Avanced Firebird Driver Parameters. It activates e.g. $$ as a delimiter but unfortunately doesn’t deactivate ;
      However, congrats to your great work!
      Thanks
  6. Mikemcg
    • Just discovered DBeaver and it is best tool of this kind I have used. However the lack of support for DELIMITER in MySQL means I can’t run scripts to create Stored Procedures so I still need to revert to other tools when I would prefer to just use this one.

      Any fixes or workaround much appreciated.

  7. Serge
    • Priority of this feature was increased, hopefully next version of DBeaver will have support of custom delimiters.
  8. Serge
    • Version 3.4.0 supports custom script delimiters:
  9. aleishman » Tue Dec 01, 2015 2:25 pm
    • Your tool is absolutely awesome but the delimiter thing is a serious issue. Being ablt to set and change the delimiter at will in a long script file is extremely important
  10. tb_ai » Sat Jan 09, 2016 10:27 pm
    •  

      Like Serge said, the new version can solve the delimiter problem (workaround solution).
      Window > Preferences > Editors/SQL Editor/SQL Processing:
      change Statements delimiter: from “;” to “~” (DBeaver only allow 1 char, so I pick “~”)

      You need to remove the delimiter statement and replace your delimiter with “~”

      I was using mySQL workbench but after updating my CentOS 7, the resultgrid is no long showing. So I switch to DBeaver and it works very well.

 

References

  1. Dbeaver
    • Dbeaver Forum
      • MySQL DELIMITER statement
        Link
    • Dbeaver Community
  2. IBM
    • Home > Db2 for z/OS 11.0.0 > Db2 codes > SQL codes
      • SQL error codes
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s