|
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 |
|
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; 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
|
|
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:
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
1
Some notes:
(06 Sep '13, 16:50)
Volker Barth
1
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
1
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
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 As Volker mentions, an ODBC Administrator is also required (which we supply with the ODBC files: FYI: Apple also offers an ODBC manager for Mac OS X:
(16 Sep '13, 10:11)
Jeff Albion
|