SA17 ERROR handling Spatial SRID in stored PROCEDURE

[former_SQLA_member1694873]

I'm getting an error reading spatial data using a stored procedure.

First create some data:

CREATE OR REPLACE TABLE "DBA"."TEST" (
    "ID" INTEGER,
    "FEATURE_GEOMETRY" ST_Geometry(SRID=3857) NULL,
    PRIMARY KEY ( "ID" ASC )
) IN "system";
INSERT INTO "DBA"."TEST" ("ID","FEATURE_GEOMETRY") VALUES(1,'Point (-79.3832 43.6682)');

Then try to retrieve it via a stored procedure:

CREATE OR REPLACE PROCEDURE TEST()
BEGIN
    SELECT * FROM TEST;
END;
SELECT * FROM TEST();

This produces the error: Transform from SRID 3857 to 2147483647 not supported.

However, it works if I add the following RESULT clause to the procedure:

CREATE OR REPLACE PROCEDURE TEST()
RESULT (ID INTEGER, FEATURE_GEOMETRY TEST.FEATURE_GEOMETRY%TYPE )
BEGIN
    SELECT * FROM TEST;
END;
SELECT * FROM TEST();

I would have expected the implied RESULT would use the data types from the base table.

I'm running SQL Anywhere is 17.0.0.1358.

[VolkerBarth]

FWIW, the same behaviour with 17.0.4.2053...

The difference seems to be due to the fact that without a result set clause, the ST_Geometry type seems to default to the default SRID.

You can check that with sa_describe_query:

select name, domain_name_with_size
from sa_describe_query('SELECT * FROM TEST()')
where column_number = 2

returns

FEATURE_GEOMETRY;ST_Geometry(SRID=2147483647)

for the procedure without RESULT CLAUSE and

FEATURE_GEOMETRY;ST_Geometry(SRID=3857)

for the one including that clause.

Yes, I'm aware that this is just an observation and no explanation...:)

[former_SQLA_member1694873]

Thanks Volker. That's what I figured was going on, but I hadn't thought of using sa_describe_query() to 'prove' it.