aspose file tools*
The moose likes JDBC and the fly likes '\r','\n' -commands used in MYSQL  query is not working Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark " Watch " New topic
Author

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

Viswanathan Ramamoorthy
Greenhorn

Joined: Nov 01, 2010
Posts: 10
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

Joined: Nov 01, 2010
Posts: 10
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

Joined: Mar 06, 2001
Posts: 13459

Congrats, and thanks for taking the time to update the thread.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: '\r','\n' -commands used in MYSQL query is not working