Automated file upload using asp

[ucfcarver]

I am not even sure if this is possible but I figured I would ask.

I am trying to upload a file with out anyone having to select it and push a button. However the file is being sent to a remote sql server that only has asp installed.

I figured I could do this using a script that is activated when the wireless network is detected. (The linux computer will move in and out of the network through out the day.)

However since I am new to linux, sql servers, and coding I need some help figuring out how to do this. If you could help withe the code or at least point me in the right direction it would be greatly appreciated.

[WingedPanther]

Can you connect directly to the server and send SQL commands? If so, this shouldn't be too hard. If you have to connect to a web-server, that will make it a little trickier.

[ucfcarver]

I have inserted a a row of data into the database using the terminal and the tsql command. I just manually entered the values. However while the linux box is out of network range it will be adding data to a csv file that is what needs to be imported.

I could not figure out how to go from the terminal commands by hand to an automated system.

[WingedPanther]

You'll probably need a daemon running to monitor when the network comes in range.

[ucfcarver]

I have ubunut installed and it came with network manager and i am using that to trigger the upload. I just have no clue how to write a script or whatever i need to automate the process of inserting the data from my file to the sql server.

[debtboy]

This thread is confusing...
Here are my assumptions, are they true?

1.) You have Mobile Linux box (Ubuntu) moving in and out of wireless network range

2.) You have MSSQL Server installed on another machine (Windows Server 200x OS, I assume)

3.) Mobile Linux box collects data in a csv file

4.) You have successfully created something that automatically uploads the csv file to the (Windows Server 200X) when in range

5.) You need a script or program running on (Windows Server 200X) to automatically parse the local csv file and insert that data into the locally installed MSSQL Server.

IS THIS CORRECT?
IN NOT PLEASE DETAIL YOUR SITUATION.

[ucfcarver]

OK you were close

1) linux box(ubunut) moved in and out of network

2) sql server on other machine ( not entirely sure what version it is all i have been told is it is sql express)

3) model linux box collects data in csv file

4)The only connection i have successfully made is in the command terminal using tsql command from the freetds package. It was not automated i had to enter values by hand.
$ tsql -H servername -p port -U user -P password

5)I need to somehow automate the process so the data is read out of the csv file and inserted into the remote sql database

P.S. the ms sql server has asp and obviously the sql, so they are the only commands that it can understand.

Also i am not in charge of the server so if anything would need to be done to the server i have to go through some other people in the office

[debtboy]

4)The only connection i have successfully made is in the command terminal using tsql command from the freetds package. It was not automated i had to enter values by hand.
$ tsql -H servername -p port -U user -P password

Were you on the sql box or the linux box when you made this connection??

NOTE:
I have a few things to do, I'll be back on the forum later tonight.

one more thing... I assume ASP not ASP.NET... correct?

[ucfcarver]

Ok i had to check and it looks like asp and asp.net are on the server so either would work.

as far as the connection i was able to make i was on the linux box. I used the following commands in the terminal

carver@carver-laptop:~$ tsql -H <server> -p <port> -U <user>
Password:
1> INSERT INTO Route_1
2> VALUES(1,4,8,-2,"Fri Aug 28 14:18:31 2009")
3> go

[debtboy]

Ah Ha!!
So you can directly write to the database from the linux box.

This is what WingedPanther was asking, and he's absolutely right,
"this shouldn't be to hard"

You need only make a simple shell script to write to the database
when the linuxbox is in network range. You can use your trigger
top kick off the shell script. be sure to chmod 777 the script
to make it executable.

The comma delimited file is probably exactly what you want
and if not, it's easy enough to manipulate it.

For example...

#!/bin/env bash
while read -r single_line
do
    #comment $single_line already contains your values string
    tsql -H servername -p port -U user -P password
    INSERT INTO Route_1
    VALUES($single_line)
    go 
done < your_file.csv

This should be pretty close to what you need.
Let's say you only need the first, third and forth values from the line...

#!/bin/env bash
while read -r single_line
do
    your_values=`echo $single_line | cut -d "," -f1,3,4`
    tsql -H servername -p port -U user -P password
    INSERT INTO Route_1
    VALUES($your_values)
    go 
done < your_file.csv

If the order is messed up change your sql around

INSERT INTO Route_1
(field1, field4, field3)
VALUES($your_values)

You can manipulate it any way you want.
You can rename or delete the csv after the DB insert.
You can log each insert to keep track, you can do
most anything you want.

If your having trouble with your trigger,
just make it a cron job and add an if [ ] fi statement
detecting network or mapped file.

Play around with it, it will work and it saves you the
unix ODBC bridge install etc... and troubleshooting

NOTE:
ASP or ASP.NET on the sql server doesn't help at all
unless there was also a web server and you could upload
your own ASP files to it. Even then you would need a batch
file to kick off a browser w/ your custom asp files as the default.
You would still have to then kill off the browser after completion.

The shell script is a much better solution.

Hope this helps.