insert from proxy table with "on existing update" fail ! BUG ?

#21

Thanks very much for providing the details!

Here's the story on collations and proxy tables: "There is nothing in
Remote Data Access that handles different collations; we have no idea
what collations are supported on other products, what the names of
those collations are, or how they map to SQL Anywhere collations."

As far as case sensitivity is concerned, I was gently told to RTFM
<g>... here is the relevant section from the Version 8.0.2 Help:

=====
Adaptive Server Anywhere SQL User's Guide
15. Accessing Remote Data
Troubleshooting remote data access
Case sensitivity

The case sensitivity setting of your Adaptive Server Anywhere database
should match the settings used by any remote servers accessed.

Adaptive Server Anywhere databases are created case insensitive by
default. With this configuration, unpredictable results may occur when
selecting from a case sensitive database. Different results will occur
depending on whether ORDER BY or string comparisons are pushed off to
a remote server or evaluated by the local Adaptive Server Anywhere.
=====

I checked the V6 Help (when proxy tables were introduced, and the
V11.0.1 Help (the current version) and they say exactly the same
thing.

Well, not *exactly*... ASA changed to SA along the way

So, in your case, you are absolutely correct to copy the data and deal
with it locally.

But "evil"? Not so much, IMO.

Breck

On 17 Nov 2009 04:56:08 -0800, Krisztián Pintér
<pinter.krisztian (AT) karatnet (DOT) hu> wrote:

Quote:

On Tue, 17 Nov 2009 12:33:09 +0100, Breck Carter [TeamSybase]
NOSPAM__breck.carter (AT) gmail (DOT) com> wrote:

Can you provide a simple reproducible? I am 100% sure that the
engineers at iAnywhere will be interested in it.

no, but i can tell you some details from head:

it was a proxy table to an odbc link to some file based data, like dbase
or paradox. don't laugh, our customers sometimes provide us with such data.

on one of the columns, there was an index, a case sensitive, cp852 or
whatever DOS code page.

the query was something like

select *
from proxy_table
join local_table on proxy_table.col1 = local_table.col2

and it returned only a small subset of the expected matches. but if we use

select *
from proxy_table
join local_table on ''||proxy_table.col1 = ''||local_table.col2

all the needed rows are returned. it turns out that the missing lines has
case-different or accent-different spelling, which should be treated as
equal by our collation. but it seems that the join is performed as a
nested loop with table scan on local_table and index lookup on proxy_table.
if the fields are tricked to be expressions, index lookups are rendered
impossible, and the result is correct.

ah, and we use a rather archaic ASA 8.0.2.4542

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

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail

#22

Here's the story on collations and proxy tables: "There is nothing in
Remote Data Access that handles different collations; we have no idea
what collations are supported on other products, what the names of
those collations are, or how they map to SQL Anywhere collations."

As far as case sensitivity is concerned, I was gently told to RTFM
<g>... here is the relevant section from the Version 8.0.2 Help:

=====
Adaptive Server Anywhere SQL User's Guide
15. Accessing Remote Data
Troubleshooting remote data access
Case sensitivity

The case sensitivity setting of your Adaptive Server Anywhere database
should match the settings used by any remote servers accessed.

Adaptive Server Anywhere databases are created case insensitive by
default. With this configuration, unpredictable results may occur when
selecting from a case sensitive database. Different results will occur
depending on whether ORDER BY or string comparisons are pushed off to
a remote server or evaluated by the local Adaptive Server Anywhere.
=====

I checked the V6 Help (when proxy tables were introduced, and the
V11.0.1 Help (the current version) and they say exactly the same
thing.

Well, not *exactly*... ASA changed to SA along the way

So, in your case, you are absolutely correct to copy the data and deal
with it locally.

But "evil"? Not so much, IMO.

Breck

On 18 Nov 2009 02:01:42 -0800, Krisztián Pintér
<pinter.krisztian (AT) karatnet (DOT) hu> wrote:

Quote:

On Wed, 18 Nov 2009 10:34:23 +0100, Volker Barth
No_VBarth (AT) spam_global-finanz (DOT) de> wrote:

Besides their shortcomings, SA proxy tables are really useful in
heterogenous db environments.

evil has to be useful. if a feature is not useful, noone uses it, and
it can't do its evil.

evil things are useful, and thus lure you into using them. and they
provide just enough good things, so you don't give up on them. and
they put the maximum amount of evil on you, that you can bear.

that's what proxy tables do.

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

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail

#23

Trust me, YOUR problem is not being overlooked, within the limitations
of this forum.

Please understand that this forum is operated on a volunteer basis,
and there is NO guaranteed service level. Many iAnywhere employees,
including senior support, professional services, engineers and
management, answer questions here, but they all do so on a volunteer
basis as well.

So, please be patient with us.

Thanks!

Breck Is Not One Of Those Employees

On Wed, 18 Nov 2009 02:08:46 -0800 (PST), Bofcilo <sprintrz (AT) gmail (DOT) com>
wrote:

Quote:

On Nov 18, 10:34*am, Volker Barth <No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de
wrote:
I agree with Breck and his MS Linked Server adventure (been there, too):

Besides their shortcomings, SA proxy tables are really useful in
heterogenous db environments.

That said, I typically do just as you propose: Select into a local temp
table, and then use SA for the further processing

Regards
Volker

Krisztián Pintér schrieb:

"insert into main_table ON EXISTING UPDATE select * from proxy_table"
returns error : << update operation attempted on non-updatable remote
query >> Hmm, i don't update remote (proxy) table, i do update local
table with data from proxy table ....

if you want to go safe, proxy tables should be used only to select data
into temp tables from them, using simple a where clausa, and nothing else.

you can't even trust order by. nor joins.

for example, if you use join, and the proxy table has an index, it might be
used to carry out the join, even if the collation does not match to the
local db.

proxy tables are inherently evil.

This discussion is going to wrong way. I do not ask do you and other
like proxy table. I do not ask what is better Sybase or Microsoft. I
repeat error message from my first post

update operation attempted ON non-updatable remote
query >> Hmm, i don't update remote (proxy) table, i do update local
table with data from proxy table ....

Please focus on this and nothing else. There are two options:
1. It is bug, because i do not update remote query
2. Error message is wrong, may should be like : "you can not use ON
EXISTING UPDATE if you use remote query"

In version ASA7, we use two step. First step is loop through proxy
table and update existing records, and
second for insert new records from proxy table with where clause "not
exists". And everything works fine. We could work on same way, but we
did test new INSERT command and it doesn't work correct.

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

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail

#24

I do not agree that your experience proves proxy tables are evil;
please see my reply to your other post here.

Proxy tables are astoundingly useful. I have based the entire Foxhound
database schema diagnosis and performance monitoring tool on Remote
Data Access. Every single byte of data that Foxhound gathers (and it
can be gigabytes per hour when there are 1000 connections open on the
target database) is gathered via Remote Data Access.

If proxy tables have proved to be less than useful to you, because of
the documented warning about case sensitivity, then it's
understandable that you don't like them.

You are free to call proxy tables "evil". We are also free to
disregard your opinion.

Breck

On 18 Nov 2009 02:01:42 -0800, Krisztián Pintér
<pinter.krisztian (AT) karatnet (DOT) hu> wrote:

Quote:

On Wed, 18 Nov 2009 10:34:23 +0100, Volker Barth
No_VBarth (AT) spam_global-finanz (DOT) de> wrote:

Besides their shortcomings, SA proxy tables are really useful in
heterogenous db environments.

evil has to be useful. if a feature is not useful, noone uses it, and
it can't do its evil.

evil things are useful, and thus lure you into using them. and they
provide just enough good things, so you don't give up on them. and
they put the maximum amount of evil on you, that you can bear.

that's what proxy tables do.

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

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail

#25

I think you already understand how this forum operates, so please
accept my apologies for sounding harsh.

Breck

On 18 Nov 2009 11:50:17 -0800, "Breck Carter [TeamSybase]"
<NOSPAM__breck.carter (AT) gmail (DOT) com> wrote:

Quote:

Trust me, YOUR problem is not being overlooked, within the limitations
of this forum.

Please understand that this forum is operated on a volunteer basis,
and there is NO guaranteed service level. Many iAnywhere employees,
including senior support, professional services, engineers and
management, answer questions here, but they all do so on a volunteer
basis as well.

So, please be patient with us.

Thanks!

Breck Is Not One Of Those Employees

On Wed, 18 Nov 2009 02:08:46 -0800 (PST), Bofcilo <sprintrz (AT) gmail (DOT) com
wrote:

On Nov 18, 10:34*am, Volker Barth <No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de
wrote:
I agree with Breck and his MS Linked Server adventure (been there, too):

Besides their shortcomings, SA proxy tables are really useful in
heterogenous db environments.

That said, I typically do just as you propose: Select into a local temp
table, and then use SA for the further processing

Regards
Volker

Krisztián Pintér schrieb:

"insert into main_table ON EXISTING UPDATE select * from proxy_table"
returns error : << update operation attempted on non-updatable remote
query >> Hmm, i don't update remote (proxy) table, i do update local
table with data from proxy table ....

if you want to go safe, proxy tables should be used only to select data
into temp tables from them, using simple a where clausa, and nothing else.

you can't even trust order by. nor joins.

for example, if you use join, and the proxy table has an index, it might be
used to carry out the join, even if the collation does not match to the
local db.

proxy tables are inherently evil.

This discussion is going to wrong way. I do not ask do you and other
like proxy table. I do not ask what is better Sybase or Microsoft. I
repeat error message from my first post

update operation attempted ON non-updatable remote
query >> Hmm, i don't update remote (proxy) table, i do update local
table with data from proxy table ....

Please focus on this and nothing else. There are two options:
1. It is bug, because i do not update remote query
2. Error message is wrong, may should be like : "you can not use ON
EXISTING UPDATE if you use remote query"

In version ASA7, we use two step. First step is loop through proxy
table and update existing records, and
second for insert new records from proxy table with where clause "not
exists". And everything works fine. We could work on same way, but we
did test new INSERT command and it doesn't work correct.

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

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail

#26

On Nov 18, 8:59*pm, "Breck Carter [TeamSybase]"
<NOSPAM__breck.car... (AT) gmail (DOT) com> wrote:

Quote:

I do not agree that your experience proves proxy tables are evil;
please see my reply to your other post here.

Proxy tables are astoundingly useful. I have based the entire Foxhound
database schema diagnosis and performance monitoring tool on Remote
Data Access. Every single byte of data that Foxhound gathers (and it
can be gigabytes per hour when there are 1000 connections open on the
target database) is gathered via Remote Data Access.

If proxy tables have proved to be less than useful to you, because of
the documented warning about case sensitivity, then it's
understandable that you don't like them.

You are free to call proxy tables "evil". We are also free to
disregard your opinion.

Breck

On 18 Nov 2009 02:01:42 -0800, Krisztián Pintér

pinter.kriszt... (AT) karatnet (DOT) hu> wrote:
On Wed, 18 Nov 2009 10:34:23 +0100, Volker Barth *
No_VBarth (AT) spam_global-finanz (DOT) de> wrote:

Besides their shortcomings, SA proxy tables are really useful in *
heterogenous db environments.

evil has to be useful. if a feature is not useful, noone uses it, and
it can't do its evil.

evil things are useful, and thus lure you into using them. and they
provide just enough good things, so you don't give up on them. and
they put the maximum amount of evil on you, that you can bear.

that's what proxy tables do.

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

SQLA questions and answers:http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databaseshttp://www.risingroad.com/
Breck.Carter at gmail

I agree with Breck about case sensitivity and proxy table. It is
impossible mission to connect with remote servers and don't thinking
about what is behind proxy ? DBF, MySQL, MSSQL and so on... Proxy
table is fantastic feature. And every feature has limitations.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

insert from proxy table with "on existing update" fail ! BUG ?

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

Re: insert from proxy table with "on existing update" fail ! BUG ? - 11-18-2009 , 01:25 PM

Re: insert from proxy table with "on existing update" fail ! BUG ? - 11-18-2009 , 01:26 PM

Re: insert from proxy table with "on existing update" fail ! BUG ? - 11-18-2009 , 01:50 PM

Re: insert from proxy table with "on existing update" fail ! BUG ? - 11-18-2009 , 01:59 PM

Re: insert from proxy table with "on existing update" fail ! BUG ? - 11-18-2009 , 02:19 PM

Re: insert from proxy table with "on existing update" fail ! BUG ? - 11-18-2009 , 03:04 PM