Remote Function error "Count field incorrect"

[#1]

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.

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