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

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) { }
   }