Error interpreting sql statements with blank lines

[orencio]

Hello, this topic was treated in this thread: http://dbeaver.jkiss.org/forum/viewtopic.php?f=2&t=669, although I don't know how to solve it.

This is a silly example (with postgres):

select *

from objeto;

returning this error:

SQL Error [42601]: ERROR: error de sintaxis en o cerca de «from»
  Position: 1

In Window->Preferences->Editors->SQL Editor->SQL Processing, Statement delimiter is set ; but I don't found anything about don't use blank lines as statement delimiter too.

Is it possible ignore blank lines as statement delimiter?

It is very useful when writing multiples PL procedures in the same script because I separate some of its code with blank lines to be more readable, and I want to execute only one of this statements (execute as statement not as script).

In Oracle sqlplus/sqldeveloper, the character / is used to specify the end of statement (function, trigger, anonymous block, etc).

Regards.

[serge-rider]

Currently statement execution command (ctrl+enter) threats blank lines as statement delimiter. At the moment there is no workaround for that. Mostly because some databases works that way. Also because it is simply convenient. But you can select your statement (with any number of blank lines/special characters inside) and execute it (ctrl+enter). Also you can execute it as script (alt+x) - in this case only statement delimiter (; by default) will be used as delimiter.

I could add some additional config option (like "Use blank line as statement delimiter") for SQL processing.. WDYT?

[orencio]

It should be wonderful. For example, for execute statement (CTRL+Intro), specify group of chars (Ex.: \n, ;, /, ...).

And there is same special cases (ex. Postgresql) where you can create dollar quoted string:

create function f() returns varchar as $$
    ...
$$ language ...;

$$ ... $$ is a string. But it is possible something like $any_text$ ... $any_text$. This is a particuarity of Postgresql, and I think dbeaver doesn't understand very well.

[orencio]

Hello, this might be a solution:

in org.jkiss.dbeaver.ui.TextUtils.java, add a new method to check dollar string delimiter:

    public static String[] hasDollarStringDelimiter(IDocument document, int line) throws BadLocationException {
        /*
         * It returns an String array. The first element is type of dollar string delimiter
         * (O: Open string delimiter, C: Close string delimiter). The second element is
         * the delimiter string (examples: $$, $body$, ...).
         * If there not found delimiter string in this line, it returns null.
         *
         * A string delimiter must be at the end of the line (if it is opening),
         * or at the begin of the line (if it is closing).
         *
         * Example:
         *    create ... $body$
         *      ...
         *    $body$ ....
         */
        IRegion region = document.getLineInformation(line);
        if (region == null || region.getLength() == 0) {
            return false;
        }

        String str = document.get(region.getOffset(), region.getLength());
        String matchStringBeg = "^[ \\t]*(\\$[a-zA-Z0-9]*\\$)([ \\t].*)?$";
        String matchStringEnd = "^.*[ \\t](\\$[a-zA-Z0-9]*\\$)[ \\t]*$";
        if (str.matches(matchStringBeg))
            return (String[]) {"O", str.replaceAll(matchStringBeg, "$1"))};
        else if (str.matches(matchStringEnd))
            return (String[]) {"C", str.replaceAll(matchStringEnd, "$1"))};
        else
            return null;
    }

and a modify in org.jkiss.dbeaver.ui.editors.sql.SQLEditorBase.java the method extractQueryAtPos:

    public SQLQuery extractQueryAtPos(int currentPos)
    {
        Document document = getDocument();
        if (document == null || document.getLength() == 0) {
            return null;
        }
        int docLength = document.getLength();
        IDocumentPartitioner partitioner = document.getDocumentPartitioner(SQLPartitionScanner.SQL_PARTITIONING);
        if (partitioner != null) {
            // Move to default partition. We don't want to be in the middle of multi-line comment or string
            while (currentPos < docLength && !isDefaultPartition(partitioner, currentPos)) {
                currentPos++;
            }
        }

        // Extract part of document between empty lines
        int startPos = 0;
        int endPos = document.getLength();
        try {
            int currentLine = document.getLineOfOffset(currentPos);
            int lineOffset = document.getLineOffset(currentLine);
            int linesCount = document.getNumberOfLines();

            // First search for dollar quoted strings.
            int firstLineDollar = currentLine, lastLineDollar = currentLine;
            int firstLine, lastLine;
            String[] dollarDelimiterOpen = null, dollarDelimiterClose = null;
            while (firstLineDollar > 0) {
                dollarDelimiterOpen = TextUtils.hasDollarStringDelimiter(document, firstLine);
                if (dollarDelimiterOpen != null) {
                  if (dollarDelimiterOpen[0] != "O") {
                      // It is not an open delimiter.
                      dollarDelimiterOpen = null;
                  }
                  break;
                }
                firstLineDollar--;
            }
            if (dollarDelimiterOpen != null) {
                while (lastLineDollar < linesCount) {
                    dollarDelimiterClose = TextUtils.hasDollarStringDelimiter(document, lastLine);
                    if (dollarDelimiterClose != null) {
                        if (dollarDelimiterClose[0] != "C") {
                            // It is not a close delimiter.
                            dollarDelimiterClose = null;
                        }
                        break;
                    }
                    lastLineDollar--;
                }
            }
            // Chek if there found valids strings delimiters.
            if (
                (dollarDelimiterOpen != null) &&
                (dollarDelimiterCloe != null) &&
                (dollarDelimiterOpen[1] == dollarDelimiterClose[1])
            ) {
                // They was found. Use they positions as new firstLine, lastLine.
                firstLine = firstLineDollar;
                lastLine = lastLineDollar;
            } else {
                // They was not found, so check only for blank lines.
                firstLine = currentLine;
                lastLine = currentLine;
            }

            // Search for blank lines.
            while (firstLine > 0) {
                if (TextUtils.isEmptyLine(document, firstLine)) {
                    if (isDefaultPartition(partitioner, document.getLineOffset(firstLine))) {
                        break;
                    }
                }
                firstLine--;
            }
            while (lastLine < linesCount) {
                if (TextUtils.isEmptyLine(document, lastLine)) {
                    if (isDefaultPartition(partitioner, document.getLineOffset(lastLine))) {
                        break;
                    }
                }
                lastLine++;
            }
            if (lastLine >= linesCount) {
                lastLine = linesCount - 1;
            }
            startPos = document.getLineOffset(firstLine);
            endPos = document.getLineOffset(lastLine) + document.getLineLength(lastLine);

            // Move currentPos at line begin
            currentPos = lineOffset;
        } catch (BadLocationException e) {
            log.warn(e);
        }
        return parseQuery(document, startPos, endPos, currentPos);
    }

I haven't test yet because I have not deply the project in any IDE.

What do you think?

[serge-rider]

Interesting, thanks. However I thought about modifying SQL syntax parser rules (extending delimiter rule). And also modifying extractQueryAtPos to make "blank lines as delimiters" configurable.

Don't forget about multi-database nature of SQL editor. All database specific stuff should be in SQLDialect/driver configuration. Dollar signs as delimiters are definitely not standard and should be somehow handled by PostgreSQL extension..

[serge-rider]

Will be added in the next version

[annanyearian]

Where is this setting? Searching the preferences screen for "blank lines" returns no result, also tried other combinations of related words/phrases.

[shoulders]

just for reference there is now a setting

Windows --> Preferences --> Editors --> SQL Editor --> SQL Processing --> Delimiters --> Blank line is a statement delimiter