User Name Password Register
DaniWeb IT Discussion Community
All
What is DaniWeb IT Discussion Community?
You're currently browsing the Java section within the Software Development category of DaniWeb, a massive community of 397,768 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,450 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Java advertiser: Lunarpages Java Web Hosting
Views: 2893 | Replies: 2
Reply
Join Date: Nov 2004
Posts: 1
Reputation: agtmulder17 is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
agtmulder17 agtmulder17 is offline Offline
Newbie Poster

IO and Embedded SQL

  #1  
Nov 22nd, 2004
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

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 5:05 pm. Reason: added [code] tags
AddThis Social Bookmark Button
Reply With Quote  
Join Date: Sep 2004
Posts: 84
Reputation: jerbo is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 1
jerbo jerbo is offline Offline
Junior Poster in Training

Re: IO and Embedded SQL

  #2  
Nov 22nd, 2004
I assume you are wanting to take the value from 'stdin' and build your query?
Then:
ResultSet query =
stat.executeQuery("SELECT * FROM Grade_Report");
becomes
// 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.
Reply With Quote  
Join Date: Sep 2004
Posts: 84
Reputation: jerbo is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 1
jerbo jerbo is offline Offline
Junior Poster in Training

Re: IO and Embedded SQL

  #3  
Nov 22nd, 2004
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.
Reply With Quote  
Reply

Only community members can participate in forum threads. You must register or log in to contribute.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 

DaniWeb Java Marketplace
Thread Tools Display Modes

Similar Threads
Other Threads in the Java Forum

All times are GMT -4. The time now is 4:16 am.
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC