Solved

how do i run sql statements on a db2/400 database form my java applet

Posted on 2003-10-29
6
240 Views
Last Modified: 2010-05-18
ok i am simply trying to connect to my database so i can run some simple SQL statements

this is proving to be very difficuly for me... i am really new too java in general and am not having much luck

so i guess im asking if anyone has an example of a SIMPLE java applet that will allow me to run some SQL statments

i have the database configured on ODBC, i dont know if that is necessary or not..

but any help would be awsome!
0
Comment
Question by:SOBKing
  • 3
6 Comments
 
LVL 14

Accepted Solution

by:
sudhakar_koundinya earned 250 total points
ID: 9647787


The following source code shows you how to connect a DB2 database to a Java Applet. This is a simple example which allows the user to type in an SQL on the tables in a Database and return the results to a scrollable text region. It can easily be altered to work with any kind of database.

Here is an example of how you would call the applet in your HTML file:

<HTML>
<HEAD>
<TITLE>Database Example</TITLE>
</HEAD>
<BODY>
<H1>Database Example</H1>
<hr>
<applet code=dbf.class width=650 height=350>
</applet>
<hr>
</BODY>
</HTML>

Notice the “applet code” command in the above example. This is where you call your applet class and set the width and height of the applet. The class name is case sensitive.

Below is the source code for the applet. Notice the comments in the java source code. There are sometimes lines of code which are commented out and say that that is the code for the application version. DB2 requires different commands if you’re creating an applet, than if you’re creating an application. Note the differences. If you are using a different database language, this limitation should not apply.

// use net.URL class for database identifier
import java.net.URL;
import java.awt.*;
import java.applet.Applet;

//import java.sql.*;
// application version
// for applet, must use tempjava.sql to work with db2
import tempjava.sql.*;

// load the driver classes
// IBM JDBC drivers to access DB2 database.
// load this package to load the JDBC kernel -- DriverManager.
//import ibm.sql.*;
// application version
// use the applet version
import ibm.netsql.*;

// begin the applet ********************************************

