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.

[reimer_pods · Accepted solution]

The documentation is a little bit unclear about VARCHAR returns: Remote Procedure Calls.

LONG VARCHAR is explicitly stated as not being a valid return type, while CHAR is, but not a word about VARCHAR (without LONG). So possibly VARCHAR(32767) is interpreted as LONG VARCHAR, which would generate that error

So I suppose you'd be better off using a procedure with a result set instead of that function (but that's just my two cents) .

[VolkerBarth · Accepted solution]

but not a word about VARCHAR

As SQL Anywhere usually treats CHARs as VARCHARs (and states that "CHAR is a domain, implemented as VARCHAR."), I would think that VARCHAR is valid, too.

There could also be a limitation to 255 characters, as several builtin functions treat longer strings differently.

In case VARCHAR(32767) is not valid for parameters and return values of RPCs, it may also be invalid as part of a RPC's result set.

That's my own guesswork, obviously.

[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...

[Former Member]

WOW! I am surprised at the lack of capability! Returning CHAR(255) still didn't work. But returning INT did.

So I guess I will make remote tables on the remote server, and run the actual function there.

[Breck_Carter]

You're the last person I would expect to be surprised, given your history with RPC oddities over the years.

Anyway, try CHAR ( 64 )... that's another magic number like 255 in The Funky World Of ODBC And Other Remote Oddities.

[Breck_Carter]

Another suggestion: Turn the RPC function into an RPC procedure that returns a singleton SELECT result set, then wrap the procedure call in a CREATE FUNCTION on the destination side if you really really really really really want a value-returning-function-thingie.

[Former Member]

We have created a SQLA web-server for an iPad project. The IT guys have isolated it via firewalls from the database server. I don't want to keep any actual data on the web server database.

I probably could never have succeed with the RPC anyway, as I would no doubt need a LONG VARCHAR to return the JSON payload I am creating.

So I have now created Remote Tables on the web server, and run the native SP on the web server instead of having it as an RPC. Problem solved and web service providing data as needed.

[jack_schueler]

Looking into this since it doesn't seem right.

[Former Member]

Hi JB.

Just wondering if you found anything?

My current problem is solved, but we are just starting a few projects on this SQLA web server and will have many more uses for RPCs.

Thanks, Bill

[Breck_Carter]

CREATE PROCEDURE with RESULT set is highly recommended, as it does not seem to have any of the data type restrictions affecting procedure OUT parameters or function RETURNS clauses. Foxhound makes heavy use of result-set-returning RPC procedures where SQL Anywhere is used on both sides. If you are dealing with Other Software on the remote side, your mileage may vary.