MySQL Query result question

pranjal_815 · 12-14-2012, 02:41 AM

Hi Guys,

I was working on shell script in which I have multiple Insert is performing on single table getting data from different table. But sometime it works correctly and sometime it does not.

I am in search is there any way through which I can story my query result into a file. Please do not take me wrong by the words query result, as here Query Result means the result that you get when you run your query i.e, 12 rows effected.

I have tried bla bla method but it did not work for me.

For your help i m providing you the sample :

Query is as follows :

INSERT INTO table_name1(fk_req_id,cli,points,source,gift_redeemed,gift_code,points_befor_redmptn,points_after_re dmptn,NAME,address,city,state,pin,age,dateofbirth,brand,COMMENT,intensity,aniversary,education,servi ce,business,completestatus,update_date)select fk_req_id,cli,ttl_points,'PANEL',gift_redeemed,gift_code,points_befor_redmptn,points_after_redmptn,n ame,address,city,state,pin,age,dateofbirth,curr_barand,comment,intensity,aniversary,education,servic e,business,completestatus,insert_date from table_name2 where date(insert_date) = date_sub(current_date,interval 4 day)" >>$myfile

Here $myfile is the log path where I want to write the logs or redirect my output.

Any help and suggestion would be helpful.

regards,
Pranjal

Habitual · 12-15-2012, 09:03 AM

Quote:

I was working on shell script in which I have multiple Insert is performing on single table getting data from different table. But sometime it works correctly and sometime it does not.

Redirecting "12 rows effected" using a piped redirect is NOT the way to do debugging of shell scripts.

Quote:

I am in search is there any way through which I can story my query result into a file

What query is that? You are doing inserts.
It's already in a file. (see "working on shell script")

Quote:

I have tried bla bla method but it did not work for me.

Never heard of it. Did you author this method too?

Redirecting a shell script for an "Insert into" statement(s), near as I can remember,
the only output IS "nn rows effected"

Wim Sturkenboom · 12-15-2012, 11:47 PM

I'm curious as well what method 'bla bla' as there is a risk that the below link does method 'bla bla'.

As you can pipe the output of the bash mysql command to grep (as shown in http://www.cyberciti.biz/faq/using-m...shell-scripts/), I'm quite sure you can redirect it to a file.

No mysql at hand to test, though.

PS
It can be useful to post the relevant part of your code.

pranjal_815 · 12-16-2012, 11:51 PM

@Habitual

Quote:

I am in search is there any way through which I can story my query result into a file

Quote:

What query is that? You are doing inserts.
It's already in a file. (see "working on shell script")

I am using Insert Query. I want the o/p of the Insert to be saved in a file so that i can keep track of the information.

Quote:

I have tried bla bla method but it did not work for me.

Here bla bla means different methods that I found on net but none of them work for me.

If you have command to do so then please share the procedure through which I can do it.

Regards,
Pranjal

Wim Sturkenboom · 12-18-2012, 10:04 AM

An insert query on the command line does not return any message about the number of inserted rows. The only thing you can do is to check if the query was executed succesfully.

Code:

wim@VirtualBox:~$ mysql -u root -p menagerie -e "insert into pet values('Buffy','Harold','dog','f','1989-05-13',null);"
Enter password: 
wim@VirtualBox:~$ echo $?
0
wim@VirtualBox:~$ mysql -u root -p menagerie -e "insert into pet values('Buffy','Harold','dog','f','1989-05-13',null);"
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
wim@VirtualBox:~$ echo $?
1
wim@VirtualBox:~$

What you're after is probably the following.

Code:

wim@VirtualBox:~$ mysql -u root -p menagerie -e "insert into pet values('Buffy','Harold','dog','f','1989-05-13',null); select row_count()"
Enter password: 
+-------------+
| row_count() |
+-------------+
|           1 |
+-------------+

Depending on your needs, you can make it a bit easier for further parsing

Code:

wim@VirtualBox:~$ mysql -u root -p menagerie -e "insert into pet values('Buffy','Harold','dog','f','1989-05-13',null); select row_count()\G"
Enter password: 
*************************** 1. row ***************************
row_count(): 1
wim@VirtualBox:~$

You can get rid of the '**....**' using grep -v and you can get rid of the row_count() using cut (both used in the example below)

Code:

wim@VirtualBox:~$ mysql -u root -p menagerie -e "insert into pet values('Buffy','Harold','dog','f','1989-05-13','1989-05-13'); select row_count()\G" | cut -d: -f 2 |grep -v "\*"
Enter password: 
 1
wim@VirtualBox:~$

And lastly you can redirect the output to a file

Code:

wim@VirtualBox:~$ mysql -u root -p menagerie -e "insert into pet values('Buffy','Harold','dog','f','1989-05-13','abc'); select row_count()\G" | cut -d: -f 2 |grep -v "\*" > mysql.test.txt
Enter password: 
wim@VirtualBox:~$ cat mysql.test.txt 
 1
wim@VirtualBox:~$

Hope this helps

PS
Please pay attention to the notes on http://dev.mysql.com/doc/refman/5.0/...tion_row-count

PPS
Obviously you need to change the user and the database