Skip to Content
Author's profile photo Werner Daehn

Hana Adapter SDK – Interaction via SQL

This post is part of an entire series

Hana Smart Data Integration – Overview

There are multiple UIs like the Web based FlowGraph editor and ReplicationEditor or Hana Studio available to setup and use the Adapters. But under the cover they are all executing SQL commands. Hence it might be a good idea to look at the adapters from that angle as well.

Recap – SQLs for creating the agent and adapter

In the previous chapters the agent and the adapter was registered in Hana. These applications use SQL regular commands, in particular

CREATE AGENT “mylaptop” PROTOCOL ‘TCP’ host ‘10.16.94.83’ PORT 5050; (see CREATE AGENT – SAP Library)

CREATE ADAPTER “HelloWorldAdapter” AT LOCATION AGENT “mylaptop”; (see CREATE ADAPTER – SAP Library)

If the IP address changed or one adapter is hosted at another location, there are the matching ALTER commands available as well.

To check which agents and adapters are available, the Hana data dictionary views AGENTS, ADAPTERS, ADAPTER_LOCATIONS can be queried.

Up to now, the adapter – let’s call it the middleware – was installed and Hana made aware of it.

Creating the source connection

In order to actually use an adapter, a remote-source has to be created. This provides all the login information the adapter needs to connect to a particular system using the adapter as a driver.

CREATE REMOTE SOURCE “myHelloWorldConnection” ADAPTER “HelloWorldAdapter”

AT LOCATION AGENT “mylaptop” CONFIGURATION

  ‘<?xml version=”1.0″ encoding=”UTF-8″?>

  <ConnectionProperties name=”conn“>

    <PropertyEntry name=”name“>Myself</PropertyEntry>

  </ConnectionProperties>’

WITH CREDENTIAL TYPE ‘PASSWORD’ USING

  ‘<CredentialEntry name=”credential“>

    <user>me</user>

    <password>unknown</password>

  </CredentialEntry>’;

The information what parameters it needs and what their names are is stored in the ADAPTERS data dictionary table, like for above adapter it reads:

<?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?>

<RemoteSourceDescription>

    <ConnectionProperties displayName=”Connection Info” isRepeatable=”false” name=”conn“>

        <PropertyEntry displayName=”Hello whom?” id=”1″ isRepeatable=”false”

          isRequired=”false” name=”name” type=”STRING”/>

    </ConnectionProperties>

    <CredentialProperties displayName=”Credentials” name=”credentials”>

        <CredentialMode displayName=”Credentials Mode” id=”2″ isRepeatable=”false”

          isRequired=”false” name=”credentials_mode” type=”ENUM”>

            <EnumValue displayName=”Technical User” name=”technicaluser”/>

            <EnumValue displayName=”None” name=”none”/>

            <EnumValue displayName=”Secondary Credentials” name=”secondarycredentials”/>

        </CredentialMode>

        <CredentialEntry displayName=”Credentials” id=”3″ name=”credential“>

            <user displayName=”Username” id=”4″ isRequired=”true” type=”STRING”/>

            <password displayName=”Password” id=”5″ isRequired=”true” type=”STRING”/>

        </CredentialEntry>

        <Activator id=”2″ name=”credentials_mode”>

            <Value>technicaluser</Value>

        </Activator>

    </CredentialProperties>

</RemoteSourceDescription>

But frankly, using Hana Studio -> Provisioning from the Catalog view is much easier.

adapter sdk sql 1.png

Adding virtual tables

A virtual table can be seen as a database View inside Hana on top of a remote table to blend this table into the Hana dictionary seamlessly. Hence the syntax is quite simple:

CREATE VIRTUAL TABLE v_hello AT “myHelloWorldConnection”.”<NULL>”.”<NULL>”.”HELLO”;

The more interesting question is how to know the values for database, owner and table name. Since these are remote tables, the only place to get the information from is the adapter itself. Using the procedure GET_REMOTE_SOURCE_OBJECT_TREE the adapter can be queried.

call SYS.GET_REMOTE_SOURCE_OBJECT_TREE (‘myHelloWorldConnection’, ”, ?, ?);

adapter sdk sql 2.png

But here as well, using the Catalog Editor might be simpler.

adapter sdk sql 3.png

