March 27, 2008 at 6:31 am
I have a process flow which works like this:
User fills out .xls template of data
Submits .xls to custom app
app reads .xls
app updates SQL DB via connection
Drawback: as template changes app needs to be rewritten and compiled. The main function of the app doesn't change which is to get data from .xls to SQL. How it does this changes.
So this got me to thinking that I would change the process so that a proc on the DB server would read the data from the .xls via OPENDATASOURCE. This would make modifying the process easier.
Now what about the GUI? I can still have the same old GUI and have it call the proc. The gui would never need to change.
Here's my question. If my DB lives in a secure place and doesn't have access to call to the outside world (or file system) is there another way to pass the .xls tp the proc? Do procs accept streams? Can I store the .xls as a blob and read from it that way?
Thanks, I look forward to the forums thought (and flames) as always.
ST
March 28, 2008 at 7:50 am
souLTower,
Couple of questions:
1) What kind of processing does 'app' do to the XLS data.
2) How does 'app' connect to the database? I am assuming that you reference the XLS data by four part naming convention.
3) Why can't you access the XLS file directly?
4) I am easily confused, so I need more details (I know, this is a statement).
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
March 28, 2008 at 9:36 am
Thanks for replying. Currently the app processes the xls data, creates insert statements, and performs the updates via SQL connection object in java. This is not optimal since if the processing operation changes I need to update the app and make sure that all users are using the updated version.
I wanted to go to a system where a simpler app simply copies the xls file to a location then calls a proc via the DB connection. The app would never have to change. The proc would handle the processing. This way if the processing rules change the client app doesn't have to know about it.
The problem is that my DB is securely hosted and the hosts don't want to allow the server to connecto to the outside to access the file. I'm interested in options. Can I write the file object to the DB server and store as a BLOB? If so how can I access the file? I believe there is a way to pass serialized objects to a proc, maybe that's a solution but I have no experience in that. It would likely not be allowed.
Thanks
ST
March 28, 2008 at 12:27 pm
ST,
XLS File on the server problem:
You cannot have the server access the outside world. Would it be possible to have some process "drop off" the XLS file onto a locked down/secured folder on the database server? (IE: C:\incoming)
What to do with the data problem:
From what you have described it sounds like you have you data processing logic already in a Stored Procedure. Assuming that is the case. You can load the data from the XLS file into a staging table within SQL Server. From there you can call the Stored Procedure, which will scrub, validate, or whatever else on the data itself, and move the processed data to it's final destination table. Using this method, you have access to the "raw" data, and the finished data, as well as the Stored Procedure, all contained within the database.
Just my thoughts typed out ...
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
March 29, 2008 at 6:58 am
Thanks, Damon. Actually I don't have the proc problem, I know just how I want to do that. The file problem: The server admin doesn't want to open access to a directory since the outside world would be accessing it. It sounds like I have to stay with the original plan. I'm going to use bean scripting stored in the DB to handle the processing on the client side of the app.
Thanks for thinking out loud.
ST
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply