cancel
Showing results for 
Search instead for 
Did you mean: 

connection pool

Former Member
0 Kudos

Dear All,

I am searching a way to make a connection to a MSSql from my custom iView. Is it possible to make the connection by using the connection pool that located in the "KM Admin > Configuration > Content Management > Utilities > JDBC Connection Pool" ? What should I do to create and use the connection pool?

Thanks a lot

Sam

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Yes, actually I was looking exactly the same place but thought it may not be that, it was too simple to be true

Thanks again, much appriciated !

daniel_davinci
Active Contributor
0 Kudos

Hi Sam,

Are you custom developing a java iView?

If so you can utilise the connector framework.

A quick brief can be found via

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/documents/a1-8-4/sap connector framework

Regards

Daniel

Former Member
0 Kudos

Hi Daniel,

Thanks for your help. But seems it is not my case.

Regards

Sam

daniel_davinci
Active Contributor
0 Kudos

Hi Sam,

Could you explain what you are trying to achieve a little more clearly. What sort of custom iView are you trying to create? or what is the purpose of the iView that you wish to create?

Regards

Daniel

Former Member
0 Kudos

Hi Daniel,

I am trying to make use of the connection pool of EP. And looking for some sample code on doing this.

Luckily I found this a minute before

Thanks for your help

Sam

detlev_beutner
Active Contributor
0 Kudos

Hi Sam,

at least, as far as I know, this is not supported. I would suggest to create a new dbpool and use this, see https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/documents/a1-8-4/use sap j2ee database pooling service

Hope it helps

Detlev

Former Member
0 Kudos

This is an EP5 document, Detlev

Well, it still works, but SAP recommends using the connector framework with EP6. Imagine the chaos, if the count of db pools grows in a bigger productive system and they have to be configured in all 3 system landscapes.

The framework is delivered with a JDBC connector. So you can use it in a common way.

Further information on using the JDBC connector can be found here:

good article:

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/documents/a1-8-4/using j2ee connector architecture with ep6 iview development.pdf

API:

http://media.sdn.sap.com/html/submitted_docs/60_sp2_javadocs/raan/index.html

cool demo:

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/documents/a1-8-4/java applications and jdbc data sources.abst

some lines from oliver:

https://www.sdn.sap.com/content/html/Using%20JDBC%20Adapter%20for%20SAP%20EP%20Connector%20Framework...

Hope this helps,

Karsten

p.s. if we're actually talking about EP5, forget everything above

Former Member
0 Kudos

Hi Karsten,

Yes, the version that I am using is still EP5 only. (what a old version it is )

Detlev, the document that you provided is what I found before.

Thanks for all your helps

Sam

detlev_beutner
Active Contributor
0 Kudos

Hi Karsten,

even for that we have talked about EP5, also on EP6 I like the standard way.

Your reasoning I cannot follow. I cannot imagine any chaos. We have just developed an application using dbpool. The DB has to be configured exactly once. After this, Hibernate was doing the rest of JDBC handling.

Sorry, for DB systems only, I still think the standard J2EE way is the right way. With the Connector Framework, you just have another wrapper around JDBC and are leaving the standard path. The only advantage I see is that configuring takes place within EP6 solely, but I don't think that this is really a problem for the standard way. EP6 works within a J2EE engine, so why developing solutions to step around J2EE techniques?

Best regards

Detlev

PS: For Hibernate using - which is highly preferrable when doing some amount of DB stuff - it is really nice just to give a JNDI name where a datasource is coming from. Can this be done by using the Connector Framework in an easy way?

Former Member
0 Kudos

Ok, EP5 was never mentioned in the thread, so how could I have known.

Back to EP6:

"Your reasoning I cannot follow. I cannot imagine any chaos. We have just developed an application using dbpool. The DB has to be configured exactly once. After this, Hibernate was doing the rest of JDBC handling."

In a bigger project, there are (at least) 3 portals, development, quality and production system. So the dbpool entries have to be configured for every portal, since they cannot be transported.

When using the connector framework, these settings are done in the system landscape editor, thus they can be transported.

"Sorry, for DB systems only, I still think the standard J2EE way is the right way. [...] EP6 works within a J2EE engine, so why developing solutions to step around J2EE techniques?"

The JCA based connector framework IS the standard way.

Even when developing a normal J2EE application on SAP J2EE Engine, you would re-use the pre-configured database connection pool and make use of resource references and data source aliases. Since these data source aliases are declared by the developer and are published to the j2ee engine during deployment, no administrator would have to do configuration.

When using the connector framework in portal applications, you don't even have to use the generic connector interface. You might have a look at the javadocs of interface INative. When retrieving a INative connection, you have all the capabilities a java.sql.Connection offers.

I can't say much about Hibernate, but by googling (nice word) for 5 minutes I got the following:

-Hibernate claims to support JCA as well

-There are limitations for Microsoft's jdbc driver and for datadirect's jdbc driver (which is delivered with the portal)

detlev_beutner
Active Contributor
0 Kudos

Hi Karsten,

OK, now I've got you where the different config's you're talking of come from. Anyhow, configuring these I wouldn't call chaos...

"these settings are done in the system landscape editor, thus they can be transported." -- So, if you are talking of dev/qa/prod, you're thinking of using the same parameters for all these? Not really? So this does not make any difference, except who is doing administration (portal admin vs. j2ee admin). Also, the "transport advantage" means somehow a disadvantage in this case, you don't want to get the system on prod overwritten by a transport, do you?

