Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jun 2007
    Location
    Maryland, USA
    Posts
    165
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Connect to MSSQL via CF?

    Hello,

    I've migrated a web site (that I didn't build) to a new provider. It runs on a MSSQL server. I've migrated the MSSQL server as well.

    When I try to access a page that connects to the MSSQL server, I run into this type of error: Data source "my-server-name" could not be found.

    The only connection string that I can find in the code is in header.cfm (included in all files):
    Code:
    <cfset request.dsn = "my-website-sql">
    I don't understand how the script can connect to the database without specifying the username and password. For that reason, I suspect that request.dsn is being defined elsewhere, but I can't figure it out.

    I have the DB Server's IP, and username and password, as well as the DB name. Can I create a working connection string in the script using these?

    Any help regarding the process of this connection would be greatly appreciated.

    Note- I only have access to the hosting provider's control panel (Network Solutions), and no direct access to server applications, command line, etc.

    Thank you!

  • #2
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    Ok, database connections are done a little differently in ColdFusion than in other server side languages. You don't create connection strings on a page-by-page basis and manually connect to the database that way. What you do instead is create a "Data Source" in the ColdFusion Administrator, which encapsulates all of the connection details for a given database under a single, unique name. It's done this way so that if your database ever changes (location, username/password, or even a switch to a different database engine entirely), you only have to update this information in one spot, and the rest of your application keeps working. That's why you're only seeing that one variable, request.dsn ("data source name") that is used to access the database in your <cfquery> or <cfstoredproc> tags (with the datasource attribute).

    That being said, your hosting provider must provide some way to create data sources via their control panel app, as they wouldn't give everyone direct access to the ColdFusion Administrator application itself. Try checking the help files of that control panel app. Otherwise, you might have to call them and ask how to do it.

    Hope that helps.

    -Greg
    Last edited by Gjslick; 05-13-2010 at 07:41 AM.

  • #3
    Regular Coder
    Join Date
    Jun 2007
    Location
    Maryland, USA
    Posts
    165
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Thanks for the info, I'm used to connecting to database via php and didn't realize the procedure.

    Maybe you can help me a bit more. My host is Network Solutions. I've created and restored a database, and during the creation process it did ask me to set the DSN.

    I tried this code:
    Code:
    <cfquery name="qryGetEvents" datasource="my-dsn">
    And encountered "Data source "my-dsn" could not be found."

    I also tried:

    <cfquery name="qryGetEvents" datasource="my-dsn" username="uid" password="pw">

    and received the same error.

    I have the Server's IP, the DSN, and my username and pw.

    Perhaps there some way or me to modify the address of the datasource and establish a connection? I've tried datasource="server-ip/my-dsn" but that also does not work.

    Another thought.. I'm working out of a virtual directory. Would that affect the connection? I considering moving the files to the root and deleting the virtual alias.


    Any info would help.

    Thanks you!

  • #4
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    Ok, again, the Data Source has to be set up with ColdFusion itself. Think of it as something that has to be "registered" with ColdFusion, like something that would have to be set up in the php.ini file (except in ColdFusion, everything is set up in the nice web interface that the ColdFusion Administrator provides, and not some ridiculously long text file ).

    One thing to realize is that the "Data Source" name is not the same thing as your database's name (although you can be set it up that way if you like). A "Data Source" is an abstract concept that ColdFusion implements to encapsulate all of the connection details under one name. Think of it as an object employed by the ColdFusion engine:
    Code:
    class MyDatasource {
      String databaseLocation = "localhost";
      String databaseName = "myDatabase";
      String username = "myUsername";
      String password = "myPassword";
      Driver driver = (SQL Server Driver);
    }
    That's why only one "datasource" name is ever needed when querying your database.
    Code:
    <cfquery name="getMyData" datasource="MyDatasource">
       SELECT * FROM myTable
    </cfquery>
    ColdFusion Data Sources are a different paradigm for accessing databases than employed in other languages, but one with many benefits. Think about if you had to switch your database in php from mysql to sql server. You would enjoy changing hundreds of mysql_xxx function calls to mssql_xxx function calls, and dealing with annoying inconsistencies such as if you used the mysql_affected_rows() function in a bunch of places, when the mssql counterpart is mssql_rows_affected(). Talk about a real pain in the you-know-what. ColdFusion provides a consistent interface for querying, regardless of the backend database engine being used. Data Sources are the layer of abstraction which makes that possible.


    I believe these are the steps that you'll need to follow for your hosting provider to set up a Data Source, assuming that you are on a shared hosting plan: http://www.networksolutions.com/supp...-windows-only/

    For the <cfquery> tag, the username and password attributes are only for a local override of the username/password that was set up for the Data Source. You won't need these attributes unless you need to use a different database user with specific permissions for a given query. Also, a virtual directory won't have anything to do with datasources.

    Good luck buddy, and let me know how it goes!

    -Greg

  • #5
    New to the CF scene
    Join Date
    Jul 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Some hosts have TWO FTP logins, one for the website, and a separate login to access your databases. Your databases are stored safely away from users without proper access to them. The host (ColdFusion hosts) then will set up your choice of Datasource names, and point it to any one of the databases you've uploaded. You'll need to supply them the database file name, your site name (if it's a shared host), the brand of database (MySQL, Sql2003 etc) and your user/password. Once the datasource (ODBC driver) is set up, you can connect to it in your query by adding datasource="[Your DSN]" to your cfquery tag. If you're using application.cfm or application.cfc with your site, you can even set up the default datasource and not have to add it to each query. ColdFusion is REALLY cool that way.

    ColdFusion ROCKS!
    Bob C in Texas


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •