Android Question Decode SQL to string with params (not is a question)

[netsistemas]

in some cases, when you debug a SQL is good to take a SQL Full that is:

not: insert into table(campo1,campo2,campo3) values (?,?,?)


instead of
insert into table(campo1, campo2, campo3) values ('mivalor1','mivalor2', 'mivalor3')

i attach this esasy code for replace a ? by val of list)

public  Sub DecodeSql(TheSql As String, Ar As List) As String

    Dim Posicion As Long = 0
    Dim Posicion2  As Int
    Dim NewSql As String
    Dim N As Long
    For n = 0 To Ar.Size -1
        Posicion2= TheSql.IndexOf2(  "?",Posicion)
        NewSql = NewSql &  TheSql.SubString2(Posicion, Posicion2) & "'" & Ar.Get(n) & "'"
        Posicion = Posicion2+1
    Next
    NewSql= NewSql & TheSql.SubString(Posicion)
    Log(NewSql)
    Return NewSql
End Sub

 

[Mahares]

i attach this esasy code for replace a ? by val of list)

I don;'t know about that. I think it can be done more easily with smart string, or string builder without having to deal with double quotes wrapping around single quotes. You asking for trouble. Can you give an example using your sub Decode to show what the final string looks like.

 

[LucaMs]

...(not is a question)​

However, an example like that should be posted in the "Code Snippets" forum; or you could have selected "Other" instead of "Questions":

 

[alwaysbusy]

I like it, can come in handy when debugging. Thank you for sharing

 

[netsistemas]

InstruccionSql = "EXEC NTS_ADDFICHAJE4 ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?"
                Dim Campos As List
                Campos.Initialize
                
                Campos.add( EmpleadoActual.Codigo ) '1
                Campos.add( Obra ) '2
                Campos.add( Partida ) '3
                Campos.add( Fecha )
                Campos.add( HoraInicio)
                Campos.add( HoraFin )
                Campos.add( Diferencia )
                Campos.add( TipoActuacion )
                Campos.add( Confirma )
                Campos.add( modGeneral.PosicionGPS.Latitud   )
                Campos.add( modGeneral.PosicionGPS.Longitud  )
                Campos.add( Veh  )
                Campos.add( txtNotas.Text)
                Campos.add( Coa  )
                Campos.add( lblVelocidad.TEXT   )
                Campos.add( modGeneral.PosicionGPS.UltimaHoraTomaDatos    )
                Campos.add( IIf( modGeneral.PosicionGPS.IsFake,1,0) ) 'isfake
                Campos.add( Cliente )
                Campos.add( 1) '19 tieMMpotemoral
                Campos.add(  FinalizadoBit ) '20 finalizado bit
                Campos.add( TipoFichaje.TipoFichaje )'tipofichaje
                Campos.add(Oportunidad ) 'oportunidad
                Campos.ADD(IDAndroid ) 'terminalid
                Campos.ADD(modGeneral.V_VersionMia ) '24'verison
                Campos.ADD(LugarTrabajo) '24'verison
                Campos.ADD("") 'nada1
                Campos.ADD("") 'nada2
            
            
            
            
            
                ProgressDialogShow("Insertando registro")
                'If CN.SqlExecuteSinMsg (  InstruccionSql,True) Then
                Dim Sl2 As String
                Sl2  = CN.DecodeSql(InstruccionSql,Campos)

and in debug, you can view sl2.
Result: (all values are inserted with quites (be carefour if text have a quotes -> Error .
This is only for debug
EXEC NTS_ADDFICHAJE4 '9999','0','0','11-01-2022','08:00','09:00','1','Trabajo normal','','4x.3xx9284','-x.6xx6476','Sin Vehiculo','','','0 km/h','11-01-2022 16:44:13','0','0','1','0','Comercial','999999','7421f6f94f8780f1','2201111643','Trabajo Presencial','',''


(for next send, i publisth in other, not question)

 

[aeric]

Sometimes the parameters are not string type.

 

[OliverA]

Result: (all values are inserted with quites (be carefour if text have a quotes -> Error .

Sometimes the parameters are not string type.

Neither should be an issue if the resulting SQL string is just used to view the parameters in DEBUG mode without actually using the string in an actual query call. The query call should still be the parameterized version. This gives you a visual what should go to the server, this way when an error is displayed you have a decent visual of the SQL statement.

 

[OliverA]

I think it can be done more easily with smart string, or string builder

It also saves on resources. Since String(s) are immutable, each & creates a new string. After awhile this process can put a toll on memory and the garbage collector. The log4jdbc code that does something similar uses something like StringBuilder. Since the produced string (in log4jdbc) is used for debugging purposes, it just does not even worry about quoting the parameters.

Link: https://github.com/arthurblake/log4...et/sf/log4jdbc/PreparedStatementSpy.java#L107

Edit: Cleaning up some of my sloppy writing style. It's not even Monday today. So what excuse do I have?

 

[netsistemas]

i use this code for debug, and with sql server. Copy and paste to Management Studio (SSMS), and F5.
Sql server do a cast betweeen string and number with no problems.
For execute in B4X not use this sql statement, i use the execute methot with params(list)