Send data from ESP to remote Oracle 11g database

[Former Member]

Hi,

     Please help me with one  sample ESP application that takes some data as input from a static file and then

     sending some calculated data to remote Oracle 11g database.

     Let say that we got two numbers from one excel sheet and perform addition/multiplication and send this data to remote Oracle DB.

Thanks

[Former Member · Accepted solution]

Hello,

I don't know if you are on ESP 5.1 SP04 or SP08?

I don't know if you are using JDBC or ODBC to connect to Oracle?

Regardless, I think there is some setup information missing from the documentation.  I have filed a

documentation bug here:

   771896 - Documentation missing JDBC setup information

If you are using JDBC, be sure to get the JAR file from Oracle.  I copied the Oracle thin client "ojdbc6.jar" to $ESP_HOME/libj and defined my connection string like this:

  jdbc:oracle:thin:@//archer.acme.com:1521

Then I created a database service entry named "oracle_archer".

Then I created a project with the following CCL to perform a calculation and insert some data to Oracle:

ATTACH INPUT ADAPTER File_Hadoop_CSV_Input1 TYPE toolkit_file_csv_input TO InputStream PROPERTIES

  csvExpectStreamNameOpcode = FALSE ,

  dir = '/tmp' ,

  file = 'some_data.csv' ;

CREATE INPUT STREAM InputStream SCHEMA ( Column1 long , Dt BIGDATETIME, Column2 integer , Column3 float , Column4 bigdatetime , Column5 string );

CREATE OUTPUT STREAM OutputStream SCHEMA ( Column1 long , Dt BIGDATETIME, Column2 integer , Column3 float , Column4 bigdatetime , Column5 string ) AS

   SELECT INST.Column1, INST.Dt, (INST.Column2 + 2) AS Column2, (INST.Column3 * 1.5) AS Column3, INST.Column4, INST.Column5 FROM InputStream INST;

ATTACH OUTPUT ADAPTER Generic_DB_Output1 TYPE db_out TO OutputStream PROPERTIES

  service = 'oracle_archer' ,

  table = 'HR.HISTORY_' ;

I ran the project and checked the result in Oracle.

Thanks,

Neal

[Former Member · Accepted solution]

Thanks Neal for quick and accurate reply.

Here I am going to give you more info on my current set up

1. I am using latest ESP 5.1 [SP08]

2. I want to connect database using ODBC.  [JDBC as well]

3. I am on 64bit windows 7.

4. I am not clear with the Driver library to be used. !!

=> I have followed  your steps to for JDBC connection like I got the ojdbc6.jar from oracle site and kept it at the $ESP_HOME/libj directory.

Then I have navigated to ESP home/Bin folder and added one more service entry say MyDbService along with the existing ones.

Added Service in service.xml->

<Service Name="MyDbService" Type="DB">

   <Parameter Name="User">testuser</Parameter>

   <Parameter Name="Password">testpass</Parameter>

   <Parameter Name="Database">testDatabase</Parameter>

   <Parameter Name="ConnectString">jdbc:oracle:thin:@//testserver.abc.com:1521</Parameter>

</Service>

So before doing anything I thought like It would be better if I test this database connection from ESP IDE.

I have started one project -> navigated to Data Service View tab-> I can see all the service names here defined in service.xml ->

Selected MyDbService and right click to Descover.

But I could not reach to this service as it says not enough parameters. !!

[Former Member · Accepted solution]

Hi Adesh,

SP08 does not use the services.xml in the bin location anymore, so there is no point to put the parameters into services.xml.

You need to add the parameters in the Data Services tab.

When authoring, if you are in the ESP Authoring tab in the server:port you should see your service and when you select it, you should see the properties in the Properties view and check it's values.

You need to add them to the right server:port , you could have a local development cluster and a remote Linux cluster.

Ben

[Former Member · Accepted solution]

Two more notes:

services.xml can be migrated into the cluster with the esp_cluster_util; is that what you have tried to do?

So what does the properties view show?

If you connect with the studio on windows, for example for discovering a schema, then you are also creating a database connection on locally on Windows, which is a different database connection then the server running on Linux will have.

BP

[Former Member · Accepted solution]

Thanks Ben,

As you said that service.xml is not used in ESP SP08 onwards and service.xml can be migrated using

esp_cluster_util.

But I have not tried that.

So the Oracle database that I am trying to connect from ESP studio is running on remote Linux Machine.

I have explored Data Service Tab in ESP Authoring view but I do not see any parameter kind window, All it asks is to right click on any of data service and select Discover.

And these data services are same services that are defined in service.xml.

For the demo purpose I have added one more service tag in service.xml that is using one dsn to connect HANA database. Now from ESP studio after right click->Discover , I am able to connect this Hana database.

Same thing I want to achieve with Oracle with or without DSN !!

Thanks

With Regards
Adesh

[Former Member · Accepted solution]

Hi,

You need to check the properties view, probably already open at the right edge of the studio, after clicking on a dataservice.

It should show the parameters that has been used to connect or you need to edit this view to put the right parameters.

Ben

[Former Member · Accepted solution]

Hello,

You may need to go under the "Window" menu -> "Show View" -> Properties. Sometimes the Properties view is small and hidden by other tabs.  When you click on an existing "service" or add a new one and then click on "newservice" you can edit the properties.

You also mention using ODBC.  On Linux, there is a bit of setup.  You need to make sure that you have the latest version of the unixODBC driver manager installed:

  http://help.sap.com/saphelp_esp51sp08cfa/helpdata/en/e7/8d0f156f0f1014a048880d763bd299/content.htm?f...

Some Linux boxes come with old versions of unixODBC that can cause ESP to crash so search for and uninstall old versions of unixODBC with Linux command such as "whereis libodbc.so.1" and rpm:

rpm -qa | grep -i unixodbc

unixODBC-2.2.11-7.1

[Former Member · Accepted solution]

Thanks Neal for extremely clear response.

I have started the data view and Properties view as well but I do not see any thing like your screen.

Is it because of Evaluation version of ESP SP08 ??

I have not licensed version of ESP, could It be the reason like I am not able to see these options ??

Thanks

With Regards

Adesh

[Former Member · Accepted solution]

Hi Neal,

Can we have small discussion session of 10-15 minutes.

I think we can resolve the problem asap.

My main problem statement is oracle database connection with my product[LMS] which is using ESP SP08 at Linux platform.

Thanks