visual J++ and retreiving rows from sql server

[Sanjeev Shahi]

need urgent help!!!
I am trying to retreive data from SQL server in visaual J++. I've created new database project and in the explorer I can see the database and tables. The ques. is now what I've to do to display the records in the table? pls reply asap
thanks
ss

[Jamie Robertson]

Originally posted by Sanjeev Shahi:
need urgent help!!!
I am trying to retreive data from SQL server in visaual J++. I've created new database project and in the explorer I can see the database and tables. The ques. is now what I've to do to display the records in the table? pls reply asap
thanks
ss

Try to be more specific as to your problem. Are you:
1. lost and don't know where to start?
2. having problems connecting?
3. having problems with a query?
4. having problems displaying/retrieving the results
5. keep getting some exception somewhere ( if it is an exception, post the exception, and the code causing the exception )
The fact that you are using MS VJ++ editor might complicate things too...
Jamie

[Sanjeev Shahi]

Jamie,
I think you are right I am lost. I can make connection and can make query also but I don't know how to display /retreive rows.
As I told in project explorer I can see connection, query(which I created) database,tables etc. but I don't know how to display result of the query I created.
I hope I am clear.
thanks,
ss

[Jamie Robertson]

sample code:

Connection con;
Statement stat;
ResultSet rs;
Vector vTable = new Vector();
try
{
   con = //connection method...
   stat = con.createStatement();
   rs = stat.executeQuery("SELECT * from some_table");
   
   while ( rs.next() )
   {
      Vector vRow = new Vector();
      vRow.add( rs.getString( 1 ) );  //add row data elements to each row vector
      vRow.add( rs.getString( 2 ) );
      ...
      vTable.add( vRow );  //add the row of data to the Vector representation of the table
   }
   rs.close();
   rs = null;
   stat.close();
   stat = null;
   con.close();
   con = null;
   DisplayVector = new DisplayVector( "some_table", vTable);
}
catch ( SQLException se )
{
   System.out.println( se.getMessage() );
}
...
///////////////// new class //////////////
/*  Displays a Vector in a JFrame  */
import java.util.Vector;
import javax.swing.*;
import java.awt.Container;
import java.awt.Color;
public class DisplayVector extends JFrame
{

public DisplayVector(String table_name, Vector tableData)
{
setTitle( table_name + " data" );
Container c = getContentPane();
JPanel tablePanel = new JPanel();
JTable table = new JTable( tableData );
JScrollPane scroller = new JScrollPane( table );
tablePanel.add( scroller );
c.add( tablePanel );
pack();
show();
}
}

This is just a quick and easy way to display your resultset. The explanation of the above code is as follows.
1. query the database for a resultset
2. save the resultset to a 2 dimensional Vector where each element in the table element is a vector that represents a row in the table ( Vector of Vectors ).
3. display the Vector in a JFrame using a JTable which has an convenient constructor that takes a vector.
That's pretty much it, nothing fancy
Jamie
[ July 30, 2002: Message edited by: Jamie Robertson ]

[Sanjeev Shahi]

Jamie,
Thanks for your help I am totally new with jdbc.There is one more question now forget about visual j++. If I am trying the same thing through simple sdk by writting code in note pad.
For making connection to database there are 3 steps as follows.......
1.Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
2.Class.forName("jdbc.DriverXYZ");
3.Connection con = DriverManager.getConnection(url, "myLogin", "myPassword");
Now my ques is.........
1. do I need anything else that java sdk for jdbc connetion.
2. how to find driver. I am using SQL server. If there is no driver present then how to create one.
3. In step 3 which url it is asking and for user and pass it will be same as windows authentication
ss

[Jamie Robertson]

Originally posted by Sanjeev Shahi:
Jamie,
Thanks for your help I am totally new with jdbc.There is one more question now forget about visual j++. If I am trying the same thing through simple sdk by writting code in note pad.
For making connection to database there are 3 steps as follows.......
1.Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
2.Class.forName("jdbc.DriverXYZ");
3.Connection con = DriverManager.getConnection(url, "myLogin", "myPassword");
Now my ques is.........
1. do I need anything else that java sdk for jdbc connetion.
2. how to find driver. I am using SQL server. If there is no driver present then how to create one.
3. In step 3 which url it is asking and for user and pass it will be same as windows authentication
ss

