Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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.
  • 2
1 Solution
Look into xp_cmdshell, which you can use to execute an operating system commands.

from: http://www.isug.com/Sybase_FAQ/ASE/Section10/5/Q10.5.6.html 

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"
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.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now