Register
It is currently Sun Dec 21, 2014 6:58 pm

SQL insert statement in bash


All times are UTC - 6 hours


Post new topic Reply to topic  [ 5 posts ] 
Author Message
 PostPosted: Wed Dec 24, 2008 7:44 am   

Joined: Wed Dec 24, 2008 7:32 am
Posts: 4
Hello,

I'm using a script that puts the information about the user (date, hour, user, host and ip) that can be found in the samba log (after setting the log uid = yes in the smb.conf) to a file. No I want to write this information to a mysql database.

On my linux server i created a database log with a table samba and the fields date, hour, user, host. And in the terminal i can execute the following command without any error

mysql -ulogger -p12345 -e"use log: insert into table samba (date, hour,user,host) values ('2008/12/24','134541','laechr','idefix');"

but when I use this command in my script with this syntax mysql -ulogger -p12345 -e"use log; insert into table samba (date, hour,user,host) values ('${D}','${H}','${U}','${H}');" i get an error

Error
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax, bla bla ... for the rigth syntax near TABLE

What do i wrong ?

Best regards,

Christof Laeremans


Top
 Profile  
 PostPosted: Wed Dec 24, 2008 3:27 pm   

Joined: Mon Nov 17, 2008 7:25 am
Posts: 221
First of all you have a kolon instead of a semi colon after "use log".

but why do it that way?
Code:
mysql -ulogger -p12345 log <<EOF
insert into samba (date, hour,user,host) values ('2008/12/24','134541','laechr','idefix');
EOF

This is abit more manageable and if you'd want to pass more then 1 sql then just add it on the next row.
(you specify database to use after -p12345, works just like doing "use log")

Best regards
Fredrik Eriksson


Top
 Profile  
 PostPosted: Thu Dec 25, 2008 2:57 am   

Joined: Wed Dec 24, 2008 7:32 am
Posts: 4
Hello,

Merry Christmas ! And thank you for your answer

There are still some problems

Problem 1
When I use my code
Code:
mysql -ulogger -p12345 -e"use log insert into table samba (date, hour,user,host) values ('${D}','${T}','${U}','${H}');"


Update : when i use this command (with values like '2008/12/25' etc ...) in the terminal (not in mysql >) then the result is the same, there is no data added !

It works without any error but when I look in my database, there isn't any data added (in the log there are 40 lines with user information)

Problem 2
When I use your code
Code:
mysql -ulogger -p12345 log <<EOF
insert into samba (date, hour,user,host) values ('2008/12/24','134541','laechr','idefix');
EOF

Then I get an error "Unexpected EOF"

Update :

When I use the code in problem 1 and problem 2 in a new file then everything works fine ! But when I use it in the code of this file, nothing happens. No data is written to the database

Code:
#!/bin/bash

LOG_FILE=/var/log/samba/log
TEMP_FILE=/tmp/`basename ${0}`.tmp
MOD_FILE=/tmp/`basename ${0}`.mod

#VARIABELEN VOOR MYSQL SERVER
DBU=root
PWD=12345
DB=log
TBL=samba


## Get date (Format : DD/MM/YY
date=$(date +%d%b%y)

# Create filename of the logfile
LOG="${date}.log"

## Get each log entries on to a single line
while read line; do
        if [ $(echo ${line} | grep -c "^\[" ) -gt 0 ]; then
                printf "\n${line}\c" >> ${MOD_FILE}
        else
                printf " ${line}" >> ${MOD_FILE}
        fi
done < ${LOG_FILE}
echo " " >> ${MOD_FILE}

## Save the login log entries to the $TEMP_FILE
grep "connect to service" ${MOD_FILE} > ${TEMP_FILE}

## Save the values into variables
while read line; do
        Date=$(echo ${line} | awk '{print $1}'|sed 's/\[//g')
        Time=$(echo ${line} | awk '{print $2}'|sed 's/[,:]//g')
        Host=$(echo ${line} | awk '{print $9}')
        IP=$(echo ${line} | awk '{print $10}'| sed 's/[)(]//g')
        SHARE=$(echo ${line} | awk '{print $14}')
        User=$(echo ${line} | awk '{print $18}')
       
   # Print the standard output line
    echo "${Date}:${Time}:${User}:${Host}:${IP}"
   
   mysql -u$DBU -p$PWD -e" use log insert into $TBL(id,datum,uur,user,host) values (NULL,'${Date}','${Time}','${User}','${Host}');"

done < ${TEMP_FILE}

rm ${MOD_FILE}
rm ${TEMP_FILE}




What do i wrong or is it impossible to do this (or am I stupid ?! :x )

Best regards,

Christof Laeremans


Top
 Profile  
 PostPosted: Sun Dec 28, 2008 3:46 pm   

Joined: Wed Dec 24, 2008 7:32 am
Posts: 4
Nobody who can help me ?

Best regards,

Christof Laeremans


Top
 Profile  
 PostPosted: Thu Jan 01, 2009 10:30 am   

Joined: Wed Dec 24, 2008 7:32 am
Posts: 4
Solved !

<some code>
mysql command <<EOF
Query
EOF
<some code>

must it be and not

<some code>
mysql command <<EOF
Query
EOF
<some code>


Top
 Profile  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 6 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron


BashScripts | Promote Your Page Too
Powered by phpBB © 2011 phpBB Group
© 2003 - 2011 USA LINUX USERS GROUP