IO and Embedded SQL

Hi, I am working on a database project building SQL statements with Java. I have successfully written the Java program to create tables and insert dummy data into them as well as run a small SQL query.

However, I am also trying to build into this (and thus need help) the ability for the user to input a name from the Student table and then output a grade, derived from the Grade_Report table I have created. I have tried a few different things but can't seem to get the input to work and produce the given output and therefore, I have gone back to the beginning of where I left off; the following program connects to a Sybase database, creates the requested tables and then outputs a query answer. Can you help. Thanks.  

import java.sql.*;
import com.sybase.jdbcx.SybDriver;
import java.io.*;
import java.util.*;

class SQLquery {
  public static void main (String args[]) {
    try {
      SybDriver sybdriver =   // Get Sybase Driver
      (SybDriver) Class
         . forName("com.sybase.jdbc2.jdbc.SybDriver")
         . newInstance();
sybdriver.setVersion(
     com.sybase.jdbcx.SybDriver.VERSION_5 );
DriverManager.registerDriver(sybdriver);

      Connection connection = null;   // Make Database Connection
String server = "database";
String port = "4101";
String database = "c670ab";
String username = args[0];
String password = args[1];
String url =   "jdbc:sybase:Tds:"
             + server + ":"
             + port + "/"
             + database;
  connection =
  DriverManager.getConnection(url, username, password);

// Begin Making Tables & Insertions
Statement stat = connection.createStatement();
stat.executeUpdate(
  "CREATE TABLE Student (Name CHAR(10),StudentNumber INT, Class INT, Major CHAR(10))" );
stat.executeUpdate(
  "INSERT INTO Student VALUES ( 'Smith',17,1, 'CS')" );
stat.executeUpdate(
  "INSERT INTO Student VALUES ( 'Brown',8,2, 'CS')" );

stat.executeUpdate(
  "CREATE TABLE Course (CourseName CHAR(30),CourseNumber VARCHAR(10),CreditHours INT, Department CHAR(5))" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Intro to Computer Science','CS1310',4,'CS')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Data Structures','CS3320',4,'CS')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Discrete Mathematics','MATH2410',3,'MATH')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Database','CS3380',3,'CS')" );

stat.executeUpdate(
  "CREATE TABLE Section (SectionIdentifier VARCHAR(4),CourseNumber VARCHAR(10), Semester CHAR(7), Year VARCHAR(2), Instructor CHAR(20))" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '85','MATH2410','Fall','98','King' )" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '92','CS1310','Fall','98','Anderson')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '102','CS3320','Spring','99','Knuth')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '112','MATH2410','Fall','99','Chang')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '119','CS1310','Fall','99','Anderson')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '135','CS3380','Fall','99','Stone')" );

stat.executeUpdate(
  "CREATE TABLE Grade_Report (StudentNumber VARCHAR(4),SectionIdentifier VARCHAR(4),Grade CHAR(1))" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '17','112','B')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '17','119','C')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '8','85','A')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '8','92','A')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '8','102','B')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '8','135','A')" );

stat.executeUpdate(
  "CREATE TABLE Prerequisite (CourseNumber VARCHAR(10),PrerequisiteNumber VARCHAR(10))" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3380','CS3320')" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3380','MATH2410')" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3320','CS1310')" );

// Run Query
ResultSet query =
  stat.executeQuery("SELECT * FROM Grade_Report");
while( query.next() ){
  System.out.println( query.getString( "Grade" ) );
}
query.close();

//  Drop Tables
stat.executeUpdate( "DROP TABLE Student" );
stat.executeUpdate( "DROP TABLE Course" );
stat.executeUpdate( "DROP TABLE Section" );
stat.executeUpdate( "DROP TABLE Grade_Report" );
stat.executeUpdate( "DROP TABLE Prerequisite" );
stat.close();

      connection.close();
    }
    catch (Exception e) { System.out.println( "error" ); }
} }


agtmulder17Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vikraman_bCommented:
the best way to do this is..
I am just showing the u need to do to make things easier
First always write a DAO(simple java class say singleton class) class in which it has methods for sellect,insert,delete and update.
e.g
public class xxxDAO{
      static xxxDAO   xxxDAOObj = null;
      Logger logger = Logger.getLogger(this.getClass().getName());

      public static xxxDAO   getInstance() {
            if (xxxDAOObj == null) {
                  xxxDAOObj = new xxxDAO   ();
            }

            return xxxDAOObj ;
      }

public void insertStudentTable(){
}
public String getStudentName(){
select name from the student table;
}
public String getStudentGrade(String studentName){
select grade from the student table where name = studentName;
}


...............................
.......etc.,
}

Then write a BO class(Simple java class say singleton class)
Here only u need to decide what r the methods to be called ..even u can handle ur transaction also.
 public String someXXXMethod(){
String grade ="";
String studentName = xxxDAO.getInstance().getStudentName();
grade = xxxDAO.getInstance().getStudentGrade(studentName );

return grade;


}

A simple flow..
do like this way..u wont get any struck...


0
objectsCommented:
something like:

