How do I generate a JDBC database URL?

Question

I have been researching this for about 3 hours today, and I feel like I'm close, but I have a few questions. The best source for information I have found so far is here: https://stackoverflow.com/a/2840358, but it doesn't answer all my questions.

A little background: I am using Microsoft SQL Server 2014 and I have verified that the IP Address, 127.0.0.1, is active and enabled with the port 1433. I have no problems connecting to the database through the SQL Server program on the same machine, but I am trying to connect through a Java program that I am writing, and it is currently giving this error: com.microsoft.sqlserver.jdbc.SQLServerException: The port number 1433/R2M_Database is not valid. I am confused by this because I know that the port 1433 is correct, and I know that the database I want to connect to is called R2M_Database. Now, here are my questions along with my source code.

1.) What is the difference between the "mysql" and "sqlserver" subprotocols, and how do I know which to use? (I'm pretty sure I'm supposed to use sqlserver, but just in case)

2.) How do I ensure that I am using the right hostname? (I'm pretty sure 127.0.0.1 / localhost is the one I'm supposed to use, but how will I be able to run this program from other machines to access the database?)

3.) Are there any external issues that could be causing this like firewalls or password requirements? (I am fairly certain that the user name and password are correct as they are the ones I use to successfully get a connection in Microsoft SQL Server 2014)

EDIT:

import java.sql.DriverManager;
import java.io.BufferedReader;
import java.io.FileReader;
import org.apache.ibatis.jdbc.ScriptRunner;

public class SQLTest {
    public static void main(String[] args){
        String script = "CreatePersons.sql";
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            new ScriptRunner(DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;databaseName=R2M_Database", userName, password))
                    .runScript(new BufferedReader(new FileReader(script)));
        } catch (Exception e) {
            System.err.println(e);
        }
    }
}

I made a slight syntax change (;databaseName= instead of /), and now I am getting this error:

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'RSquaredMacro'. ClientConnectionId:35281a40-0f87-42e4-bc46-b9a81a371529

Answer 1

It turns out that the issue was all in the formatting of my JDBC URL. The problem I had was that different subprotocols have different syntaxes for their URL-s, but the syntax for the sqlserver protocol (and how to find each part) is listed below:

"jdbc:sqlserver://" + serverName + ";database=" + databaseName

1.) serverName: To find your server's name, connect to Microsoft SQL Server (these instructions are based on SQL Server 2014), and look at the "Properties" pane on the right. Choose "Current connection parameters" in the combo box at the top, and look under "Connection Details" for the "Server name" field.

2.) databaseName: This one is fairly straightforward, as it is the name of the database that you want to access data from. Even though the user you log in to is listed under the "master" database, you do not have to log into "master," but rather, you can use any database you want, as long as the user you are logging into has permission to do the tasks you want to accomplish.

Finally: here is the link that helped me finally solve my problem, which goes into greater detail: http://www.programcreek.com/2010/05/java-code-for-connecting-ms-sql-server-by-using-sql-server-authentication/

Answer 2

1. mysql is a completely different database server. Don't use it for SQL Server.

2. you will need to provide a public address for your database server. either an ip or a hostname is fine, as long as all of your clients can see it. if you don't have one, you can try using dyndns.org or something like it. Note your clients will still need to be able to get to your server on the network (potential firewall issues). Hard to say more without knowing more about your situation.

3. A firewall could possibly be an issue but this is unlikely for localhost. I'm guessing the issue is with the TCP/IP listener, username, password, or database name settings.

Here is a link with more information about building a JDBC connection string: http://technet.microsoft.com/en-us/library/ms378428%28v=sql.110%29.aspx

Also some side notes:

1. You don't need to include the 1433 port number.

2. printing the entire stack trace will help you with debugging. (see below code)

3. you should declare a variable for your Connection and make sure it gets closed. Example:

   Connection c = null;
   try {
       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
       c = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433/R2M_Database", userName, password);
       new ScriptRunner(c).runScript(new BufferedReader(new FileReader(script)));
   } catch (Exception e) {
       e.printStackTrace();
   } finally {
       if (c != null) try { c.close(); } catch (Exception e) { }
   }