Undocumented "Invalid parameter type" message can be a big time-waster

[Breck_Carter]

The error message "Invalid parameter type" is not documented, but it can be returned from a proxy procedure call via the uninformative catch-all SQLCODE -660 exception:

SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p002': [SAP][ODBC Driver]Invalid parameter type

With -660 your ONLY hope at finding out what went wrong is the ERRORMSG() text, and in this case "Invalid parameter type" is both undocumented AND misleading.

---

One cause is a local (proxy) CREATE PROCEDURE AT statement that specifies a procedure parameter where the remote procedure doesn't have any parameters (how is "missing" an "invalid type"? ...oh, never mind 🙂

Even if you CALL the procedure with no parameters (e.g., CALL p(); ) you will still get "Invalid parameter type", presumably because the CREATE PROCEDURE AT statement contains that bogus parameter definition.

Proxy procedures are wonderful things, and worth the effort.

Here is a reproducible, followed by code that works OK because the remote procedure DOES have a parameter...

---------------------------------------------------------------------
-- On ddd2 (remote)

BEGIN
   DROP PROCEDURE p;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE PROCEDURE p() -- no parameter definition
   RESULT ( @r CHAR ( 50 ) )
BEGIN
   DECLARE @r CHAR ( 50 );
   SET @r = 'no parameter definition';
   SELECT @r;
END;

---------------------------------------------------------------------
-- On ddd1 (local) CREATE PROCEDURE ... AT ...

BEGIN
   DROP PROCEDURE proxy_p;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER ddd2_server;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'ENG=ddd2; DBN=ddd2; UID=dba; PWD=sql; DRIVER=SQL Anywhere 17;';

CREATE PROCEDURE proxy_p ( 
   @v CHAR ( 50 ) DEFAULT 'Default value' ) -- parameter definition
   RESULT ( @r CHAR ( 50 ) )
   AT 'ddd2_server...p';

SELECT * FROM proxy_p ( 'Hello' );
SELECT * FROM proxy_p ( 'World' );
SELECT * FROM proxy_p();

---------------------------------------------------------------------
Server 'ddd2_server': [SAP][ODBC Driver]Invalid parameter type
SQLCODE=-660, ODBC 3 State="HY000"

@r                                                 
-------------------------------------------------- 
(0 rows)


Server 'ddd2_server': [SAP][ODBC Driver]Invalid parameter type
SQLCODE=-660, ODBC 3 State="HY000"

@r                                                 
-------------------------------------------------- 
(0 rows)


Server 'ddd2_server': [SAP][ODBC Driver]Invalid parameter type
SQLCODE=-660, ODBC 3 State="HY000"

@r                                                 
-------------------------------------------------- 
(0 rows)

---

Here is code that works because it has been fixed...

---------------------------------------------------------------------
-- On ddd2 (remote)

BEGIN
   DROP PROCEDURE p;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE PROCEDURE p ( 
   @v CHAR ( 50 ) DEFAULT 'Default value' )
   RESULT ( @r CHAR ( 50 ) )
BEGIN
   DECLARE @r CHAR ( 50 );
   SET @r = @v;
   SELECT @r;
END;


---------------------------------------------------------------------
-- On ddd1 (local) CREATE PROCEDURE ... AT ...

BEGIN
   DROP PROCEDURE proxy_p;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER ddd2_server;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'ENG=ddd2; DBN=ddd2; UID=dba; PWD=sql; DRIVER=SQL Anywhere 17;';

CREATE PROCEDURE proxy_p ( 
   @v CHAR ( 50 ) DEFAULT 'Default value' )
   RESULT ( @r CHAR ( 50 ) )
   AT 'ddd2_server...p';

SELECT * FROM proxy_p ( 'Hello' );
SELECT * FROM proxy_p ( 'World' );
SELECT * FROM proxy_p();

---------------------------------------------------------------------
@r                                                 
-------------------------------------------------- 
Hello                                              
(1 rows)

@r                                                 
-------------------------------------------------- 
World                                              
(1 rows)


@r                                                 
-------------------------------------------------- 
Default value                                      
(1 rows)

[Breck_Carter]