"The JCA based connector framework IS the standard way." -- Maybe SAP calls it standard for SAP (portal), but I'm coming from pure J2EE world. And so far for pure DB JCA is definitely not the J2EE standard way of accessing them.

"developing a normal J2EE application on SAP J2EE Engine, you would re-use the pre-configured database connection pool and make use of resource references and data source aliases" -- Right! Right!!! That's the way I'm thinking (and ORM's are working the easiest way). And to do something in parallel within portal dev, I would like to re-use the connection pool and make use of resource references and data source aliases - that's just what I'm doing!

When using ORM's, all I want to access is a datasource alias. Period. The rest is (should be) history.

"Hibernate claims to support JCA as well" -- This is a very generic statement. I also had a look into different stuff, and all I could find is that it is possible to deploy Hibernate as a Connector. That's not what we are talking of.

Anyhow, we don't have to stick solely to Hibernate. As you mentioned yourself, the J2EE way is to have a datasource alias at hand. That's the standard starting point. And this I don't see!?

Maybe I overlook something? But without the possibility to say "Use datasource alias MYDB", "all is nothing".

Best regards

Detlev

Former Member
0 Kudos

Hi all,

How would you translate this few simple lines to use the J2EE DB pool ?

-


Class.forName("com.sap.portals.jdbc.sqlserver.SQLServerDriver");

con = DriverManager.getConnection("jdbc:sap:sqlserver://myserver.be:1433;DatabaseName=CUSTOM_PORTAL;SelectMethod=cursor","userID","password");

if(!con.isClosed())

stmt = con.createStatement();

rs = stmt.executeQuery("SELECT COUNT(*) AS NRALIAS FROM ALIASES WHERE ALIAS = '" + newAlias + "'");

while ( rs.next() )

{

String aliasHits = rs.getString("NRALIAS");

if (aliasHits.equals("1")) return false;

}

etc....

-


I'm also lookint into replacing this hardcoded material (though it works fine) with eventually the connector framework.

Another question - if you'd use the connector framework, access is configured in the datasource (j2ee visual) or do you put this in the code ? Can one use the default J2EE credentials automatically ?

In this environment the J2EE user/pass changes occasionally thus hardocing is not really good. Provided i'm extracting data from a table which is on the same DB server as the portal, it should be possible to automatically use the J2EE user/pass, no ?

Any ideas are most welcome.

detlev_beutner
Active Contributor
0 Kudos

Hi Dimitar,


InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("jdbc/YourDBAlias");
Connection con = ds.getConnection();
...

This is for having defined a Datasource Alias within J2EE engine. If you use the ConnectorFramework, you define the system within the portal (at least you can to avoid doing this hardcoded).

You shouldn't use the Portal DB if you are not really accessing Portal DB stuff, so separate your own app stuff into a different DB (of course, you can use the same DBMS). <i>If</i> you want to access the Portal DB, for sure you can access the corresponding DS Alias (when not using ConnFramew).

Hope it helps

Detlev

Former Member
0 Kudos

Thanks Detlev.

If I understand this correctly in either case one still has to provide credentials for the DB connection. Whether that be via system definition in the portal, a J2EE datasource alias, or hardcoded as per my example. Is that correct ?

There's no way to kindof use the already open connection from the J2EE to the portal DB ?

From the info I got on the connector framework and the J2EE aliases it seems to me it makes more sense that the J2EE datasources are used rather than a system definition.

If a system is used it means those connection values (+ credentials) will be coded in it, when transporting from DEV to PROD those will overwrite the Production.. this is something that wouldn't happen if J2EE aliases are used, or am I wrong ?

Sorry if my questions are a bit weird but i've been developing ASP for EP5 till now and just am entering the java world in EP6 so it's pretty much all new for me..

detlev_beutner
Active Contributor
0 Kudos

Hi Dimitar,

> There's no way to kindof use the already open

> connection from the J2EE to the portal DB ?

As said, in theory you can use the portal DB by just using the corresponding alias(es) (there is the portal DB and the KM DB, if deployed). But if you care about your own app stuff, I would create a new DB, this means new credentials. In theory, you <i>can</i> create new tables for your own app stuff within portal DB, but I would really separate this.

> it makes more sense that the J2EE datasources are

> used

My words. Nice to have convinced someone

> If a system is used it means those connection values

> (+ credentials) will be coded in it, when transporting

> from DEV to PROD those will overwrite the Production..

> this is something that wouldn't happen if J2EE aliases

> are used, or am I wrong ?

Karsten stated that transporting would be an advantage, I stated that this would be a disadvantage for the reason given here. To be fair, this disadvantage disappears if you just do not transport the system. It's just no real advantage until you use the same DB, but when Karsten talked of "really big projects", these are just the cases where you definitely will have different DBs for different system stages.

Hope it helps

Detlev

Former Member
0 Kudos

I was just looking into this but couldn't find how to create the DB alias on the J2EE of EP6 SP2 (6.20, SP25 or so).

Any idea where this is defined in 6.20 ?

detlev_beutner
Active Contributor
0 Kudos

Hi Dimitar,

see the document given earlier in this thread, it holds for J2EE 6.20, too: https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/documents/a1-8-4/use sap j2ee database pooling service

Hope it helps

Detlev