IO and Embedded SQL

Expand Post »

Hi, I am working on a project using Java and embedded SQL in the Java. I have gotten the SQL to successful do what I need it to (In this case, create tables and insert data into them). However, I am having a difficult time with IO. The goal is input a name which is associated with a table I have created, retrieve a grade for that name, and output it to the screen. Here is my code thus far, which I have not been successful in implementing IO. Thanks for your help. Sincerely, Jason

Java Syntax (Toggle Plain Text)
import java.sql.*;
import com.sybase.jdbcx.SybDriver;
import java.io.*;
import java.util.*;
 
class lab8 {
  public static void main (String args[]) {
class IO { 
   public static void main (String[] args) throws IOException { 
 
     InputStreamReader inBuff = new InputStreamReader(System.in); 
     BufferedReader stdin = new BufferedReader ( inBuff ); 
 
     System.out.print ("Student's Name: ");    
 
   try {
      SybDriver sybdriver =
      (SybDriver) Class
         . forName("com.sybase.jdbc2.jdbc.SybDriver")
         . newInstance();
sybdriver.setVersion(
     com.sybase.jdbcx.SybDriver.VERSION_5 );
DriverManager.registerDriver(sybdriver);
 
      Connection connection = null;
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);
 
      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')" );
 
ResultSet query =
  stat.executeQuery("SELECT * FROM Grade_Report");
while( query.next() ){
  System.out.println( query.getString( "Grade") );
}
query.close();
 
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" ); }
} }
import java.sql.*;
import com.sybase.jdbcx.SybDriver;
import java.io.*;
import java.util.*;

class lab8 {
  public static void main (String args[]) {
class IO { 
   public static void main (String[] args) throws IOException { 
  
     InputStreamReader inBuff = new InputStreamReader(System.in); 
     BufferedReader stdin = new BufferedReader ( inBuff ); 
  
     System.out.print ("Student's Name: ");    

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

      Connection connection = null;
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);

      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')" );

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

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" ); }
} }

Last edited by alc6379; Nov 22nd, 2004 at 6:05 pm. Reason: added [code] tags

Similar Threads

Create Access Database using SQL queries in MS Access and FileMaker Pro
XML Management in RSS, Web Services and SOAP
embedded sql statements in VB.NET
How to links database by C language? in C++

agtmulder17

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Nov 2004

Permalink

Nov 22nd, 2004

Re: IO and Embedded SQL

I assume you are wanting to take the value from 'stdin' and build your query?
Then:

Java Syntax (Toggle Plain Text)
ResultSet query =
stat.executeQuery("SELECT * FROM Grade_Report");
ResultSet query =
stat.executeQuery("SELECT * FROM Grade_Report");

becomes

Java Syntax (Toggle Plain Text)
// First create a String to hold your query at the begining of your code
String sqlQuery;
 
// Now either after you read the input or just before your query is built:
sqlQuery = "SELECT * FROM Grade_Report WHERE StudentNumber = " + stdin.toString();
 
ResultSet query = stat.executeQuery(sqlQuery);
// First create a String to hold your query at the begining of your code
String sqlQuery;

// Now either after you read the input or just before your query is built:
sqlQuery = "SELECT * FROM Grade_Report WHERE StudentNumber = " + stdin.toString();

ResultSet query = stat.executeQuery(sqlQuery);

Note, I haven't done inputs, so I think it is stdin.toString(), but am not 100% sure.

Also, the Grade_Report table only contains the StudentNumber, not name. So you would have to do some other lookups to find the Student number from the Student table if given the name.

Hopefully with the above you should be able to find the Student number when given the name.

jerbo

Reputation Points: 11

Solved Threads: 1

Junior Poster in Training

Offline

84 posts
since Sep 2004

Permalink

Nov 22nd, 2004

Re: IO and Embedded SQL

BTW - If you want to go advanced with parameter queries, then you can check out the following (Keep SQL out of code) at:
http://www.javapractices.com/Topic105.cjp

It has some excellent code to allow you to store your queries in your properties file. This allows you to modify the queries without having to modify your code.

jerbo

Reputation Points: 11

Solved Threads: 1

Junior Poster in Training

Offline

84 posts
since Sep 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Java Forum Timeline: problems accessing class instances

Next Thread in Java Forum Timeline: solvetion of my code.

DaniWeb Message

Cancel Changes

User Name
Password