B4J Code Snippet JRDC2 connect to MSSQLLocalDb

[aeric]

# MS SQL Server Express Edition
DriverClass=net.sourceforge.jtds.jdbc.Driver
JdbcUrl=jdbc:jtds:sqlserver://./DatabaseName;instance=LOCALDB#AC2F9943;namedPipe=true;domain=DomainName

You need to use jTDS 1.3.2

Reference: https://stackoverflow.com/questions/11345746/connecting-to-sql-server-localdb-using-jdbc

 

[aeric]

While trying to compile TeamViewer Alternative project, I encountered an issue with jRDC2 (Line #92 in RDCHandler.bas)

Dim data() As Byte = ser.ConvertObjectToBytes(res)

Log:

(RuntimeException) java.lang.RuntimeException: Cannot serialize object: net.sourceforge.jtds.jdbc.ClobImpl@2f0c91e6

I modified the code to handle Clob column type and it is working now. (See line #35 and #36 below)

Private Sub ExecuteQuery2 (con As SQL, in As InputStream,  resp As ServletResponse) As String
    Dim ser As B4XSerializator
    Dim m As Map = ser.ConvertBytesToObject(Bit.InputStreamToBytes(in))
    Dim cmd As DBCommand = m.Get("command")
    Dim limit As Int = m.Get("limit")
    Dim rs As ResultSet = con.ExecQuery2(Main.rdcConnector1.GetCommand(cmd.Name), cmd.Parameters)
    If limit <= 0 Then limit = 0x7fffffff 'max int
    Dim jrs As JavaObject = rs
    Dim rsmd As JavaObject = jrs.RunMethod("getMetaData", Null)
    Dim cols As Int = rs.ColumnCount
    Dim res As DBResult
    res.Initialize
    res.columns.Initialize
    res.Tag = Null 'without this the Tag properly will not be serializable.
    For i = 0 To cols - 1
        res.columns.Put(rs.GetColumnName(i), i)
    Next
    res.Rows.Initialize
    Do While rs.NextRow And limit > 0
        Dim row(cols) As Object
        For i = 0 To cols - 1
            Dim ct As Int = rsmd.RunMethod("getColumnType", Array(i + 1))
            'check whether it is a blob field
            If ct = -2 Or ct = 2004 Or ct = -3 Or ct = -4 Then
                row(i) = rs.GetBlob2(i)
            Else if ct = 2 Or ct = 3 Then
                row(i) = rs.GetDouble2(i)
            Else If DateTimeMethods.ContainsKey(ct) Then
                Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1))
                If SQLTime.IsInitialized Then
                    row(i) = SQLTime.RunMethod("getTime", Null)
                Else
                    row(i) = Null
                End If
            Else If ct = 2005 Then ' Clob
                row(i) = rs.GetString2(i)
            Else
                row(i) = jrs.RunMethod("getObject", Array(i + 1))
            End If
        Next
        res.Rows.Add(row)
    Loop
    rs.Close
    Dim data() As Byte = ser.ConvertObjectToBytes(res)
    resp.OutputStream.WriteBytes(data, 0, data.Length)
    Return "query: " & cmd.Name
End Sub

 

[OliverA]

I encountered an issue with jRDC2

@aeric: You may want to make a wish for the fix. See a previous one here: https://www.b4x.com/android/forum/threads/apply-fix-to-jrdc2-2-21.102387/#content

 

[aeric]

Else If ct = 2005 Then ' Clob

This happen when the field type is varchar(MAX).