?
Solved

Help with parsing results from MySQL query in BASH script

Posted on 2011-10-04
6
Medium Priority
?
3,079 Views
Last Modified: 2012-08-13
# Query the database for Name, Serial Number, Email Contact and Days Offline

HEDS=({$(mysql gn_config --user=myname --password=mypass-s -e "SELECT hed_location, hed_name, hed_email, DATEDIFF(NOW(),xcd_last_seen) AS last_update FROM config.cfg_hs, repository.xml_data WHERE hed_key=xcd_hed_key ORDER BY last_update DESC")})


# The total number of Headends is total number records divided by number of fields

let "tot=${#HEDS[@]}/4"
echo $tot

# Loop through each account record

for (( i=0 ; i<tot ; i++ )); do


# The field ids to work with

let "val1=$i*4"
let "val2=val1+1"
let "val3=val2+1"
let "val4=val3+1"


# Extract the values from the array


SERIAL=${HEDS[$val1]##*/}
NAME=${HEDS[$val2]}
EMAIL=${HEDS[$val3]}
DAYS=${HEDS[$val4]}

Open in new window


Above is part of my bash script, the problem I am having is that the hed_name field in the database has data with spaces in it such as "Town Hall" or "City Center" minus the quotes.  When I try to count the fields from the results and then pull the data out of it, each of these names with spaces get counted as 2 separate fields.  So if I had one result from my query and the field hed_name was "City Center" it would see 5 fields instead of 4.

Not sure if there is a better way to pull the data out of this query or some way for the hed_name field to only be counted as 1 field regardless of its contents.
0
Comment
Question by:kevandju
  • 4
6 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 36913246
Hi, maybe just a hint that will help you. I'm no mysql expert but in Oracle SQL I'd contatenate a few "" characters around your field, like this:
select hed_location, '"' || hed_name || '"' , hed_email ....

Open in new window

This would select field hed_name surrounded with double quotes. Don't know if this would work in mysql.
0
 

Author Comment

by:kevandju
ID: 36913897
I will try that here in the next hour and report back. Thanks
0
 

Author Comment

by:kevandju
ID: 36914050
Ok here is what I tried

1.) Tried
'"' || hed_name || '"'

Open in new window

and this returned "||" instead of the actual field

2.) Tried
'"' hed_name '"'

Open in new window

and this returned "hed_name" instead of the actual field although my field count formula works now

3.) Tried
' hed_name '

Open in new window

and this returned "hed_name" instead of the actual field although my field count formula works now

Any other ideas?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:Anacreo
ID: 36928398
A little known fact is if you end a MySQL command with \G (Bell) you get a single field per row...


188 ~/bin >mysql -u root -p -e "select 'user','name' from user limit 5\G" mysql
Enter password:
*************************** 1. row ***************************
user: user
name: name
*************************** 2. row ***************************
user: user
name: name
*************************** 3. row ***************************
user: user
name: name
*************************** 4. row ***************************
user: user
name: name
*************************** 5. row ***************************
user: user
name: name

So then parsing this is now trivial...
0
 

Accepted Solution

by:
kevandju earned 0 total points
ID: 36928557
I ended up getting it working with using "IFS" and doing a CONCAT query to insert commas after every value.  Here is my code.

IFS=$',\n'

# Query the database for Name, Serial Number, Email Contact and Days Offline

HEDS=($(mysql config --user=xxxxxxxxxxx --password=xxxxxxxxx -s -e "SELECT CONCAT(hed_location, ',', hed_name, ',', hed_email, ',', DATEDIFF(NOW(),xcd_last_seen), ',') AS result FROM config.cfg_headends, repository.xml_client_data WHERE hed_key=xcd_hed_key AND DATEDIFF(NOW(),xcd_last_seen) > 1 ORDER BY xcd_last_seen ASC"))


# The total number of Headends is total number records divided by number of fields

let "tot=${#HEDS[@]}/4"


# Loop through each account record

for (( i=0 ; i<tot ; i++ )); do


# The field ids to work with

let "val1=$i*4"
let "val2=val1+1"
let "val3=val2+1"
let "val4=val3+1"


# Extract the values from the user array

SERIAL=${HEDS[$val1]##*/}
NAME=${HEDS[$val2]}
EMAIL=${HEDS[$val3]}
DAYS=${HEDS[$val4]}

Open in new window

0
 

Author Closing Comment

by:kevandju
ID: 37659304
Figured out how to do it on my own, none of the expert comments helped me.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question