on 04-23-2015 11:16 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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:
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.