Select * from GradeReport, Student where GradeReport.StudentNumber=Student.StudentNumber and
Name=?
0
agtmulder17Author Commented:
objects,

I see the logic in the query you have given, however how do I implement this so that I have an input  with the given output? I have tried using

     InputStreamReader inBuff = new InputStreamReader(System.in);
     BufferedReader stdin = new BufferedReader ( inBuff );

with System.out.println(stdin);  giving me the result of the query. But I haven't for the life of me been able to successful insert these into my code in the right places to handle this. Is it more simple then I am making it? I think my main confusion is what exactly to put where to produce my output?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

objectsCommented:
   InputStreamReader inBuff = new InputStreamReader(System.in);
    BufferedReader stdin = new BufferedReader ( inBuff );
    String name = stdin.readLine();

0
agtmulder17Author Commented:
objects,

thanks for helping, so are you saying for my program, which I haven't got to work quite yet,

...
class SQLquery {
  public static void main (String args[]) {
 
    InputStreamReader inBuff = new InputStreamReader(System.in);
    BufferedReader stdin = new BufferedReader ( inBuff );
    String name = stdin.readLine();
 
    try {
...

// Run Query
ResultSet query =
  stat.executeQuery("SELECT * FROM Grade_Report, Student WHERE
                             GradeReport.StudentNumber=Student.StudentNumber AND Name =" + stdin.readLine() );
while( query.next() ){
  System.out.println( query.getString( ) );
}
query.close();

....





0
objectsCommented:
yes that looks about right, you'll just need to add quotes around the name.
0
agtmulder17Author Commented:
around name? how do you mean?
0
objectsCommented:
 stat.executeQuery("SELECT * FROM Grade_Report, Student WHERE                              GradeReport.StudentNumber=Student.StudentNumber AND Name ='" + stdin.readLine()+"'" );

0
agtmulder17Author Commented:
I keep getting a complie error

99: No method matching getString() found in interface java.sql.ResultSet.
  System.out.println( query.getString() );
                                     ^
1 error


Here is my code for the query (the tables are being created),

// Run Query
ResultSet query =
  stat.executeQuery("SELECT * FROM Grade_Report, Student WHERE GradeReport.StudentNumber=Student.Number AND Name ='" + stdin.readLine()+"'" );
while( query.next() ){
  System.out.println( query.getString() );
}
query.close();

what do you think?
0
objectsCommented:
you need to specify the column name that you want getString() to return the value of

 System.out.println( query.getString("GRADE") );
0
agtmulder17Author Commented:
Yeah, somehow I took it out, but it compiles now with no problem. I am having runtime errors now but not sure how to debug it. I seem to have been here before; here is what I have:

class SQLquery {
  public static void main (String args[])throws IOException  {

    InputStreamReader inBuff = new InputStreamReader(System.in);
    BufferedReader stdin = new BufferedReader ( inBuff );
    System.out.print ("Insert Student Name: ");
    String name = stdin.readLine();

    try {
...
 
// Run Query
ResultSet query =
  stat.executeQuery("SELECT * FROM Grade_Report, Student WHERE GradeReport.StudentNumber=Student.Number AND Name ='" + stdin.readLine()+"'" );
while( query.next() ){
  System.out.println( query.getString( "Grade" ) );
}
query.close();

I type in a name from the Student table (either Smith or Brown) and hit return, which just drops me down a line. Typing anything else or hitting return simply gives me the error  exeception at the end of my program. It seems as though my query is hanging up since the tables are being created, but not droped from the program. And suggestion of what is going on? Is there a problem with my while loop?
0
objectsCommented:
whats the error?

the fact that the last thing you do is drop the table could be a problem as if any error occurs the tables will not get dropped. And thus next time you run it it fails.
So if its done last then it should be in a finally block.
0
agtmulder17Author Commented:
I am going into Sybase each run and deleting the tables that failed to drop, to avoid a failure on the next run.

Here is the error I get. After typing 'Smith' at the 'Insert Student Name:' prompt, I hit return and the cursor drops down a line. Then hitting return before or after typing something, I get the word 'error' returned.

0
agtmulder17Author Commented:
ok, fixed small query error.

Student WHERE GradeReport.StudentNumber=Student...

is now
Student WHERE Grade_Report.StudentNumber=Student...
0
agtmulder17Author Commented:
but still getting same error situation
0
objectsCommented:
>     catch (Exception e) { System.out.println( "error" ); }

change that line to print out the details of the error

    catch (Exception e) { ex.printStackTrace(); }
0
objectsCommented:
StudentNumber is an INT in your STUDENT table but it is a VARCHAR in the others.
Thats going to cause an error. Any reason why its not a INT in all tables?
0
agtmulder17Author Commented:
go compile error now,

 Undefined variable or class name: ex
catch (Exception e) { ex.printStackTrace(); }
                      ^
1 error
0
objectsCommented:
sorry, i habitually name exceptions 'ex'

catch (Exception e) { e.printStackTrace(); }
0
agtmulder17Author Commented:
Changed back to orginal catch exeception line and Student to INT.

Now it compiles and executes, however here is what happens, which seems like a looping problem?
At the prompt, I type Smith, hit return, and it drops me to the next line. Then typing either Smith or Brown and return, gives the grades for Smith or Brown but only gives one grade on each line. I am trying to get to the point where I can get all the grades averaged for either Smith or Brown.

Do you know why I have to type in the second name and it isn't just reading a name and then returning my desired info?
0
objectsCommented:
better post your current code
0
agtmulder17Author Commented:
ok, did somemore testing. It appears that what ever is entered on the second line returns all of the grades line by line for each student. Not sure how to make the return of grades occur right from the name at the prompt and hit return?
0
agtmulder17Author Commented:
import java.sql.*;
import com.sybase.jdbcx.SybDriver;
import java.io.*;
import java.util.*;

class SQLquery {
  public static void main (String args[])throws IOException  {

    InputStreamReader inBuff = new InputStreamReader(System.in);
    BufferedReader stdin = new BufferedReader ( inBuff );
    System.out.print ("Insert Student Name: ");
    String name = stdin.readLine();


    try {
      SybDriver sybdriver =   // Get Sybase Driver
      (SybDriver) Class
         . forName("com.sybase.jdbc2.jdbc.SybDriver")
         . newInstance();
sybdriver.setVersion(
     com.sybase.jdbcx.SybDriver.VERSION_5 );
DriverManager.registerDriver(sybdriver);

      Connection connection = null;   // Make Database Connection
String server = "database";
String port = "4101";
String database = "c670ab";
String username = args[0];
String password = args[1];
String url =   "jdbc:sybase:Tds:"
             + server + ":"
             + port + "/"
             + database;
  connection =
  DriverManager.getConnection(url, username, password);

// Begin Making Tables & Insertions
Statement stat = connection.createStatement();
stat.executeUpdate(
  "CREATE TABLE Student (Name CHAR(10),StudentNumber INT, Class INT, Major CHAR(10))" );
stat.executeUpdate(
  "INSERT INTO Student VALUES ( 'Smith',17,1, 'CS')" );
stat.executeUpdate(
  "INSERT INTO Student VALUES ( 'Brown',8,2, 'CS')" );

stat.executeUpdate(
  "CREATE TABLE Course (CourseName CHAR(30),CourseNumber VARCHAR(10),CreditHours INT, Department CHAR(5))" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Intro to Computer Science','CS1310',4,'CS')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Data Structures','CS3320',4,'CS')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Discrete Mathematics','MATH2410',3,'MATH')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Database','CS3380',3,'CS')" );

stat.executeUpdate(
  "CREATE TABLE Section (SectionIdentifier VARCHAR(4),CourseNumber VARCHAR(10), Semester CHAR(7), Year VARCHAR(2), Instructor CHAR(20))" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '85','MATH2410','Fall','98','King' )" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '92','CS1310','Fall','98','Anderson')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '102','CS3320','Spring','99','Knuth')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '112','MATH2410','Fall','99','Chang')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '119','CS1310','Fall','99','Anderson')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '135','CS3380','Fall','99','Stone')" );

stat.executeUpdate(
  "CREATE TABLE Grade_Report (StudentNumber INT,SectionIdentifier VARCHAR(4),Grade CHAR(1))" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 17,'112','B')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 17,'119','C')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 8,'85','A')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 8,'92','A')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 8,'102','B')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 8,'135','A')" );

stat.executeUpdate(
  "CREATE TABLE Prerequisite (CourseNumber VARCHAR(10),PrerequisiteNumber VARCHAR(10))" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3380','CS3320')" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3380','MATH2410')" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3320','CS1310')" );

// Run Query
ResultSet query =
  stat.executeQuery("SELECT * FROM Grade_Report, Student WHERE Grade_Report.StudentNumber=Student.StudentNumber AND Student.Name ='" + stdin.r
eadLine()+"'" );
while( query.next() ){
  System.out.println( query.getString( "Grade" ) );
}
query.close();

//  Drop Tables
stat.executeUpdate( "DROP TABLE Student" );
stat.executeUpdate( "DROP TABLE Course" );
stat.executeUpdate( "DROP TABLE Section" );
stat.executeUpdate( "DROP TABLE Grade_Report" );
stat.executeUpdate( "DROP TABLE Prerequisite" );
stat.close();

      connection.close();
    }
catch (Exception e) { e.printStackTrace(); }
} }

0
objectsCommented:
you can't have the grade appear after the name you have type because you need to hit return to read the name.
What u could do is print out the name again after entered:

System.out.print("Grades for "+name+": ");
while( query.next() ){
  System.out.print( query.getString( "Grade" ) + " ");
}
System.out.println();
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
agtmulder17Author Commented:
ok, I see what you mean, that makes sense. My output however seems to give me:

Grades for : BC

or

Grades for : AABA

It looks like it is missing the 'name' of the person after 'Grades for...'
0
agtmulder17Author Commented:
Shoot, I can just take out the 'for' and it should be smooth. Thanks for your help, you have been extremely helpful! As far as I am concerned you have earned all the points. I learned a lot too. Thanks objects!

agtmulder17
0
objectsCommented:
no worries :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.