Error returning bigint from remote server

[Former Member]

I have a remote procedure call that worked correctly for some time, but is now failing.

Here is the procedure on the local database:

ALTER FUNCTION "app_owner"."tifis_get_next_identity"( in as_table varchar(128) ) 
returns bigint
at 'TIFISServer..app_owner.get_next_identity'

Executing this statement on the local database:

SELECT tifis_get_next_identity(‘crregs_central_registry’)

Produces an error:

There was an error reading the results of the SQL statement. The displayed results may be incorrect or incomplete. Expression has unsupported data type SQLCODE=-624, ODBC 3 State="HY000"

Server is running: SQLA 11.0.1.2913

Here is the procedure on the remote database:

 ALTER FUNCTION "app_owner"."get_next_identity"( in as_table varchar(128) ) 
 returns BIGINT
 begin
   declare abig_identity BIGINT;
   select GET_IDENTITY(as_table) into abig_identity;
   if abig_identity is null then
     raiserror 19000 as_table || ' does not have an identity column defined';
     return 0
   end if;
   return abig_identity
 end

Executing this statement directly on this remote database:

select get_next_identity('crregs_central_registry')”

returns a value of 100,001,361

Server is running 16.0.0.1324

(Prior to this weekend, the remote server was running 11.0.1 and the call was failing. But I did not execute the individual ISQL statements to confirm that the conditions and result was identical.)

[Former Member · Accepted solution]

And it was an unexpected solution! Changing the IN definition from varchar(128) to char(128) solved the problem.

> ALTER FUNCTION "app_owner"."tifis_get_next_identity"( in as_table char(128) ) 
returns bigint
at
'TIFISServer..app_owner.get_next_identity'

[Breck_Carter · Accepted solution]

Fiddling with remote source data types is a time-honored form of dead chicken... but it is associated more with funky remote sources like Excel, not well-implemented ones like SQL Anywhere 🙂

[VolkerBarth · Accepted solution]

Is there any difference in the settings of the odbc_distinguish_char_and_varchar option?

Otherwise, it would really come as a surprise when SA handles CHAR and VARCHAR that differently... - "CHAR is semantically equivalent to VARCHAR", they say:)

[Breck_Carter · Accepted solution]

Bill, you are my muse! (see next Monday's post in sqlanywhere.blogspot.com)

[VolkerBarth · Accepted solution]

Bill, you are my muse!

Is he supposed to be glad now? 🙂

[Former Member · Accepted solution]

it would really come as a surprise when SA handles CHAR and VARCHAR that differently

Yep! That's why the solution was such a surprise to me. I have re-confirmed with Foxhound, that all my options (odbc_distinguish_char_and_varchar) are set at their default values.

I also realized, once I had found the solution of course, that my stored proc had been running successfully for several months, but this was the first time this line of code was needed. So it wasn't really a new problem, but had been lurking in the shadows all along. I think I remember several one-liners about assuming...

I will be on vacation Monday but I now know for sure I will be checking your blog on the road!!

[jack_schueler]

I cannot reproduce any error. I am running 11.0.1.2913 as the remote and 16.0.0.1324. I used the following test table on the remote.

create table app_owner.crregs_central_registry( 
    akey bigint default autoincrement, adata char(30) );
insert into app_owner.crregs_central_registry values ( 2147483647, 'Jack' );

Here is my remote server set up.

CREATE SERVER TIFISServer
CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere Native;HOST=SCHUELER:49152;Server=demo11;DBN=demo';

CREATE OR REPLACE FUNCTION "app_owner"."tifis_get_next_identity"( in as_table varchar(128) ) 
returns bigint
at 'TIFISServer..app_owner.get_next_identity';

CREATE EXTERNLOGIN app_owner
TO TIFISServer
REMOTE LOGIN DBA IDENTIFIED BY 'sql';

SELECT app_owner.tifis_get_next_identity('crregs_central_registry');

Perhaps I am missing something about the way you set things up.

[Former Member]

The only minor difference I see is that I use a connect string with a DSN. I will look for other ways to isolate the problem. Since your scenario works fine, I am now expecting to find some small detail that is producing a mis-leading result. Thanks Jack.