How do I avoid "Count field incorrect" with a proxy function?

Breck_Carter — Sun, 05 Mar 2017 09:32:38 GMT

A simple reference to proxy function using two SQL Anywhere 17.0.4.2100 databases results in a "Count field incorrect" error message.

In particular, a SQL excerpt looks like this...

CREATE FUNCTION proxy_f_test
   ( IN @p   INTEGER )
   RETURNS VARCHAR ( 20 )
   AT 'ddd17a...f_test';

SELECT proxy_f_test ( 1000 );

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Server 'ddd17a': [SAP][ODBC Driver]Count field incorrect
SQLCODE=-660, ODBC 3 State="HY000"

and an excerpt from the "SET OPTION PUBLIC.cis_option = 7" output looks like this:

I. 03/05 09:18:46. Execute RPC (ddd17a): {? = CALL "f_test"(?)}
I. 03/05 09:18:46. ODBC error (07002). [SAP][ODBC Driver]Count field incorrect

The SQLSTATE 07002 is described here: You have not provided enough host variables for either the number of bind variables, or the statement, or the number of SELECT list items.

The full reproducible follows...

REM Test setup

"%SQLANY17%\\bin64\\dbinit.exe"^
  -dba dba,sql^
  -mpl 3^
  ddd17a.db

"%SQLANY17%\\bin64\\dbspawn.exe"^
  -f "%SQLANY17%\\bin64\\dbsrv17.exe"^
  -o dbsrv17_log_ddd17a.txt^
  ddd17a.db 

"%SQLANY17%\\bin64\\dbisql.com"^
  -c "ENG=ddd17a; DBN=ddd17a; UID=dba; PWD=sql; CON=ddd17a-1"

"%SQLANY17%\\bin64\\dbinit.exe"^
  -dba dba,sql^
  -mpl 3^
  ddd17b.db

"%SQLANY17%\\bin64\\dbspawn.exe"^
  -f "%SQLANY17%\\bin64\\dbsrv17.exe"^
  -o dbsrv17_log_ddd17b.txt^
  ddd17b.db 

"%SQLANY17%\\bin64\\dbisql.com"^
  -c "ENG=ddd17b; DBN=ddd17b; UID=dba; PWD=sql; CON=ddd17b-1"

---------------------------------
-- Connection ddd17a-1  -  master

BEGIN
   DROP FUNCTION f_test
   EXCEPTION WHEN OTHERS THEN
END;

CREATE FUNCTION f_test
   ( IN @p   INTEGER )
   RETURNS VARCHAR ( 20 )
BEGIN
   RETURN STRING ( @p, 'ms' );
END;

---------------------------------
-- Connection ddd17b-1  -  client

BEGIN
   DROP FUNCTION proxy_f_test
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN  
   DROP EXTERNLOGIN dba TO ddd17a;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER ddd17a;
   EXCEPTION WHEN OTHERS THEN
END;

SET OPTION PUBLIC.cis_option = 7;

CREATE SERVER ddd17a CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere Native; ENG=ddd17a; DBN=ddd17a; UID=dba; PWD=sql;';

CREATE EXTERNLOGIN DBA
   TO ddd17a 
   REMOTE LOGIN "dba" IDENTIFIED BY 'sql';

CREATE FUNCTION proxy_f_test
   ( IN @p   INTEGER )
   RETURNS VARCHAR ( 20 )
   AT 'ddd17a...f_test';

SELECT proxy_f_test ( 1000 );

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Server 'ddd17a': [SAP][ODBC Driver]Count field incorrect
SQLCODE=-660, ODBC 3 State="HY000"

The console log (dbsrv17 -o dbsrv17_log_ddd17b.txt) looks like this

