opinion re: xp_fileexist

Hi all,

Sorry for spamming you with this, I thought it might be the best way to get your opinion on the topic.

OVERVIEW:

We should not use MS internal procedures, since MS has the right to change/remove them at will. Given that, should the following code be allowed, or not. If not, how does anyone suggest, handling checking for the existance of a file that has been given as a parameter to some T-SQL.

--<this code is just an example, hence not neat etc.>

SET NOCOUNT ON

DECLARE @.FilePath varchar(1024)

SET @.FilePath = 'D:\Sample Data\Files Processing URL\PromotionRulesActive.txt'

--substitute a local file for testing locally

CREATE TABLE #FileExistance ([File Exists] smallint, [File is a Directory] smallint, [Parent Directory Exists] smallint)

insert into #FileExistance exec xp_fileexist @.FilePath

select * from #FileExistance

drop table #FileExistance

GethWho,
Why don't you check that from your calling code?
TSQL is not intended to be used for such purposes, and when a functionality as simple as checking whether a file exists or not is missing, that simply means you are not supposed to need such funtionality. I am sure Microsoft has "hidden" that SP intentionally, because when you do such things from SQL it almost certainly means that something is going wrong.

|||thanks for the response!
the reason I've been considering this is as part of design-by-contract. Basically, I have an SP that will get given a file location, and will use the path to do an insert into SQL. Making use of the file to do a bulk insert, or a OPENROWSET open of the file, is a T-SQL activity.
My intention is to ensure that before trying to execute the code, I ensure that the inputs I receive meet my contract - i.e that they identify a valid file location. That's why I've been toying with the xp_fileexist.
I suspect that at the end of the day, initially I will have to leave off doing the file existance checking, and eventually (when I have time to learn some c# and then do the coding) I will create my own up_fileexist code in an assembly, and use that.
Thanks for the interest and response - I welcome more comments/feedback/ideas|||

GethWho, thank You for your resonce too.
Anyhow I insist that checking a file existance is not a T-SQL activity. Indeed, bulk inserting is.
All I am saying that the code that calls your SP should make sure the file it passes exists. Validation such as that should not be done on behalf of data manipulation layer of the application. Even if the source that commits the file location to your SP is untrustworthy, you might consider creating some routine that checks the file location before submitting it to SP.
You should distribute the roles of your application between logical layers. Mixing the roles will make a maintainance nighmare out of nothing. A proper design can never end in a model where SQL needs to validate file locations, text inputs or other things like that. This is business layer responsibility.
Especially-code like that will take you approximately 5 minutes to write(in VB.net or VB6.0), even if you aren't familiar with the language. I can help you on behalf of that, mail me 'andranik at armsoft . am' if you find any difficulties.

|||Hi Andranik,
Thanks for confirming my suspicions. I think I'll try and get the time to write the .Net/C# - from what I've heard from you and some other sources, it may be something simple enough for even me to write :-)
Just a question regards the comment of trustworthiness of code. My impression of design-by-contract is that it is a methodology/design practice that drives one to write robust, resilient code that doesn't necessarily trust the calling code - hence the pre- and post- contract checking to ensure that components are robust enough to all truly robust applications. Surely that applies to all levels, and not just the front and middle tier's?
I'd appreciate some comments/pointers to good articles on design-by-contract (I'll be googling myself, but you/someone may already have a good link), and especially how it applies/doesn't apply to the data access layer.
Thanks again!
|||

Hi GethWho,
I'd reccommend Lhodka's book, "Expert C# Business Objects".

Indeed, you must verify the data at some points. You should check whether the data received from a client browser or a windows form is valid. Meanwhile, doing the same validation on all tiers is indeed redundant-you will end up writing the same code for UI, application and maybe even data access layer!
The right design choice is to encapsulate all object-related validation into object itself. In this case, using "mobile objects", that is, objects that are passed by value over the network, you can reuse the validation code on UI and application layers. Use of mobile objects has many other benefits and several drawbacks.
Data manipulation code is usually embedded into object code too. You should indeed separate the methods that read from/write into database from other methods. In this case, when a data manipulation code is called, you can make sure that the object to be persisted is valid. Indeed, no SQL-side validation is needed-the code calling SQL server-side code guarantees that the object data is valid and consistent. Otherwise, you will end doing business-layer oriented validation is SQL SP, which is...not the right choice :)
I think we should make the further discussion private because this is getting out of the topic of this forum. Feel free to mail me,
'andranik at armsoft . am'