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

OMS · Feb 5, 2022

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.

Here is the code:

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 · Feb 6, 2022

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

OMS · Feb 6, 2022

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

thanks

Erel · Feb 7, 2022

OMS said:
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 · Feb 7, 2022

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)

This line of code returns only the first 4000 bytes of it:

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 · Feb 8, 2022

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

What is the output of:

B4X:

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

OMS · Feb 8, 2022

with a small change in your code:

    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))

log result is:

net.sourceforge.jtds.jdbc.ClobImpl

DonManfred · Feb 8, 2022

OMS said:
log result is

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

Erel · Feb 8, 2022

Try this:

B4X:

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

OMS · Feb 8, 2022

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:

B4X:

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

Erel · Feb 8, 2022

OMS said:
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.

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

OMS

Member

Erel

B4X founder

OMS

Member

Erel

B4X founder

OMS

Member

Erel

B4X founder

OMS

Member

DonManfred

Expert

Erel

B4X founder

OMS

Member

Erel

B4X founder

Similar Threads

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

Member

B4X founder

Member

B4X founder

Member

B4X founder

Member

Expert

B4X founder

Member

B4X founder

Similar Threads

Privacy & Transparency

Privacy & Transparency