GeezerVison

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.util.deparser.ExpressionDeParser;
import net.sf.jsqlparser.util.deparser.SelectDeParser;
import net.sf.jsqlparser.util.deparser.StatementDeParser;

public class ReplaceColumnValues {

    static class ReplaceColumnAndLongValues extends ExpressionDeParser {

        @Override
        public void visit(StringValue stringValue) {
            this.getBuffer().append("?");
        }

        @Override
        public void visit(LongValue longValue) {
            this.getBuffer().append("?");
        }
    }

    public static String cleanStatement(String sql) throws JSQLParserException {
        StringBuilder buffer = new StringBuilder();
        ExpressionDeParser expr = new ReplaceColumnAndLongValues();

        SelectDeParser selectDeparser = new SelectDeParser(expr, buffer);
        expr.setSelectVisitor(selectDeparser);
        expr.setBuffer(buffer);
        StatementDeParser stmtDeparser = new StatementDeParser(expr, selectDeparser, buffer);

        Statement stmt = CCJSqlParserUtil.parse(sql);

        stmt.accept(stmtDeparser);
        return stmtDeparser.getBuffer().toString();
    }

    public static void main(String[] args) throws JSQLParserException {
        System.out.println(cleanStatement("SELECT 'abc', 5 FROM mytable WHERE col='test'"));
        System.out.println(cleanStatement("UPDATE table1 A SET A.columna = 'XXX' WHERE A.cod_table = 'YYY'"));
        System.out.println(cleanStatement("INSERT INTO example (num, name, address, tel) VALUES (1, 'name', 'test ', '1234-1234')"));
        System.out.println(cleanStatement("DELETE FROM table1 where col=5 and col2=4"));
    }
}

actual

INSERT INTO example (num, name, address, tel) VALUES (1, 'name', 'test ', '1234-1234')

expected

INSERT INTO example (num, name, address, tel) VALUES (?, ?, ?, ?)

And also Test failed

InsertTest > testInsertTableArrays4() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:400

InsertTest > testModifierIgnore() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:291

InsertTest > testInsertMultiRowValue() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:191

InsertTest > testInsertWithReturning2() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:244

InsertTest > testInsertWithReturning3() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:249

InsertTest > testInsertKeyWordIntervalIssue682() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:353

InsertTest > testNextValueFor() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:368

InsertTest > testInsertValuesWithDuplicateEliminationInDeparsing() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:331

InsertTest > testHexValues2() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:276

InsertTest > testHexValues3() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:281

InsertTest > testHexValues() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:271

InsertTest > testSimpleInsert() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:234

InsertTest > testInsertKeyWordEnableIssue592() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:348

InsertTest > testInsertWithKeywords() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:266

InsertTest > testModifierPriority1() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:296

InsertTest > testModifierPriority2() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:301

InsertTest > testModifierPriority3() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:306

InsertTest > testDisableKeywordIssue945() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:383

InsertTest > testInsertWithReturning() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:239

InsertTest > testKeywordDefaultIssue1470() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:410

InsertTest > testInsertOnConflictIssue1551() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:476

InsertTest > testBackslashEscapingIssue827() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:378

InsertTest > testIssue223() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:311

InsertTest > testNextVal() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:363

InsertTest > testOracleHint() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:393

InsertTest > testKeywordPrecisionIssue363() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:316

InsertTest > testDuplicateKey() FAILED
    org.opentest4j.AssertionFailedError at InsertTest.java:286

SpecialOracleTest > testAllSqlsParseDeparse() FAILED
    org.opentest4j.AssertionFailedError at SpecialOracleTest.java:302

Do you find out this issue??

Reacted by Zhang Xudong

Activity

manticore-projects commented on Aug 4, 2022
manticore-projects
on Aug 4, 2022
Contributor
More actions
Greetings! Thank you for reporting.

Inside the SelectDeParser, a ValueList will only be appended instead of visiting each of its expressions:

@Override public void visit(ValuesList valuesList) { buffer.append(valuesList.toString()); }

We are going to change that of course.

On the mentioned tests, you are doing something wrong. All Test must and do pass before anything is comitted into the Repository.
manticore-projects
self-assigned this
on Aug 4, 2022
manticore-projects
added
improvement
on Aug 4, 2022
manticore-projects
added a commit that references this issue on Nov 15, 2022
feat: Deparser for Expression Lists
This issue will close once commit 695ba4a is merged into the 'master' branch.
695ba4a

manticore-projects commented on Nov 15, 2022

manticore-projects

on Nov 15, 2022

Contributor

More actions

Greetings,

after applying PR #1666 your INSERT example should work like that:

        String providedSql ="INSERT INTO example (num, name, address, tel) VALUES (1, 'name', 'test ', '1234-1234')";
        String expectedSql ="INSERT INTO example (num, name, address, tel) VALUES (?, ?, ?, ?)";

        net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(providedSql);
        StringBuilder builder = new StringBuilder();
        ExpressionDeParser expressionDeParser = new ExpressionDeParser() {
            @Override
            public void visit(StringValue stringValue) {
                buffer.append("?");
            }

            @Override
            public void visit(LongValue longValue) {
                buffer.append("?");
            }
        };

        SelectDeParser selectDeParser = new SelectDeParser(expressionDeParser, builder);
        expressionDeParser.setSelectVisitor(selectDeParser);
        expressionDeParser.setBuffer(builder);

        StatementDeParser statementDeParser = new StatementDeParser(expressionDeParser, selectDeParser, builder);
        statement.accept(statementDeParser);

        Assertions.assertEquals(expectedSql, builder.toString());

wumpz
closed this as completedin #1666 on Nov 20, 2022
wumpz
closed this as completedin bff268a on Nov 20, 2022

Metadata

Assignees

manticore-projects

Labels

improvement

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions

You are visitor 01402520 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.

GeezerVison

Test Failed Issue about 4.5 #1608

Description

Activity

manticore-projects commented on Aug 4, 2022

manticore-projects commented on Nov 15, 2022

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions