B4J Question Problems comparing varbinary fields in mysql

victormedranop · Dec 22, 2020

i am using this function to get an update some fields in de mysql server

B4X:

Try
        Dim rs As ResultSet
  
        'Dim sql_que_data As String = $" Select c.id As CASEID from GMT.Dispositivos d inner join GMT.Perfiles p on d.ID = p.DeviceID inner join GMT.Casos c on c.ProfileID = p.ID where d.imei = ${imei}"$
       
        Dim sql_que_data As String = $"Select c.ID As CASO from GMT.Dispositivos d inner join GMT.Asociacion_Dispositivos ad on ad.PanicbuttonID = d.ID inner join GMT.Perfiles p on p.DeviceID = ad.ShacklesID inner join GMT.Casos c on c.ProfileID = p.ID where d.imei = ${imei}"$
        Log(sql_que_data)
        rs = Main.sql1.ExecQuery(sql_que_data)
        Do While rs.NextRow
           
            Dim Buffer() As Byte
            Buffer = rs.GetBlob("CASO")
           
            If rs.GetString("CASO").Length > 0 Then
                Try
                    Dim sql_update As String = "UPDATE Ubicacion set lat = ?,lon=? where caseID = ?"
                    Dim sql_update2 As String = $"UPDATE Ubicacion set lat = ${lattitude},lon=${longitude} where caseID = ${Buffer}"$
                    Log(sql_update2)
                   
                    Dim Params As List
                    Params.Initialize
                    Params.Add(lattitude)
                    Params.Add(longitude)
                    Params.Add(Buffer)
                   
                    Main.sql1.ExecNonQuery2(sql_update, Params)
                Catch
                    LogError(LastException)
                    Return
                End Try
            Else
                Log("CaseID ELSE ->"&rs.GetString("CASO"))
            End If
           
        Loop
    Catch
        Log(LastException)
    End Try

does not give me any error but does not update de table.

if any one have the proper way to compair varbinary on mysql server.

thanks

victor

Jeffrey Cameron · Dec 22, 2020

I'm not sure, but it may be that the buffer parameter is getting converted to string. Try changing from a parameter LIST to an object array:

B4X:

Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, Buffer))

victormedranop · Dec 23, 2020

i change to this, the log response before the update have all the data

UPDATE Ubicacion set lat='18.430633544921875', lon='-69.97968292236328' WHERE CaseID = '[Ljava.lang.Object;@45f45fa1'

if i remove the where condition ----- > works

it seems my problem is with the varbinary comparison.

B4X:

             Dim sql_update As String = "UPDATE Ubicacion set lat = ?,lon=? "
                Log($"UPDATE Ubicacion set lat='${lattitude}', lon='${longitude}'"$) '' WHERE CaseID = '${Array As Object(Buffer)}'"$)            
                Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude))

B4X:

Sub update_location (imei As String , lattitude As String , longitude As String)
    
    Try
        Dim rs As ResultSet
        Dim sql_que_data As String = $"Select c.ID As CASO from GMT.Dispositivos d inner join GMT.Asociacion_Dispositivos ad on ad.PanicbuttonID = d.ID inner join GMT.Perfiles p on p.DeviceID = ad.ShacklesID inner join GMT.Casos c on c.ProfileID = p.ID where d.imei = ${imei}"$
        Log(sql_que_data)
        rs = Main.sql1.ExecQuery(sql_que_data)
        Do While rs.NextRow

            Dim Buffer() As Byte
            Buffer =  rs.GetBlob("CASO")
                Try
                Dim sql_update As String = "UPDATE Ubicacion set lat = ?,lon=? where caseID = ?"
                Log($"UPDATE Ubicacion set lat='${lattitude}', lon='${longitude}' WHERE CaseID = '${Array As Object(Buffer)}'"$)            
                Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, Buffer))        
                Catch
                    LogError(LastException)
                    Return
                End Try    
        Loop
    Catch
        Log(LastException)
    End Try
    
End Sub

victormedranop · Dec 23, 2020

I remove the return on the catch an got an error , dont know whats its means.

B4X:

 Before start of result set

Daestrum · Dec 23, 2020

What datatype is CaseID?

victormedranop · Dec 23, 2020

Daestrum said:
What datatype is CaseID?

VarBinary == blog

victormedranop · Dec 23, 2020

I use the library ByteConverter
and did this

B4X:

            Dim bb As ByteConverter
            Log(bb.HexFromBytes(Buffer)) =====> 1F6CF190ADD69F48A66D09525DF38E41

same in database

Daestrum · Dec 24, 2020

Not 100% certain this would work , but worth a try