One might ask, "surely you would notice if the parameter was missing from the procedure you were calling?", and you would (should) be correct.

Of course, that assumes you look at the actual remote procedure code on the actual remote database, and not the source code copy of the procedure that is supposed to be there.

If the message had said "missing" then this would have been just another simple bug among many...

but noooo, the cloaking device said "invalid type" 🙂

[VolkerBarth]

As the error message at least came from the remote server, would you have received more information via cis_option = 7? (Of course I agree that the error message is misleading...)

[VolkerBarth]

Another one for your list of characteristic errors? 🙂

[Breck_Carter]

> would you have received more information via cis_option = 7?

Quite possibly... something else I didn't think of.

[Breck_Carter]

...and the answer is no. The only new information that cis_option = 7 provides is another undocumented error code HY105. There are some Google hits on HY105 but they don't seem to be helpful OR related to SQL Anywhere at all.

I. 03/25 09:07:11.     TCPIP link started successfully
I. 03/25 09:07:11. Now accepting requests
I. 03/25 09:10:54. Connect to 'ddd2_server' class 'saodbc' using 'ENG=ddd2; DBN=ddd2; UID=dba; PWD=sql; DRIVER=SQL Anywhere 17;'
I. 03/25 09:10:54. Connected to 'dbodbc17.dll' version '17.00.4882' odbc version '03.52'
I. 03/25 09:10:54. Server name = 'ddd2'
I. 03/25 09:10:54. DBMS name = 'SQL Anywhere'
I. 03/25 09:10:54. DBMS version = '17.00.0009'
I. 03/25 09:10:54. ODBC conformance = level 2
I. 03/25 09:10:54. ODBC SQL conformance = Extended grammar
I. 03/25 09:10:54. SQL identifier case = Case insensitive
I. 03/25 09:10:54. Maximum connections = 0
I. 03/25 09:10:54. Maximum statements = 0
I. 03/25 09:10:54. SQLGetData extensions = any column, any row, any order 
I. 03/25 09:10:54. Search pattern escape = '~'
I. 03/25 09:10:54. Concatenation null behavior = SQL_CB_NON_NULL
I. 03/25 09:10:54. Cursor commit behavior = SQL_CC_PRESERVE
I. 03/25 09:10:54. Cursor rollback behavior = SQL_CR_PRESERVE
I. 03/25 09:10:54. Cursor types = value concurrency, rowver concurrency, locking concurrency, read only 
I. 03/25 09:10:54. Read only datasource = 'N'
I. 03/25 09:10:54. Correlation names are supported
I. 03/25 09:10:54. Transactions that contain DDL are automatically committed
I. 03/25 09:10:54. Default isolation = read uncommitted 
I. 03/25 09:10:54. Multiple result sets = 'Y'
I. 03/25 09:10:54. Multiple transactions = 'Y'
I. 03/25 09:10:54. Isolation options = serializable, repeatable read, read committed, read uncommitted 
I. 03/25 09:10:54. Outer join capabilities = inner, not ordered, all comparison ops, nested, full, right, left 
I. 03/25 09:10:54. Maximum owner name length = 128
I. 03/25 09:10:54. Maximum qualifier name length = 0
I. 03/25 09:10:54. Maximum table name length = 128
I. 03/25 09:10:54. Execute RPC (ddd2_server): {CALL "p"(?)}
I. 03/25 09:10:54. ODBC error (HY105). [SAP][ODBC Driver]Invalid parameter type
I. 03/25 09:10:54. Execute RPC (ddd2_server): {CALL "p"(?)}
I. 03/25 09:10:54. ODBC error (HY105). [SAP][ODBC Driver]Invalid parameter type
I. 03/25 09:10:54. Execute RPC (ddd2_server): {CALL "p"(?)}
I. 03/25 09:10:54. ODBC error (HY105). [SAP][ODBC Driver]Invalid parameter type

[Breck_Carter]

I don't think so... this was just a dumb programmer error, not related to some aspect of SQL Anywhere that ENCOURAGES the error (which is what would make it a characteristic error)

[VolkerBarth]

Ah, I see, guess that (probably short) list of dumb programmer errors is not yet published:)