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) |
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" :)
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...)
Another one for your list of characteristic errors? :)
> would you have received more information via cis_option = 7?
Quite possibly... something else I didn't think of.
...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 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)
Ah, I see, guess that (probably short) list of dumb programmer errors is not yet published:)