change

B4X:

Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, Buffer))

to
(assuming you have byteconverter called bc)

B4X:

Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, "x'" & bc.HexFromBytes(Buffer) & "'")

victormedranop · Dec 24, 2020

Daestrum said:
Not 100% certain this would work , but worth a try

change

B4X:

Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, Buffer))

to
(assuming you have byteconverter called bc)

B4X:

Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, "x'" & bc.HexFromBytes(Buffer) & "'")

no still not working i am getting mad >( =

OliverA · Dec 24, 2020

I think @Daestrum was on the right track. Looking at https://stackoverflow.com/a/8567926, pass the value as a string and use

B4X:

$“0x${bc.HexFromBytes(Buffer)}“$

To create the string to pass as parameter.
Note: don’t copy paste the above since it was typed out on a phone and the wrong type of quotes may be pasted.

victormedranop · Dec 24, 2020

OliverA said:
I think @Daestrum was on the right track. Looking at https://stackoverflow.com/a/8567926, pass the value as a string and use

B4X:

$“0x${bc.HexFromBytes(Buffer)}“$

To create the string to pass as parameter.
Note: don’t copy paste the above since it was typed out on a phone and the wrong type of quotes may be pasted.

i think the problem is in the first query.

B4X:

Dim sql_que_data As String = $"Select c.ID As CASO from GMT.Dispositivos d inner join GMT.Asociacion_Dispositivos ad on ad.PanicbuttonID = d.ID inner join GMT.Perfiles p on p.DeviceID = ad.ShacklesID inner join GMT.Casos c on c.ProfileID = p.ID where d.imei = ${imei}"$

OliverA · Dec 24, 2020

It could also be your first comparison

B4X:

If rs.GetString("CASO").Length > 0 Then

Maybe try

B4X:

If Buffer <> Null And Buffer.Size > 0 Then

Rationale: If CASO is Null, the GetString may return a string with the value "null", in which case, Length would return 4

victormedranop · Dec 24, 2020

There is something very interest.
with every update the data fromthe first query change but no from hex

B4X:

UPDATE Ubicacion set lat='18.430479049682617', lon='-69.97966766357422' WHERE CaseID = '[B@7181ae3f'
UPDATE Ubicacion set lat='18.430574417114258', lon='-69.97943115234375' WHERE CaseID = '[B@52aa2946'

OliverA · Dec 24, 2020

What's the full code (as of now) that generates this?

victormedranop · Dec 24, 2020

OliverA said:
What's the full code (as of now) that generates this?

B4X:

Sub update_location (imei As String , lattitude As String , longitude As String)
    
    Try
        Dim rs As ResultSet
        Dim sql_que_data As String = $"Select c.ID As CASO from GMT.Dispositivos d inner join GMT.Asociacion_Dispositivos ad on ad.PanicbuttonID = d.ID inner join GMT.Perfiles p on p.DeviceID = ad.ShacklesID inner join GMT.Casos c on c.ProfileID = p.ID where d.imei = ${imei}"$
        Log(sql_que_data)
        rs = Main.sql1.ExecQuery(sql_que_data)
        Dim Buffer() As Byte 
        Dim bc As ByteConverter
        Do While rs.NextRow
            Buffer = rs.GetBlob("CASO")
            If Buffer <> Null And Buffer.Length > 0 Then


            Dim pp As String = bc.HexFromBytes(Buffer)
            Log(bc.HexFromBytes(Buffer))
            Log(pp)
            Log(Buffer.Length)
                Try
                    Dim sql_update As String = "UPDATE Ubicacion set lat = ?,lon=? where CaseID = ?"
                    Log($"UPDATE Ubicacion set lat='${lattitude}', lon='${longitude}' WHERE CaseID = '${bc.HexToBytes(pp)}'"$)
                    Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude,bc.HexToBytes(pp)))
                    'Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude,longitude,Buffer))
                Catch
                    Log(LastException)
                    Return
                End Try    
            End If
        Loop
        
    Catch
        Log(LastException)
    End Try
    
End Sub

OliverA · Dec 24, 2020

1) Do not convert pp back to bytes, leave it as a hex string.
2) prepend pp with "0x". Look at the code i provided. (As long as HexFromBytes does not already do this)

B4X:

