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.