Check the files exist before the result of sql query
I have a website with admin panel, which people can upload files and data. My website is on 2 servers and use nlb, and also two servers are synced together with dfs, also I have 2 SQL Server on both servers and they are synced too.
The problem is syncing files on both servers take sometimes,
Now just imagine I have a table with these fields:
Name, price, fileName
File address is point to somewhere of physical disk which is synced in 2 servers.
Now imagine the website run this query:
Select * from myTable
How can I tell SQL that just show that records which the
fileName field exists in physical disk?
Note: I want it to do it with SQL, not in my application.
One Solution collect form web for “Check the files exist before the result of sql query”
There are two options:
You already mentioned it. It is a good idea to add a column (FileExists bit (Y/N)) which tells you directly the status of physical copy.
Second option is tedious as you need to create custom logic which identifies the physical copy at runtime as per the first commented link given by @MichałKomorowski.
Ideally a database is used to store data and communicate with the application. Outer world interfaces within a database will be tedious work as well as decrease performance.
For example, if you implement a function to check physical file and in select query as above you want, you used that function, so extra headache to checking the status will definitely increase the time and performance decrease. Just think again.