7 Replies Latest reply on Jun 1, 2007 9:35 AM by Dan Bracuk

    Database Independent

    tapping Level 1
      I have created a Coldfusion application. I need for the users to use different database connections besides just SQL Server(our database at the office where I work). Can you configure Coldfusion to use any other database besides just SQL Server. We need to tell Coldfusion which database the user is using, and program ahead.

      Thank you in advance.
        • 1. Re: Database Independent
          Kronin555 Level 1
          It's not as simple as changing the datasource. SQL, while a standard, is implemented and supported differently by different database servers.

          Your best bet is to use an ORM framework that supports the different database servers you envision your users wanting to use.
          • 2. Re: Database Independent
            JR "Bob" Dobbs-qSBHQ2 Level 3
            You could design your application around an ODBC connection. You will take a slight performance hit when using OBDC vs a database specific driver.
            • 3. Database Independent
              paross1 Level 2
              quote:

              You could design your application around an ODBC connection.
              Not so much really. The "connection" isn't really the issue but, as Kronin555 pointed out, there are different "flavors" of SQL, so you would have to make your code as vanilla as possible, which would mean that you would not be able to take any advantage of your particular DBMS and its built in functions. Things like string and date functions differ widely between databases, and you would have a very hard time making your CF code portable.

              The only practical way that you might be able to make your CF code more portable would be to strictly use stored procedures instead of imbedded SQL so that all of your SQL would be contained in your particular database, and you would create "standard" procedure calls in CF with cfstoredproc, cfprocparam, and cfprocresult tags. In other words, your proc calls would just need to pass parameters to and from your stored procedures, and possibly return result sets, and these proc calls could be standard in your CF code, with the stored procedures behind the scenes designed for the applicable DBMS. T-SQL in SQL Server and PL/SQL in Oracle are very robust and even MySQL has stored procedure capability (depending on the version).

              Phil
              • 4. Re: Database Independent
                Dan Bracuk Level 5
                quote:

                Originally posted by: tapping
                I have created a Coldfusion application. I need for the users to use different database connections besides just SQL Server(our database at the office where I work). Can you configure Coldfusion to use any other database besides just SQL Server. We need to tell Coldfusion which database the user is using, and program ahead.

                Thank you in advance.

                Are you talking about a general situation where you want to be able to connect to more than one database server, or are you talking about a specific situation where you have similar databases using different db software, such as oracle, sybase, etc?
                • 5. Re: Database Independent
                  tapping Level 1
                  We will have the following situation:

                  Standard configuration is Coldfusion accessing SQL Server via DSN setup in Coldfusion.
                  We need to support users who may want to use MySQL, SyBase AnyWhere, or Oracle.

                  Can we tell ColdFusion via a configuration item which database type to use? Or do we need to configure this when we install coldfusion at our customer’s location?



                  • 6. Re: Database Independent
                    Level 7
                    tapping wrote:
                    Can we tell ColdFusion via a configuration item which database type to
                    use? Or do we need to configure this when we install coldfusion at our
                    customer?s location?

                    Have you investigated the Administration API that allows one to
                    programmaticly control some of the cfide/Administrator settings. I have
                    not done this, but it is my understanding that it should allow the
                    programmatic setting of DSNs.
                    • 7. Re: Database Independent
                      Dan Bracuk Level 5
                      quote:

                      Originally posted by: tapping
                      We will have the following situation:

                      Standard configuration is Coldfusion accessing SQL Server via DSN setup in Coldfusion.
                      We need to support users who may want to use MySQL, SyBase AnyWhere, or Oracle.

                      Can we tell ColdFusion via a configuration item which database type to use? Or do we need to configure this when we install coldfusion at our customer’s location?


                      As long as you can connect to a db and have the appropriate permissions, you can have as many datasources on your server as you want, and have as many different types of databases as you want. We have 45 data sources on our server and at least 5 database types.