Android Socket Timeout Exception during Rest data connection, only during mobile network connection

Question

After updating Delphi version 10.1 to 10.4.2, I encountered this problem, which only occurs on some Android 10 and 11 devices.

The APP exchanges data with the server via TDSRestConnection. On the same phone, where previously the app developed with Delphi 10.1 worked correctly, stops working after the update to Delphi version 10.4.2 and after the Android upgrade on the phone.

The APP works only over the WiFi network and no longer works over the mobile network, generating this error after a while (7/8 seconds):

java.net.SocketTimeoutException: failed to connect to /xxx.xxx.xx.xx (port 17200) from /100.118.154.102 (port 42050) after 300000ms: isConnected failed: ETIMEDOUT (Connection timed out).

I also tried using different internet mobile providers (TIM, VODAFONE) The 4G connection is Very Good and the Internet speed test doesn't show any problem.

The error doesn't appear immediately. The function GetSql is called more times in the sincro procedure, but unfortunately it stops working not at the same point.

The code:

Client side:

const fConnectTimeout=300000;
const fReadTimeout=300000;


function TServerMethods1Client.GetSql(StringaSql: string; const ARequestFilter: string): TFDJSONDataSets;
begin
  if FGetSqlCommand = nil then
  begin
    FGetSqlCommand := FConnection.CreateCommand;
    FGetSqlCommand.RequestType := 'GET';
    FGetSqlCommand.Text := 'TServerMethods1.GetSql';
    FGetSqlCommand.Prepare(TServerMethods1_GetSql);
  end;
  FGetSqlCommand.Parameters[0].Value.SetWideString(StringaSql);
  FGetSqlCommand.Execute(ARequestFilter);
  if not FGetSqlCommand.Parameters[1].Value.IsNull then
  begin
    FUnMarshal := TDSRestCommand(FGetSqlCommand.Parameters[1].ConnectionHandler).GetJSONUnMarshaler;
    try
      Result := TFDJSONDataSets(FUnMarshal.UnMarshal(FGetSqlCommand.Parameters[1].Value.GetJSONValue(True)));
      if FInstanceOwner then
        FGetSqlCommand.FreeOnExecute(Result);
    finally
      FreeAndNil(FUnMarshal)
    end
  end
  else
    Result := nil;
end;

function SelectSql(StringaSQL:String;tb:TFDMemTable;out Errore:String):Boolean; overload;
var
  jds: TFDJSONDataSets;
  data: TFDAdaptedDataSet;
begin
  try

     result:=false;

     if DMClientRestModule=nil then DMClientRestModule:=TDMClientRestModule.Create(nil);

     DMClientRestModule.DSRestConnection1.Port := STRTOINT(REST_PORT);
     DMClientRestModule.DSRestConnection1.Host := REST_HOST;
     DMClientRestModule.DSRestConnection1.SessionID := '';
     DMClientRestModule.DSRestConnection1.HTTP.ConnectTimeout := fConnectTimeout;
     DMClientRestModule.DSRestConnection1.HTTP.ReadTimeout := fReadTimeout;

     jds :=TFDJSONDataSets.Create;

     jds := DMClientRestModule.ServerMethods1Client.GetSql(StringaSql);
     if (Assigned(jds)) then begin
       data := TFDJSONDataSetsReader.GetListValue(jds, 0);
       tb.AppendData(data);
       tb.Open;
       result:=true;
     end;


     try
       DMClientRestModule.Free;
       DMClientRestModule:=NIL;
     except
     end;

  except
     result:=false;
     Errore:=GetStrException(ExceptObject,ExceptAddr);
  end;

END;

Server side:

function TServerMethods1.GetSql(StringaSql: String): TFDJSONDataSets;
var
 dset:TFDJSONDataSets;
begin

try

 DbConn.Params.Clear; //TFDConnection
 DbConn.Params.Database:=fdb;
 DbConn.Params.UserName:=fuid;
 DbConn.Params.Password:=fpwd;
 DbConn.Params.Add('server='+fdbserver);
 DbConn.Params.Add('port='+fport);
 DbConn.Params.DriverID:='MySQL';

try
 q1.Close; // TFDQuery

 if not DbConn.Connected then
  DbConn.Connected:=true;

 if not DbConn.Connected then
     log('Not conneted to db')
      else
       log('Connected to db')  ;
  with q1 do
   begin
    close;
    SQL.Clear;
    SQL.Add(StringaSql);
    Log(StringaSQl);
    open;
   end;

  Result := TFDJSONDataSets.Create;
  // The "TFDJSONDataSetsWriter" class provides static "ListAdd" method.
  // It uses reflection to convert results of the query into "TFDJSONDataSets".
  TFDJSONDataSetsWriter.ListAdd(Result,'',q1);
except
  Log(GetStrException(ExceptObject,ExceptAddr));
  Result := TFDJSONDataSets.Create;
  TFDJSONDataSetsWriter.ListAdd(Result,'',q1);
end;
finally
  DbConn.Connected:=false;
end;

end;

update 16/04/2021

