MYSQL Query into java jdbc prepared statement, error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

Question

I have the following working MYSQL Query (it makes a pivot join over two tables):

SET @sql = NULL;
SELECT  
 GROUP_CONCAT(DISTINCT
    CONCAT(
    'MAX(IF(sk.keywords = ''', sk.keywords,''', kr.rank, NULL)) AS ',  CONCAT('`',sk.keywords, '`')
    )
) INTO @sql
 FROM 
     search_keywords sk
    WHERE sk.product_id = 2 AND sk.active = 'Y'; 

SET @sql = CONCAT('SELECT eval_date, ', @sql, '  
                   FROM keyword_ranking AS kr 
                   LEFT JOIN  search_keywords AS sk 
                    ON kr.search_keywords_id = sk.id 
                   GROUP BY eval_date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This query I want to insert into a java prepared statement. If I just copy it, I 'll get a misform sql syntax error:

st = connection.prepareStatement(
              "SET @sql = NULL; "
            + "SELECT "
            + "GROUP_CONCAT(DISTINCT "
            + "CONCAT("
            + "'MAX(IF(sk.keywords = ''', sk.keywords,''', kr.rank, NULL)) AS ',  CONCAT('`',sk.keywords, '`')"
            + ") INTO @sql "
            + "FROM search_keywords sk "
            + "WHERE sk.product_id = ? AND sk.active = ?; "
            + "SET @sql = CONCAT('SELECT eval_date, ', @sql, ' "
            + "FROM keyword_ranking AS kr  "
            + "LEFT JOIN  search_keywords AS sk  "
            + "ON kr.search_keywords_id = sk.id  "
            + "GROUP BY eval_date'); "
            + "PREPARE stmt FROM @sql; "
            + "EXECUTE stmt; "
            + "DEALLOCATE PREPARE stmt; "
        );

Error:

Error com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(sk.keywords = CONCAT(''', sk.keyword' at line 1

Just a guess: Java may only let you execute one statement per query. So in your case, you should separate into multiple queries. — Sirko
– Sirko, Commented Jul 6, 2015 at 10:22
thank you Sirko, but how can I do this in java, when I use the @SQL within the second query? — GobiRan
– GobiRan, Commented Jul 6, 2015 at 10:24
You can probably consider having your SQL as stored procedure and invoking this SP from Java. — Stan
– Stan, Commented Jul 6, 2015 at 10:31
See docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html for a quick intro to transactions (== multiple connected queries) — Sirko
– Sirko, Commented Jul 6, 2015 at 10:46

GobiRan · Accepted Answer · 2015-07-06 12:00:41Z

Thank you @Stan & @Sirko. I believe you both are right. I first tried the transactions, but there I also got this error, so either I handled the transactions wrong or I still had to escape the single quotes somehow. As I allready tried lots of different variations of escaping I thougth to give the stored procedure a try. This works!!!

For others who are interested to create the stored procedure, I will paste my code below:

Store procedure in Mysql:

CREATE DEFINER=`gobiran`@`%` PROCEDURE `getKeywordHistoryByProductId`(IN PRODUCT_ID INT(11))
BEGIN

SET @sql = NULL;

SELECT  
 GROUP_CONCAT(DISTINCT
    CONCAT(
    'MAX(IF(sk.keywords = ''', sk.keywords,''', kr.rank, NULL)) AS ',  CONCAT('`',sk.keywords, '`')
    )
) INTO @sql
 FROM 
     search_keywords sk
    WHERE sk.product_id = PRODUCT_ID AND sk.active = 'Y'; 

SET @sql = CONCAT('SELECT eval_date, ', @sql, '  
                   FROM keyword_ranking AS kr 
                   LEFT JOIN  search_keywords AS sk 
                    ON kr.search_keywords_id = sk.id 
                   GROUP BY eval_date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

call the stored procedure:

ResultSet rs = null;
        Connection connection = DataBaseHelper.getConnection();
        CallableStatement stmt = null;
        try {

            stmt = connection.prepareCall("{ call getKeywordHistoryByProductId(?) }");
            stmt.setInt(1, productId);
            rs = stmt.executeQuery();

// Thats it, now work with the Resultset ... 
            ResultSetMetaData md = rs.getMetaData();

            int columns = md.getColumnCount();
            List<Object[]> result = new ArrayList<Object[]>();
            String[] header = new String[columns];
            int index = 0;
            while (rs.next()){
                Object[] entry = new Object[columns];
                for (int i = 0; i < columns; i++) {
                    if(index == 0) {
                        header[i] = md.getColumnName(i+1);
                    }
                    entry[i] = rs.getObject(i+1);
                }
                if(index == 0) {
                    result.add(header);
                }
                result.add(entry);
                index++;                
            }

... just proforma: finish the try / catch / finally clause and close stmt, connection, rs in finnaly

Collectives™ on Stack Overflow

MYSQL Query into java jdbc prepared statement, error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related