Manipulating Dos Files from SQL

Is it possible to manipulate files using SQL commands, for example get a file name from a directory?
What I need to do is poll a directory and if any files are there I would like to get the file name and details, size, datestamp and so on and enter these details into a database. Then maybe do some thing wit the file, move itmaybe.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Look into xp_cmdshell, which you can use to execute an operating system commands.


Using xp_cmdshell in a stored procedure

You can use xp_cmdshell in stored procedures to perform a variety of tasks
in the server environment. In this example, the last chance threshold stored
procedure sp_lct_action first prints a warning message in the error log,
then alerts the system administrator via e-mail:

create procedure sp_lct_action
@db_name varchar (30),
@seg_name varchar (30),
@freespace int,
@status int
dump transaction @dbname to tmpdevice
print "Alert! Last Chance Threshold reached for Adaptive Server"
print "in Database %1!", @database
xp_cmdshell "mail sybasesa < $HOME/lct_mailtext"

Using xp_cmdshell in a trigger

You can use xp_cmdshell in triggers to gain more flexibility in responding
to changes in data. In this example, the trigger pricetrig fires when a
stock price is updated; it launches the stockaction application on a remote
server whenever the price exceeds a predefined threshold:

create trigger pricetrig
on stocks
for update
if update (price)
      if (select stocks.price + inserted.price - deleted.price
        from stocks, inserted, deleted
        where stocks.stockname = inserted.stockname
        and inserted.stockname = deleted.stockname) >100
        exec xp_cmdshell "rsh app_server /usr/bin/stock_action"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joe WoodhousePrincipal ConsultantCommented:
Leonstryker's answer is a good one because xp_cmdshell is available in all versions of ASE from 11.5 onwards, and is included in the base product.

There are some specific file-handling features in ASE 12.5.x - the XFS (for eXtended File System) premium option. Note however that this is *not* included in the base product and must be separately licensed. (Usually the premium features aren't super expensive though.) You could almost certainly get your current Sybase account manager to generate you a 30- or 60-day eval key to have a look at it.

It extends the basic "proxy table" functionality (where you build a local proxy or shadow table that is like a UNIX symbolic link or Windows shortcut to data somewhere else).

You can build a proxy table and point it at a directory. Now when you query this "table" you will see one row for each file in the directory. The columns of the "table" include all the file attributes - name, creator, date created, etc, including a column for the file's contents. Deleting a row deletes the file. Inserting a row will create a file.

I know a manufacturing company who does exactly what you're talking about using this feature - polling a directory and doing things with the files.

So, the xp_cmdshell method is free, more widely available and very generic - but you'll have to go to a bit of trouble coding your own scripts etc to deal with the files. The XFS option gives you that functionality up-front, but you have to pay for it and it's only for ASE 12.5.x.

Good luck!
I would like to add one more thing.  Due to the nature of xp_cmdshell, realize that anyone who has access to it in effect has admin right on the server machine.  Its an important thing to keep in mind.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.