CODEWITHSUNDEEP
sqlbashdatetimeinsert
Gui O
Gui O

Reputation: 383

SQL and NOW() into bash script

I have a file, "sharp.csv" in which data are like this :

<name>;<src_ip_address>;<mac_address>;<ip_address>;Vlan1;NOW()

Edit : These data are filled by a perl script I want to insert these data into a SQL database with the help of a BASH script

+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| code_site     | varchar(64) | NO   | PRI | NULL    |       |
| ip_source     | varchar(64) | NO   | PRI | NULL    |       |
| mac_relevee   | varchar(64) | NO   | PRI | NULL    |       |
| ip_relevee    | varchar(64) | YES  |     | NULL    |       |
| vlan_concerne | varchar(64) | YES  |     | NULL    |       |
| date_polling  | datetime    | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

So my commands are just :

arp_router_file="$DIR/working-dir/sharp.csv"
db_arp_router_table="routeur_arp"
$mysql -h $db_address -u $db_user -p$db_passwd $db_name -e "load data local infile '$arp_router_file' REPLACE INTO TABLE $db_arp_router_table fields terminated by ';';"

But my NOW() command won't work. Data are beeing inserted, but the "date_polling" row is filled with "0000-00-00 00:00:00"

Upvotes: 0

Views: 82

Answers (2)

Gui O
Gui O

Reputation: 383

To avoid sending NOW() instruction to MySQL, i'll send it a datetime with perl

use Time::Piece ;
my $t = localtime ;
my $date = $t->ymd;
my $time = $t->hms;

And instead of 

<name>;<src_ip_address>;<mac_address>;<ip_address>;Vlan1;NOW()

It'll be 

<name>;<src_ip_address>;<mac_address>;<ip_address>;Vlan1;$date $time\n

Perl send it as a String, MySQL understand is as a correct Datetime

Upvotes: 0

Jean-Karim Bockstael
Jean-Karim Bockstael

Reputation: 1405

NOW() is a function but the load data command will treat all the fields in the input file as literal data, thus trying to insert "NOW()" in a datetime field. The string "NOW()" can't be converted to a valid datetime value, so you end up with the default "0000-00-00 00:00:00" value.

You will have to build actual SQL INSERT queries from your input file, you can do this using awk:

cat input_file.csv | awk -F';' '{print "INSERT INTO routeur_arp (code_site, ip_source, mac_relevee, ip_relevee, vlan_concerne, date_polling) VALUES (\"" $1 "\", \"" $2 "\", \"" $3 "\", \"" $4 "\", \"" $5 "\", " $6 ");"}' > sql_statements.sql

Upvotes: 1

Related Questions