Question Re: Directory Access Server Delete in Technology Q&A

Directory Access Server Delete

former_SQLA_member1694868 — Tue, 11 Sep 2012 13:21:41 GMT

I have two folders that the database writes files to using the directory access features. The destination folder is a landing point for documents requested by a webpage. Once they are viewed by the webpage they are no longer useful and can be cleaned up at some point.

My initial plan was to have an event that ran each night to delete files from this folder that had a create date older than 30 minutes prior.

This query displays the records I would want to delete correctly:

SELECT *

FROM ClientDataLand

WHERE create_date_time

<= DATEADD(MINUTE,-30,NOW())

The problem is that generates Error code -728 -- Update operation attempted on non-updatable remote query.

So how could one delete from a directory access server based on conditions, such as the create time?

Re: Directory Access Server Delete

regdomaratzki — Tue, 11 Sep 2012 15:05:44 GMT

Directory Access Servers are pretty picky about the type of cursors allowed on them. You can get around the issue by using a cursor in a stored procedure that only calls fetch next :

create procedure DelFiles ( in @expire integer )
begin
declare @fn varchar(128);
declare @cur cursor for select file_name from DBA.MyDir 
                        where create_date_time < dateadd( minute, -1 * @expire, now() );
open @cur;
fetch next @cur into @fn;
while sqlcode = 0 loop
  delete from DBA.MyDir where file_name = @fn;
  fetch next @cur into @fn;
end loop;
close @cur;
end

In this example, I've allowed you to pass in a parameter specifying how many minutes old a file should be to be deleted.

Re: Directory Access Server Delete

former_SQLA_member1694868 — Tue, 11 Sep 2012 21:33:48 GMT

Works like a charm. Thanks Reg.

Re: Directory Access Server Delete

VolkerBarth — Wed, 12 Sep 2012 06:02:36 GMT

Just to add: Nick Elson has answered a similar question in the general NG (thread "proxy table usable columns for delete" from 2012-08-09) - explaining the cause:

Q: one issue / question i am having . . .is it possible to issue deletes against the files querying by create_date_time, the only column that appears to be usable in the delete where clause is the file_name column. this is sql anywhere 12.01.

A: I am able to reproduce this and I believe this is a 'by design' limitation. So the bare SQL Delete Statement will not work. It seems to come down to the implementation not being set driven. The server is effectively doing this in Full Passthrough mode and the OS function to do a delete does not take such search criteria. That only accepts the filename.

One could write procedural logic to select for that criteria and fetch the file name via a cursor and use that to issue the correct delete operation. (see: execute immediate for that last part http://dcx.sybase.com/index.html#1201/en/dbreference/execute-immediate-statement.html )

Obviously, the suggested solution is exactly what Reg has implemented:)

Re: Directory Access Server Delete

thomas_duemesnil — Wed, 12 Sep 2012 06:15:48 GMT

I don't understand why do I need to use execute immediate for the delete ? Is this necessary for the cursor ?

Re: Directory Access Server Delete

VolkerBarth — Wed, 12 Sep 2012 07:07:14 GMT

I second that question.)
Possibly even a positioned delete would do? - As to Nick's explanation, I would think it's just that the DELETE on a directory access table needs a single filename as parameter, not any other condition, so the cursor shouldn't be a problem here... but I don't know myself.

Re: Directory Access Server Delete

regdomaratzki — Wed, 12 Sep 2012 09:44:00 GMT

I only had execute immediate here because I'd copied the SQL from another sample. The following SQL works as well, so I've edited my answer, and I don't see why a positioned delete wouldn't work either, although I haven't tested it myself.

delete from DBA.MyDir where file_name = @fn;