Creating DataSource in Visual Administrator for Oracle DataBase

[Former Member]

Hi Experts,

I want to connect to external Orcle Database from my application(EJbs,Web Dynpro) where i need to call some stored Procedures.for that one i want to create

DataSource in Visual Administrator.

i got some doubts for creating DataSource in Visual Administrator

1.which SQL engine i need to use(open SQL or Native SQL or Vendor SQL)

2.shall i need to deploy any additional drivers(jars) in WAS

any help will be appriciated

thanks in Advance

With Regards

Naidu

[Former Member · Accepted solution]

I have not used any other SQL engine besides VenderSQL and we use prepared statments as follows.

The backend DAO method is called and passed a list of names, etc, whatever to execute the query against. When processing a list of queries the method will create the prepared statment once then execute it each time for the list. This saves you some time. However the Prepared Statement is lost after the method exits.

Native SQL is different in that I will keep the Prepared Statement in cache for the next time the method is called saving the creating of the Prepared statement again. I believe the benefits are a trade off. There are many factors to consider how often will the statement be called in its lifetime, keeping something in cache a statment takes memory resources. How many statments will you need to execute as Pooled vs non Pooled Prepared. Can you only meet your performance goals via using Native SQL. There are a lot of factors to consider.

I use the Oracle Supplied Drivers for connecting to Oracle ojdbc14.jar, and Vender SQL as this meets our goals and allows for Prepared Statments. I believe for your purposes Native SQL might be the answere however I have not sucessfully connected to an Oracle DB using Oracle supplied ojdbc14.jar and the Native SQL setting of the DataSource. I have only successfully connnected using VenderSQL.

Therefore can not say if it is worth the effort to go Native SQL.

[Former Member]

Thanks Steven,

Thanks for your Valuable inputs.

With Regards

Naidu

[Former Member]

HI Steven,

Thanks for ur qucick Replay.Which SQl Engine U suggest in my Scenario Native or Vendor.

can u give some explanation why particular SQL engine is better in my scenario.

I am calling some stored Procedures in my application.

Thanks in advance

With Regards

Naidu

[Former Member]

Using Vender SQL you can also use a connection pool in the tabs where you define your Datasource you also state how many connections to open initally and also max and how many to increment by when needing new connections.

[Former Member]

HI Experts,

I am able to create DataSource in Visual Admin to the Externa Oracle System.

Now My Quesion is Which SQL Engibe will be better(Native or Vendor).

i am able to create with Native SQL Engine and Vendor SQl engine.

in the SAP Help it was mentioned that Native SQL supports connection pooling and statement Pooling..etc does it mean that Vendor Sql won't support Connection pooling ,satement pooling.

Thanks in advance

With Regards

Naidu

[Former Member]

Hi MOnis,

We are trying this and we are expericeing some problems.once it is done,i wll close the thread.

thanks

With Regards

Naidu

[Former Member]

Has your query been solved ?

If yes, then please provide the solution and close the thread so that it can be used for future reference.

[Former Member]

I can see you are still looking for answeres, I have included more explicit examples of how to connect to an Oracle DB via an SAP Visual Administrator Datasource.

First the DB has to exist and you should be able to connect to it using some other application to test with like Toad for example.

Secondly for installing the Data Source via Visual Administrator you will need to verify that you have a an Database Driver defined in the Visual Admin first, if not define one. Defining an oracle driver is as simple as providing an alias name for the driver and supplying the Oracle classses.jar, or ojdbc14.jar. Click Create Driver in the Visual Admin enter an alias name and click Browse to find one of the jar files mentioned above. Once done click the diskette looking icon to save.

After the above 2 steps you can either define the Datasource manually in the Visual Administrator or Import the datasource via an xml file as defined below.

I have included a mock datasource.xml file which you can copy and paste and more or less use as a reference.

<b>Note</b>

With Oracle I have always sected <b>Vender SQL</b> as I can not get any of the other selections to work properly when connecting to our Oracle DB. I have also used this approach to connect to Oracle 8 databases as well. We have been using these Datasources in production for many years without incident.

Variables Defined

<b> data-source-name </b> PORTFOLIODS is the name we use to look up the Datasource via JDBC.

<b>driver-name </b> The name that you have defined for the Oracle jar libary you added as a Database Driver via the Visual Administrator.

<b> url </b> The "Type 3" Network URL pathname to the Oracle DB.

<b> user </b> The account you want to log in as.

<b> password </b> The account password for the DB.

Cheers,

Alex

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE data-sources SYSTEM 'data-sources.dtd'>
<data-sources>
  <application-name>chep.com/JDBCConnectior_PORTFOLIODS.xml</application-name>
  <data-source>
    <data-source-name>PORTFOLIODS</data-source-name>
    <driver-name>Oracle9i</driver-name>
    <max-connections>25</max-connections>
    <max-time-to-wait-connection>60</max-time-to-wait-connection>
    <sql-engine>vendor_sql</sql-engine>
    <jdbc-2.0>
      <cpds-class-name>oracle.jdbc.pool.OracleConnectionPoolDataSource</cpds-class-name>
      <object-factory>oracle.jdbc.pool.OracleDataSourceFactory</object-factory>
      <properties>
        <property>
          <property-name>url</property-name>
          <property-value>jdbc:oracle:thin:@usdenedu169.cnn.com:1521:ORCL</property-value>
        </property>
        <property>
          <property-name>user</property-name>
          <property-value>LogisticsApplication</property-value>
        </property>
        <property>
          <property-name>password</property-name>
          <property-value>logisticsAppPwd</property-value>
        </property>
      </properties>
    </jdbc-2.0>
  </data-source>
</data-sources>

[Former Member]

You need to make an entry in the tnsnames.ora file with the details of the external server that u would be using.

The choice of the engine depends on the sql sts that you would be using.

In our case for one scenario we used OpenSQL and for another scenario we used VendorSQL.

We did not deploy any additional drivers to the WAS. You can try without installing

any additional drivers and check if it works. Cannot guarantee on this.

[Former Member]

Hi Balaram,

please go to this url

http://searchsap.techtarget.com/tip/0,289483,sid21_gci1246926,00.html

username: manivannan123@yahoo.com

pwd: Mylife

this is definitely helpful for u.

R u satisfied

Regards,

P.Manivannan.

[Former Member]

Hi Steven Toth and Melwyn Monis,

Thanks for Ur Quick Respnse.if I use Open SQL or Native SQL as SQL Engine,

is it necessary deploy or add any derivers(jars) to visial administrator.

in addition to creating datasource in Visual admin,is it necessary to maintain

database details in 'tns' file.

among all these SQL engines,which is preferable one.

thanks in advance

With Regards

Naidu

[Former Member]

Hi Balaram,

You may use any of the 3 SQL engines.

You can check out the following link:

<a href="http://help.sap.com/saphelp_nw70/helpdata/en/c0/3ad4d5cdc66447a188b582aad537d3/frameset.htm">http://help.sap.com/saphelp_nw70/helpdata/en/c0/3ad4d5cdc66447a188b582aad537d3/frameset.htm</a>

Hope this helps.

[Former Member]

For Connection to 9i at my site we have always used the VenderSQL setting in combination with the Oracle Driver libraries ojdbc14.jar.

Before setting up the DataSource you have to add the Driver in the Visual Administration.