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
Code: Copy 
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
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:
Code: Copy 
Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, Buffer))
 
Upvote 0
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.


Code: Copy 
             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))


Code: Copy 
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
What datatype is CaseID?
 
Upvote 0
I use the library ByteConverter
and did this

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

same in database
 

Attachments

  • Untitled.png
    Untitled.png
    4.7 KB · Views: 500
Upvote 0
Not 100% certain this would work , but worth a try

change
Code: Copy 
Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, Buffer))

to
(assuming you have byteconverter called bc)
Code: Copy 
Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, "x'" & bc.HexFromBytes(Buffer) & "'")
 
Upvote 0
Daestrum said:
Not 100% certain this would work , but worth a try

change
Code: Copy 
Main.SQL1.ExecNonQuery2(sql_update, Array As Object(lattitude, longitude, Buffer))

to
(assuming you have byteconverter called bc)
Code: Copy 
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
I think @Daestrum was on the right track. Looking at https://stackoverflow.com/a/8567926, pass the value as a string and use
Code: Copy 
$“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
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
Code: Copy 
$“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.
Code: Copy 
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
It could also be your first comparison

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

Maybe try
Code: Copy 
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
There is something very interest.
with every update the data fromthe first query change but no from hex

Code: Copy 
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
What's the full code (as of now) that generates this?
 
Upvote 0
OliverA said:
What's the full code (as of now) that generates this?
Click to expand...

Code: Copy 
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
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)

Code: Copy 
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
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)

Code: Copy 
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
What are the log outputs?
 
Upvote 0
I think is the VARBINARY type problem.

try to do a manual update and is does not work

Code: Copy 
 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 Question
B4J Question DBUTILS and mysql problems
Replies
7
Views
4K
moore_it
moore_it
Harris
  • Article Article
B4J Code Snippet MySQL Negative Dates - Long DateTime (such as Birthdays) problem
Replies
0
Views
4K
Harris
Harris
S
  • Question Question
B4J Question Problem with Designer & TextField
Replies
8
Views
4K
SeaBee
S
udg
  • Question Question
Other MySQL, different type of data in a field
Replies
5
Views
3K
OliverA
OliverA
M
  • Question Question
B4J Question Mysql and "LOAD DATA INFILE"
Replies
8
Views
4K
micro
M
Back
Top