SA17 ERROR handling Spatial SRID in stored PROCEDURE

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.

asked 16 Jun '16, 15:42

Terry Wilkinson
746●30●35●48
accept rate: 25%

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...:)

(17 Jun '16, 03:52) Volker Barth

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

(17 Jun '16, 11:31) Terry Wilkinson

flat view

Be the first one to answer this question!

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-17 ×222
stored-procedure ×124
data-type ×30
spatial ×24

question asked: 16 Jun '16, 15:42

question was seen: 1,237 times

last updated: 17 Jun '16, 11:31

SAP SQL Anywhere

SA17 ERROR handling Spatial SRID in stored PROCEDURE

Follow this question

Related questions