Android Question Connect with SQL SERVER - Alway connect or not.

[netsistemas]

Y use direct connect to SQL SERVER with

        CC = $"jdbc:jtds:sqlserver:${ServerConfig.Server};charset=utf8;databaseName=${ServerConfig.DB};user=${ServerConfig.Usu};password=${ServerConfig.clave};appname=miappname;wsid=somethinghere;loginTimeout=10;socketTimeout=10"$
        SqlServer.Initialize("net.sourceforge.jtds.jdbc.Driver", CC)

the question is

- keep alway connect? or close and reopen connection in any ExecQuery, ExecNonQuery, etc

(now, i use a PUBLIC VAR in a module and only open database when app is open. (Cheking error if conection are losed) )
This question is for b4x (Android and/or java)

 

[Erel]

This question is for b4x (Android and/or java)

I guess that you mean B4J.

Making a direct connection from a mobile device is not recommended and is likely to be unreliable.

Best solution is to use jRDC2.

You cannot assume that the connection is always valid. It will break when there are disconnections.

 

[netsistemas]

I cannot use JRDC2, as the implementations must be cross-platform and multilanguage. Tried using JRDC2 to consume that service in .NET but it was no easy task. We are finally implementing WEB SERVICE. We have downloaded Sql Server for Android, as it does not allow SSL, but until we migrate EVERYTHING, we will continue to use direct connection and optimize it to the maximum, hence the question.
I think I am going to keep the connection always open, and catching errors to REOPEN the connection when this happens.

The connection is effectively lost when the device 'hangs'.

I have to replace all SELECT, UPDATE, etc, by WebService functions, which, being asynchronous, give a bit of a problem. For more problems, the records it returns are MAPS or LISTS depending on the number of records, fields and values returned. Null values are not returned within the MAP.

i write this for the forum!!! goodlock
-----

No puedo usar JRDC2, pues las implementaciones deben ser multiplataforma y multilenguaje. Intenté usar JRDC2 para consumir ese servicio en .NET pero no era tarea fácil. Finalmente estamos implementando WEB SERVICE.
Sql Server lo hemos descargado para Android, pues no permite SSL, pero hasta que migremos TODO, seguiremos usando conexión directa y optimizarla al máximo, de ahí la cuestión.
Creo que voy a mantener la conexión siempre abierta, y detectando errores para REABRIR la conexión cuando esto suceda.
Efectivamente se pierde la conexión cuando el dispositivo 'se suspende'.
Tengo que sustituir todos los SELECT, UPDATE, etc, por funciones del WebService, que al ser asíncrono, dan un poco de problema.
Para mas problemas, los registros que devuelve son MAPAS o LISTAS en función del número de registros, campos y valores devueltos. Los valores null no son devueltos dentro del MAPA.
Escrito esto para la comunidad !!!. Suerta.

 

[KMatle]

Easy. Use OkHttpUtils and call that webservice. Or use 50 lines of php code to do so. Here you can filter the data like you need it.

 

[Erel]

I have to replace all SELECT, UPDATE, etc, by WebService functions, which, being asynchronous, give a bit of a problem.

Don't try to make synchronous calls to a remote database. Bad things will happen.

You can use jRDC2 and add another servlet that receives and returns json or any other standard format.

 

[Albert Kallal]

the question is

- keep alway connect? or close and reopen connection in any ExecQuery, ExecNonQuery, etc

Well, as noted, you not be doing a "lot" of updates this way. Since it not all that great of a approach, then I connect each time.

So, if you say do nothing for 5+ minutes, there is a good chance the connection will go away, or not be active.

So, I have a sqlite to SQL server sync routine. I always re-connect when I start the code. Eg this:

    Wait for (MyConnect) Complete (ret As Boolean)
    If ret = False Then
        lblConnectStatus.Text = "Connect Fail"
        Return
    End If
  
    lblConnectStatus.Text = "Connect ok"  
    ProgressBar1.Visible = False
... code to do updates to SQL server follows.

and my Connect routine is this:

Sub MyConnect As ResumableSub

    ProgressBar1.Visible = True
  
    Log(Main.Username & " - " & Main.Password)
    Log(Main.driver)
    Log(Main.jdbcUrl)
  
    Main.mysql.InitializeAsync("mysqlWAIT", Main.driver, Main.jdbcUrl, Main.Username, Main.Password)
    Wait For mysqlWAIT_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If

    ProgressBar1.Visible = False

    If (Success = True) Then
        lblConnectStatus.Text = "Connect ok"
    Else
        lblConnectStatus.Text = "Connect Fail "
    End If
  
    Return Success
  
End Sub

I should note that after the first connect, then any other re-connect occurs VERY fast.

And as noted, one would not have truckloads of multiple update routines all over the place - at least one should not.
The above connect routine does have some UI parts - and if I had code all over the place, then I suppose those "UI" parts in the connect routine would be removed.

So for me? I have adopted the approach of a re-connect each time. But that's because I am starting a sync routine - not that I have "general" code all over the place that executes updates with that direct connection. And to be fair? I never did get around to writing code to close the connection.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada