B4J Question JRDC2: Update Null value in field of type image

[Omar Moreno]

Hello everyone.

One database server has SQL Server 2008 R2 and another one is going to install SQL Server 2017.
I am using the Jrdc2 server with the jtds1.3.1 driver, but since it is not updated since 2013-06-08.
I am testing with the Microsoft driver mssql-jdbc-6.4.0.jre8.jar and also with mssql-jdbc-6.4.0.jre9.jar.

On the JRDC2 server I have an UPDATE command that can update an image field which can accept NULL values.

In the B4A application the images are saved in SQLite and then sent to the server, but in the Jrdc2 server:

When I use the #AdditionalJar: Jtds-1.3.1:
If I send a BLOB value it updates it well
If I send a NULL value it updates it OK.

When I use the #AdditionalJar: mssql-jdbc-6.4.0.jre8.jar or the #AdditionalJar: mssql-jdbc-6.4.0.jre9.jar:
If I send a BLOB value it updates it well.
If I send a NULL value, it generates an error:

(SQLServerException) com.microsoft.sqlserver.jdbc.SQLServerException: Conflict of operand types: nvarchar is incompatible with image

B4A:

Sub CreateCommand(Name As String, Parameters() As Object) As DBCommand
    '
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = Name
    If Parameters <> Null Then cmd.Parameters = Parameters
    Return cmd
    '
End Sub

For Fil = 0 To MiCursor.RowCount -1
    '
    MiCursor.Position = Fil
    Dim cmd As DBCommand
   cmd = CreateCommand("update_e_3", Array As Object(MiCursor.GetBlob("IMAGE_X "), MiCursor.GetString("ID_X")))
    DatosX.Add(cmd)
    '
Next'
MiCursor.Close
'
Dim req As DBRequestManager
req.Initialize(Me,rdclink)
Dim Job As HttpJob = req.ExecuteBatch(DatosX,Null)
Wait For (Job) JobDone(Job As HttpJob)
ProgressDialogHide
If Job.Success Then
    Msgbox("UPDATE OK", "MENSAJE")
Else
    Msgbox(Job.ErrorMessage, "ERROR")
End If
Job.Release

B4J, config.properties:

sql.update_e_3=UPDATE [TABLA-X] SET X_IMAGE = ? WHERE X_ID = ?;

What could I be doing wrong or is it a bug in JRDC2 or in Microsoft's Jdbc Driver?
Thank you.

 

[Erel]

Try to create another command like this:

sql.update_e_3_with_null=UPDATE [TABLA-X] SET X_IMAGE = null WHERE X_ID = ?;

Does it work?

 

[Omar Moreno]

Hi.
When the NULL value is forced on the command, it works fine.

So if GetBlob does not send a NULL value, I will use this command:

sql.update_e_3_with_null=DECLARE @XIMAGE AS VARBINARY(MAX) \
                         SET @XIMAGE = CONVERT(VARBINARY(MAX),?)  \
                         UPDATE [TABLA-X] \
                        SET X_IMAGE = CASE @XIMAGE WHEN '' THEN NULL ELSE @XIMAGE END \
                        WHERE X_ID = ?;

In B4A the only problem is that you should be careful when placing all the image parameters at the beginning of the list so that the server catches them according to the order of arrival.

Thank you.

 

[OliverA]

What could I be doing wrong or is it a bug in JRDC2 or in Microsoft's Jdbc Driver?

Behind the scenes, B4J's SQL library uses JDBC's setObject to set parameter values. There is not really a standard that a JDBC driver developer has to follow as what it will accept in the setObject method that will translate to the proper underlying database type, outside of passing the method the proper type. Add that to the language differences in handling/representing NULLs and you can have cases like this were one JDBC driver interprets your NULL parameter the way you think it should and another JDBC driver is more picky (does not like your representation of NULL) and errors out.