0

I need to execute a set of statements which uses user-defined variables using JDBC.

Below is my query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.fieldname = ''',
      fieldname,
      ''', pa.fieldvalue, NULL)) AS ',
      fieldname
    )
  ) INTO @sql
FROM product_additional;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id');

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

I want some way to execute the above statements using JDBC. I stored the above statements in a Java string and tried to execute in the following way.

 String sqlString = //above statements
 Statement stmt = connection.prepareStatement(sqlString);
 System.out.println("executing query ***************************");
 ResultSet rs = stmt.executeQuery(sql);

But it throws the below error.

Caused by: 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(
                   'IF(cv.' at line 2

FYI, the above query is to convert row data into columnar data (pivot functionality).

Is there a way to execute the above query using JDBC and MySQL?

Mark Rotteveel
  • 90,369
  • 161
  • 124
  • 175
Preethi
  • 1,630
  • 2
  • 15
  • 22
  • You may be running into [this issue](http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement), since Statement is throwing an error on the second line in your query. – Thomas Apr 24 '15 at 20:45
  • Well you can run the script through comand line using runtime but not with jdbc – Somnath Sarode Apr 25 '15 at 14:28
  • @Thomas: Thanks for the link. It works! – Preethi Apr 27 '15 at 18:04

2 Answers2

0

From my understanding you can't select into a variable like you are trying in mysql.

select
  *
into 
 @variable
from
  my_existing_table

If you want to select into a temporary table you have to create it manually. Like below.

CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table AS (SELECT * FROM my_existing_table)
Ryan-Neal Mes
  • 5,593
  • 6
  • 42
  • 71
-2
stmt.executeUpdate("SET @sql = NULL");
...
stmt.executeUpdate("PREPARE stmt FROM @sql");
stmt.executeQuery("EXECUTE stmt");
stmt.executeUpdate("DEALLOCATE PREPARE stmt");
weirdan
  • 2,264
  • 21
  • 25
mengjian.ke
  • 1