Database Connections

[PaulCooper]

We have an already provided Ebase application (version 3.4). I am make a few modifications and I want to access the URL of the database connection in FPL. Can this be obtained or will I need to write a custom function?

[Steve]

Hi Paul,

This cannot be accessed though FPL, you will need to write a custom function.

You will need to get your connection via JNDI lookup and access the meta data for this connection.

Steve

[PaulCooper]

Hi Paul,

This cannot be accessed though FPL, you will need to write a custom function.

You will need to get your connection via JNDI lookup and access the meta data for this connection.

Steve

Thanks Steve.

I assume you mean something along the lines of:

Code: Select all

Context context = new InitialContext();
 dataSource = (javax.sql.DataSource) context.lookup("java:myDataSource");

Paul

[Steve]

Hi Paul,

Yes, exactly like that.

Here's a URL with an example.

http://www.javapractices.com/topic/Topi ... .do?Id=127

Steve

[PaulCooper]

Sorry it has been a while getting back to this. But what would be the lookup string?

Reading up on use of Datasource objects isn't making things any clearer. Obviously the stuff I am reading is more geared around creating connections rather than extracting information from existing connections.

FYI I have three entries under IT elements/Database and the one I am after is called "SWIFT".

My apologies for requiring so much hand-holding.

[Steve]

Hi Paul,

Is the SWIFT database configured in the ufs.xml of your tomcat configuration

It should be found here:

<ebase_install_dir>/UfsServer/tomcat/conf/Catalina/localhost/ufs.xml

You should see an entry in the xml file for the database resource SWIFT containing the attribute: name="jdbc/SWIFT".

If so you should be able to look up up using:

Code: Select all

Context context = new InitialContext(); 
 DataSource dataSource = (javax.sql.DataSource) context.lookup("jdbc/SWIFT"); 

If that does not work... try changing the lookup "jdbc/SWIFT" to "java:comp/env/jdbc/SWIFT"

Steve

[PaulCooper]

Hi Paul,

Is the SWIFT database configured in the ufs.xml of your tomcat configuration

It should be found here:

<ebase_install_dir>/UfsServer/tomcat/conf/Catalina/localhost/ufs.xml

You should see an entry in the xml file for the database resource SWIFT containing the attribute: name="jdbc/SWIFT".

If so you should be able to look up up using:

Code: Select all

Context context = new InitialContext(); 
 DataSource dataSource = (javax.sql.DataSource) context.lookup("jdbc/SWIFT"); 

If that does not work... try changing the lookup "jdbc/SWIFT" to "java:comp/env/jdbc/SWIFT"

Steve

Thanks, only we are using JBoss rather than tomcat so I did a search on the server box for ufs.xml and there isn't one. Not sure what file the information would be in.

[Steve]

Hi Paul,

What is it you are trying to do exactly?

Do you just want to setup an Ebase connection to the SWIFT database within the designer (so this can be used for database resources) or do you need to URL for other purposes?

Steve

[PaulCooper]

What I am trying to do is get the URL programatically of the database connection set up in AIS (an ebase application which is a new frontend to the Oracle Forms based Swift application by Northgate). To do this I am going to look up this url against some parameters I have setup in UFSSetup.properties. The look up will be like the folowing:

UFSSetup.properties file:

Code: Select all

CEC.oracle_forms.databases = SW27FAM,SW27TEST
CEC.oracle_forms.database_url.SW27FAM = jdbc:oracle:thin:xxx.xxx.xxx.xxx:pppp:aaaaaaa
CEC.oracle_forms.database_url.SW27TEST = jdbc:oracle:thin:xxx.xxx.xxx.xxx:pppp:bbbbbbb

I get the current application instance's url which is say "jdbc:oracle:thin:xxx.xxx.xxx.xxx:pppp:aaaaaaa". I then get the value of CEC.oracle_forms.databases which is "SW27FAM,SW27TEST". I split this a check the value for each of "CEC.oracle_forms.database_url.<database>" and get the url stored in UFSSetup.properties. If it matches the url I got earlier I then know which Oracle SID the database is. In this case SW27FAM.

Once I have this then I can pass this SID to an Oracle Form via a url call to an existing Oracle Application server (running Swift) which will log on for me and in turn call the actual Oracle Form I want. I currently have this SID value hard coded and I am also currently passing the user credentials in plain text (See my other post on this forum about my attempts to get custom functions working that will encrypt the user credentials).

