CREATE FUNCTION using JDBC connection

nirkanirka Registered User

Hi,

I'm using DBVisualizer to connect to vertica (with JDBC connection).

I tried to create a simple function from myVertica page:
https://my.vertica.com/docs/7.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONSQLFunctions.htm

CREATE FUNCTION myzeroifnull(x INT) RETURN INT AS BEGIN RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END); END;

It works with vsql, but doesn't work using JDBC with DBVisualizer.
I get this error:

ERROR: Syntax error at or near "EOL"

Its not possible to create function this way?

Thanks

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Hi,

    Run it like this:

    --/ CREATE FUNCTION myzeroifnull(x INT) RETURN INT AS BEGIN RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END); END; /

    Or, you should be able to execute the entire buffer using the 'SQL Commander/ Execute Buffer' menu selection. According the the documentation for DbVisualizer:
    Execute Buffer sends the complete editor buffer for execution as one statement. No comments are removed and no parsing of individual statements based on any delimiters is made. This operation is useful when executing SQL blocks or SQLs used to create procedures, functions, etc.

  • nirkanirka Registered User

    Thanks, the block start / end needs to be in new lines and it works

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file