help on a script that checks mysql db

kernel-P4N1C · 03-04-2010, 07:51 AM

same error chrism01

What does this do ?
N=$((N+1)) increase the line number... and actually doesn't do anything... dunno why i left it there.

ok this script function as follows.
the codes are on a text file... it reads the file one by one and crop the reading from 16:27 VALUE=${LINE:16:27}
then it is supposed to check that on a table name accesscodes that have the following structure

Code:

+-------------+-------------+------+-----+---------------------+-------+
| Field       | Type        | Null | Key | Default             | Extra |
+-------------+-------------+------+-----+---------------------+-------+
| id          | bigint(20)  | NO   | PRI | 0                   |       |
| accesscode  | varchar(50) | NO   |     |                     |       |
| clientid    | bigint(20)  | YES  |     | NULL                |       |
| createtime  | datetime    | NO   |     | 0000-00-00 00:00:00 |       |
+-------------+-------------+------+-----+---------------------+-------+

what i'm doing is basically checking if the codes has been imported on the db... if not then it is supposed to do something but i haven't get that far since i'm stuck with this issue.

kernel-P4N1C · 03-04-2010, 01:59 PM

ok so i guess everybody gave up

smoker · 03-04-2010, 02:22 PM

I can't see where you told the while loop what file to work on.
I also don't understand the while condition.

So Yes, I've given up.

$value could be coming from anywhere.

kernel-P4N1C · 03-04-2010, 03:48 PM

the loop looks for a file here

done<$folder$file

I've modified the loop to look like:

Code:

while IFS= read -r LINE ; do
        x=${LINE:16:27} # cropping the record and leaving access code
dbexist=`mysql -h$mysqlhost -u$mysqluser -p$mysqlpass -e "SELECT id FROM dbname.accesscodes WHERE accesscode='"$x"'"`
done<$folder$file

this basically read the $file line by line, crop the data on the record to pick the actual access code and run the query.
N is no longer needed as i'm not counting the number of lines here..

