'\r','\n' -commands used in MYSQL query is not working

[Viswanathan Ramamoorthy]

Hi all,

For my assigned task i have to retrive the values from the DB and that should be written in file.
for the using the following code :

Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test_server?", "root", "mysql");
stmt = connection.createStatement();
String Query7="SELECT concat(concat(concat("+ office_Id_List.get(i).toString() +" , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.CU1:'),"
+" concat(min(c.customer_id), ':'),concat(max(c.customer_id), ':'),count(c.customer_id))," +
" '\n' concat(concat("+ office_Id_List.get(i).toString() +" , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.CU2:'),"+
" concat(min(c.customer_id), ':'),concat(max(c.customer_id), ':'),count(c.customer_id))," +
" '\n' concat(concat("+ office_Id_List.get(i).toString() +" , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.CU5:')," +
" concat(min(c.customer_id), ':'),concat(max(c.customer_id), ':'),count(c.customer_id))," +
" '\n' concat(concat("+ office_Id_List.get(i).toString() +" , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.CU7:')," +
" concat(min(c.customer_id), ':'),concat(max(c.customer_id), ':'),count(c.customer_id))," +
" '\n' concat(concat("+ office_Id_List.get(i).toString() +" , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.AC1:')," +
" concat(min(s.account_id), ':'),concat(max(s.account_id), ':'),count(s.account_id))," +
" '\n' concat(concat("+ office_Id_List.get(i).toString() +" , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.AC4:')," +
" concat(min(s.account_id), ':'),concat(max(s.account_id), ':'),count(s.account_id)))" +
" INTO OUTFILE 'd://"+office_Id_List.get(i).toString()+"" + CurDate+"01.CTL' "+
" FROM center_group_customer_master c ," +
" saving_accounts s" +
" WHERE c.status = 'V' AND c.customer_id = s.customer_id" ;


I got the Error:
java.sql.SQLException: 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 '(concat(827600 , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(c' at line 1


I printed my Query in console
Its output is :

SELECT concat(concat(concat(827600 , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.CU1:'), concat(min(c.customer_id), ':'),concat(max(c.customer_id), ':'),count(c.customer_id)), '
' concat(concat(827600 , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.CU2:'), concat(min(c.customer_id), ':'),concat(max(c.customer_id), ':'),count(c.customer_id)), '
' concat(concat(827600 , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.CU5:'), concat(min(c.customer_id), ':'),concat(max(c.customer_id), ':'),count(c.customer_id)), '
' concat(concat(827600 , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.CU7:'), concat(min(c.customer_id), ':'),concat(max(c.customer_id), ':'),count(c.customer_id)), '
' concat(concat(827600 , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.AC1:'), concat(min(s.account_id), ':'),concat(max(s.account_id), ':'),count(s.account_id)), '
' concat(concat(827600 , LPAD(DAY(curdate()),2,0),LPAD(MONTH(curdate()),2,0),LPAD(YEAR(curdate()),4,0), '01.AC4:'), concat(min(s.account_id), ':'),concat(max(s.account_id), ':'),count(s.account_id))) INTO OUTFILE 'd://827600111201101.CTL' FROM center_group_customer_master c , saving_accounts s WHERE c.status = 'V' AND c.customer_id = s.customer_id;

Here while at the Execution the '\n' - command is converted to ' '. So this the problem
If i Removed that '\n' its working fine but am not getting the desire output. all the datas were written in single line.

Looking forward for your help

Thanks in Advance

Viswanathan.R

[Viswanathan Ramamoorthy]

hi guys,

I found the feasibility by adding one more \ inside the '\r'.
Escape Sequence character. That Bug is Fixed.

Thanks
Visu

[David O'Meara]

Congrats, and thanks for taking the time to update the thread.