Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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

Here is the procedure on the remote database:

 ALTER FUNCTION "app_owner"."get_next_identity"( in as_table varchar(128) ) 
 returns BIGINT
   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

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

(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.)

asked 29 Apr '13, 17:56

Bill%20Aumen's gravatar image

Bill Aumen
accept rate: 16%

edited 29 Apr '13, 18:11

Mark%20Culp's gravatar image

Mark Culp

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
permanent link

answered 10 May '13, 00:55

Bill%20Aumen's gravatar image

Bill Aumen
accept rate: 16%

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 :)

(10 May '13, 05:37) Breck Carter

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:)

(10 May '13, 06:00) Volker Barth
Comment Text Removed

Bill, you are my muse! (see next Monday's post in

(10 May '13, 09:18) Breck Carter
Replies hidden

Bill, you are my muse!

Is he supposed to be glad now? :)

(10 May '13, 09:42) Volker Barth

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!!

(10 May '13, 10:38) Bill Aumen

I cannot reproduce any error. I am running as the remote and 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.

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';


SELECT app_owner.tifis_get_next_identity('crregs_central_registry');

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

permanent link

answered 30 Apr '13, 10:23

JBSchueler's gravatar image

accept rate: 20%

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.

(30 Apr '13, 11:51) Bill Aumen
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 29 Apr '13, 17:56

question was seen: 3,226 times

last updated: 10 May '13, 11:02