But the create virtual table statement allows to pass parameters to the adapter as well. This is for example used with the file adapter. A virtual table for a file needs lot of knowledge, what the delimiter is, what the first column should be named and its datatype etc. Hence a virtual table for the file adapter can be created with a SQL like this:

CREATE VIRTUAL TABLE v_fixed AT “myFileAdapter”.”<NULL>”.”<NULL>”.”fixed”

REMOTE PROPERTY ‘dataprovisioning_parameters’=

‘<?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?>

  <Parameters>

    <Parameter name=”FORMAT”>FIXED</Parameter>

    <Parameter name=”FORCE_FILENAME_PATTERN”>fixed%.txt</Parameter>

    <Parameter name=”ROW_DELIMITER”>\r\n</Parameter>

    <Parameter name=”SKIP_HEADER_LINES”>1</Parameter>

    <Parameter name=”COLUMNSSTARTENDPOSITION”>0-1;2-7;8-15</Parameter>

    <Parameter name=”ROWLENGTH”>16</Parameter>

    <Parameter name=”COLUMN”>COL1;NVARCHAR(2)</Parameter>

    <Parameter name=”COLUMN”>COL2;NVARCHAR(6)</Parameter>

    <Parameter name=”COLUMN”>COL3;NVARCHAR(8)</Parameter>

    <Parameter name=”CODEPAGE”>UTF-8</Parameter>

  </Parameters>’;

The select command

A select command can be used to get data from a virtual table. What part of the user SQL is sent to the Adapter is controlled by the AdapterCapabilities, the remaining logic is executed in Hana. Well, that’s the Smart Data Access federation logic.The interesting part for us as adapter developers is that we can again send additional information to the adapter as part of the select statement.

File adapter is a good example for that again:

SELECT * FROM V_FILECONTENTTEXT AS “V_FILECONTENTTEXT” WHERE NAME LIKE ‘fixed1.txt’

WITH DATAPROVISIONING PARAMETERS

(<PropertyGroup name=”__DP_TABLE_OPTIONS__”>

      <PropertyGroup name=”V_FILECONTENTTEXT”>

        <PropertyEntry name=”CODEPAGE”>UTF-32</PropertyEntry>

      </PropertyGroup>

  </PropertyGroup>);

The main difference to above is that the select statement can contain multiple tables potentially and then the SQL parser needs to know which parameter belongs to what table. Hence the SQL contains the virtual table name as a parent XML node.

The adapter parameters

The adapter itself supports setting parameters as well. These are not part of any SQL command, it is part of the startup code of the adapter process. Therefore it is set by using the configTool/dpagentconfigtool.exe shipped as part of the data provisioning agent install.

The HelloWorldAdapter does not require any parameters to be set but the FileAdapter lets you enter the root directory where all the files are located, in order to make sure nobody writes e.g. into the c:\Windows directory.

adapter sdk sql 4.png

