Help with parsing results from MySQL query in BASH script

# 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.
kevandjuAsked:
Who is Participating?
 
kevandjuAuthor Commented:
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
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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
 
kevandjuAuthor Commented:
I will try that here in the next hour and report back. Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kevandjuAuthor Commented:
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
 
AnacreoCommented:
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
 
kevandjuAuthor Commented:
Figured out how to do it on my own, none of the expert comments helped me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.