B4J Question LONGNVARCHAR datatype is limited to 4000 bytes, How to get NvarChar(Max) from SQL serever?

[OMS]

Hi,
In my b4j server application I am trying to connect SQL server and execute an stored procedure that returns a NVARCHAR(MAX) in output param.
But I only recieve the first 2000 bytes (utf) of the result string.

Dim myResponse As String
Dim SQLconnection As SQL = SQLdb.myConnectionPool.GetConnection
Dim CallableStatement As JavaObject = SQLconnection.CreateCallStatement("exec  [SP_myStoredProc] ?,?,?,?  ", Array("Value_1", "Value_2", "Value_3", "" ))
CallableStatement.RunMethod("registerOutParameter", Array (4,-1))
CallableStatement.RunMethod("execute", Null)
myResponse = CallableStatement.RunMethod("getString",Array (4))

According to this page https://developer.android.com/reference/java/sql/Types
I should use type -16 LongNvarChar which is not supported by jSQL library. So I use data type -1 ( LongVarChar)
How can I have the unlimited length of NvarChar(Max) which is returned by SQL serever?

 

[Erel]

What is the output of Log(myResponse.Length)?

 

[OMS]

hi,
Log(myResponse.Length) returns 4000 exactly.

thanks

 

[Erel]

I should use type -16 LongNvarChar which is not supported by jSQL library

Where exactly is the problem? Might be simple to solve with JavaObject.

 

[OMS]

Accually, the data type -16 is not my problem.
I need to have the full length of the output-param which my sotredProcedure returns (it may be 12,000 bytes or more)

myResponse = CallableStatement.RunMethod("getString",Array (4))

In this page we can read about useLOBs option
http://jtds.sourceforge.net/faq.html

Unfortunately, I could not make use of it because when I tried to call functions like getClob or getObject according to the following page, I noticed that getClob method is not supported by the CallableStatement object and getObject returned something that I could not convert to string
https://developer.android.com/reference/java/sql/CallableStatement

I guess I am very close to answer but I got disappointed and need your help
Thank you

 

[Erel]

s not supported by the CallableStatement object and getObject returned something that I could not convert to string

What is the output of:

Dim o As Object = CallableStatement.RunMethod("getObject", Null)
Log(o)
Log(GetType(o))

 

[OMS]

    CallableStatement.RunMethod("registerOutParameter", Array (4,-1))
    CallableStatement.RunMethod("execute", Null)
    'myResponse = CallableStatement.RunMethod("getString",Array (4))
    Dim o As Object = CallableStatement.RunMethod("getObject", Array (4))
    Log(o)
    Log(GetType(o))

net.sourceforge.jtds.jdbc.ClobImpl

 

[DonManfred]

log result is

Ist must be TWO lines of log. Post the 2nd one too.

 

[Erel]

Try this:

Dim b() As Byte = Bit.InputStreamToBytes(o.As(JavaObject).RunMethod("getAsciiStream", Null))
Dim s As String = BytesToString(b, 0, b.length, "utf8")

 

[OMS]

That code returned only 4000 bytes and encoding was not UTF-8 and some text were converted to '?????'
I think getAsciiStream method is using "US-ASCII" encoding

when I replaced the method name with getCharacterStream, I got this error:

  java.lang.RuntimeException: java.lang.ClassCastException: java.io.BufferedReader cannot be cast to java.io.InputStream

 

[Erel]

That code returned only 4000 bytes and encoding was not UTF-8 and some text were converted to '?????'

I was only guessing the encoding.
I think that the problem is somewhere else.

And after a quick google search: https://sourceforge.net/p/jtds/bugs/513/
I recommend you to switch to MS jdbc driver.