Query/Report/View to draw data from more than 1 database

[vlad1]

Hi, we have an SQL Anywhere (v11)/Powerbuilder (v11) application that uses the SQL Anywhere database through the ODBC. At the start of the application, a specific database is always picked, as some our clients host many different databases on the same server, and run them separately through application. The application itself does not support access to more than 1 database at a time.

Is there anywhere a quick example / guide about writing an SQL query or a report that might draw data from more than one ODBC, therefore, more than one database hosted on the same server? A need may arise to run reports which consolidate data from 2 (or more) different databases.

Thanks

[Former Member · Accepted solution]

Yes... This can be done by creating a remote server and proxy tables. That way you can use tables from one database to another.

Are you using Sybase Central for the database development/design?

Andreas.

[vlad1 · Accepted solution]

Hi, yes, using Sybase Central for the main database development.

[Former Member · Accepted solution]

Ok.

Are you working with a server? Or the databases are on the machine where resides the application?

[vlad1 · Accepted solution]

Both, but the application can be portable too. Right now in one main office, all users are running the application from the shared network drive. External users are running it locally from their hard drives, and have the ODBC network settings set to go through a VPN network address to reach the server.

[Former Member · Accepted solution]

This means that you have to setup two odbc connections. In the database to which you connect through you pb application you have to add a remote server to the other one. This can be done through a wizard in sybase central or with a command (in isql) like the following one:

CREATE SERVER "server name" CLASS 'SAODBC' USING '<DSN NAME TO CONNECT>';

In the wizard you have the ability to test the connection. If you use the command you can test it from the properties of the created object under Remote Servers.

You may have to create an external login, or you can directly connect if the same users exists on both databases. You can setup the remote server as readonly if you want.

You can add to the main database proxy tables and remote procedures. You can (and have to) set privileges and views for proxy tables, and privileges to remote procedures. To add tables or remote procedures do what you would do to create a new object (table or procedure) and choose the appropriate option to the menu (as shown in the following images):

If you need more help don't hesitate to ask. But I recommend to see the part of the help file (documentation) about remote servers.

Finally If you are using the old runtime engine (provided for free with older versions of powerbuilder), I'm not sure if you can use remote servers, as this version has different license than the others.

Andreas.

[vlad1 · Accepted solution]

This is wonderful. Thank you very very much, we really appreciate it. We are probably several months down the line before any of this would probably become relevant or see if we need it, but it's certainly very useful to know that it's possible, and to have basic steps on how to achieve it.