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%20Wilkinson's gravatar image

Terry Wilkinson
496162136
accept rate: 40%

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
Be the first one to answer this question!
toggle preview

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

Question tags:

×101
×70
×25
×21

question asked: 16 Jun '16, 15:42

question was seen: 152 times

last updated: 17 Jun '16, 11:31