0

I'm working on an MS SQL database at work. We're in early development, and I'm playing around with PHP while a coworker of mine is using ASP.NET. In the end, we'll probably combine our efforts and stick with one or the other. For this reason, I thought it would be wise to do as much programming in stored procedures on the database as possible.

So I'm currently working on this bit where the user uploads a text file, the data gets rearranged, and inserted into a table on the server. Now I'm basically faced with three options:

  1. Do all of the rearranging in PHP and build the query string and execute it.
  2. Insert the data as-is into a different table and call a stored procedure to rearrange it and insert it into the bigger target table.
  3. Simply pass the data as parameters into a stored procedure that does all the manipulating and joining at one time.

From what I've read so far, I understand that option 1 is probably not so good. Really my question is, is option 3 too far in the other direction? I have a feeling it would be more difficult, but it would be the most portable and I wouldn't have that extra table with the yet-to-be-manipulated data sitting on the server. Or is option 2 the way to go since it will likely make the most sense to anyone who comes after me trying to analyze what my code is doing?

Andrew
  • 4,145
  • 2
  • 37
  • 42

2 Answers2

1

I dont like to have my Stored procedure do some kind of logic to deal with reading files and cleaning up.I would seperate the process into different logical layers to handle this. I would like to do the file operation in my business logic level and supply clean data to stored proc (my data access layer) so that it can save the data to relevant tables. This way you can use the same data access methods if you want to save data from a different part of your app / a different app trying to save the already cleaned data. I think that is more scalable solution.

So i suggest you to do the file cleaning operation in PHP file and then pass the data to stored proc. Make sure you are not passing the data to be saved in query strings and save it directly reading from there. Because then you will be a victim of SQL Injection.

Shyju
  • 214,206
  • 104
  • 411
  • 497
0

I'd suggest adding some middle layer between two web parts (probably SOAP service so both PHP and ASP.NET can easily work with it) that would do all the processing and data manipulation.

Fedor Hajdu
  • 4,657
  • 3
  • 32
  • 50