Android Question Getting the sql generate in execute2 method. Return full sql internal

netsistemas · Oct 21, 2020

This code , in any cases, crack, and ineed a debug/run in Server Management.
My questión is if there are nay thenic to get the sql in back of internal code.

(This sql is easy, but, if the sql are very long, is complicated)

B4X:

    Sql = "Insert Into tabla(Cod_Proveedor, name) values (?, ?)"
    CN.SqlExecute2(Sql,Array As String("1","PEPE"))

some like
sqlDecodeSql (sql, array as string(1))
and get THIS code:
insert into tabla(cod_proveedor, name) values (1, "PEPE")

It would be useful to copy and paste in the corporate administrator

drgottjr · Oct 21, 2020

are you talking about a helper function that writes a long, repetitive sql string for you? you would simply feed it the parameters? like this?

B4X:

Dim sqlInsert As String = "Insert Into tabla(Cod_Proveedor, name) values (?, ?)"
Dim insertList As List
insertList.Initialize
insertList.Add( createEntry( "1", "Pepe" ) )
insertList.Add( createEntry( "1", "Carlos") )
insertList.Add( createEntry( "1", "Juanillo") )
insertList.Add( createEntry( "1", "Loro") )

Log( sqlDecodeSql( sqlInsert, insertList ) )

'-----------------------------------------------------------------------------------------
Sub createEntry( item1 As String, item2 As String ) As String
    Return( $""${item1}","${item2}""$)
End Sub

Sub sqlDecodeSql (sql As String, entries As List) As String
    For Each item As String In entries
        Log( sql & ", array(" & item & ")")
    Next
End Sub

the idea is simple enough, but there are some problems. obviously, you would have separate sql strings for, eg, insert, delete, select, update. the system would be built on strict sets of input rules, which may or may not be suitable for your system.

my "solution" involves 2 help functions: one to create the parameters, the other to combine them.
your actual sql insert would look some like:

B4X:

CN.SqlExecute2(sqlInsert,Array(entry))

you could run the insert from within the second sub. and if you had a number of entries, you should run a batch command instead of executing numerous insert operations.

see what you think. the biggest issue was maintaining the quotation marks around each field.

netsistemas · Oct 21, 2020

thanks.
Is not that i are searching.
I need that in log, view:
insert into Tabla(Cod_Proveedor, Name) Values (1,"pepe")
or if the cod_proveedor is STRING in database (char) view : ... values ("1","pepe")

I think that this is not posible easy, and for all database system (Sql server, mysql, etc) I use now, sql server (remote hosting) for my current proyects.

I can do something to replace '?' by items in array, but is not perefect.
Thanks again. May be, Erel with your source code in bx4 can do this in a future (easy way).

Something like this:

B4X:

public  Sub TestSql
    Dim Sq
    Sq = "Insert Into tabla(Cod_Proveedor, name) values (?, ?)"
    DecodeSql(Sq,Array As String("1","PEPE"))
End Sub
private 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

Log out:
Insert Into tabla(Cod_Proveedor, name) values ("1", "PEPE")

Android Question Getting the sql generate in execute2 method. Return full sql internal

netsistemas

Active Member

drgottjr

Expert

Attachments

netsistemas

Active Member

Similar Threads

Android Question Getting the sql generate in execute2 method. Return full sql internal

netsistemas

Active Member

drgottjr

Expert

Attachments

netsistemas

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency