cancel
Showing results for 
Search instead for 
Did you mean: 

error loading data from HANA with DBInput_Adapter

0 Kudos

Hello Colleagues,

I am new in ESP, just tried to load data from HANA into an input window with DBInput_Adaptor:

CREATE INPUT WINDOW EventsIn

SCHEMA (

  SeqNo long,

  UID string , IA_TYPE string , IA_ORIGIN string

)

PRIMARY KEY (SeqNo)

KEEP 2 HOURS

AUTOGENERATE ( SeqNo )

;

ATTACH INPUT ADAPTER HINTin

  TYPE db_in

  TO EventsIn

  PROPERTIES service = 'hanaService' ,

  table = 'ONE_AND_ONLY' ,

  permutation = 'UID=ID:IA_TYPE=IA_TYPE:IA_ORIGIN=ID_ORIGIN' ;

And I got the following error while running the project:

ConnectionReader(EventsIn) error reading from Connection(HINTin): 2015-04-23 22:08:26 DBInput_Adapter::getNext() getData failed. 0 records consumed, 0 read, 0 bad, 0 good.

Error retrieving column 4 value from result set.

The table is defined in HANA as below:

create column table "ONE_AND_ONLY" (ID nvarchar(255), IA_TYPE nvarchar(20), ID_ORIGIN nvarchar(20));

Could you please help me with this problem?

Thanks a lot,

Tao

Accepted Solutions (0)

Answers (1)

Answers (1)

JWootton
Advisor
Advisor
0 Kudos

You need to be sure you have defined a Data Service for the HANA database that you are trying to connect to.  Hopefully you are using the current version of ESP:  SP09 (or HANA smart data streaming SP09).  The service.xml file was used in older releases of ESP but has been replaced by the Data Service definitions that are managed from the Studio.

Be sure you:

1. Have installed the HANA ODBC driver on the same host where the ESP (or SDS) server will run

2. Define an ODBC DSN on that same server - giving it the HANA connection info

3. In the Streaming Development perspective of the Studio (either ESP Studio or HANA Studio with streaming plugin installed):  define a Data Service for the HANA database you will be connecting to

See this blog post for all the steps and details: http://scn.sap.com/docs/DOC-60817

You may also want to check the project error log.

0 Kudos

Hello Jeff,

Thanks for help!

I have created an data servie, discover works too.

The strange thing is: the hana data service works fine in the output adaptor, I can write into a hana table with this service. But with the same service, I got problem with input adaptor:

ConnectionReader(EventsIn) error reading from Connection(HINTin): 2015-04-23 22:08:26 DBInput_Adapter::getNext() getData failed. 0 records consumed, 0 read, 0 bad, 0 good.

Error retrieving column 4 value from result set.


Also, with the input adaptor, if the same table exists in multiple schema, I could not find a way to specify which schema I want to read from. The field mapping is a mess too, I got the same table and the same columns multiple times, cannot do a unique mapping.


Thanks and regards,

Tao

JWootton
Advisor
Advisor
0 Kudos

Use the fully qualified table name in the table property, e.g:

ATTACH INPUT ADAPTER MyInput TYPE db_in

TO MACHINE_REF_window1

PROPERTIES

  table = 'MY_STREAMING_USER.MACHINE_REF' ,

  service = 'hanaservice' ;

In the example above, the table MACHINE_REF is in the HANA database schema called MY_STREAMING_USER

You raise a good point though:  this is not obvious and is not documented. I'll get it added to the documentation.

Now if this doesn't fix the problem, it could be a problem with the field mapping. If so, and if you are using HANA studio with the streaming plugin, an easy thing to do is let the Studio create the input stream/window for you, defining the schema to match the HANA table:  just open the streaming project in the visual editor, then go to the HANA Systems view, find the table you want to use for input, and drag it into the streaming project diagram.  If you are using ESP Studio, then sorry,  this trick isn't available and you'll just have to troubleshoot your field mapping.

P.S. - I noticed that you posted this in the "Event Stream Processor" space.  This space is really intended to share non-technical information and isn't monitoring by the tech-team.  In the future, please post technical discussion items to the Streaming Developer Center: