Remote Accessing A HANA Database From SAP SQL Anywhere
SAP SQL Anywhere is a comprehensive suite of solutions that provides data management and synchronization technologies that enable the rapid development and deployment of database-powered applications in embedded, SaaS, remote and mobile environments.
In the context of the HANA platform, SQL Anywhere can act as a database server for local applications in remote offices, as a local data store for mobile and desktop applications, or as a data collection in M2M or “Internet of Things” smart appliances. In all cases, the data stored and managed can be synchronized bi-directonally with a variety of database servers, including SAP HANA.
If you are developing an application using SQL Anywhere that is going to involve data synchronization or run in an occasionally connected environment, it is often useful to be able to query the consolidated database (for example, HANA). Fortunately, SQL Anywhere has built support to access remote database servers directly. You can create a connection to a remote HANA server (as well as any other database server that has a decent ODBC driver) and then send SQL statements to that server, or create proxy tables that look like like regular tables in your SQL Anywhere database, but when you select from them the data is actually retrieved from the remote HANA database.
We’ll demonstrate how this works using DBISQL, a tool that ships with SQL Anywhere that allows a user to execute adhoc SQL statements against SQL Anywhere and get results.
Note: You can also connect directly to a HANA database from DBISQL, but that is not what we will do in this demo/tutorial
In order to create a connection to a remote database server in SQL Anywhere, you must first have the ODBC driver for that remote server installed. The HANA ODBC driver is part of the HANA client available in the HANA Client Developer Edition.
Once the HANA client is installed, you can create a DSN to connect to your HANA database (using the ODBC administrator for example). Once the DSN is created, you can create a remote server by connecting to your SQL Anywhere database from DBISQL and executing:
CREATE SERVER "MyHanaInstance" CLASS 'hanaodbc' USING 'DSN=HANATest;UID=XXX;PWD=XXX'
Once you have created the remote server, you can start using it immediately by executing the statement:
FORWARD TO MyHanaInstance;
The “FORWARD TO” statement tells the database server to forward any statement issued on the current connection to the specified remote server.
So if we execute
SELECT * FROM MyHANATable;
The database server will forward the entire statement to HANA, where it will be executed and the results will be returned back to the local SQL Anywhere connection.
To end communication with the remote server, you simply execute the “FORWARD TO” statement again with no server name:
In addition, if you had a batch of statements you wished to execute, you could do it all at once. The following batch creates a table in the HANA remote database and inserts a row into it (assuming the remote access connection has permission to do so of course).
FORWARD TO MyHanaInstance; CREATE TABLE MyHANATable( col1 integer, col2 varchar(256) ); INSERT INTO MyHANATable VALUES( 1, 'Hello World' ); FORWARD TO;
This is a very useful feature any time you want to lookup data in a HANA database while working on SQL Anywhere for your embedded/remote/mobile application, or if you wanted to build in application logic which behaves differently depending on whether or not the HANA database is available.
It is also useful to interact with both a SQL Anywhere database and a HANA database, to do cross-database joins for example. You can do this via the use of SQL Anywhere proxy tables. As I mentioned earlier, after creating the remote server connection, you can create local tables which reference the remote HANA database directly. For example:
CREATE EXISTING TABLE Proxy2MyHanaTable AT 'MyHanaInstance..OWNER.MYHANATABLE';
This statement creates a table called “Proxy2MyHanaTable” in the SQL Anywhere database. Any DML statement run against this table is executed against the HANA database table “MyHANATable” (eg. insert/update/delete).
For example, select directly from the proxy table as if it were a base table in the database:
SELECT * FROM Proxy2MyHanaTable;
This will execute the SELECT statement against the MyHANATable in the HANA database and return the results to the SQL Anywhere database.
Note: You can also define a proxy table more explicitly by providing the exact columns you wish to include as part of the proxy table, and what their datatypes are. See the documentation for more details.
Once your proxy tables are created, you can perform a cross-database joins:
SELECT * FROM Proxy2MyHanaTable ptHANA, MyLocalTable tLocal WHERE ptHANA.col1 = tLocal.col1;
Finally, you can also create proxy(remote) procedure and function calls in a very similar fashion. eg.
CREATE PROCEDURE RemoteProc() AT 'MyHANAInstance..JASON.myHANAProc';
As with proxy tables, any call to RemoteProc() will call JASON.myHANAProc() in the HANA database.
Remote data access, including proxy tables and remote procedures can be a very useful tool in applications dealing with occasionally connected situations, allowing access to a HANA database from the same connection being used to access a SQL Anywhere database. It can also be very useful during application development for activities like sucking a dataset from HANA into the local SQL Anywhere database for testing.