I'm trying to connect to a database on a sql server using jTDS-1.2.5. I know that this is dangerous and insecure, and I shouldn't be doing it this way, but I just want to try it, so humor me. To test jTDS I wrote this code in netbeans:
package dbconnecttest;
import java.sql.*;
import net.sourceforge.jtds.jdbc.*;
import net.sourceforge.jtds.jdbcx.*;
public class DBConnectTest {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
Connection con = null;
try {
Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
String connString = "jdbc:jtds:sqlserver://MY_SERVER_NAME:24923/Phone_Test;user=testLogin;password=xxxxxxxx;instance=MYINSTANCE";
con = DriverManager.getConnection(connString,"testLogin","xxxxxxxx");
try {
PreparedStatement stmt = con.prepareStatement("SELECT * FROM Product_Inventory_Test WHERE ProductTest = 'Car'");
stmt.execute();
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
System.out.println(rs.getString("ProductTest").replace(" ", "") + " price: $" + rs.getString("PriceTest"));
}
stmt.close();
} catch (Exception e) {
System.out.println("Statement error: " + e.getMessage());
}
con.close();
}
catch (Exception e) {
System.out.println("Connection Error: " + e.getMessage());
}
}
}
Notice, I am connecting on a port other than 1433. I have enabled TCP/IP in the configuration manager, and set the TCP port for all the IP address to 24923. I also made sure it wasn't dynamically assigning TCP ports. This code works fine in netbeans, and it pulls the data from the database without any problems. I'm using similar code in eclipse on my android app:
package com.xxxxxx.xxxxxx;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import android.os.Bundle;
import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.view.Menu;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import net.sourceforge.jtds.jdbc.*;
import net.sourceforge.jtds.jdbcx.*;
public class LoginActivity extends Activity {
TextView labelLogin;
TextView labelError;
EditText txtUsername;
EditText txtPassword;
Button btnLogin;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_login);
//Assign properties to login form views
labelLogin = (TextView)findViewById(R.id.labelLogin);
labelError = (TextView)findViewById(R.id.labelError);
txtUsername = (EditText)findViewById(R.id.txtUsername);
txtPassword = (EditText)findViewById(R.id.txtPassword);
btnLogin = (Button)findViewById(R.id.btnLogin);
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.login, menu);
return true;
}
@SuppressWarnings("deprecation")
public void login_onClick(View view) {
Connection con = null;
try{
Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
String connString = "jdbc:jtds:sqlserver://MY_SERVER_NAME:24923/Phone_Test;user=testLogin;password=xxxxxxxx;instance=MYINSTANCE";
String username = "testLogin";
String password = "xxxxxxxx";
con = DriverManager.getConnection(connString,username,password);
PreparedStatement stmt = null;
try {
//Prepared statement
stmt = con.prepareStatement("SELECT * FROM Logins WHERE Username = ? AND Password = ?");
stmt.setString(1, txtUsername.toString());
stmt.setString(2, txtPassword.toString());
stmt.execute();
ResultSet rs = stmt.getResultSet();
if(rs.next()) {
//Start new activity
AlertDialog ad = new AlertDialog.Builder(this).create();
ad.setTitle("Success!");
ad.setMessage("You have logged in successfully!");
ad.setButton("OK", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
dialog.dismiss();
}
});
ad.show();
}
stmt.close();
}
catch (Exception e) {
stmt.close();
AlertDialog ad = new AlertDialog.Builder(this).create();
ad.setTitle("Error");
ad.setMessage("Prepared statement error: " + e.getMessage());
ad.setButton("OK", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
dialog.dismiss();
}
});
ad.show();
}
con.close();
}
catch (Exception e) {
AlertDialog ad = new AlertDialog.Builder(this).create();
ad.setTitle("Error");
ad.setMessage("Connection error: " + e.getMessage());
ad.setButton("OK", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
dialog.dismiss();
}
});
ad.show();
}
}
}
I have added the jar file to the classpath, and everything seems to work fine. When I run the app on an emulator, though, it gives me the error:
Connection Error: Unable to get information from SQL Server: MY_SERVER_NAME.
I'm kinda stuck on what to do next. Everything seems to be working fine, it just won't connect. I've tried going to command prompt and trying sqlcmd -L, and it has my server in the list. I can also telnet into my server from there. netstat -an shows that it's listening on port 24923 with local ip address 0.0.0.0. I'm just not sure what the problem is, and I've checked the jTDS FAQ tips about resolving this error already. Any ideas/suggestions?
