Hi there,

At present, our I.T. department are exploring different methods of connecting to our IDMS database via the internet.

We have realised that we could use an alternative to ODBC drivers to connect to our IDMS database. The alternative is JDBC Type 4 drivers. If we have to use ODBC drivers, it means we are going to have to set up a lot of new software on our mainframe which will be a lengthy process.

If we use JDBC, we may be able to establish a connection with our IDMS database a lot sooner than if we were to use ODBC. It all depends upon the pros and cons of both ODBC and JDBC.

So, can JDBC be connected to through PHP, ASP, etc.........or does it have to be connected to via JSP and Java? Will using JDBC limit which scripting technologies we can use? If connecting to JDBC via PHP, ASP etc is possible, is it a tricky process? Or is it relatively straight forward? What does it involve?

Will using JDBC limit what kind of applications we can build? Or is it just as flexible as ODBC?

We don't want rush ahead and implement the use of JDBC without checking with first. Even though it may be quicker to set up JDBC initially, there may be complications that pop-up further down the line that some of you may be aware of?

Also, the way that we set up the connection to our database mentions these 3 possible routes:

1) TCP/IP connects to the Type 4 JDBC driver, which resides on the Java Platform.
2) Type 4 JDBC driver connects to a servlet, which connects to web server (these also reside on the Java Platform)
3) The web server then connects to an applet on the browser client.

When it says any Java Platform, does this mean Windows, Linux etc? And then where it says it connects to an applet on the browser client, does that mean it connects to the internet browser? I am a little confused.

Another route is as follows:

1) TCP/IP connects to the Type 4 JDBC driver, which resides on the Java Client.
2) The Type 4 JDBC driver then connects directly to the applet, which also resides on the Java Client.

What is a Java Client?

The final route is using JDBC type 3 drivers. It says that to use these drivers, we must have a JDBC Server which is located on the Java Platform. It connects this way:

1) TCP/IP connects to the JDBC server, which resides on the Java Platform.
2) The JDBC server connects to the Type 3 JDBC driver, which resides on the browser client.
3) The type 3 ODBC driver then connects to an applet on the browser client.

I am so stumped by all this. Can anyone help clarify all this for me???

Any advice on this subject would be great.

Thank you.

JDBC is a database connector / API for use with the Java programming language.

So unless your site backend is going to be coded in Java it's of little or no interest to you.

Originally Posted by chrishirst

JDBC is a database connector / API for use with the Java programming language.

So unless your site backend is going to be coded in Java it's of little or no interest to you.

Hi there,

Thanks for the reply.

I understand that Java is the primary language to be used, but is there NO way what-so-ever that java can be bridged with PHP? I've read a few articles saying that PHP can call Java classes. If the database connection is defined in the java class, can the connection work if PHP calls that class??

Consider an alternative.
Setup a J2EE server (tomcat or Jetty would be fine) with Spring/Hibernate(or iBatis) to communicate with the database, deal with connection pooling, and implement caching for queries (selects).

Have the developers implement a simple REST API on the J2EE server. Now the PHP and ASP servers can make REST calls to the J2EE server instead of talking to the database directly.

Benefits:

• simple API for web devs (ASP/PHP)
• massively scalable
• changes to DB schemas will not require changes to PHP/ASP code
• optimizations are transparent
• web devs won't be able to do wacky things to the database
• no duplicate code (and maintenance) between the PHP and ASP servers

Originally Posted by willcode4beer

Consider an alternative.
Setup a J2EE server (tomcat or Jetty would be fine) with Spring/Hibernate(or iBatis) to communicate with the database, deal with connection pooling, and implement caching for queries (selects).

Have the developers implement a simple REST API on the J2EE server. Now the PHP and ASP servers can make REST calls to the J2EE server instead of talking to the database directly.

Benefits:

• simple API for web devs (ASP/PHP)
• massively scalable
• changes to DB schemas will not require changes to PHP/ASP code
• optimizations are transparent
• web devs won't be able to do wacky things to the database
• no duplicate code (and maintenance) between the PHP and ASP servers

Does this use any of the JDBC drivers?

We are aiming to use the JDBC type 4 drivers, as Computer Associates have provided us with software (CA-IDMS Server) that utilises those drivers. This server is able to communicate with our CA-IDMS database using TCP/IP and JDBC Type 4 drivers.

We don't have any problem coding in Java, we are just worried that it won't be suitable for what we are trying to achieve. We don't want to implement a connection to our database using JDBC Type 4 and then realise that Java can't do what we want it to do. Can Java do pretty much everything PHP can do these days? I've never touched Java, apart from extremely briefly at university.

Your JDBC drivers would be used on the J2EE server.
Since the other servers talk to it using REST, they don't have to care that a database exists.

