Possible Sybase Bug: "Update operation attempted on non-updatale remote query"

[Markus KARG]

I like to update a locale table using data taken from a remote table, so I
do:

UPDATE LocalTable
SET localField = remoteField
FROM MyRemoteTable
WHERE otherRemoteField = otherLocalField

Obviously the above SQL command expresses clearly that the update shall
happen only on the local table, but dbisql tells me:

"Update operation attempted on non-updatable remote query" (SQLCODE -728)

The documentation says:

"You attempted an update or delete operation on a remote table that is
currently not implemented. Examples of such operations include positioned
updates and updates that require building of work tables."

Well, I do NOT attempt to update or delete ON A REMOTE TABLE.

So is that a Sybase bug, is something missing in the documentation?

And what is the correct way to do the above task?

Thanks
Markus

[Andrei Iliev]

I also saw this behavior in ASA 9.0.2 and SA 11. Actually it does not
depends on whether remote server was declared as a readonly or not.

=========
Andrei

Markus KARG пишет:

[Karim Khamis [Sybase iAnywhere]]

Andrei and Markus,

This is not a bug in the software. Updates involving a join between
remote and local tables are not allowed. The documentation needs to be
changed to indicate that it does not matter whether the table being
updated is local or remote. I will send a note to the documentation team
to correct the error in the documentation.

As far as how to get around the problem, I really have no suggestion
other than to use a temporary table and insert the data from the remote
table into the temp table. I am hoping somebody else on this newsgroup
can give you a better workaround.

Karim

[Volker Barth]

Markus,

I agree that one should suspect that such an update is doable but my
experience has told me to use a local temp table here, typically inside a
stored proc, i.e.

a) create a local temp table with the necessary fields from the remote
table,
b) fill the local temp table with remote data,
c) join your local table with the temp table - with the full join
capabilities.

Maybe not the real solution but a workaround?

HTH
Volker

"Markus KARG" <ka...@quipsy.de> wrote in news:49bf85c5@forums-1-dub...

[Markus KARG]

Volker,

thanks for the workaround, yes that should work (while it is a bit lot of
unnecessary work due to the fact that SA11 has no "DECLARE TABLE FROM
SELECT" command to copy the result set's structure).

But still the open question is whether that is a bug and should get reported
to Sybase?

Regards
Markus

"Volker Barth" <No_VBarth@Spam_GLOBAL-FINANZ.de> schrieb im Newsbeitrag
news:49bf9995$1@forums-1-dub...

[Markus KARG]

Karim,

nice that we can convince Sybase to update the documentation to make clear
that UPDATE ... FROM actually is what the documentation means with "JOIN"
even if there neither is a comma or the JOIN keyword involved! :-)

On the other hand, I do not understand why SA11 is not support that, since
it could just use a temporary table on its own (implicitly) without forcing
me to write code like this:

BEGIN
DECLARE LOCAL TEMPORARY TABLE LocalCopy (a INTEGER PRIMARY KEY, b
VARCHAR(200)) NOT TRANSACTIONAL;
INSERT INTO localCopy SELECT a, b FROM RemoteTable;
UPDATE LocalTable SET localFieldX = b FROM LocalCopy WHERE localFieldY =
a;
END

(which BTW works pretty fine)

A main problem with this workaround is that the average application
programmer doesn't know the difference physical between local and remote
table but just sees a logical view, so he is bit of upset when he notices
that some FROMs ARE working while others create exceptions...

Regards
Markus

"Karim Khamis [Sybase iAnywhere]" <kkh...@sybase.com> schrieb im
Newsbeitrag news:49bf9c37$1@forums-1-dub...

[Markus KARG]

Karim,

so why not accepting this as a feature proposal? In fact I do not see that
it is a big problem for the server to create such a work table, as it was no
problem for me to build the temporary table, too. What is the problem with
that? Why not creating a work table?

Regards
Markus

"Karim Khamis [Sybase iAnywhere]" <kkh...@sybase.com> schrieb im

Newsbeitrag news:49bfa23c$1@forums-1-dub...
> Markus,
>
> The problem is that in order to perform this update, the server would need
> to build a work table; and if you look at the description of the error,
> you can see that such updates are not allowed if they require the building
> of work tables.
>
> Karim

[Karim Khamis [Sybase iAnywhere]]

Volker,

Suppose your remote table is named remtab; then executing something like:

SELECT * INTO #temp FROM remtab

