Supporting multiple db's in an app
-
So i'm currently in the design phase of a web based application. I would like to build the app to support multiple databases (MS SQL server, Oracle, etc). It's a classic three tier architecture (presentation, logic, data). However, I'm starting with MS SQL first. I'm thinking about having the business logic layer access the data layer via stored procedures. In other words all database logic is contained in stored procedures which is probably optimal for an MS SQL database backend (comments, suggestions here). My question, is what's the best method of making this available for multiple databases? I am by no means a database expert (just a lowly product manager who is learning how to code) but just wanted to get your thoughts on this since I'm sure this has been encountered before. Do other databases support stored procedures? Do I need to rewrite stored procedures for a specific db vendor? Any thoughts? Thanks
-
So i'm currently in the design phase of a web based application. I would like to build the app to support multiple databases (MS SQL server, Oracle, etc). It's a classic three tier architecture (presentation, logic, data). However, I'm starting with MS SQL first. I'm thinking about having the business logic layer access the data layer via stored procedures. In other words all database logic is contained in stored procedures which is probably optimal for an MS SQL database backend (comments, suggestions here). My question, is what's the best method of making this available for multiple databases? I am by no means a database expert (just a lowly product manager who is learning how to code) but just wanted to get your thoughts on this since I'm sure this has been encountered before. Do other databases support stored procedures? Do I need to rewrite stored procedures for a specific db vendor? Any thoughts? Thanks
ez2 wrote: My question, is what's the best method of making this available for multiple databases? I'm no DB expert but I think you want to look into ODBC -Jack To an optimist the glass is half full. To a pessimist the glass is half empty. To a programmer the glass is twice as big as it needs to be.
-
ez2 wrote: My question, is what's the best method of making this available for multiple databases? I'm no DB expert but I think you want to look into ODBC -Jack To an optimist the glass is half full. To a pessimist the glass is half empty. To a programmer the glass is twice as big as it needs to be.
Jack Handy wrote: I'm no DB expert but I think you want to look into ODBC Why? Use OLE-DB, hence ADO or ADO.NET. It's provides access to all sorts of datasources including ODBC datasources. And it's easier to use. Jason Gerard
-
So i'm currently in the design phase of a web based application. I would like to build the app to support multiple databases (MS SQL server, Oracle, etc). It's a classic three tier architecture (presentation, logic, data). However, I'm starting with MS SQL first. I'm thinking about having the business logic layer access the data layer via stored procedures. In other words all database logic is contained in stored procedures which is probably optimal for an MS SQL database backend (comments, suggestions here). My question, is what's the best method of making this available for multiple databases? I am by no means a database expert (just a lowly product manager who is learning how to code) but just wanted to get your thoughts on this since I'm sure this has been encountered before. Do other databases support stored procedures? Do I need to rewrite stored procedures for a specific db vendor? Any thoughts? Thanks
ez2 wrote: Do other databases support stored procedures? Do I need to rewrite stored procedures for a specific db vendor? Any thoughts? Yes. Oracle PL/SQL is about very different from MS SQL Server T-SQL except for the most basic of queries. SQL Server, IBM DB2, and others have far more ANSI-92 SQL support than does Oracle. Plus Oracle treats stored procs different that SQL Server. Oracle is a pain in the arse to code against. ez2 wrote: In other words all database logic is contained in stored procedures which is probably optimal for an MS SQL database backend (comments, suggestions here). Yes, stored procedures provide a good performance boost. Just try to keep the business logic in your objects and out of the stored procedures. Much easier to maintain and port to other database systems that way. Jason Gerard