Java RSS Java RSS

IO and Embedded SQL

Reply

•
•
Join Date: Nov 2004
Posts: 1
Reputation: agtmulder17 is an unknown quantity at this point 
Solved Threads: 0
agtmulder17 agtmulder17 is offline Offline
Newbie Poster

IO and Embedded SQL

 
0
  #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

Java Syntax (Toggle Plain Text) 
  1. import java.sql.*;
  2. import com.sybase.jdbcx.SybDriver;
  3. import java.io.*;
  4. import java.util.*;
  5.  
  6. class lab8 {
  7.   public static void main (String args[]) {
  8. class IO { 
  9.    public static void main (String[] args) throws IOException { 
  10.  
  11.      InputStreamReader inBuff = new InputStreamReader(System.in); 
  12.      BufferedReader stdin = new BufferedReader ( inBuff ); 
  13.  
  14.      System.out.print ("Student's Name: ");    
  15.  
  16.    try {
  17.       SybDriver sybdriver =
  18.       (SybDriver) Class
  19.          . forName("com.sybase.jdbc2.jdbc.SybDriver")
  20.          . newInstance();
  21. sybdriver.setVersion(
  22.      com.sybase.jdbcx.SybDriver.VERSION_5 );
  23. DriverManager.registerDriver(sybdriver);
  24.  
  25.       Connection connection = null;
  26. String server = "database";
  27. String port = "4101";
  28. String database = "c670ab";
  29. String username = args[0];
  30. String password = args[1];
  31. String url =   "jdbc:sybase:Tds:"
  32.              + server + ":"
  33.              + port + "/"
  34.              + database;
  35. connection =
  36.   DriverManager.getConnection(url, username, password);
  37.  
  38.       Statement stat = connection.createStatement();
  39. stat.executeUpdate(
  40.   "CREATE TABLE Student (Name CHAR(10),StudentNumber INT, Class INT, Major CHAR(10))" );
  41. stat.executeUpdate(
  42.   "INSERT INTO Student VALUES ( 'Smith',17,1, 'CS')" );
  43. stat.executeUpdate(
  44.   "INSERT INTO Student VALUES ( 'Brown',8,2, 'CS')" );
  45.  
  46. stat.executeUpdate(
  47.   "CREATE TABLE Course (CourseName CHAR(30),CourseNumber VARCHAR(10),CreditHours INT, Department CHAR(5))" );
  48. stat.executeUpdate(
  49.   "INSERT INTO Course VALUES ( 'Intro to Computer Science','CS1310',4,'CS')" );
  50. stat.executeUpdate(
  51.   "INSERT INTO Course VALUES ( 'Data Structures','CS3320',4,'CS')" );
  52. stat.executeUpdate(
  53.   "INSERT INTO Course VALUES ( 'Discrete Mathematics','MATH2410',3,'MATH')" ); 
  54. stat.executeUpdate(
  55.   "INSERT INTO Course VALUES ( 'Database','CS3380',3,'CS')" );
  56.  
  57. stat.executeUpdate(
  58.   "CREATE TABLE Section (SectionIdentifier VARCHAR(4),CourseNumber VARCHAR(10), Semester CHAR(7), Year VARCHAR(2), Instructor CHAR(20))" );
  59. stat.executeUpdate(
  60.   "INSERT INTO Section VALUES ( '85','MATH2410','Fall','98','King' )" );
  61. stat.executeUpdate(
  62.   "INSERT INTO Section VALUES ( '92','CS1310','Fall','98','Anderson')" );
  63. stat.executeUpdate(
  64.   "INSERT INTO Section VALUES ( '102','CS3320','Spring','99','Knuth')" );
  65. stat.executeUpdate(
  66.   "INSERT INTO Section VALUES ( '112','MATH2410','Fall','99','Chang')" );
  67. stat.executeUpdate(
  68.   "INSERT INTO Section VALUES ( '119','CS1310','Fall','99','Anderson')" );
  69. stat.executeUpdate(
  70.   "INSERT INTO Section VALUES ( '135','CS3380','Fall','99','Stone')" );
  71.  
  72. stat.executeUpdate(
  73.   "CREATE TABLE Grade_Report (StudentNumber VARCHAR(4),SectionIdentifier VARCHAR(4),Grade CHAR(1))" );
  74. stat.executeUpdate(
  75.   "INSERT INTO Grade_Report VALUES ( '17','112','B')" );
  76. stat.executeUpdate(
  77.   "INSERT INTO Grade_Report VALUES ( '17','119','C')" );
  78. stat.executeUpdate(
  79.   "INSERT INTO Grade_Report VALUES ( '8','85','A')" );
  80. stat.executeUpdate(
  81.   "INSERT INTO Grade_Report VALUES ( '8','92','A')" );
  82. stat.executeUpdate(
  83.   "INSERT INTO Grade_Report VALUES ( '8','102','B')" );
  84. stat.executeUpdate(
  85.   "INSERT INTO Grade_Report VALUES ( '8','135','A')" );
  86.  
  87. stat.executeUpdate(
  88.   "CREATE TABLE Prerequisite (CourseNumber VARCHAR(10),PrerequisiteNumber VARCHAR(10))" );
  89. stat.executeUpdate(
  90.   "INSERT INTO Prerequisite VALUES ( 'CS3380','CS3320')" );
  91. stat.executeUpdate(
  92.   "INSERT INTO Prerequisite VALUES ( 'CS3380','MATH2410')" );
  93. stat.executeUpdate(
  94.   "INSERT INTO Prerequisite VALUES ( 'CS3320','CS1310')" );
  95.  
  96. ResultSet query =
  97.   stat.executeQuery("SELECT * FROM Grade_Report");
  98. while( query.next() ){
  99.   System.out.println( query.getString( "Grade") );
  100. }
  101. query.close();
  102.  
  103. stat.executeUpdate( "DROP TABLE Student" );
  104. stat.executeUpdate( "DROP TABLE Course" );
  105. stat.executeUpdate( "DROP TABLE Section" );
  106. stat.executeUpdate( "DROP TABLE Grade_Report" );
  107. stat.executeUpdate( "DROP TABLE Prerequisite" );
  108. stat.close();
  109.  
  110.       connection.close();
  111.     }
  112.     catch (Exception e) { System.out.println( "error" ); }
  113. } }
Last edited by alc6379; Nov 22nd, 2004 at 6:05 pm. Reason: added [code] tags
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2004
Posts: 84
Reputation: jerbo is an unknown quantity at this point 
Solved Threads: 1
jerbo jerbo is offline Offline
Junior Poster in Training

Re: IO and Embedded SQL

 
0
  #2
Nov 22nd, 2004
I assume you are wanting to take the value from 'stdin' and build your query?
Then:
Java Syntax (Toggle Plain Text) 
  1. ResultSet query =
  2. stat.executeQuery("SELECT * FROM Grade_Report");
becomes
Java Syntax (Toggle Plain Text) 
  1. // First create a String to hold your query at the begining of your code
  2. String sqlQuery;
  3.  
  4. // Now either after you read the input or just before your query is built:
  5. sqlQuery = "SELECT * FROM Grade_Report WHERE StudentNumber = " + stdin.toString();
  6.  
  7. 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 Quick reply to this message  
•
•
Join Date: Sep 2004
Posts: 84
Reputation: jerbo is an unknown quantity at this point 
Solved Threads: 1
jerbo jerbo is offline Offline
Junior Poster in Training

Re: IO and Embedded SQL

 
0
  #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 Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the Java Forum
Thread Tools Search this Thread



account android api applet application array arrays automation bidirectional binary birt bluetooth class classes client code columns component constructor database designadrawingapplicationusingjavajslider draw eclipse error errors exception expand fractal game givemetehcodez graphics gui guidancer homework html ide image inetaddress inheritance integer intellij j2me java javamicroeditionuseofmotionsensor javaprojects jlabel jme jni jpanel jtextfield jtree julia linux list loop map method methods midlethttpconnection mobile mobiledevelopmentcreatejar monitoring myaggfun netbeans newbie nullpointerexception open-source oracle plazmic print problem program project property recursion ria scanner search server set sharepoint smart sms smsspam sort sourcelabs splash sql sqlite static string subclass support swing testautomation threads tree unlimited webservices windows
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC