How to connect Android Studio with SQL Server data

问题:

We are currently having the same problem as here: How to connect Android Studio with SQL Server database, but it hasn't been answered.

We also succeeded the connection on Eclipse, using the sqljdb4.jar file, we tried on android the same code as in Eclipse, but it works only on Eclipse.

We also tried another driver, jtds1.3.1.jar, but it didn't work either

We replaced the login and password by x and y ;)

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_flux_sequence);

    String url = "jdbc:jtdc:sqlserver://vps342074.ovh.net/IZY309";
    //String url = "jdbc:jtdc:sqlserver://vps342074.ovh.net/MSSQLSERVER;databaseName=IZY309";
    //String url = "jdbc:sqlserver://vps342074.ovh.net\\MSSQLSERVER;databaseName=IZY309";
    String user = "x";
    String pass = "y";

    try {
        //TextView textViewToChange = (TextView) findViewById(R.id.textViewCol5);
        //textViewToChange.setText("Hello");
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection(url, user, pass);

        Statement statement = con.createStatement();
        ResultSet resultat = statement.executeQuery("SELECT CATEGORIE FROM dbo.PPROFIL WHERE CATEGORIE = 'ac' ");
        while (resultat.next()) {

            String result = resultat.getString(1);
            TextView textViewToChange = (TextView) findViewById(R.id.textViewCol5);
            textViewToChange.setText(result);
            Log.d("My Custom Tag", result);


        }resultat.close();
        statement.close();
    } catch (SQLException e) {
    // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

private static void close(Connection con) {
    // TODO Auto-generated method stub
    close(con);

}

Here are the returned errors :

W/System.err: java.sql.SQLException: No suitable driver
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at java.sql.DriverManager.getConnection(DriverManager.java:186)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at java.sql.DriverManager.getConnection(DriverManager.java:213)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at 
com.example.gaetan.fluxsequence.flux_sequence.onCreate(flux_sequence.java:32)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at android.app.Activity.performCreate(Activity.java:6876)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1135)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3207)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3350)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at android.app.ActivityThread.access$1100(ActivityThread.java:222)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1795)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at android.os.Handler.dispatchMessage(Handler.java:102)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at android.os.Looper.loop(Looper.java:158)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at android.app.ActivityThread.main(ActivityThread.java:7229)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at java.lang.reflect.Method.invoke(Native Method)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1230)
02-15 14:34:01.896 4180-4180/com.example.gaetan.fluxsequence W/System.err:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1120)

回答1:

Before you make connection, you must add 2 lines of code:

StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
StrictMode.setThreadPolicy(policy);

Complete code as: (may be)

package com.ipvsoft.sendsms.utility;

import android.os.StrictMode;
import android.util.Log;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


public class SQLConnection {
    private static final String LOG = "DEBUG";
    private static String ip = "192.168.3.85";
    private static String port = "1433";
    private static String classs = "net.sourceforge.jtds.jdbc.Driver";
    private static String db = "THTData";
    private static String un = "sa";
    private static String password = "admin";
    public static Connection connect() {
        Connection conn = null;
        String ConnURL = null;
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);
        try {
            Class.forName(classs);
            ConnURL = "jdbc:jtds:sqlserver://" + ip +":"+port+";"
                    + "databaseName=" + db + ";user=" + un + ";password="
                    + password + ";";
            conn = DriverManager.getConnection(ConnURL);
        } catch (SQLException e) {
            Log.d(LOG, e.getMessage());
        } catch (ClassNotFoundException e) {
            Log.d(LOG, e.getMessage());
        }
        return conn;
    }
}

回答2:

The error that you are getting is because you are missing the line Class.forName("net.sourceforge.jtds.jdbc.Driver");

Add it before establishing the database connection, so it will look something like this in your code:

String url = "jdbc:jtdc:sqlserver://vps342074.ovh.net/IZY309";
try {
    //TextView textViewToChange = (TextView) findViewById(R.id.textViewCol5);
    //textViewToChange.setText("Hello");
    Class.forName("net.sourceforge.jtds.jdbc.Driver");

    Connection con = DriverManager.getConnection(url, user, pass);

    Statement statement = con.createStatement();
    ResultSet resultat = statement.executeQuery("SELECT CATEGORIE FROM dbo.PPROFIL WHERE CATEGORIE = 'ac' ");
    while (resultat.next()) {

        String result = resultat.getString(1);
        TextView textViewToChange = (TextView) findViewById(R.id.textViewCol5);
        textViewToChange.setText(result);
        Log.d("My Custom Tag", result);
    }
    resultat.close();
    statement.close();

} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}