Investigating better, it seems that the Sql String that causes the exception is the following (Long text sql string)

          StringaSql :='SELECT PLANNING_ENGINEERS.DATE_JOB,NOT_A_JOB, ';
          StringaSql := StringaSql + ' PLANNING.PLAN_ID,PLANNING.CONTACTID,PLANNING.CALLER_REFER, ';
          StringaSql := StringaSql + ' PLANNING.COMPANYID,PLANNING.DEPOTID,CALLTYPEID,TIME_BEGIN,';
          StringaSql := StringaSql + ' TIME_END,LOCATION_DESCRIPTION,ADDRESS1,ADDRESS2,ZIP,PLACE,PROVINCE,';
          StringaSql := StringaSql + ' PHONE1,PHONE2,FAX,EMAIL,LATITUDE,LONGITUDE, ';
          StringaSql := StringaSql + ' PLANNING.DELETED,PLANNING.CLOSED,PLANNING.SUSPENDED,PLANNING.NOTES,';
          StringaSql := StringaSql + ' PLANNING.INTERNAL_NOTES,PLANNING.SUGGESTION_NOTES,PLANNING.CLOSING_NOTES,ORDER_REFERENCE,EXTERNAL_REFERENCE, ';
          StringaSql := StringaSql + ' EXTERNAL_REFERENCE_DESC ';
          StringaSql := StringaSql + ' ,(SELECT CALLTYPES.DESCRIPTION FROM CALLTYPES WHERE CALLTYPES.CALLTYPE_ID=PLANNING.CALLTYPEID)';
          StringaSql := StringaSql + ' AS CALL_DESC ';
          StringaSql := StringaSql + ' ,(SELECT CALLTYPES.TYPE FROM CALLTYPES WHERE CALLTYPES.CALLTYPE_ID=PLANNING.CALLTYPEID)';
          StringaSql := StringaSql + ' AS CALL_TYPE ';
          StringaSql := StringaSql + ' ,(SELECT DEPOTS.NOTES FROM DEPOTS WHERE DEPOTS.DEPOT_ID=PLANNING.DEPOTID)';
          StringaSql := StringaSql + ' AS DEPOT_NOTES ';
          StringaSql := StringaSql + ' FROM PLANNING,PLANNING_ENGINEERS WHERE  ';
          StringaSql := StringaSql + ' PLANNING.DELETED=''N'' AND PLANNING.CLOSED=''N'' AND PLANNING.SUSPENDED=''N'' ';
          StringaSql := StringaSql + ' AND PLANNING_ENGINEERS.PLANID=PLAN_ID ';
          StringaSql := StringaSql + ' AND PLANNING_ENGINEERS.ENGINEERID='+'42';
          StringaSql := StringaSql + ' AND PLANNING_ENGINEERS.DATE_JOB BETWEEN '+QuotedStr(FormatDateTime('YYYYMMDD', date))+' AND '+QuotedStr(FormatDateTime('YYYYMMDD',date))+' ';
          StringaSql := StringaSql + ' ORDER BY DATE_JOB,TIME_BEGIN';

During the WIFI connection, the call to the server works fine then it send back the result.

Instead during the 4G connection, the call raise the exception below:

Exception ENetHTTPClientException in module ... java.net.SocketException : Connection reset.

I also reported a ticket RSP-33699 to Embarcadero

Here the video: RestIssue

score 1 · Answer 1 · answered Apr 23 '21 at 12:11

1

I suppose you use cleartext HTTP (ie. http://172.16.30.24:8088). Consider given solution :)

Upgrading from Delphi 10.1 to 10.4.1 changed the target SDK in your project. One of the changes you might expect is that Android does not allow you to use direct claertext HTTP requests starting from Android 9.

In your AndroidManifest.xml file you need to remember of internet permission and put one additional line as shown below:

<?xml version="1.0" encoding="utf-8"?>
<manifest ...>
    <uses-permission android:name="android.permission.INTERNET" />
    <application
        ...
        android:usesCleartextTraffic="true"
        ...>
        ...
    </application>
</manifest>

More information can be found here: StackOverflow and Android docs

answered Apr 23 '21 at 12:11

S. Dabrowski

51
1
6

Yes I'm using cleartext HTTP and I also changed the android manifest file. – Gianluca Colombo Apr 23 '21 at 17:50
Then I think it might be a bug as you mentioned at the end. I've had similar problem after updating IDE from 10 to 10.3 but in my case it was cleartext issue. However, you also wrote: " it seems that the Sql String that causes the exception is the following (Long text sql string)". If it takes much time to process it might cause this error indeed. Did you try with simpler sql string that takes less time? Would it still raise this exception? – S. Dabrowski Apr 26 '21 at 09:28
The response time is 0,1 seconds. The problem seems to be related to sending long text request to the server. – Gianluca Colombo Apr 26 '21 at 12:22
1

Maybe instead of sending such a long string, try sending only the parameters that your SQL needs and store the rest of your SQL on server. Then, when you handle the request on server side, create `StringaSql` with these parameters. Also don't know if you tried with shorter string already :) – S. Dabrowski Apr 26 '21 at 15:44

Android Socket Timeout Exception during Rest data connection, only during mobile network connection

1 Answers1