I. 03/05 09:17:47. SQL Anywhere Network Server Version 17.0.4.2100 I. 03/05 09:17:47. Developer edition, not licensed for deployment.
I. 03/05 09:17:47.  I. 03/05 09:17:47. Copyright © 2016 SAP SE or an SAP affiliate company.
I. 03/05 09:17:47. All rights reserved.
I. 03/05 09:17:47. Use of this software is governed by the SAP Software Use Rights Agreement.
I. 03/05 09:17:47. Refer to http://global.sap.com/corporate-en/our-company/agreements/index.epx.
I. 03/05 09:17:47.  I. 03/05 09:17:47. Processors detected: 8 logical processor(s) on 4 core(s) on 1 physical processor(s)
I. 03/05 09:17:47. Processor license restriction (licensed processors): all logical processors on at most 8 core(s) on any number of physical processors
I. 03/05 09:17:47. This server is licensed to use: all logical processors on at most 8 core(s) on any number of physical processors
I. 03/05 09:17:47. Processors in use by server: 8 logical processor(s) on 4 core(s) on 1 physical processor(s)
I. 03/05 09:17:47. This server is licensed to:
I. 03/05 09:17:47.     Developer Edition
I. 03/05 09:17:47.     Restricted Use
I. 03/05 09:17:47. Running Windows 7 Build 7601 Service Pack 1 on X86_64
I. 03/05 09:17:47. Server built for X86_64 processor architecture
I. 03/05 09:17:47. 51968K of memory used for caching
I. 03/05 09:17:47. Minimum cache size: 51192K, maximum cache size: 15050852K
I. 03/05 09:17:47. Using a maximum page size of 4096 bytes
I. 03/05 09:17:47. Multiprogramming level: minimum:8, current:20, maximum:80
I. 03/05 09:17:47. Automatic tuning of multiprogramming level is enabled
I. 03/05 09:17:47. Starting database "ddd17b" (C:\\projects\\foxhound5\\test\\040 volume tests\\11 Three Foxhounds with 10 target databases each, each of which has 10 connections\\problem_with_proxy_function\\ddd17b.db) at Sun Mar 05 2017 09:17
I. 03/05 09:17:47. Performance warning: Database file "C:\\projects\\foxhound5\\test\\040 volume tests\\11 Three Foxhounds with 10 target databases each, each of which has 10 connections\\problem_with_proxy_function\\ddd17b.db" consists of 16 disk fragments
I. 03/05 09:17:48. Transaction log: ddd17b.log
I. 03/05 09:17:48. Starting checkpoint of "ddd17b" (ddd17b.db) at Sun Mar 05 2017 09:17
I. 03/05 09:17:48. Finished checkpoint of "ddd17b" (ddd17b.db) at Sun Mar 05 2017 09:17
I. 03/05 09:17:48. Database "ddd17b" (ddd17b.db) started at Sun Mar 05 2017 09:17
I. 03/05 09:17:48. Database server started at Sun Mar 05 2017 09:17
I. 03/05 09:17:48. Trying to start SharedMemory link ...
I. 03/05 09:17:48.     SharedMemory link started successfully
I. 03/05 09:17:48. Trying to start TCPIP link ...
I. 03/05 09:17:48. Unable to start on default port; starting on port 49180 instead
I. 03/05 09:17:53.     TCPIP link started successfully
I. 03/05 09:17:53. Now accepting requests
I. 03/05 09:17:56. Cache size adjusted to 51192K
I. 03/05 09:18:46. Connect to 'ddd17a' class 'saodbc' using 'DRIVER=SQL Anywhere Native; ENG=ddd17a; DBN=ddd17a; UID=dba; PWD=sql;'
I. 03/05 09:18:46. Connected to 'dbodbc17.dll' version '17.00.2100' odbc version '03.52'
I. 03/05 09:18:46. Server name = 'ddd17a'
I. 03/05 09:18:46. DBMS name = 'SQL Anywhere'
I. 03/05 09:18:46. DBMS version = '17.00.0004'
I. 03/05 09:18:46. ODBC conformance = level 2
I. 03/05 09:18:46. ODBC SQL conformance = Extended grammar
I. 03/05 09:18:46. SQL identifier case = Case insensitive
I. 03/05 09:18:46. Maximum connections = 0
I. 03/05 09:18:46. Maximum statements = 0
I. 03/05 09:18:46. SQLGetData extensions = any column, any row, any order 
I. 03/05 09:18:46. Search pattern escape = '~'
I. 03/05 09:18:46. Concatenation null behavior = SQL_CB_NON_NULL
I. 03/05 09:18:46. Cursor commit behavior = SQL_CC_PRESERVE
I. 03/05 09:18:46. Cursor rollback behavior = SQL_CR_PRESERVE
I. 03/05 09:18:46. Cursor types = value concurrency, rowver concurrency, locking concurrency, read only 
I. 03/05 09:18:46. Read only datasource = 'N'
I. 03/05 09:18:46. Correlation names are supported
I. 03/05 09:18:46. Transactions that contain DDL are automatically committed
I. 03/05 09:18:46. Default isolation = read uncommitted 
I. 03/05 09:18:46. Multiple result sets = 'Y'
I. 03/05 09:18:46. Multiple transactions = 'Y'
I. 03/05 09:18:46. Isolation options = serializable, repeatable read, read committed, read uncommitted 
I. 03/05 09:18:46. Outer join capabilities = inner, not ordered, all comparison ops, nested, full, right, left 
I. 03/05 09:18:46. Maximum owner name length = 128
I. 03/05 09:18:46. Maximum qualifier name length = 0
I. 03/05 09:18:46. Maximum table name length = 128
I. 03/05 09:18:46. Execute RPC (ddd17a): {? = CALL "f_test"(?)}
I. 03/05 09:18:46. ODBC error (07002). [SAP][ODBC Driver]Count field incorrect

Re: How do I avoid "Count field incorrect" with a proxy function?

VolkerBarth — Sun, 05 Mar 2017 16:11:48 GMT

By using a newer EBF, methinks...

See that rather similar question:

Remote Function error "Count field incorrect" and Jack's answer.

According to the online 17.0.4.2179 EBF readme file, I guess it has been fixed as CR #802464.

Re: How do I avoid "Count field incorrect" with a proxy function?

Breck_Carter — Mon, 06 Mar 2017 07:58:44 GMT

Yes, indeed, that answer is exactly correct.

Tests with SQL Anywhere 16.0.0.2344 EBF and 17.0.6.2806 EBF ( note the point-six !!! ) show the code now works...

SELECT @@VERSION, proxy_f_test ( 1000 );

@@VERSION,proxy_f_test(1000)
'16.0.0.2344','1000ms'

@@VERSION,proxy_f_test(1000)
'17.0.6.2806','1000ms'

Question Re: How do I avoid "Count field incorrect" with a proxy function? in Technology Q&A

How do I avoid "Count field incorrect" with a proxy function?

Re: How do I avoid "Count field incorrect" with a proxy function?

Re: How do I avoid "Count field incorrect" with a proxy function?