I have been asked to modify an existing ASP application so that it can work with an Oracle database. Currently, it works with Access and SQL Server. It is a commercial product, so it must now work with any of the three DBMSs: Access, SQL Server and Oracle.
The code is littered with SQL statements like:
strSQL = "SELECT name FROM emp WHERE empno = " & intEmpno
strSQL = "UPDATE emp SET name = '" & strName & "' WHERE empno = " & intEmpno
Now, this is BAD practise for Oracle, as it makes no use of bind variables, so the SQL cannot be reused. I know the solution to this: use prepared statements like this:
strSQL = "SELECT name FROM emp WHERE empno = ?"
strSQL = "UPDATE emp SET name = ? WHERE empno = ?"
... then pass the necessary values as parameters.
I have experimented with this on Oracle, and it works fine. Now here are my concerns:
1) Does the above prepared syntax work with Access (Jet) and SQL Server? I think the answer is "yes", but am not in a position to test this at the moment.
2) Assuming it works with Access and SQL Server, will it also benefit these DBMSs, or make no difference, or could it be detrimental to performance in some way?
I know in SQL Server there is a built in stored proc called sp_executesql that does pretty much the same thing that you have described. As for access I really don't know it that much so I'm not sure if it has anything that can help you.
The only other suggestions I can give is to either write everything in ANSI so that all dbs understand it or writing an include file that holds the code for communicating with the data and right seperate componets for each DB. As far as my experience with SQL and Oracle I have never truely written one code for both servers and have it so that it will be optimized properly for either one. Making it generic enough did lose some performance. Sucking it up and writing code for both and using the proper code did take a lot longer but was worth it at the end when it came to performance.
If you can use ADODB.Command objects in your ASP application then these can be used for prepared statement support with the prepared property. I've heard that some SQL Server drivers are set to automatically convert prepared statements to temporary stored procedures which can sometimes cause performance problems. If you can use the Command object I'd imagine that most databases would be supported, and especially SQL Server and Access.