Bash scripting - using variables in MySQL queries 3

[aalnaif]

I am writing a bash script to update a MySQL table using a previously defined variable. For some weird reason, the value that is populated in the table does not match the value of the variable. Here is my code:

var_date="2007-03-15"
mysql -u username -ppassword dbname -e "update customer_description set visit_date = ${var_date};"

The second line basically updates the MySQL table so that the "visit_date" column is set to $var_date. In this case, the visit_date column should be set to "2007-03-15". However, when I ran this script, the visit_date column was set to "1986". I have no idea where "1986" came from, but for some reason "2007-03-15" was replaced with "1986". Does anyone know why this would happen?

P.S. The MySQL datatype of the visit_date column is a varchar(20), meaning a variable-length string with a maximum length of 20 characters.

 

[thedaver]

What's the default value of visit_date in the table?

Have you tried the script without the {} characters?

Have you tried using "export" before the "var_date=..."?



D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[TonyGroves]

Your query ends up as:[tt]
update customer_description set visit_date = 2007-03-15;[/tt]
which of course MySQL converts to:[tt]
update customer_description set visit_date = 1989;
[/tt]
You would need to use quotes:[tt]
update customer_description set visit_date = '$date';
[/tt]
You might also think about converting the column to a date type, for efficiency.

 

[TonyGroves]

That should of course have been $var_date, not $date.

 

[stevexff]

To second what Tony said:

Holding it as a varchar is inefficient on two counts[ol][li]Each field held on the database has to be stored as a length plus the actual value. Date fields are just held as a fixed size integer[/li][li]When you use a date type, you get all the built in date manipulation functions of SQL for free, so you don't have to deal with it in your code. This is particularly important if you are coding in shell script, which doesn't have much in the way of built-in help for dates and times.[/li][/ol]

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[aalnaif]

Thanks for all your advice... it works now after adding the quotes as Tony suggested. I also changed the datatype to date. Tony, I can't believe I missed where 1989 came from.