I'm using jtds 1.3.0 in AsyncTask for Microsoft SQL 2008 connection. And my doInBackground() method returns a ResultSet object. Then I read rows from the ResultSet in onPostExecute() method. Normally my query should return 4701 rows. But I get NetworkOnMainThreadException around 58th-60th rows then connection stops(I'm able to read the first ~60 rows). My original query is:
SELECT ID,TITLE,CODE FROM COMPANIES
For experimenting I've tried:
SELECT TOP 50 ID,TITLE,CODE FROM COMPANIES --No problem.
SELECT TOP 100 ID,TITLE,CODE FROM COMPANIES --Throws exception.
The logcat output:
android.os.NetworkOnMainThreadException
at android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode.java:1166)
at libcore.io.BlockGuardOs.recvfrom(BlockGuardOs.java:163)
at libcore.io.IoBridge.recvfrom(IoBridge.java:506)
at java.net.PlainSocketImpl.read(PlainSocketImpl.java:488)
at java.net.PlainSocketImpl.access$000(PlainSocketImpl.java:46)
at java.net.PlainSocketImpl$PlainSocketInputStream.read(PlainSocketImpl.java:240)
at libcore.io.Streams.readFully(Streams.java:81)
at java.io.DataInputStream.readFully(DataInputStream.java:99)
at java.io.DataInputStream.readFully(DataInputStream.java:95)
at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java:881)
at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket.java:762)
at net.sourceforge.jtds.jdbc.ResponseStream.getPacket(ResponseStream.java:477)
at net.sourceforge.jtds.jdbc.ResponseStream.read(ResponseStream.java:114)
at net.sourceforge.jtds.jdbc.ResponseStream.readInt(ResponseStream.java:329)
at net.sourceforge.jtds.jdbc.TdsData.readData(TdsData.java:728)
at net.sourceforge.jtds.jdbc.TdsCore.tdsRowToken(TdsCore.java:3080)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2347)
at net.sourceforge.jtds.jdbc.TdsCore.getNextRow(TdsCore.java:772)
at net.sourceforge.jtds.jdbc.JtdsResultSet.next(JtdsResultSet.java:611)
at com.example.sql.FragmentMain$1.onPostExecute(FragmentMain.java:69)
at com.example.sql.FragmentMain$1.onPostExecute(FragmentMain.java:52)
at android.os.AsyncTask.finish(AsyncTask.java:632)
at android.os.AsyncTask.access$600(AsyncTask.java:177)
at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:645)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:146)
at android.app.ActivityThread.main(ActivityThread.java:5602)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1283)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1099)
at dalvik.system.NativeStart.main(Native Method)
Error-generating code piece:
new DatabaseTask(
((ActivityMain) getActivity()).getConnectionURL()
, getString(R.string.query_all_companies))
{
@Override
protected void onPreExecute()
{
super.onPreExecute();
if (D) Log.d(TAG, "Querying the companies..");
}
@Override
protected void onPostExecute(ResultSet resultSet)
{
super.onPostExecute(resultSet);
try
{
if (resultSet != null)
{
int i = 0;
while (resultSet.next()) /*while loop causes error according to logcat.*/
{
if (D) Log.i(TAG,i++);
}
}
} catch (SQLException e)
{
e.printStackTrace();
}
}
}.execute();
Finally, my sdk settings in gradle files:
minSdkVersion 14
targetSdkVersion 22
and dependencies:
dependencies {
compile fileTree(include: ['*.jar'], dir: 'libs')
compile files('libs/jtds-1.3.0.jar')}
I didnt include my AsyncTask class for the simplicity. Basically I open a connection and call Connection.execute() in doInBackground(). If anyone wants to reproduce the error, I can edit my post.
Thank you
Here is my AsyncTask class.
public class DatabaseTask extends AsyncTask<Object,String,ResultSet>
{
private final boolean D = true;
private final String TAG = "DatabaseTask";
private String url;
private String sqlStatement;
public DatabaseTask(String url, String sqlStatement)
{
this.url = url;
this.sqlStatement = sqlStatement;
}
private void addParams(PreparedStatement preparedStatement,Object... params) throws SQLException
{
if (params != null && params.length > 0)
{
for (int i = 0; i < params.length; i++)
{
Object param = params[i];
if (param instanceof String)
{
preparedStatement.setString(i+1, param.toString());
if (D) Log.d(TAG, "String parameter \'" + param + "\' added.");
} else if (param instanceof Integer)
{
preparedStatement.setInt(i+1, (Integer) param);
if (D) Log.d(TAG, "Integer parameter \'" + param + "\' added.");
} else if (param instanceof Double)
{
preparedStatement.setDouble(i+1, (Double) param);
if (D) Log.d(TAG, "Double parameter \'" + param + "\' added.");
} else if (param instanceof BigDecimal)
{
preparedStatement.setBigDecimal(i+1, (BigDecimal) param);
if (D) Log.d(TAG, "BigDecimal parameter \'" + param + "\' added.");
}
else
if (D) Log.e(TAG,"SQL parameter type is not supported.");
}
}
}
@Override
protected ResultSet doInBackground(Object... params)
{
try
{
Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
Connection connection;
connection = DriverManager.getConnection(url,
ActivityMain.DB_USER_NAME,
ActivityMain.DB_PASSWORD);
if (connection != null)
{
if (D) Log.d(TAG, "Connection successful.");
connection.setAutoCommit(true);
PreparedStatement preparedStatement = connection.prepareStatement(sqlStatement);
addParams(preparedStatement, params);
preparedStatement.execute();
return preparedStatement.getResultSet();
}
else
{
if (D) Log.d(TAG, "Connection failed.");
}
} catch (ClassNotFoundException e)
{
e.printStackTrace();
} catch (InstantiationException e)
{
e.printStackTrace();
} catch (IllegalAccessException e)
{
e.printStackTrace();
} catch (SQLException e)
{
if (D) Log.e(TAG,e.getMessage());
e.printStackTrace();
}
return null;
}
}
I've finally found the solution. From this link I saw that if the fetch size of ResultSet is exceeded, it tries to connect to internet and fetch the next portion of data. So, either using ResultSet.setFetchSize(cacheSize) or moving the ResultSet.next() method call into the AsyncTask solves the problem.