Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Viswanathan Ramamoorthy
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 10
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Congrats, and thanks for taking the time to update the thread.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic