Query over two SQL Anywhere 12 databases

How can I query two SQL Anywhere 12 databases? Both databases use the same database server name. I would like to do something like:

select * from SkyArtist.Hades.hades.invoices ih inner join SkyArtist.Periodicals.periodicals.invoices ip on ih.id = ip.hades_invoice_id

How can I run such a command in Interactive SQL? And, later on, as embedded SQL?

In the Sybase documentation I found something about linking to the other database (sp_addlinkedserver) but I don't understand it enough to get it to work. I am also not sure if this command means linking to a SQL Server, not an SQL Anywhere database.

TIA, Robert

asked 05 Sep '13, 03:28

robert
853●47●54●68
accept rate: 0%

edited 10 Sep '13, 05:37

Volker Barth
40.2k●361●550●822

flat view

2 Answers:

active answers oldest answers newest answers popular answers

Here's a snippet of template code...

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 12;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 EXISTING TABLE proxy_t 
   AT 'ddd2_server...t';

/* or...

CREATE EXISTING TABLE proxy_t (
   pkey   INTEGER NOT NULL,
   data   INTEGER NOT NULL,
   PRIMARY KEY ( pkey )
 ) AT 'ddd2_server...t';

*/

SELECT * FROM proxy_t ORDER BY proxy_t.pkey;

permanent link

answered 06 Sep '13, 17:21

Breck Carter
32.5k●541●726●1050
accept rate: 20%

Hi Breck

We are very happy, thanks a lot, the missing piece to be able to make queries was the CREATE EXTERNLOGIN ... now it works great on our OS X 10.8.4 server :-)

(16 Sep '13, 08:14) robert

If Breck's posting helped to answer your requestion, you might want to accept the answer (click on the grey "check" symbol to the left of the answer). This will also mark the question as "answered".

(16 Sep '13, 08:29) Reimer Pods

Yes it did and I accept of course the answer and checked it - thanks for pointing me to accept it :-)

(16 Sep '13, 14:27) robert

flat view

The SELECT you have stated would work on a system like MS SQL Server or ASE (one database engine, one master database, several user databases).

With SQL Anywhere, you cannot access a second database (even if running on the same server) "directly" but with the help of Remote Data Access (aka "proxy tables").

Some links/samples:

Docs on Remote Data Access
Docs: Joins between tables from multiple local databases
FAQ: Import data from databases. (It deals with INSERT, however, selecting does work as well, of course...)

permanent link

answered 05 Sep '13, 04:35

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 05 Sep '13, 07:10

Thanks Volker, for your references.

I could run following statement without error (I just assumed it's 'sajdbc' for CLASS, as there is always odbc referenced and the docu says 'saodbc':

CREATE SERVER remote_periodicals CLASS 'sajdbc' USING 'periodicals';

But the second needed statement gives an error:

CREATE EXISTING TABLE acquisition_types AT 'remote_periodicals...acquisition_types';

saying it's unable to connect to server 'remote_periodicals'

which sounds to me like the first statement is wrong, although there is no error with it.

It would be of much help to have a concrete code example for JDBC, but I can't find one.

(06 Sep '13, 09:20) robert

Replies hidden

Some notes:

I would highly recommend to use the ODBC server class "saodbc" instead of the JDBC-based "sajdbc", since all JDBC-based classes are deprecated as of SA 12 and have been removed in SA 16.
That being said, AFAIK, the CREATE SERVER statement is not "critical" and will usually work. The real test whether you can access the desired tables is delayed until you try to create a proxy table or try to list the remote tables (say, with sp_remote_tables). Only then the local server will really try to access the remote database. (And that's why only then you are facing an error...)
Are the credentials for the local database and the remote one the same? If not, you will need to create an extern login to the remote server (i.e. a mapping of a local user to a remote one).
IMHO, the CREATE SERVER statement should include a complete connection string (USING 'SERVER=...;DBN=...;...") or should refer to an ODBC DSN (USING 'DSN=...').

(06 Sep '13, 16:50) Volker Barth

It might also be noted that even when using JDBC as your preferred API there's nothing wrong with using ODBC here, as it's not your application code (or client) that does establish the remote connection, it's the local database engine itself, and that (as a C/C++-based program) can surely be expected to use ODBC more efficiently than a JDBC connection - it's simply a "more native" choice for the database engine.

(06 Sep '13, 16:54) Volker Barth

Another note: According to the docs, when using calss sajdbc, the using clause for a database server with more than one database (yes, that's apparently your situation) would need to name the database, too, such as USING 'host-name:port-number/periodicals'.

(06 Sep '13, 17:00) Volker Barth

I can in my current environment (SQL Anywhwere 12 on OS X 10.8.4) establish a Server Connection with the Server Class String 'sajdbc', but not with the string 'saodbc'. But I don't know where are the dependencies defined.

(10 Sep '13, 05:26) robert

Replies hidden

Sorry, don't know for OS X, though SA should contain an ODBC manager library for Unix systems (including OS X), cf. the docs on ODBC under Unix.

(10 Sep '13, 05:39) Volker Barth

String 'sajdbc', but not with the string 'saodbc'.

The SAODBC driver will be referencing the SQL Anywhere ODBC driver on Mac OS. The files required to be locatable are listed in the documentation and need to be located by the environment variable $DYLD_LIBRARY_PATH.

As Volker mentions, an ODBC Administrator is also required (which we supply with the ODBC files: libdbodm16.dylib).

FYI: Apple also offers an ODBC manager for Mac OS X:

http://support.apple.com/kb/DL895

(16 Sep '13, 10:11) Jeff Albion

showing 2 of 7 show all flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

sa-12 ×438
macosx ×31
remote-data-access ×31
database-option ×28

question asked: 05 Sep '13, 03:28

question was seen: 3,988 times

last updated: 16 Sep '13, 14:27

SAP SQL Anywhere

Query over two SQL Anywhere 12 databases

Follow this question

Related questions