Question Re: Procedure 'openxml' not found (SA16 bug?) in Technology Q&A

Procedure 'openxml' not found (SA16 bug?)

former_SQLA_member1694875 — Fri, 07 Jul 2017 04:15:21 GMT

SQL Anywhere 16.0.0.2471

We get an error message "Procedure 'openxml' not found" when calling OPENXML operator (till SA12 it was called 'system procedure' in the docs) with quotes, e. g.:

select test from "openxml"(null,'/Test') with(test integer);

This way it works fine:

select test from openxml(null,'/Test') with(test integer);

SA11 works fine in both cases.

Is it a bug or I am missing something about such behavior change?

I wouldn't have asked about it (as this problem seems trivial) but we have lots of procedures affected in one SA16 database. I am sure these quotes were sometime added automatically. Other databases do not have these quotes (maybe this is related to the option preserve_source_format as it was turned off in that same database while turned on in others).

Re: Procedure 'openxml' not found (SA16 bug?)

VolkerBarth — Fri, 07 Jul 2017 06:47:44 GMT

Wild guess: That undesired "double quoting" may be related to a (AFAIK) undocumented behaviour change in v16, see that FAQ and the discussion with Chris Keating under the question.

Although this does not explain for me why the first call would fail, other that openxml is not a procedure but "an operator" and therefore has no owner and the like...?

Re: Procedure 'openxml' not found (SA16 bug?)

former_SQLA_member1694875 — Fri, 07 Jul 2017 07:38:53 GMT

I saw that discussion before creating this question but that could only possibly explain the (faulty?) origin of these quotes.

Re: Procedure 'openxml' not found (SA16 bug?)

VolkerBarth — Fri, 07 Jul 2017 07:49:18 GMT

Does that query return different results for your v11 / v16 databases:

select *
from sa_reserved_words()
where reserved_word like 'open%';

or for

select connection_property('non_keywords');

Bottom line: As "openxml" is a reserved word (at least with 12 and above, have not v11 available), it's apparently a bug to enclose that in double quotes - unless you really want to use a stored procedure with that name (and in my understanding, you don't want to...). So that might be a bug w.r.t. unloading/reloading a v11 database in v16.

Re: Procedure 'openxml' not found (SA16 bug?)

former_SQLA_member1694875 — Fri, 07 Jul 2017 08:24:55 GMT

sa_reserved_words() does not exist in SA11. And the second query returns empty strings in both versions.

I would like to accept your bottom line as the answer.

Openxml was named as 'system procedure' till SA12 and renamed to 'operator' in SA16 docs. It seems that it was related to the changes in how double quotes are interpreted.

To conclude, this is NOT a bug of SA16. The bug possibly was in some earlier versions (11 or 12) which added these unnecessary quotes but this does not matter now.

Re: Procedure 'openxml' not found (SA16 bug?)

VolkerBarth — Fri, 07 Jul 2017 08:44:47 GMT

Openxml was named as 'system procedure' till SA12 and renamed to 'operator' in SA16 docs.

Ah, yes, my oversight. Nevertheless, with v12.0.1.4403, openxml is not listed with sysprocedure and is listed as reserved word, so I guess it is treated as "operator" there, too.

BTW: You say with option preserve_source_format='On' the false quoting did not happen? I'd recommend to use that setting anyway:)