Creating Vertica Functions in Third Party Tools

Third party SQL client tools that connect to Vertica often provide a SQL command window where you can type SQL commands and view the results. Most of these tools’ SQL command windows support issuing multiple commands in a single SQL block, using the semicolon as the command delimiter. Here is an example in DbVisualizer:


The Vertica CREATE FUNCTION statement can present problems in the SQL window of a third party tool, because it uses semicolons to separate the commands in the function. A simple example is shown below. Note the semicolons at the end of the third and fourth lines. CREATE OR REPLACE FUNCTION public.myzeroinnull(z NUMERIC) RETURN NUMERIC AS BEGIN RETURN (CASE WHEN (z IS NULL) THEN 0 ELSE z END); END; Creating a Vertica function in the SQL command window of a third party tool may result in an error such as: [CREATE - 0 rows, 0.001 secs] [Code: 4856, SQL State: 42601] [Vertica][VJDBC](4856) ERROR: Syntax error at or near "EOL" This is because the tool sees the first semicolon and thinks it’s the end of the statement. It then parses the SQL into two statements and tries to execute them one at a time, confusing the Vertica driver. This can be seen in the driver debug output, such as the JDBC debug info shown below. Note that the output ends at the location of the first semicolon. Jun 21 15:41:18.823 DEBUG 20 com.vertica.io.ProtocolStream.sendMessage: FE => [Parse, , CREATE OR REPLACE FUNCTION public.myzeroinnull ( z NUMERIC ) RETURN NUMERIC AS BEGIN RETURN (CASE WHEN (z IS NULL) THEN 0 ELSE z END)] Jun 21 15:41:18.824 DEBUG 20 com.vertica.io.ProtocolStream.sendMessage: FE => [Flush] Jun 21 15:41:18.870 DEBUG 20 com.vertica.io.ProtocolStream.receiveOneResponseMessage: BE <= [Error] Jun 21 15:41:18.874 ERROR 20 com.vertica.core.VConnection.handleError: [Vertica][VJDBC](4856) ERROR: Syntax error at or near "EOL" Most of the third party tools provide a way for you to change the delimiter, temporarily and/or permanently. Changing the delimiter for the duration of the CREATE FUNCTION execution is necessary to get the command to execute correctly. Each tool has different methods of accomplishing this. We won't cover them all, but here are details for two of the more popular SQL client tools. Refer to your tool's documentation for details on where and how to modify the command delimiter.

DbVisualizer:
In DbVisualizer, you can pass hints in the SQL to change the delimiter temporarily. We used the @delimiter hint successfully. @delimiter ++; CREATE OR REPLACE FUNCTION public.myzeroinnull(z NUMERIC) RETURN NUMERIC AS BEGIN RETURN (CASE WHEN (z IS NULL) THEN 0 ELSE z END); END; ++ @delimiter ;++ See Also:

• “Executing Complex Statements “ in the DbVisualizer User Guide: http://confluence.dbvis.com/display/UG100/Executing+Complex+Statements
• Vertica Integration with DBVisualizer: Connection Guide

SQuirreL:
In the Sessions menu > Session Properties > SQL tab, you can change the Statement Separator character to something like the pipe symbol (|) temporarily. After you execute the SQL CREATE FUNCTION, set the delimiter back to the semicolon (;).

See Also:
Vertica Integration with SQuirreL: Connection Guide