Remote Function error "Count field incorrect"

[Former Member]

SQLA 16.0.0.1915

I have a simple Function on my database:

`CREATE OR REPLACE FUNCTION app_owner.mbdevu_return_payload_test(IN as_mbdevu_device_id VARCHAR(128))

RETURNS VARCHAR(32767) BEGIN RETURN 'Bill Aumen test'; END;`

When I execute Select mbdevu_return_payload_test('x') from ISQL, it works as expected.

I have created a Remote Server and Function: CREATE OR REPLACE FUNCTION "app_owner"."mbdevu_return_payload_test"( in "as_mbdevu_device_id" varchar(128) ) returns varchar(32767) at 'TIFISServer..app_owner.mbdevu_return_payload_test'

When I execute in ISQL from the remote database: SELECT app_owner.mbdevu_return_payload_test('x') I get an error -660 Count Field Incorrect

It all seems so simple, I must be missing something basic.

[jack_schueler]

This is a bug in the software and is now corrected. The fix will appear in 16.0.0.2342 and 17.0.4.2177 or later versions. Thanks for reporting the problem.

[Former Member]

Thanks JB.

[VolkerBarth]

Jack, so VARCHAR/NVARCHAR and CHAR/NCHAR will work as supported types then (up to their maximum of 32767 characters), or is there any length limitation?

(I'm asking as the docs are somewhat indifferent here, as Reimer has pointed out, and some client APIs seem to restrict regular VARCHARs to 32767 bytes, not characters, and describe longer data types as long varchar...)

[jack_schueler]

You do have to put up with the vagaries of ODBC. I tried to return an NVARCHAR(32767) result filled with the character Yee from the Deseret alphabet (2 surrogate pairs, or 4 bytes per character). The NVARCHAR string requires 4*32767 bytes of storage in the database. The character set used to transmit the data is UTF-8. On the local server side, I end up with an NVARCHAR result containing 16384 characters and a BYTE_LENGTH of 65535 (and no warning about the truncation). Now 65,535 is an odd number and when I looked at the last character in the string, it is garbage. This suggests that ODBC limited the result to a maximum 65,535 bytes.

Presumably, if all of your National Characters can be represented as single-byte UTF-8 then you don't have to worry.

[VolkerBarth]

Well, I have not yet had to cope with the Deseret alphabet (probably as I'm not aware when the latter days will begin), but of course I have to deal with German umlauts which do require 2 bytes with UTF-8...

So a big thanks for the clarification:)

[Breck_Carter]

The Deseret alphabet consists entirely of emoji characters, thus making it far ahead of its time. Consider, for example, the glyph for the letter Gay...