Regarding Java, it can do everything PHP does and much much more.

Regarding the outline I suggested above, I should explain the main benefit.
If you have both PHP and ASP servers talking to the same database, making the same queries, you will have duplicate code (SQL).
If only one server connects to the database, then the duplication is removed.
You could also test the ASP/PHP code without a database (or J2EE server) by creating mock responses (xml). This allows teams with a dependency to work without being blocked.

Just be sure you configure your database connection using JNDI. Don't let developers create direct connections. This will give you a single point to configure things like connection pooling and environment switching.
To get a connection, the devs would just query JNDI.

These diagrams might help to explain what we are trying to achieve.

We intend to use the Type 4 JDBC driver. The web developers we are working with say that it is possible to connect to our IDMS database using PHP, MySQL and JDBC driver Type 4.

In-order to allow SQL to execute on our IDMS we have to create SQL schemas, which can affect the way in which SQL executes and the form of the results it returns.

My concerns are using mySQL with our IDMS, and whether PHP can connect to JDBC type 4. I've not seen any examples of this yet, and I've been looking on the web for the past 3 days.

Any ideas?

I'm afraid I don't know much about having PHP use JDBC drivers.

I do feel I should point out a security problem with the planned architecture. Allowing an applet to have direct communication with the database is a HUGE invitation for trouble.

Originally Posted by willcode4beer

I'm afraid I don't know much about having PHP use JDBC drivers.

I do feel I should point out a security problem with the planned architecture. Allowing an applet to have direct communication with the database is a HUGE invitation for trouble.

Hi there,

I'm not sure if it will have direct comm's with the DB?

If it comes out into the open that it does, I will point this out.

In the meantime, if anyone else can help me I would very much appreciate it.

@Willcode4beer: could you explain the process of gathering a teste connection to a DB using TCP/IP and JDBC drivers, using the data source name that CA have provided me? I am guessing it will be similar to jdbc:idms(hostnameort) - thats just a random guess (dont laugh at the newb lol).

I know how to compile java scripts to class files and test them. I just don't understand how to set up the code to test this connection. Also, I have a link in my start menu to "start the JDBC server"......would that server be able to be conencted to if its running on my machine to provide the connection? I'm all new to this so its a little confusing at the moment.

any help would be fantastic.

rgds
jp

Originally Posted by willcode4beer

I'm afraid I don't know much about having PHP use JDBC drivers.

I do feel I should point out a security problem with the planned architecture. Allowing an applet to have direct communication with the database is a HUGE invitation for trouble.

Sorry to be a pain, but could you throw together a diagram for the J2EE / REST architecture you described above?

Here ya go:



Be sure the database config (user/pass, pooling, etc) done with JNDI, NOT in the java source code.

The easiest way to build the webapp would be with Spring and Hibernate. You may want to consider using EHCache to inprove performance as well. The web app should serve XML instead of web pages.

Setup this way, the PHP and ASP servers would make simple HTTP requests to access the data. These servers would be completely unaware of the database.