public class dbf extends java.applet.Applet
{

// constructor for the base applet.
// save in a file called: "dbf.java" -- case sensitive.
// The applet name must match the file name the applet is stored in.

Button DBFConnect = new Button("Connect to DB2");
TextField QueryField = new TextField(40);
TextArea OutputField = new TextArea(10,95);

// Here we create the objects we plan to use in the applet.
// The Connection object is part of the JDBC API, and is the
// how we link the JdBC’s function to the applet and
// register the driver with DriverManager

Connection con ;

static{
  try{
          // Class.forName("ibm.sql.DB2Driver");
          // application call: change to applet call
            Class.forName("ibm.netsql.DB2Driver");
     }catch(ClassNotFoundException e)
     { e.printStackTrace(); }
} // end static

// since this data is published on the internet, you will need
// to setup a user which can be used anonymously by anyone who
// accesses this data. You can have that user setup with read
// only privileges as well.
// optionally, you can provide url, name, and password fields
// which the user must fill in before being granted access. You
// would not normally want to do this on an open web site.. ie:
// you are granting users the ability to look at a table full of
// products, real estate, etc.
// if this is used on an intranet, you might then want to setup
// name and password fields, but not necessarily the URL.

String url = "jdbc:db2://your.server.url:7777/sample" ;
String Name = "theusername" ;
String Password = "thepassword" ;

public void init()
{
QueryField.setEditable(true);
OutputField.setEditable(false);

// create a new instance of GridBagLayout
GridBagLayout gridbag = new GridBagLayout();
GridBagConstraints Con = new GridBagConstraints();

// Set the layout of the applet to the gridbag that we created
setLayout(gridbag);

// Set the font and color of the applet.
setFont(new Font("Ariel", Font.PLAIN, 12));
setBackground(Color.gray);

Con.weightx=1.0;
Con.weighty=0.0;
Con.anchor = GridBagConstraints.CENTER;
Con.fill = GridBagConstraints.NONE;
Con.gridwidth = GridBagConstraints.REMAINDER;

// put the connect button on a row by itself, set the constraints
// and add it.
gridbag.setConstraints(DBFConnect, Con);
add(DBFConnect);
add(new Label("SQL Query"));
gridbag.setConstraints(QueryField, Con);
add(QueryField);

// Add a label and set the colors.
Label result_label = new Label("Result");
result_label.setFont(new Font("Ariel", Font.PLAIN, 16));
result_label.setForeground(Color.blue);
gridbag.setConstraints(result_label, Con);
add(result_label);

// to make the outputfield take up as much of the remaining
// space as possible, use the weighty=1 to set it apart
// from the previously added components and give it a lot of
// room

Con.weighty=1.0;
gridbag.setConstraints(OutputField, Con);
OutputField.setForeground(Color.white);
OutputField.setBackground(Color.black);
add(OutputField);

// show is a deprecated line (used in older java version)
// to use the new 1.1.1 command issue this instead:
// setVisible(true);

show();

} // init

 

public boolean handleEvent(Event evt)
{

// The standard format for this method includes the Event class
// where all the properties are set. This is deprecated and
// there is a better way of doing it now, but if you want to
// support the older Java versions, this will work on those and
// 1.1.1 as well.

if (evt.target == QueryField) // if the cursor is in the QueryField
{
  char c = (char) evt.key ; // check if the Enter key is pressed
  if (c == '\n')
  {
   // pass the contents of the queryfield to the select method
   // the results will show in the outputfield:
   OutputField.setText(Select(QueryField.getText()));
   return true ;
  }
  else{ return false ; }
}

if (evt.target == DBFConnect)
{
// when the user clicks the “connect” button, connect to the database
// specified in the url and the user name specified in the name
// field.

try
{

  // Connect to the database.
  con = DriverManager.getConnection(url, Name, Password);

  // change the name of the button, to indicate the connection
  // has been made
  DBFConnect.setLabel("Reconnect to DB2");
}

// set exceptions
// an exception will be thrown if there is a problem connecting
// using the specified parameters. The exception will be printed
// to the outputfield and the console.
catch( Exception e)
{
  e.printStackTrace();
  OutputField.setText(e.getMessage());
}
return true ;
}
return false ;

}// handleEvent() end

public String Select(String QueryLine)
{

// Use this method to create the SQL query command
// we return a String

String Output="";
int columns ;
int pos ;

try
{

// this can throw exceptions if an invalid query is entered
// or if the connection breaks

// create a Statement class to execute the query.
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(QueryLine);

// use the getMetaData method to return the number of columns
// in the result as an integer variable.

columns=(rs.getMetaData()).getColumnCount();

// use the next method to return fetch each row, one at a time.
// this is one method of doing this, using inputStream is another.

while(rs.next())
{

  // get each column one by one for each row
  for( pos=1; pos<=columns; pos++)
  {
    // get each field and add a space to it, then append it
    // to the output variable

    Output+=rs.getObject(pos)+" " ;
  } // end for loop

// add a RETURN for each row to start a new line
Output+="\n";

} // end while loop

// close the query connection streams, but stay connected to the
// database
stmt.close();
}

// catch any exceptions thrown during query or retrieval

catch( Exception e)
{

e.printStackTrace();
Output=e.getMessage();

}

// return the result
return Output;

}

public void destroy()
{

try {con.close(); }
catch( Exception e)
{
  e.printStackTrace();
  System.out.println(e.getMessage());
}

} // end destroy

}// end applet dbf
0
 

Author Comment

by:SOBKing
ID: 9647935
where would i get ibm.netsql

i have seen this line in a few examples i have been looking at
import ibm.netsql.*;
but i dont seem to have that
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9648059
Install DB2 (server or client) on your machine as your Web server. The Java applet and JDBC support is installed as part of DB2.

Extract DB2's tempjava.sql and ibm.netsql packages from the sqllib\java\db2java.zip file and move them to the appropriate directories
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 11424049
:-)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
scoreUp challenge 14 48
mapShare challenge 13 69
compre toata in where clue oracle 4 44
JList custom Cell Renderer refresh 15 43
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now