note:
i've replce $x by $y on the query and asigned a value to y above... works fine; somehow is not picking up the value of x... i've print out x (as seen on older version) and x value comes out fine [i've repalace $VALUE for $x]

smoker · 03-04-2010, 04:06 PM

I think you only need one set of quotes around $x (or $y whatever it is now)
Which kind is open to debate, try both types by themselves.

In that loop you are setting IFS to the default of splitting on spaces and whitespace.

Is that what you need ?

chrism01 · 03-04-2010, 08:17 PM

You can see from my example that you don't need all those various quote marks.
As a test from the cmd line, just run the bare line like I did to prove that you can communicate with the DB ok.
Then, just add var=$(stuff_from-test) eg

Code:

accesscode=<number>
dbexist=$(mysql -h$mysqlhost -u$mysqluser -p$mysqlpass -e "SELECT id FROM dbname.accesscodes WHERE accesscode=$x")
echo $dbexist

Just try the lines above; use absolute vars for the test for host/user/passwd.
Let's show that the db connect/select works first before trying the rest of your code.

The way to test the whole script is to add

set -xv

just under the
#!/bin/bash

line.

If you have issues, don't just say 'same error'
Post entire cmd & err msgs; please try the short/manual version first.

catkin · 03-04-2010, 10:27 PM

Quote:

Originally Posted by smoker

In that loop you are setting IFS to the default of splitting on spaces and whitespace.

That is not correct. IFS= puts an empty string in IFS so it has no characters for use when parsing a string into words. Unsetting IFS makes Bash treat it as if it were set to the default value.

Here's a demonstration

Code:

IFS= read var rest << EOF
word1 word2
EOF
echo "var is '$var', rest is '$rest'"

unset var rest

unset IFS
read var rest << EOF
word1 word2
EOF
echo "var is '$var', rest is '$rest'"

The output is

Code:

var is 'word1 word2', rest is ''
var is 'word1', rest is 'word2'

kernel-P4N1C · 03-05-2010, 07:06 AM

Not working...
output

Code:

ERROR 1054 (42S22) at line 1: Unknown column '27060022900' in 'where clause'
dbexist

anyways, if i do it like this

Code:

dbexist=$(mysql -h$mysqlhost -u$mysqluser -p$mysqlpass -e "SELECT id FROM shop.accesscodes WHERE accesscode='$x'")

it talks to database fine
output:

Code:

dbexist id 6842

as i said; the single quote have something to do here.

Quote:

Originally Posted by chrism01

You can see from my example that you don't need all those various quote marks.
As a test from the cmd line, just run the bare line like I did to prove that you can communicate with the DB ok.
Then, just add var=$(stuff_from-test) eg

Code:

accesscode=<number>
dbexist=$(mysql -h$mysqlhost -u$mysqluser -p$mysqlpass -e "SELECT id FROM dbname.accesscodes WHERE accesscode=$x")
echo $dbexist

Just try the lines above; use absolute vars for the test for host/user/passwd.
Let's show that the db connect/select works first before trying the rest of your code.

The way to test the whole script is to add

set -xv

just under the
#!/bin/bash

line.

If you have issues, don't just say 'same error'
Post entire cmd & err msgs; please try the short/manual version first.

smoker · 03-05-2010, 07:58 AM

Quote:

Originally Posted by catkin

That is not correct. IFS= puts an empty string in IFS so it has no characters for use when parsing a string into words. Unsetting IFS makes Bash treat it as if it were set to the default value.

What is the default value ?

I was trying to work out what was going on from this:
http://tldp.org/LDP/abs/html/x17304.html#MAILBOXGREP

And as we have never seen the text file the OP is trying to read from, I thought maybe he's picking up some extra fluff and passing it to the sql query.

If his coordinates are wrong for the text he's extracting, he could be capturing a newline char and putting that in the query.

Those coordinates can be affected by whitespace, length of expected terms, whether it splits on spaces or a character etc.

As seen by his last test, a direct assignment of $x doesn't throw a mysql error. So it must be his coded assignment of $x.

I was going to suggest Chris's test next. It's been done. Now the OP needs to sanitise his input.

I would have used chomp in perl, I don't know the equivalent bash function.

kernel-P4N1C · 03-05-2010, 10:27 AM

The typical file is something like:

Code:

 xxxxxxxxxxxxxxx123456789AB
 xxxxxxxxxxxxxxx123456789AB
1EOF 2

EOF 2 <--- basically it's a counter of the lines
the xxxx means numbers that i don't care; the data is basically the last 11 characters

smoker · 03-05-2010, 10:47 AM

Quote:

Originally Posted by kernel-P4N1C

The typical file is something like:

Code:

 xxxxxxxxxxxxxxx123456789AB
 xxxxxxxxxxxxxxx123456789AB
1EOF 2

EOF 2 <--- basically it's a counter of the lines
the xxxx means numbers that i don't care; the data is basically the last 11 characters

Why does your code extract up to the 27th character then ?

Code:

x=${LINE:16:27} # cropping the record and leaving access code

Are the coordinates zero based or start from 1 ?
Are they inclusive ?
Because char 28 is a newline.
char 1 (or 0 depending on the indexing) is a space.
So why start at 16 ?

catkin · 03-05-2010, 11:16 AM

Quote:

Originally Posted by smoker

What is the default value ?

I was trying to work out what was going on from this:
http://tldp.org/LDP/abs/html/x17304.html#MAILBOXGREP

And as we have never seen the text file the OP is trying to read from, I thought maybe he's picking up some extra fluff and passing it to the sql query.

If his coordinates are wrong for the text he's extracting, he could be capturing a newline char and putting that in the query.

[snip]

I would have used chomp in perl, I don't know the equivalent bash function.

The default value of $IFS is <space><tab><newline> (implied in GNU Bash Reference, Word Splitting).

The effect of IFS= in the ABS example you linked is to prevent any word splitting. From the "Word Splitting" link above: "If the value of IFS is null, no word splitting occurs".

Bash's read command will anyway terminate at newline so he cannot be capturing a newline char.

If I knew perl I might be able to tell you of bash's equivalent of chomp!

smoker · 03-05-2010, 11:23 AM

@catkin
Oh, ok. I was confused by the comment he'd put under that line, i.e. :

Quote:

while IFS= read -r mail
# ^^^^ Reset $IFS.
# Otherwise "read" will strip leading & trailing space from its input.

I was confused between reset and unset.

I didn't know that about bashs read command.

Quote:

# chomp( LIST )
# chomp

This safer version of "chop" removes any trailing string that corresponds to the current value of $/ (also known as $INPUT_RECORD_SEPARATOR in the English module). It returns the total number of characters removed from all its arguments. It's often used to remove the newline from the end of an input record when you're worried that the final record may be missing its newline. When in paragraph mode ($/ = "" ), it removes all trailing newlines from the string. When in slurp mode ($/ = undef ) or fixed-length record mode ($/ is a reference to an integer or the like, see perlvar) chomp() won't remove anything. If VARIABLE is omitted, it chomps $_ .

catkin · 03-05-2010, 11:43 AM

Quote:

Originally Posted by smoker

@catkin
Oh, ok. I was confused by the comment he'd put under that line, i.e. :

I was confused between reset and unset.

I didn't know that about bashs read command.

The comment says one thing, the code does another! IFS is neither being reset (not meaningful in bash-speak) nor unset; it is being set to the empty string a.k.a "null".

Thanks for the chomp info. Bash has nothing as sophisticated as that. read and IFS can go some way toward it; parameter expansion can trim off leading and trailing whitespace.

smoker · 03-05-2010, 12:29 PM

Quote:

Originally Posted by catkin

The comment says one thing, the code does another! IFS is neither being reset (not meaningful in bash-speak) nor unset; it is being set to the empty string a.k.a "null".

Ok, I thought its being on tldp would mean it was actually reliable. Silly really.
Thanks for the linked info anyway, I really ought to learn more bash, but as I mainly script for the web (if at all), perl suits me better.

cheers