should create a local table with the same schema as remtab and all of
the data moved from the remote table into #temp.

Karim

[Karim Khamis [Sybase iAnywhere]]

Markus,

The problem is that in order to perform this update, the server would
need to build a work table; and if you look at the description of the
error, you can see that such updates are not allowed if they require the
building of work tables.

Karim

[Karim Khamis [Sybase iAnywhere]]

Sorry Volker, the last note was actually answering a question from Markus.

Markus, take note that in addition to using *, you could also specify a
column list in order to restrict the number of columns involved as well
as a where clause to restrict the number of rows being fetched from the
remote. So, something like the following will also work fine:

SELECT c2, c3 INTO #temp FROM remtab where c1 < 100

Karim

[Markus KARG]

Wow, yes, that is working pretty great! Thanks for this tip!

Just two questions about that:

(a) Is that a special Sybase trick, or is that ANSI SQL?

(b) I am using SQL Anywhere since 5.0. When was that invented and why was I
so blind not to see it?

Thanks!
Markus

"Karim Khamis [Sybase iAnywhere]" <kkh...@sybase.com> schrieb im

Newsbeitrag news:49bfa36d$1@forums-1-dub...

[Karim Khamis [Sybase iAnywhere]]

No, this feature is not part of the ANSI SQL standard. The feature is in
ASE and SQL Server, and was put into SA as part of the TSQL
compatibility feature. I believe the support has been in SA since 5.0.

Karim

[Volker Barth]

"Markus KARG" <ka...@quipsy.de> wrote in news:49bf9e65$1@forums-1-dub...

>
> A main problem with this workaround is that the average application
> programmer doesn't know the difference physical between local and remote
> table but just sees a logical view, so he is bit of upset when he notices
> that some FROMs ARE working while others create exceptions...
>

IMHO, just as the average programmer is going to use a somewhat complex
query he will unevitably notice the difference between local and remote
table access - as Breck Carter states, the later sometimes feel like
"running on a geological time scale":)
I think there are a *lot* of restrictions w.r.t. remote access in contrast
to local access when more than really simple statements are concerned, and I
remember a *lot* of situations where I had to finetune such statements to
make them run in acceptable time. But of course, remote access has its very
uses.

Volker

[Glenn Paulley [Sybase iAnywhere]]

SELECT INTO #TEMP_TABLE is a Sybase Transact-SQL extension, and is not
in the ANSI standard.

It has been supported in SQL Anywhere since at least the 5.5 release.

Glenn

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport

[Volker Barth]

"Markus KARG" <ka...@quipsy.de> wrote in news:49bfa989$1@forums-1-dub...

> Wow, yes, that is working pretty great! Thanks for this tip!
>

> (b) I am using SQL Anywhere since 5.0. When was that invented and why was
I
> so blind not to see it?
>
> Thanks!

SELECT INTO #Temp was at least supported since V 5.5 (I guess, as a
Transact-SQL feature).

Regards
Volker

[Markus KARG]

Sad that it is not an ANSI SQL feature -- could imagine a lot of nice use
cases, e. g. not using dozens of variable DECLAREs when keeping temporary
data in stored procedures etc.

BTW, I just checked the SA 8.0.2 manual and it in the section about the
SELECT INTO command it doesn't tell a word about that feature. The SA11
manual contains it correctly. Seems that is the reason why I did not know
it.

Regards
Markus

"Karim Khamis [Sybase iAnywhere]" <kkh...@sybase.com> schrieb im

Newsbeitrag news:49bfac31$1@forums-1-dub...

[Glenn Paulley [Sybase iAnywhere]]

In 8.0.2 SELECT INTO #TEMP was documented under "Transact-SQL
Compatibility".

Adaptive Server Anywhere SQL User's Guide
12. Transact-SQL Compatibility
Writing compatible SQL statements

Writing compatible queries

Glenn

--

[Markus KARG]

I understand. So Sybase meanwhile luckily understood that INTO #TEMP is
something that is not just a compatibility burden but that it is a really
great feature. Are there more great things like this, documented in the
"wrong" place? :-)

"Glenn Paulley [Sybase iAnywhere]" <pau...@ianywhere.com> schrieb im
Newsbeitrag news:49bfbeba$1@forums-1-dub...

[Glenn Paulley [Sybase iAnywhere]]