I have this working and the Oracle Form is being called and is logging on and then doing what I need it to do. So now I am trying to get the SID and the encryption parts done.

The whole aim is because we already have 100s of Oracle Form fronted Oracle Reports and we want to have these called from inside AIS which will save 1000s of man hours writing new reports in a totally new set of reporting technologies.

Paul

P.S. As I am completely new to all this type of work (I am used to doing Oracle Forms and Reports work normally) I am not 100 percent sure if a second instance of AIS application connecting to another database would be using a same UFSSetup.properties file. If they use separate files then I can dump all this research and coding and just put the SID value in UFSSetup.properties and use that instead.

[Steve]

Hi Paul,

I found the Database connections configured within:

UfsServer/JBoss/conf/tomcat/jboss.jcml

I am not really a JBoss expert either

My JBoss is a very old one.

Steve

[PaulCooper]

Hi Paul,

I found the Database connections configured within:

UfsServer/JBoss/conf/tomcat/jboss.jcml

I am not really a JBoss expert either

My JBoss is a very old one.

Steve

Neither that folder nor that file exist on my server.

Paul

[Steve]

Have you tried searching the entire folder structure (Ebase) for the jboss.jcml file?

Steve

[PaulCooper]

Have you tried searching the entire folder structure (Ebase) for the jboss.jcml file?

Steve

