I have two database. One database has proxy table from second
database. Select from proxy table work fine.
First command is "insert into main_table select * from proxy_table"
and it works fine.
Second command is test for new options for INSERT command, ON
EXISTING ...

"insert into main_table ON EXISTING SKIP select * from proxy_table"
works fine (skip)
"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 ....

BUG ?

You cannot insert values into a proxy table with the ON EXISTING clause. Please
see the documentation:
http://dcx.sybase.com/1101en/dbrefer...statement.html


SQL Anywhere Developer Community:
http://www.sybase.com/developer/libr...ere-techcorner


SQL Anywhere Blog Center: http://www.sybase.com/sqlanyblogs




Bofcilo wrote:

The proxy table is the one on the SELECT side, not the INSERT side.

On 16 Nov 2009 08:43:45 -0800, Bruce Hay
<h_a_y_@_i_a_n_y_w_h_e_r_e_d_o_t_c_o_m> wrote:

--
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

This is a very irritating limitation of remote servers, and it
persists through to version 11.0.1 (I just confirmed that with the
code below).

Could not execute statement.
Remote server does not have the ability to support this statement
SQLCODE=-706, ODBC 3 State="HY000"
Line 43, column 1
You can continue executing or stop.

INSERT t2 ON EXISTING UPDATE
SELECT * FROM proxy_t1

Personally, I'd call it a "bug" since the proxy table is just
providing the data to the INSERT statement, and after the main
database receives the data it can do the ON EXISTING UPDATE logic
without any participation on the part of the remote server process.

Let's just say this has bugged me for years (pun intended).

Breck

---------------------------------------------------------------------
-- Run this on ddd1

BEGIN
DROP TABLE t1;
EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t1 (
pkey INTEGER NOT NULL PRIMARY KEY,
col1 INTEGER NOT NULL );

INSERT t1 VALUES ( 1, 1 );
INSERT t1 VALUES ( 3, 1 );
INSERT t1 VALUES ( 5, 1 );
COMMIT;

---------------------------------------------------------------------
-- Run this on ddd2

BEGIN
DROP TABLE t2;
EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t2 (
pkey INTEGER NOT NULL PRIMARY KEY,
col1 INTEGER NOT NULL );

INSERT t2 VALUES ( 2, 2 );
INSERT t2 VALUES ( 4, 2 );
INSERT t2 VALUES ( 6, 2 );
COMMIT;

BEGIN
DROP TABLE proxy_t1;
EXCEPTION WHEN OTHERS THEN
END;

BEGIN
DROP EXTERNLOGIN DBA TO ddd1;
EXCEPTION WHEN OTHERS THEN
END;

BEGIN
DROP SERVER ddd1;
EXCEPTION WHEN OTHERS THEN
END;

CREATE SERVER ddd1 CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere 11;ENG=ddd1;DBN=ddd1';

CREATE EXTERNLOGIN DBA TO ddd1 REMOTE LOGIN dba IDENTIFIED BY 'sql';

-- The AT owner is optional...
-- Will fail if table not found...

CREATE EXISTING TABLE proxy_t1 AT 'ddd1..DBA.t1';

INSERT t2 ON EXISTING UPDATE
SELECT * FROM proxy_t1;
COMMIT;

SELECT * FROM t2 ORDER BY pkey;





On Mon, 16 Nov 2009 06:58:08 -0800 (PST), Bofcilo <sprintrz (AT) gmail (DOT) com>
wrote:

--
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

My typical workaround around this (and other somewhat surprising
limitations of proxy tables) is to import them into a local temp table
and then join over the temp table - and thereby having full SA SQL support.
My feeling is that statements like INSERT ... IN EXISTING might use some
kind of server-side cursors that just do not work with proxy tables.

That said, I'm still glad of the proxy table feature. IMHO, trying to
access SA data from a MS SQL server is *much* less fun, and I guess
Breck will agree


HTH
Volker


Breck Carter [TeamSybase] wrote:

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.

Ok, "evil", you got my attention

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

If it involves collations, a simple reproducible would consist of:

- dbinit options for the two databases ( I am hoping this can be
demonstrated using two SQLA databases, and that it doesn't require an
Oracle cluster

- simple CREATE TABLE statements; one or two columns.

- simple data used to load the table(s); LOAD TABLE statements and the
associated files are best because the data would remain safe and
untouched as people fiddled around with the code.

- SELECT statements that demonstrate the problem: These statements
would work properly when proxy tables are NOT involved, and
incorrectly when involving proxy tables.

The easiest thing for you would be to send ME the reproducible:
breck.carter at gmail.com

If it really is a bug (and I am not doubting you), I can help you
"tune" the reproducible so it quickly proves your point. The simpler
the reproducible, the quicker the response.

I have a huge personal stake in proxy tables working well, and so far,
ever since they were introduced in version 6 I have been happy. Not
with the performance (which sucks) or the missing features (which make
me nuts) but with the tremendous flexibility.

I have recently had reason to use Microsoft SQL Server Linked Server
feature and guess what? With a single TEXT column containing 1
character I can make MSS 2008 leak memory so badly that in 15 minutes
it blows the server... not just MSS, but the computer has to be
rebooted... 64-bit Core 2 Duo, not some netbook.

Now THAT'S something you don't see with proxy tables

Breck Thinks People Should Check Out My "Signature Block" Below



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

--
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

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


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

On Nov 16, 5:43*pm, Bruce Hay <h_a_y_@_i_a_n_y_w_h_e_r_e_d_o_t_c_o_m>
wrote:
select side !. I think it is BUG.
ON EXISTING SKIP works.

On Nov 16, 8:05*pm, "Breck Carter [TeamSybase]"
<NOSPAM__breck.car... (AT) gmail (DOT) com> wrote:
Breck, first thanks for replying. Second thanks for supporting : it is
BUG. We are waiting for EBF for this .