We try hard - very hard - to ensure that new features are documented in
the "new features" section with each release. Obviously, however,
something can be a "new feature" only once. I also hope you can
appreciate that SQL Anywhere is a complex product that offers oodles of
SQL language extensions and compatibility with other relational
products, so it is very difficult to retain 100% accuracy in the
documentation (though I would argue that we're usually pretty close).

Glenn

[Markus KARG]

Sure I appreciate it. I just am angry that I was not using that feature for
the last ten years since I just didn't read the chapter about ASE -- because
I did not care about ASE.

"Glenn Paulley [Sybase iAnywhere]" <pau...@ianywhere.com> schrieb im

Newsbeitrag news:49bfeb65$1@forums-1-dub...

[Breck Carter [TeamSybase]]

I have never liked the segregation of Transact SQL syntax into a
documentation ghetto; e.g., SELECT has two topics in 8.0.2 and here is
the one you missed:

Adaptive Server Anywhere SQL User's Guide
12. Transact-SQL Compatibility
Writing compatible SQL statements
Writing compatible queries

Why am I not surprised that folks missed it? <g>

FWIW in my book it's right up there in the section about creating
local temporary tables.

Breck

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck....@risingroad.com

[Breck Carter [TeamSybase]]

On 17 Mar 2009 08:34:40 -0800, "Markus KARG" <ka...@quipsy.de> wrote:

>Are there more great things like this, documented in the
>"wrong" place?

Yes, there are.

Well, not quite as hidden as SELECT INTO #t in the V8 docs, but
ROLLBACK TRIGGER comes to mind, as well as RAISERROR (no, that's not a
speling misteak, there is only one E). Transact SQL compatibility was
added to the product in V5 and has been maintained and enhanced ever
since, but historically there has been some anti-Transact-SQL bigotry
that prevented some people from promoting features to folks NOT coming
from an ASE/MSS environment.

Disclosure: I am an Anti-Transact-SQL bigot, but I acknowledge that
there are a handful of statement- and lower-level features that are
useful... VERY useful.

Anyway, by V11 the docs had merged the SELECT INTO #t syntax into main
topic... and 11.0.1 introduced the explicit SELECT ... INTO LOCAL
TEMPORARY TABLE t (no more # required).

At last count, there are about 57 different kinds of temporary tables,
I'm getting too old to keep up :)

The last time I read the docs cover-to-cover in one go was V6. It's
been a struggle ever since, it's probably time to do it again... sigh.

Breck

[Breck Carter [TeamSybase]]

No matter how hard you try, I will always be SCREAMING AT YOU about
the matter... you have the hearing loss to prove it.

I think every developer at iAnywhere should be paired with an
autonomous IvanAnywhere Doc Robot looking over the developer's
shoulder: "Is that a new feature? Is it? Is it? Tell me about it!"

These robots might not be quite as intrusive as one might think, if
the developer could choose the robot's "skin"... say, Brad Pitt or
Summer Glau... or Bender :)

Breck

On 17 Mar 2009 10:26:45 -0800, "Glenn Paulley [Sybase iAnywhere]"
<pau...@ianywhere.com> wrote:

--

[Volker Barth]

Breck,

just take *YOUR* personal BreckAnywhere robot and let him read, and he will
eventually ask you "Did you know about that feature already?":)
And with the robot's "Senior level" option set, he might even ask "Can you
still remember that feature?".

Bad luck that there is not fast LOAD DOCS to MyMind LOGGING ON statement :)

Volker

"Breck Carter [TeamSybase]" <NOSPAM__br...@gmail.com> schrieb im
Newsbeitrag news:s641s4941q0abvd5e...@4ax.com...

[Breck Carter [TeamSybase]]

Forte Agent NNTP client is playing games with me; just so everyone's
clear, I am screaming at Glenn, not Markus!

Breck

On 17 Mar 2009 22:35:52 -0800, "Breck Carter [TeamSybase]"

[Markus KARG]

Breck,

thanks for your explanations. :-)

I also have to admit that I never cared about that T-SQL compatibility but
it really looks as some of those features a really smart. But in fact, I
appreciate the long-written form "INTO LOCAL TEMPORARY TABLE" as it looks
much more as Watcom-SQL (and yes, I love Watcom-SQL for its clearness, even
if it looks a bit lenghty).

Thanks
Markus

"Breck Carter [TeamSybase]" <NOSPAM__br...@gmail.com> schrieb im
Newsbeitrag news:s641s4941q0abvd5e...@4ax.com...