|
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:
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.) |
|
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' 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
Is he supposed to be glad now? :)
(10 May '13, 09:42)
Volker Barth
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. 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
|