Yes I did and came up blank. The server is a virtual Windows Server which doesn't have a "grep" like tool so I can't search the actual contents of the files (the in built search tool doesn't search contents of files either). So I am current trying to get my win32 ports of unix command line tools to work on the server.

Paul

[Steve]

I suspect you need to be looking for a file that contains:

UFSREPOSITORY

This is the name of the Ebase datasource.

Steve

[PaulCooper]

That is the file WEB-INF/jboss-web.xml

and contains:

Code: Select all

<resource>
        <res>jdbc/UFSREPOSITORY</res>
        <res>javax.sql.DataSource</res>
        <jndi>java&#58;/jdbc/UFSREPOSITORY</jndi>
    </resource>

Interestly one of the Database entries contains the Datasouce of "UFSREPOSITORY" but the one I am after has the Datasource field empty.

Paul

[PaulCooper]

I have found the xml file with the information in it is the file:

server\ebase\exports\27.0\IT_elements\Databases\SWIFT.xml

and contains:

Code: Select all

<xml>
<document>
  <header>
    <UFSRelease>3.4.0</UFSRelease>
    <exportedByUser>REDACTED</exportedByUser>
    <exportedDate>7/3/2011 17&#58;6&#58;55</exportedDate>
    <transportList>
      <transportObject></transportObject>
    </transportList>
  </header>
  <body>
    <transportObject>
      <externalDatasourceId></externalDatasourceId>
      <dbUser>REDACTED</dbUser>
      <dbPassword>REDACTED</dbPassword>
      <useConnectionPooling>N</useConnectionPooling>
      <JDBCUrl>jdbc&#58;oracle&#58;thin&#58;@REDACTED</JDBCUrl>
      <JDBCDriver>oracle.jdbc.driver.OracleDriver</JDBCDriver>
      <databaseType>Oracle</databaseType>
      <databaseDescription>Swift - Data Quality Check</databaseDescription>
    </transportObject>
  </body>
</document>

(I have replaced sensistive information with the text "REDACTED")

This the object I am trying to access from Java and the element <JDBCUrl> particularly.

Paul

[Steve]

Hi Paul,

You cannot access the Ebase configured database connection directly from a function. If you were using version 4, you could do this easily as you have direct access to the Database connections through the API.

There should be a corresponding JNDI lookup configured within the jboss-web.xml for the SWIFT database. Once you have the connection, you can get the MetaData and then get the URL from this.

In answer to:

P.S. As I am completely new to all this type of work (I am used to doing Oracle Forms and Reports work normally) I am not 100 percent sure if a second instance of AIS application connecting to another database would be using a same UFSSetup.properties file. If they use separate files then I can dump all this research and coding and just put the SID value in UFSSetup.properties and use that instead.

There will be only one UFSSetup.properties file for each instance of Ebase running in tomcat/JBoss. This is located in the tomcat/webapps/ufs/classes/ folder. A second AIS application server should be using its own version of UFSSetup.properties.

Steve

[Steve]

Hi Paul,

Have you considered maybe adding your own properties file and looking up information from this.

This maybe much easier. You can add a properties file to the classpath (tomcat/webapps/ufs/WEB-INF/classes) and lookup configuration data from there.

I am not sure if that would help. I am still struggling to understand exactly how this all ties together. .... by the sounds of things you want to lookup the Database SID based on the AIS system running. You could easily add this configuration yourself as a separate properties file and look it up.

Kind regards

Steve

[PaulCooper]

Have you considered maybe adding your own properties file and looking up information from this.

This maybe much easier. You can add a properties file to the classpath (tomcat/webapps/ufs/WEB-INF/classes) and lookup configuration data from there.

I am not sure if that would help. I am still struggling to understand exactly how this all ties together. .... by the sounds of things you want to lookup the Database SID based on the AIS system running. You could easily add this configuration yourself as a separate properties file and look it up.

Unless I can specify the properties file used for the instance of AIS (ie for the connection) then that changes nothing and only moves the look up from one file to another. To be honest I don't know if a second AIS instance (using a different database) would be a totally new war installation or just a configuration in the existing installation. If it is a new installation then I can forget looking up the connection information and just add the SID property to the UFSSetup.properties file and use that.

Assuming it in the same installation then the issue is that Oracle uses SIDs and a lookup in the tnsnames.ora file to get the database connection information and make a connection. Ebase just uses a JDBC connection string. To get the SID I need to translate the JDBC connection string into a SID. To do that I need the JDBC Connection String or at least the URL part of it and do a lookup.

To get the username and password I have had to intercept the logon jsp and point it at a new jsp page that saves those two values in session variables before redirecting through to the correct logon servlet. (This is because Ebase doesn't have inbuilt session username and password variables like Oracle Forms does and they are stored as hashes instead I believe.) In that intercept page I have hardcoded the SID as a session variable. This isn't ideal so I want to change this to a lookup based on the JDBC connection information.

Yes it is all rather complicated

Paul

[Steve]

Hi Paul,

Each AIS system should have its own properties if added to webapps/WEB-INF/classes directory, so this should not be shared across AIS systems. I am not familiar with the AIS system, so I just guessing here.

It just dawned on me that the SWIFT database configuration is not using JNDI lookup I see the SWIFT Database export is using direct connection and is not using Database Pooling.

<transportObject>
<externalDatasourceId></externalDatasourceId>
<dbUser>REDACTED</dbUser>
<dbPassword>REDACTED</dbPassword>
<useConnectionPooling>N</useConnectionPooling>
<JDBCUrl>jdbc:oracle:thin:@REDACTED</JDBCUrl>
<JDBCDriver>oracle.jdbc.driver.OracleDriver</JDBCDriver>
<databaseType>Oracle</databaseType>
<databaseDescription>Swift - Data Quality Check</databaseDescription>
</transportObject>

Perhaps a configuration file is the only way you can do this? Or a Database lookup that contains configuration data for each AIS system and you uniquely identify each one on startup?


Steve

[PaulCooper]

Each AIS system should have its own properties if added to webapps/WEB-INF/classes directory, so this should not be shared across AIS systems. I am not familiar with the AIS system, so I just guessing here.

It just dawned on me that the SWIFT database configuration is not using JNDI lookup I see the SWIFT Database export is using direct connection and is not using Database Pooling.

Perhaps a configuration file is the only way you can do this? Or a Database lookup that contains configuration data for each AIS system and you uniquely identify each one on startup?

If I read between the lines you are implying that that two instances of an application would use different webapps/WEB-INF/classes folders because they would actually be two totally separate web-apps. In that case the UFSSetup.properties file would be different, as that file is in that folder too, and in that case I can forget about doing a connection look up. (The reason I am not sure that this is the case is that two instances of the same Oracle Forms applications would use the same application but you would log on to a different database.) I will contact Northgate who created AIS and put the question to them.

Paul

[Steve]

Hi Paul,

That was going to be my next suggestion... speak to Northgate and ask them how the AIS system is put together. I do not have that knowledge, so I am guessing.

Tell me how you get on.

Kind regards

Steve

[PaulCooper]

I got a response from Northgate. It turns out that each database requires a separate application installation. So this means I can forget extracting database connection URLs and doing look ups and instead just create a single entry in the UFSSetup.properties file saying:

Code: Select all

CEC.oracle_forms.database_sid = SW27FAM

Job done.

Thanks for all your help Steve.

Regards,

Paul

[Steve]

Hi Paul,

That's great news! Saves a lot of hassle.

Steve