If you need to scale, you could put a load balancer infront of the J2EE server and cluster them (I'd recommend terracotta for this). You could also cluster databases if needed (many people just build clusters of MySQL or PostgreSQL).

Here's an example of configuring a JDBC datasource using JNDI on Tomcat. The config is different depending on the server used but, the use of it will be the same (regardless of the server chosen, because of the J2EE standard).

A good dev should be able to get this started and working in less than a day. Then the time can be spent adding support for the various types of queries to be done.

Originally Posted by willcode4beer

Here ya go:



Be sure the database config (user/pass, pooling, etc) done with JNDI, NOT in the java source code.

The easiest way to build the webapp would be with Spring and Hibernate. You may want to consider using EHCache to inprove performance as well. The web app should serve XML instead of web pages.

Setup this way, the PHP and ASP servers would make simple HTTP requests to access the data. These servers would be completely unaware of the database.

If you need to scale, you could put a load balancer infront of the J2EE server and cluster them (I'd recommend terracotta for this). You could also cluster databases if needed (many people just build clusters of MySQL or PostgreSQL).

Here's an example of configuring a JDBC datasource using JNDI on Tomcat. The config is different depending on the server used but, the use of it will be the same (regardless of the server chosen, because of the J2EE standard).

A good dev should be able to get this started and working in less than a day. Then the time can be spent adding support for the various types of queries to be done.

Thanks!

I'm confused though. I'm brand new to Java and development so I'm not clued up on all the technical terms, and the technical documents may as well be written in a foreign language

So we'd get the dev's to install Apache Tomcat on the webserver (is this the J2EE server itself?). When you say build the web app with Spring / Hibernate (is this just a framework that Java can utilise???), do you mean that it would all be coded in Java? I'm confused because the dev's already have an application written in PHP (which we want to be able to use, but connecting PHP to IDMS via JDBC Type 4 is the problem).

Will there be a Java VM used within this framework?

Dev's then configure the JDBC Type 4 in this server? How is that done? Do you have any coding examples?

Where does REST come into it? Is REST just the way in which you write the code?

How is the data gathered from the IDMS via TCP/IP? What invokes the SQL requests?



I'm very confused!

Originally Posted by jpmad4it

So we'd get the dev's to install Apache Tomcat on the webserver (is this the J2EE server itself?).

yes, it takes about five minutes to install

Originally Posted by jpmad4it

When you say build the web app with Spring / Hibernate (is this just a framework that Java can utilise???), do you mean that it would all be coded in Java?

Spring provides a framework. Hibernate, is an ORM (Object Relational Mapping) library. It simplifies working with the database.

Originally Posted by jpmad4it

I'm confused because the dev's already have an application written in PHP (which we want to be able to use, but connecting PHP to IDMS via JDBC Type 4 is the problem).

in this case, why not use the database they developed for (since it works)?

Originally Posted by jpmad4it

Will there be a Java VM used within this framework?

You will need a Java VM to run tomcat, the code will run in tomcat

Originally Posted by jpmad4it

Dev's then configure the JDBC Type 4 in this server? How is that done? Do you have any coding examples?

http://tomcat.apache.org/tomcat-5.5-...les-howto.html

Originally Posted by jpmad4it

Where does REST come into it? Is REST just the way in which you write the code?

I would use this so you only need the SQL in one place, instead of your various apps (as described).

Originally Posted by jpmad4it

How is the data gathered from the IDMS via TCP/IP? What invokes the SQL requests?

If you use hibernate, then hibernate would. If not, then you'd write prepared statements in Java that would contain the SQL.

Originally Posted by willcode4beer

yes, it takes about five minutes to install

Spring provides a framework. Hibernate, is an ORM (Object Relational Mapping) library. It simplifies working with the database.

in this case, why not use the database they developed for (since it works)?

You will need a Java VM to run tomcat, the code will run in tomcat

http://tomcat.apache.org/tomcat-5.5-...les-howto.html

I would use this so you only need the SQL in one place, instead of your various apps (as described).

If you use hibernate, then hibernate would. If not, then you'd write prepared statements in Java that would contain the SQL.

Thanks for the help, I really appreciate it.

Is this a good definition of a software framework, like Spring for example:

A software framework is a re-usable design for a software system (or subsystem). A software framework may include support programs, code libraries, a scripting language, or other software to help develop and glue together the different components of a software project. Various parts of the framework may be exposed through an API.

And Hibernate is an ORM library that works well within the Spring framework?

I'm not sure what you mean by "use the database they developed for"? The developers plan to use TYPO3 (a PHP and MySQL Content Management System) to access our IDMS (network and non-relational database). The problem is that to access the IDMS we have to use either ODBC or JDBC. ODBC is out of the question as it uses CAICCI, which requires us to install a massive VTAM component on the mainframe which we are not prepared to do. So the only option is to use JDBC (which doesn't support PHP, hence the PHP-Java bridge software which enables us to use PHP together with JDBC) or an ODBC-JDBC bridge (not evaluated this yet - may be useful).

I'm still confused about JNDI. I know its an API. Does it basically allow databases to be binded to a name? So is that Tomcat article saying showing how to connect to a database via connection pooling? This (JNDI) is the best way to configure the database connection I remember you saying? I am not sure if it will work with the IDMS JDBC driver that Computer Associates have created and provided us with?

I should clear this up now, I don't think we will be using PHP AND ASP, what I meant was PHP OR ASP. So does that get rid of the duplicate SQL problem, and therefore we won't need REST? Or does REST bridge the gap from PHP to the Java Platform? If REST doesn't bridge the gap between PHP and the Java Platform, what does?

EDIT: when we first spoke you said that the "applet" would be connecting directly to the IDMS. Is this still an issue even though the PHP sends the request to the PHP-Java Bridge, which sends the request to JDBC, which connects via TCP/IP? Is the JDBC connection configuration the "applet" you were referring to?

Sorry for all the questions, but thank you very much for all your brilliant help.

regards
Jp

Yes, that is a good definition for a framework

Originally Posted by jpmad4it

And Hibernate is an ORM library that works well within the Spring framework?

Yes, they work very well together. Spring makes working with Hibernate much easir.

Originally Posted by jpmad4it

I'm not sure what you mean by "use the database they developed for"?

You said there was a working application. Isn't it using a database?

Originally Posted by jpmad4it

I'm still confused about JNDI. I know its an API. Does it basically allow databases to be binded to a name? So is that Tomcat article saying showing how to connect to a database via connection pooling? This (JNDI) is the best way to configure the database connection I remember you saying? I am not sure if it will work with the IDMS JDBC driver that Computer Associates have created and provided us with?

It will work fine. JNDI doesn't care about the specifics of the databse. JNDI can be used to configure anything (Strings, Content Repositories, JDBC connections, whatever...).

Originally Posted by jpmad4it

I should clear this up now, I don't think we will be using PHP AND ASP, what I meant was PHP OR ASP. So does that get rid of the duplicate SQL problem, and therefore we won't need REST? Or does REST bridge the gap from PHP to the Java Platform? If REST doesn't bridge the gap between PHP and the Java Platform, what does?

If there is only one client then there wouldn't be any duplication

Originally Posted by jpmad4it

EDIT: when we first spoke you said that the "applet" would be connecting directly to the IDMS. Is this still an issue even though the PHP sends the request to the PHP-Java Bridge, which sends the request to JDBC, which connects via TCP/IP? Is the JDBC connection configuration the "applet" you were referring to?

Your diagram showed the applet connecting to the database.
Applets don't care about PHP or anything else, they run in their own context. I do strongly recommend against letting an applet connect directly to the database. Anyone with, any kind of knowledge, could get access to the username/password used to connect to the database.

Originally Posted by jpmad4it

Sorry for all the questions, but thank you very much for all your brilliant help.

regards
Jp

No problem.

BTW, if you're in the San Francisco area, I could show you how to wire this stuff up.

Originally Posted by willcode4beer

Yes, that is a good definition for a framework

Yes, they work very well together. Spring makes working with Hibernate much easir.

You said there was a working application. Isn't it using a database?

It will work fine. JNDI doesn't care about the specifics of the databse. JNDI can be used to configure anything (Strings, Content Repositories, JDBC connections, whatever...).

If there is only one client then there wouldn't be any duplication

Your diagram showed the applet connecting to the database.
Applets don't care about PHP or anything else, they run in their own context. I do strongly recommend against letting an applet connect directly to the database. Anyone with, any kind of knowledge, could get access to the username/password used to connect to the database.

No problem.

BTW, if you're in the San Francisco area, I could show you how to wire this stuff up.

The working application (coded in PHP) uses a MySQL database. We have IDMS

One client? We're going to have lots LOL. So REST is needed?

Which diagram, the large one or the smaller one? The smaller one shows two ways of connecting via JDBC Type4. One via the applet on the Java client, and another via a web server with a java platform running on it.

I so SO wish I was in the US lol. I'm in the UK. My Auntie lives near Escondido near San Diego, give her a call lol!

Or just write it up for me anyway!!

Thanks again

The PHP server will be using a MySQL database too, can the REST API communicate with the MySQL - both ways i.e. send data to MySQL and receive data from mySQL using ODBC / JDBC??? Could we take something from the IDMS and place it in the MySQL in this architecture?

Also, how is the code converted and sent as XML back to the PHP server?

Wow,

That sounds like a lot of trouble for no reason (and overkill for most applications).

Is your current database really really complex and critical? I'm thinking export it to something more usable...

Pod.

Originally Posted by just_a_pod

Wow,

That sounds like a lot of trouble for no reason (and overkill for most applications).

Is your current database really really complex and critical? I'm thinking export it to something more usable...

Pod.

I my application, I've got to deal with several millions visitors per day. I also have several different applications that each render (the same) data in different ways. I need to be able to optimize the database schemas and queries without editing all of the applications.
Some examples: rhapsody.com, realguide, realarcade, as well as sites that get rendered inside of client software. In the case of the realguide, it also needs to be able to handle 16 language/regions.

The truth is, such a system isn't really very complex. But, it is highly scalable, very easy to maintain, and very easy to make changes to. To me it is worth making an upfront investment in a solid framework to make my future work easier to do.

Originally Posted by willcode4beer

I my application, I've got to deal with several millions visitors per day. I also have several different applications that each render (the same) data in different ways. I need to be able to optimize the database schemas and queries without editing all of the applications.
Some examples: rhapsody.com, realguide, realarcade, as well as sites that get rendered inside of client software. In the case of the realguide, it also needs to be able to handle 16 language/regions.

The truth is, such a system isn't really very complex. But, it is highly scalable, very easy to maintain, and very easy to make changes to. To me it is worth making an upfront investment in a solid framework to make my future work easier to do.

@Willcode4beer: Sorry to be a pain, Would these architectures also be possible:

Combining Ruby on Rails with IBM WebSphere Application Server, using JRuby to deploy web apps, using JDBC Type 4 to connect to the database via TCP/IP.

Can I run Ruby on Rails and use JRuby to set up a JDBC type 4 connection to the database, and to deploy web apps?

What other application servers can ruby run on?