TO USE JDBC:ODBC
1. you need to set up an ODBC datasource name( DSN ) in your control panel. To do that you will need the ODBC drivers installed in your computer for your selected database.
2. Microsoft site will have the drivers you need, but most Windows OS's comes with them already installed, you'll just have to choose them from the DSN setup.
3. no. It will be the username and password you set up through the database security features.
The alternative to using JDBC:ODBC bridge is to use Microsoft's type 4 driver for SQL server and skip out on all the DSN setup.
Jamie

[Jamie Robertson]

Originally posted by Sanjeev Shahi:
...For making connection to database there are 3 steps as follows.......
1.Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
2.Class.forName("jdbc.DriverXYZ");
3.Connection con = DriverManager.getConnection(url, "myLogin", "myPassword");

make that:
Class.forName("jdbc.odbc.JdbcOdbcDriver"); //loads the driver
String url = "jdbcdbcSNNAME";
//DSNNAME is the datasource name you created in the control panel
Connection con = DriverManager.getConnection(
url, "userID", "passwd");

Jamie
[ July 30, 2002: Message edited by: Jamie Robertson ]

[Sanjeev Shahi]

jamie,
I tired the changes you told me to do but it is giving following error..........
ClassNotFoundException: jdbc.odbc.JdbcOdbcDriver
No suitable driver
ss

[Sanjeev Shahi]

Jamie,
I tried the following option also
The alternative to using JDBC DBC bridge is to use Microsoft's type 4 driver for SQL server and skip out on all the DSN setup.

but for this when I run applicatio it gives following error after couple of mins

[Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: JVM_recv in
socket input stream read
Is the whole process too complicated?
ss

[Jamie Robertson]

Originally posted by Sanjeev Shahi:
jamie,
I tired the changes you told me to do but it is giving following error..........
ClassNotFoundException: jdbc.odbc.JdbcOdbcDriver
No suitable driver
ss

post your connection code so we can have a look at it.

[Sanjeev Shahi]

here is my code..............
import java.sql.*;
import java.util.*;
import javax.swing.*;
public class dbtest2
{
public static void main(String[] args)
{

try {
Class.forName("jdbc.odbc.JdbcOdbcDriver");
}catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try{
Connection con;
Statement stat;
ResultSet rs;
String url = "jdbc dbc:test";
// :test is system DSN
con = DriverManager.getConnection(url, "user id", "pass");
stat = con.createStatement();
rs = stat.executeQuery("SELECT * from table_name");
while ( rs.next() ) {
System.out.println(rs.getString(1) + "----" + rs.getString(2));
}
rs.close();
rs = null;
stat.close();
stat = null;
con.close();
con = null; } catch ( SQLException se ){ System.out.println( se.getMessage() );}
( "some_table", vTable);
}
}

ss

[Sanjeev Shahi]

please omit last line of code.......
( "some_table", vTable);

thanks
ss

[Jamie Robertson]

the actual code looks OK... I use the same methods to connect to an Access database located on a remote server. The problem may be technical. SQL server is more complicated. It should have a port in which to connect to, listeners, security that access doesn't. I am not familiar with connecting to SQL server,nor am I familiar with using a DSN, so I wouldn't be much help there either. So, the best way for me to help you is if you use a type 4 driver ( like the one from here. This should eliminate a lot of environment problems. So if you want to try to use the Microsoft driver I would:
1. I would first get the database working. by this I mean, you should be able to connect and send/receive SQL commands from a SQL editor. This way you can ensure that everything is running fine on the SQL Server side of things.
2. make sure you have the jdk 1.2 or higher as well as a current JVM. This will ensure that the java environment is JDBC ready.
3. I would use the type 4 driver to connect to the database using the connection procedure outlined in its documentation. ( ensure that the driver is placed in the computer's classpath first! )
4. trouble shoot from there.
Connection to SQL Server using the type 4 driver I believe is as follows:

String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; 
Class.forName(driver);
 
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://<hostname>:<port number>;databasename=<database name>","username","password"); 

good luck,
Jamie