Assigned Tags

      40 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thank you for these nice blog. It helps me a lot.


      Is only SELECT is allowed in virtual tables created as part of SDI?

      When I tried the INSERT, it works fine without any error. But in Remote Source data is not added also Rows Affected is zero.

      Statement 'insert into "abc"."abc::northwind replication.VT_dbo.Products" ("ProductName","Discontinued") ...'

      successfully executed in 31 ms 334 µs  (server processing time: 6 ms 452 µs) - Rows Affected: 0


      Note: I am on HCP SPS09, remote source is MS SQL Server2012 connected using MssqlLogReaderAdapter


      Thanks!

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      In SP9 only selects do work, SP10 supports inserts as well. Both, the SDI solution in the SQL Server adapter.

      Not sure why you do not get an error when executing an insert into the virtual table.

      Author's profile photo Mushtak AliBaig
      Mushtak AliBaig

      Hi Warner,

      We are trying to connect Mysql server via SDA

      I Created an Agent and Adapter, When I try to connect in by using connection parameters I  I am getting an error JDBC 403 .Cannot get remote source objects. Can you help me on this

      Author's profile photo Former Member
      Former Member

      Hi,

      How we can connect to MYSQL from Smart Data Access.

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      A MySQL Adapter is on my ToDo list for OpenSource. At the moment I would use the OpenSource JDBC adapter. Want to try that?

      hana-native-adapters/jdbcadapter at master · SAP/hana-native-adapters · GitHub

      We can work together on that if you like.

      Author's profile photo Former Member
      Former Member

      Hi Werner,

      Sure i would like to work together.

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      Please send me an email.

      We need to define the scope, timing, degree of support you expect, who is doing what.

      But the JDBC driver would be a a good start.

      Author's profile photo Former Member
      Former Member

      hi werner, i'll be very interested in your mySQL adapter, if you could share.

      how far have you got with this ?

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      The SDA part is ready, would like to put it in the OpenSource branch but need somebody to test it. And then we should implement realtime.

      Author's profile photo Glen Spalding
      Glen Spalding

      i would be happy to test it, as we have a simple real life use.

      if you can just let me know what i need to do, thanks

      glen

      Author's profile photo Glen Spalding
      Glen Spalding

      hello werner

      what's your timeline for getting it in OpenSource, or do you want it tested before ?

      as i mentioned, i'd be happy to help

      thanks

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      I have added it to the OpenSource github. It compiles (Java 1.7 minimum) but has not been tested. Best would be to use that as a starting point for adapter development.

      It is not(!) a prebuild adapter jar file, okay?

      Author's profile photo Glen Spalding
      Glen Spalding

      It is not(!) a prebuild adapter jar file, okay?

      ah, was hoping it was.

      let me have a chat with our java team and see what needs to be done, i'm not the java person, just the tester 🙂

      thanks

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      As I assume you will find issues continue talking to your Java folks, please. Meanwhile I have put the MySQLAdapter*.jar file into the root directory of the hana-native-adapters aas well for you to download.

      Don't forget, it requires the jdbc driver to be copied in the lib directory after the adapter had been installed, else you will get a class-not-found error.

      Author's profile photo Glen Spalding
      Glen Spalding

      hi werner, i have an open mind, so no problem. will catch up soon

      Author's profile photo Glen Spalding
      Glen Spalding

      i werner,

      requires the jdbc driver to be copied in the lib directory

      which lib directory, we tried a couple here,"...usr/sap/dataprovagent/lib", but is was no good

      /wp-content/uploads/2016/06/2016_06_22_980998.png

      thanks

      glen

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      That's the correct directory though, that's the directory the other JDBC drivers go as well. I just checked the help docs and they say:

      Before registering your adapter with the SAP HANA system, be sure that you have downloaded and installed the correct JDBC libraries.

      Place your files in <DPAgent_root>/lib,

      Permissions wrong, e.g. execute rights for world?

      Author's profile photo Former Member
      Former Member

      hi werner, could you let us know, the exact driver that would work, because that's the only missing thing, we cannot verify. everything else, looks fine with the install.

      thanks

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      It is not the jar file that cannot be loaded, it is the OSGi dependencies being not setup correctly. I have tried various ways e.g. DynamicImport-Package but could not get it to work either.

      Therefore I have changed the adapter a bit, it now asks for the jar file to be specified in an additional remote source property. If the file is found in the dataprov\lib directory, then it is set as default automatically.

      WATCHOUT: For testing I had to bring my environment to the SDI DPAGENT 1.00 SP03 Patch 0. This came out 4 weeks ago, please doublecheck if your dpagent is that recent.

      Jar file got updated as well, old deleted, new added.

      With this I had been able to connect to a MySQL database i quickly installed and did browse the remote source. Did not read from any tables as I assume this works. (Source code was provided by somebody else)

      Author's profile photo Former Member
      Former Member

      hi werner, we managed to get it working thanks

      on one of our tables, we are getting the following error:

      16:50:35 (Catalog) (dberror) 338 - zero-length columns are not allowed: SEED: line 1 col 282 (at pos 282)



      any clues ?

      others are working fine

      thanks

      g

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      Can you create me a test case, then I can debug and correct it right away. Say your create table and one row as insert statement?

      Author's profile photo Former Member
      Former Member

      did you get my email, i copied table SQL for you

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      Did now.

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      Hi Glen, the new version of the adapter is uploaded. I moved the jar file into a new sub directory, though.

      It does work with all datatypes now except BIGINT. There I can't see what I am doing wrong.

      And LOBs are supported as well but I have not tested large LOBs > 1M in size.

      Please execute a

      alter adapter "MysqlAdapter" refresh at location agent "<agent_name>";

      to update the adapter capabilities. It does a projection now as well, not only select * from...;

      Also drop all virtual tables and recreate them. Before the table names were all being converted to uppercase which would have issues in case you have two tables of same name but different casing, which is supported in Unix.

      Author's profile photo Former Member
      Former Member

      did you get my new email, in response ?


      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      nope.

      Author's profile photo Former Member
      Former Member

      hi werner

      i thought we were there

      the virtual table that was in error, now gets created, however, all tables cannot run the SELECT statement on the virtual tables.

      the definition are still viewable for all tables

      here is the error we now get

      14:38:31 (Catalog) Create new virtual table 'WOVERTON"."prd-nodbx02_portal_cnf_emails' successfully

      14:38:41 (Data Preview) Could not open 'WOVERTON'.'prd-nodbx02_portal_cnf_emails'.

      Error: (dberror) 403 - internal error: Remote execution error NullPointerException while processing request "SDA Request for MysqlAdapter for request type FEDERATION_GET_NEXT" for query "SELECT "prd-nodbx02_portal_cnf_emails"."id", "prd-nodbx02_portal_cnf_emails"."name", "prd-nodbx02_portal_cnf_emails"."langid", "prd-nodbx02_portal_cnf_emails"."subject", "prd-nodbx02_portal_cnf_emails"."headers", "prd-nodbx02_portal_cnf_emails"."content" FROM "cnf_emails" "prd-nodbx02_portal_cnf_emails" LIMIT 1000 "

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      Works for me and my test cases. Just created a virtual table on the mysql.user as V_USER and then executed a "select top 3 * from v_user;"

      Do you have a test case for me?

      Author's profile photo Former Member
      Former Member

      hi werner, our developer is back now, so i would like to carry on this issue.

      our version of mySQL is this  Ver 14.14 Distrib 5.6.28

      what version are you using ?

      have you done any new amendments over the last week, that we could try

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      I have checked the code and did not find anything obvious which could cause a null value exception. MySQL was the latest version, don't know the exact one (new server image).

      Author's profile photo Former Member
      Former Member

      ok, i'll see if we revert back to the previous version, what would happen

      thanks

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      We have two options. You provide me a simple test case. You run the adapter code in Hana Studio and debug. The latter would have the advantage that you can fix it faster. As you wish. I am interested in making this adapter more solid.

      Author's profile photo Former Member
      Former Member

      hi werner, new developments ...

      we were going to revert to the previous adaptor that worked.

      we then found that our system was not well, so we registered it on another instance.

      then, it worked. actually, we found out that we did install the latest adaptor instead,

      so in the end, we had your latest adaptor installed, registered to a new instance.

      and it all works.

      the data types and lengths look to be converting just fine.

      thanks

      lets see where we go from here

      🙂

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      Cool. If you believe there is anything we could do better, let me know.

      Author's profile photo Former Member
      Former Member

      hi werner, some more information for you

      the SDI mySQL worked for us, on HANA SPS122, however, we recently moved the SDI Adapter, AND upgraded it from SP3 to SP4, and now we get the following error, when we try to expand the SDI node in HANA Studio

      (Catalog) Error reading Remote Object: InternalError: dberror(CallableStatement.execute): 403 - internal error: Cannot get remote source objects: NullPointerException while processing request "SDA Request for MysqlAdapter for request type GET_SOURCE_VERSION"

      Screenshot_1.png

      we are thinking you may need to check it with the latest SDI Adapter v4

      thanks

      glen

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      The adapter is currently compiled against the 1.3.0 or 1.3.1 version of the agent. Your agent is of version 1.2.4.

      Any chance to ask your Java developer to get the source code and compile it yourself to avoid these kinds of problems?

      Author's profile photo Former Member
      Former Member

      we will upgrade the adapter to 1.3.1, and try again, thanks

      Author's profile photo Former Member
      Former Member

      hi werner, we upgraded to 1.3.2, and was successful in creating a mySQL virtual table.

      we are now testing, and will let you know how it goes

      thanks for all you effort and patience    

      Author's profile photo Mushtak AliBaig
      Mushtak AliBaig

      Hi Glen,

      We are trying to connect MYSQL db by using same link. But not able to achieve it.
      Can you please explain the steps performed for the MySQL remote source by using JDBC connectors.

      Thanks in Advance

      Regards

      Mushtak

      Author's profile photo Former Member
      Former Member

      hi werner, have you had a chance to see my response, below

      thanks