Sub update_location (imei As String , lattitude As String , longitude As String)
    
    Try
        Dim rs As ResultSet
        Dim sql_que_data As String = $"Select c.ID As CASO from GMT.Dispositivos d inner join GMT.Asociacion_Dispositivos ad on ad.PanicbuttonID = d.ID inner join GMT.Perfiles p on p.DeviceID = ad.ShacklesID inner join GMT.Casos c on c.ProfileID = p.ID where d.imei = ${imei}"$
        Log(sql_que_data)
        rs = Main.sql1.ExecQuery(sql_que_data)
        Dim Buffer() As Byte 
        Dim bc As ByteConverter
        Do While rs.NextRow
            Buffer = rs.GetBlob("CASO")
            If Buffer <> Null And Buffer.Length > 0 Then


            'Dim pp As String = bc.HexFromBytes(Buffer)
            Dim pp As String = $"0x${bc.HexFromBytes(Buffer)}"$ 'Hex string for MySQL.
            Log(bc.HexFromBytes(Buffer))
            Log(pp) 'Should log a proper hex string for MySQL with 0x pre-pended
            Log(Buffer.Length)
                Try
                    Dim sql_update As String = "UPDATE Ubicacion set lat = ?,lon=? where CaseID = ?"
                    'Log($"UPDATE Ubicacion set lat='${lattitude}', lon='${longitude}' WHERE CaseID = '${bc.HexToBytes(pp)}'"$)
                    Log($"UPDATE Ubicacion set lat='${lattitude}', lon='${longitude}' WHERE CaseID = '${pp}'"$) 'Just pass pp without reconverting to bytes
                    'Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude,bc.HexToBytes(pp)))
                    Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, pp)) 'Were just passing the MySQL hex string
                    'Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude,longitude,Buffer))
                Catch
                    Log(LastException)
                    Return
                End Try    
            End If
        Loop
        
    Catch
        Log(LastException)
    End Try
    
End Sub

victormedranop · Dec 24, 2020

OliverA said:

1) Do not convert pp back to bytes, leave it as a hex string.
2) prepend pp with "0x". Look at the code i provided. (As long as HexFromBytes does not already do this)

B4X:

Sub update_location (imei As String , lattitude As String , longitude As String)
   
    Try
        Dim rs As ResultSet
        Dim sql_que_data As String = $"Select c.ID As CASO from GMT.Dispositivos d inner join GMT.Asociacion_Dispositivos ad on ad.PanicbuttonID = d.ID inner join GMT.Perfiles p on p.DeviceID = ad.ShacklesID inner join GMT.Casos c on c.ProfileID = p.ID where d.imei = ${imei}"$
        Log(sql_que_data)
        rs = Main.sql1.ExecQuery(sql_que_data)
        Dim Buffer() As Byte
        Dim bc As ByteConverter
        Do While rs.NextRow
            Buffer = rs.GetBlob("CASO")
            If Buffer <> Null And Buffer.Length > 0 Then


            'Dim pp As String = bc.HexFromBytes(Buffer)
            Dim pp As String = $"0x${bc.HexFromBytes(Buffer)}"$ 'Hex string for MySQL.
            Log(bc.HexFromBytes(Buffer))
            Log(pp) 'Should log a proper hex string for MySQL with 0x pre-pended
            Log(Buffer.Length)
                Try
                    Dim sql_update As String = "UPDATE Ubicacion set lat = ?,lon=? where CaseID = ?"
                    'Log($"UPDATE Ubicacion set lat='${lattitude}', lon='${longitude}' WHERE CaseID = '${bc.HexToBytes(pp)}'"$)
                    Log($"UPDATE Ubicacion set lat='${lattitude}', lon='${longitude}' WHERE CaseID = '${pp}'"$) 'Just pass pp without reconverting to bytes
                    'Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude,bc.HexToBytes(pp)))
                    Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, pp)) 'Were just passing the MySQL hex string
                    'Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude,longitude,Buffer))
                Catch
                    Log(LastException)
                    Return
                End Try   
            End If
        Loop
       
    Catch
        Log(LastException)
    End Try
   
End Sub

I try that to with no luck

OliverA · Dec 24, 2020

What are the log outputs?

victormedranop · Dec 24, 2020

I think is the VARBINARY type problem.

try to do a manual update and is does not work

B4X:

 UPDATE Ubicacion u set u.lat='18.430633544921875', u.lon='-69.97968292236328' 
WHERE u.CaseID = c.ID  from GMT.Dispositivos d 
inner join GMT.Asociacion_Dispositivos ad on ad.PanicbuttonID = d.ID 
inner join GMT.Perfiles p on p.DeviceID = ad.ShacklesID 
inner join GMT.Casos c on c.ProfileID = p.ID 
where d.imei = '353161076774465' AND u.Location_StatusID = 1 AND u.deviceID = d.ID )  ;

B4J Question Problems comparing varbinary fields in mysql

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Attachments

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency