Proxy tables live update or one time only

[Former Member]

Hi, I am still learning and READING and READING... Proxy tables, I can create them, remove them, I just don't fully understand them.

Are they only a link to the existing table or are the a temp copy of the table with no update or are they a separate table that is updated and changed as the original table is updated and changed?

Maybe I have read it and just not understood, can some one with the knowledge explain to me how the proxy is associated with the original table. This is a set of proxy tables in a separate data base same machine/server. I use:

myConnATT.Execute "Create existing table WOSTATUS at 'autotiretech...WOSTATUS'; select * from WOSTATUS;"

thanks George

[Former Member]

I am also finding that this works on the machine the databases are on but doing in the VB code on a remote machine using ODBC tcpip connection it fails, or better this fails:

myConnATT.Execute "Create existing table ADDON AT 'AutoTireTech...ADDON' ; select * from ADDON ;"

with a authentication violation.

the user login has REMOTE DBA access. I can drop tables, just having a problem creating them from the client.

I so do appreciate any suggestions, I don't ask until my eyes are burning from exhaustion.

thanks

george

[VolkerBarth]

I'm somewhat puzzled as to the REMOTE DBA permission - that is a feature used for MobiLink and SQL Remote:

The REMOTE DBA authority grants a limited set of DBA permissions to SQL Remote or MobiLink synchronization users.

From your question itself, I would conclude you simply are using an ordinary connection from your machine to a database located on a different machine, which has nothing to do with MobiLink or SQL Remote.

For CREATE EXISTING TABLE, RESOURCE or DBA authority is needed, REMOTE DBA won't suffice (unless used during synchronization/replication).

[jeff_albion]

"with a authentication violation", meaning SQLCODE -98: http://dcx.sybase.com/index.html#1201/en/saerrors/errm98.html ?

If so, see: http://dcx.sybase.com/index.html#1201/en/dbadmin/running-s-4726602.html and this question.

[VolkerBarth]

OK, that would be a different "beast" then (aka missing OEM permissions)...

[VolkerBarth · Accepted solution]

Well, think of a proxy table as a particular form of view:

• A regular view (in contrast to a materialized view) does not contain data itself but makes data from the underlying tables available. - If the view is updateable, DML statements will modify the data in the underlying tables.
• Similar, a proxy table does not contain data itself but makes data available from the underlying remote data source. - If the proxy table is updateable (which will depend on the permissions of both the local and remote tables and the readonly attribute of the remote server), DML statements will modify the data in the underlying remote table.

In other words, the data is not cached locally but is stored in the remote table, so your idea of a "link" might be most appropriate.

[MarkCulp]

Proxy tables are a link to a table in another (external) database. Each time you reference a proxy table in a query the appropriate contents from the 'remote' table are retrieved. SQL Anywhere does not cache any contents of the remote table.

Another resource that may be of help is my 2001 Techwave presenation: EM413 Using Adaptive Server Anywhere's Remote Data Acess Feature (Thanks to Breck for finding/compiling the list of presentations - see Tales from the Doc Face)

[Breck_Carter]

Thanks for the shout-out!

Techwave 2000, 2003 and 2004 presentations have been unearthed and will be added to the list shortly... plus there's a brand-new article just published: SQL Anywhere Spatial Data Synchronization Sample by "anon." at http://www.sybase.com/detail?id=1067224

[Breck_Carter]

If you want to run a CREATE statement on database 1 (the local database) and have that statement create (a) a new real table on database 2, plus (b) a proxy table on database 1 that is linked to the new table on database 2, you cannot use CREATE EXISTING TABLE... simply because no table exists yet.

There are two ways to create a proxy table...

CREATE EXISTING TABLE local_proxy_table_name AT '...existing_remote_real_table_name';

CREATE TABLE local_proxy_table_name ( [column definitions] ) AT '...new_remote_real_table_name';

From your later comment, it appears you want the second format. Here is an end-to-end demonstration using two brand-new SQL Anywhere databases; note that a real table t2 is created on database ddd2 by a CREATE statement that is executed on database ddd1, a kind of "push create" that you seem to be asking for:

"%SQLANY12%\\Bin32\\dbinit.exe" ddd1.db
"%SQLANY12%\\Bin32\\dbinit.exe" ddd2.db

"%SQLANY12%\\Bin32\\dbspawn.exe" -f "%SQLANY12%\\Bin32\\dbeng12.exe" ddd1.db 
"%SQLANY12%\\Bin32\\dbspawn.exe" -f "%SQLANY12%\\Bin32\\dbeng12.exe" ddd2.db

"%SQLANY12%\\Bin32\\dbisql.com" -c "ENG=ddd1;DBN=ddd1;UID=dba;PWD=sql"
"%SQLANY12%\\Bin32\\dbisql.com" -c "ENG=ddd2;DBN=ddd2;UID=dba;PWD=sql"

---------------------------------------------------------------------
-- On ddd1 (local) CREATE EXISTING TABLE ... AT ...

REATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;ENG=ddd2;DBN=ddd2';

/* or...

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DSN=ddd2';

*/

CREATE EXTERNLOGIN DBA -- optional for SQL Anywhere
   TO ddd2_server 
   REMOTE LOGIN "DBA" IDENTIFIED BY 'sql';

CREATE TABLE proxy_t2 (
   pkey   INTEGER NOT NULL,
   data   INTEGER NOT NULL,
   PRIMARY KEY ( pkey )
 ) AT 'ddd2_server...t2';

INSERT proxy_t2 VALUES ( 1, 2 );
INSERT proxy_t2 VALUES ( 2, 2 );
COMMIT;

SELECT * FROM proxy_t2 ORDER BY proxy_t2.pkey;

/*
pkey,data
1,2
2,2
*/

---------------------------------------------------------------------
-- On ddd2 (remote) display new table

SELECT * FROM t2 ORDER BY t2.pkey;

/*
pkey,data
1,2
2,2
*/

[Former Member]

I 3rd the motion as the previous two answers hit it on the head ...

Indeed, I have used proxy tables linking to Microsoft SQL Server, FoxPro databases, Access datatabase, and the latest and greatest - to MySQL tables on a remote MySQL server on a web host!

[Former Member]

Thanks for the read and info, sometimes it is just hard to find the answer in the piles of books. I am an old unix guy from the 80's (yes there were computers back then). Some of the acronyms are the same but the meanings are different, like cuffs at the bottom of your slacks, every ten years they are in again.

Okay, this tells me what I need, I can create the proxy's once and if things are right, they are 'linked' and have live update.

Can any one see a reason why I can create the tables local (same machine) but not remote (by running the vb code on a client) using odbc tcpip connection? From my perspective they should be the same.

Any thought?

I don't know how this vote thing works, I did thumbs up and correct answer, is there more I need to do?

[VolkerBarth]

See my comment on your question to the REMOTE DBA permission.

When you are connecting locally vs. from a client machine, do you use the same credentials to connect to the (local) database? (Or otherwise: Is the difference just between running the same VB code from the server machine vs. a client machine?) - If that is the only difference, it should not lead to different behaviour at all.

---

Just to add: In case you are using a ODBC DSN to specify the server's properties (in CREATE SERVER ... USING ...), the ODBC DSN is specific to the machine the local database server is running on, not to the client you are connecting from.

[Breck_Carter]

If you want to run a CREATE statement on database 1 (the local database) and have that statement create (a) an real table on database 2, plus (b) a proxy table on database 1 that is linked to the table on database 2, you cannot use CREATE EXISTING TABLE... simply because no table exists yet.

...see separate answer for more

[Breck_Carter]

...the 80's? You young 'uns had it easy! I'll bet you had your own terminal on your desk! In the 70's we had to queue up to use the departmental keypunch, then wait for hours to see if the code even compiled.

[Former Member]

Reading what I'm replying to, this might be an irrelevant comment, but...

I believe by default the credentials used are the login/pass of the "calling" database (i.e. the database you're in). There is a "CREATE EXTERNLOGIN" command used to change this to an arbitrary login/pass based on a specific user (unfortunately you cannot set the login for groups of users)

Any login/pass information you specify will be used only when you first attempt to use a proxy table. One possible workaround I've seen is something like "call sp_remote_tables('remote_server_name')", which will create a connection without a proxy table being involved.

If you're attempting an integrated login then it will most likely be using the rights of the DBSRV/DBENG process of the "calling" database i.e. if you are using a service then it would use SYSTEM, if you're running through a shortcut (or the engine is dynamically launched through ODBC settings) then it would be your local desktop login.

[Former Member]

I replaced one of those machines at a base near Ramstien Germany... with a Plexus... I am losing it on this. one time it will work, one time it will not and it is just driving me nuts.

I will put together a video of what is happening. Maybe it is just me.

[VolkerBarth]

AFAIK, you're fully right w.r.t. to the mapping of local and remote credentials (which still is a mystery to me in times:)).

To clarify: My comment was simply focussed on the local connection itself, as I have assumed the CREATE TABLE would fail for missing local permissions. - I'm still not sure whether this is fitting here or not.