B4J Question Problems comparing varbinary fields in mysql

victormedranop

victormedranop

Well-Known Member
Licensed User
Longtime User
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
 
Last edited:
Sort by date Sort by votes
Jeffrey Cameron

Jeffrey Cameron

Well-Known Member
Licensed User
Longtime User
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))
 
Upvote 0
victormedranop

victormedranop

Well-Known Member
Licensed User
Longtime User
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
 
Upvote 0
victormedranop

victormedranop

Well-Known Member
Licensed User
Longtime User
I remove the return on the catch an got an error , dont know whats its means.

B4X: 
 Before start of result set
 
Upvote 0
Daestrum

Daestrum

Expert
Licensed User
Longtime User
What datatype is CaseID?
 
Upvote 0
victormedranop

victormedranop

Well-Known Member
Licensed User
Longtime User
I use the library ByteConverter
and did this

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

same in database
 

Attachments

  • Untitled.png
    Untitled.png
    4.7 KB · Views: 122
Upvote 0
Daestrum

Daestrum

Expert
Licensed User
Longtime User
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) & "'")
 
Upvote 0
victormedranop

victormedranop

Well-Known Member
Licensed User
Longtime User
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) & "'")
Click to expand...



no still not working i am getting mad >( =
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
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.
 
Upvote 0
victormedranop

victormedranop

Well-Known Member
Licensed User
Longtime User
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.
Click to expand...


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}"$
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
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
 
Upvote 0
victormedranop

victormedranop

Well-Known Member
Licensed User
Longtime User
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'
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
What's the full code (as of now) that generates this?
 
Upvote 0
victormedranop

victormedranop

Well-Known Member
Licensed User
Longtime User
OliverA said:
What's the full code (as of now) that generates this?
Click to expand...

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
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
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
 
Upvote 0
victormedranop

victormedranop

Well-Known Member
Licensed User
Longtime User
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
Click to expand...


I try that to with no luck
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
What are the log outputs?
 
Upvote 0
victormedranop

victormedranop

Well-Known Member
Licensed User
Longtime User
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 )  ;
 
Upvote 0
You must log in or register to reply here.

Similar Threads

moore_it
  • Question
B4J Question DBUTILS and mysql problems
Replies
7
Views
978
moore_it
moore_it
Harris
  • Article
B4J Code Snippet MySQL Negative Dates - Long DateTime (such as Birthdays) problem
Replies
0
Views
2K
Harris
Harris
S
  • Question
B4J Question Problem with Designer & TextField
Replies
8
Views
895
SeaBee
S
udg
  • Question
Other MySQL, different type of data in a field
Replies
5
Views
1K
OliverA
OliverA
M
  • Question
B4J Question Mysql and "LOAD DATA INFILE"
Replies
8
Views
2K
micro
M
Top