The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 29 Apr '13, 17:56

Bill%20Aumen's gravatar image

Bill Aumen
2.0k294273
accept rate: 16%

edited 29 Apr '13, 18:11

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262


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

answered 10 May '13, 00:55

Bill%20Aumen's gravatar image

Bill Aumen
2.0k294273
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
1

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 sqlanywhere.blogspot.com)

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

permanent link

answered 30 Apr '13, 10:23

JBSchueler's gravatar image

JBSchueler
1.9k2836
accept rate: 16%

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×187
×137
×46
×25

question asked: 29 Apr '13, 17:56

question was seen: 1,261 times

last updated: 10 May '13, 11:02