Sockets/Java Problem

Tar.Aldarion

Hello, I have a client and a server contacting each other over a socket.
On the server side I query a database at the end of this code snippet and try to send the following over the socket:

  if (one.equals("Booking")) {
                    try {
                        Statement statement = connection.createStatement();
                        ResultSet r = statement.executeQuery("select SeatsAvail from trainoperator WHERE Departure = '" + three + "'  and  Arrival = '" + four + "'and  TrainID = '" + seven + "'");
                        r.next();
                        int Seats;
                        Seats = r.getInt("SeatsAvail");
                        int seatsordered = Integer.parseInt(two);
                        if (Seats < seatsordered) {
                            String errormsg = "NoTickets";
                            out.println("NoTicket:" + " " + errormsg);
                        } else {
                           connection.setAutoCommit(false);
                            statement.executeUpdate("UPDATE trainoperator SET SeatsAvail = Seatsavail - '" + two + "' WHERE Departure = '" + three + "'  and  Arrival = '" + four + "' and TrainID ='" + seven + "'");
                            statement.executeQuery("set @unique = UUID()");
                            statement.executeUpdate("INSERT into bookingtable (TicketNum, BookingNum, DepartureStation, ArrivalStation, TrainID)values('" + two + "', @unique,'" + three + "','" + four + "','" + seven + "');");
                           // statement.executeUpdate("commit;");
                            connection.commit();
                            //else connection.rollback();

ResultSet RS = statement.executeQuery("SELECT * from addroute");
            RS.next();

do
              {  //System.out.println(RS.getString("NewDeparture") + (RS.getString("NewArrival") + RS.getString("Weight") + RS.getString("OperatorNum") + (RS.getString("Trainid"))));
                 out.println("AddRoutes:"+" "+RS.getString("NewDeparture")+" " + (RS.getString("NewArrival")+ " " + RS.getString("Weight") + " " + RS.getString("OperatorNum")+ " " + (RS.getString("Trainid")+ " " + bookingid)));
                  
              }
                 while
                (RS.next());

This should send all the data in the SQL table over the socket.
The sytem.out line shows that all the information is being read correctly from the table and being returned. However when I try to send the data over the socket to the server only the first row of the table is received.

Client code snippet is:

String responseLine;

                               
                                if ((responseLine = in.readLine()) != null) {

                                    System.out.println("responseLine is: " + responseLine);
                                    StringTokenizer st = new StringTokenizer(responseLine);

I think I just don't understand how you sned and recieve the data in a loop. The first row is sent and recieved, furhter rows are not.

fergalr

Tar.Aldarion wrote: »

Hello, I have a client and a server contacting each other over a socket.
On the server side I query a database at the end of this code snippet and try to send the following over the socket:

  if (one.equals("Booking")) {
                    try {
                        Statement statement = connection.createStatement();
                        ResultSet r = statement.executeQuery("select SeatsAvail from trainoperator WHERE Departure = '" + three + "'  and  Arrival = '" + four + "'and  TrainID = '" + seven + "'");
                        r.next();
                        int Seats;
                        Seats = r.getInt("SeatsAvail");
                        int seatsordered = Integer.parseInt(two);
                        if (Seats < seatsordered) {
                            String errormsg = "NoTickets";
                            out.println("NoTicket:" + " " + errormsg);
                        } else {
                           connection.setAutoCommit(false);
                            statement.executeUpdate("UPDATE trainoperator SET SeatsAvail = Seatsavail - '" + two + "' WHERE Departure = '" + three + "'  and  Arrival = '" + four + "' and TrainID ='" + seven + "'");
                            statement.executeQuery("set @unique = UUID()");
                            statement.executeUpdate("INSERT into bookingtable (TicketNum, BookingNum, DepartureStation, ArrivalStation, TrainID)values('" + two + "', @unique,'" + three + "','" + four + "','" + seven + "');");
                           // statement.executeUpdate("commit;");
                            connection.commit();
                            //else connection.rollback();

ResultSet RS = statement.executeQuery("SELECT * from addroute");
            RS.next();

do
              {  //System.out.println(RS.getString("NewDeparture") + (RS.getString("NewArrival") + RS.getString("Weight") + RS.getString("OperatorNum") + (RS.getString("Trainid"))));
                 out.println("AddRoutes:"+" "+RS.getString("NewDeparture")+" " + (RS.getString("NewArrival")+ " " + RS.getString("Weight") + " " + RS.getString("OperatorNum")+ " " + (RS.getString("Trainid")+ " " + bookingid)));
                  
              }
                 while
                (RS.next());

This should send all the data in the SQL table over the socket.
The sytem.out line shows that all the information is being read correctly from the table and being returned. However when I try to send the data over the socket to the server only the first row of the table is received.

Client code snippet is:

String responseLine;

                               
                                if ((responseLine = in.readLine()) != null) {

                                    System.out.println("responseLine is: " + responseLine);
                                    StringTokenizer st = new StringTokenizer(responseLine);

I think I just don't understand how you sned and recieve the data in a loop. The first row is sent and recieved, furhter rows are not.

Maybe I'm missing something, but I'm a little confused by the question here - in your client code snippet, you don't seem to show any sort of looping construct? (If you were just leaving the loop out, then please include it as context)

All you seem to do is check if a line isn't null, and print it out - which will, of course, only print out one line.

Is this in a loop construct you just haven't shown?
Otherwise, I wouldn't expect it to print out more than a single line.

If you want to keep reading from the server, and printing out, again and again you need something more like:

[B][COLOR="Red"]while[/COLOR][/B] ((responseLine = in.readLine()) != null) 
{
      //process responseLine
           //check for 'quit' or similar communication ending message, and quit if you receive it 
}

Tar.Aldarion

Whoops, while was surrounding more things so I didn't put it in by accident. Dunno why it didn't work but I just put in your while and it works fine, whoops!

voxpop

fergalr is right about not having any loop when reading the data from the socket. Basically when reading from the socket the underlying objects are reading a number of bytes into a buffer. There are different implementation of Input/Output streams that give you extra abstraction and do the work for you on demarcation. Readline as the name suggests, reads a line of data from the underlying socket, so you need to keep looping until there are no more lines to read.

Another option (which adds a bit of overhead, but removes any demarcation issues) is using the ObjectInputStream and ObjectOutputStream. When using these streams you can write a string object (or other objects) over the socket and read the whole object in one go on the client side. It makes socket comms very easy. The only caveats are that the object needs to be serializable, but String is anyway and its a little slower than other methods.

Tar.Aldarion

cheers.


While I'm asking questions!


Here I query a database and return values.
If (len == 1) loops works fine.
However in the else if (len ==2) part I get the error
java.sql.SQLException: Illegal operation on empty result set.

at the line: TrainID = "" + (rr.getString("TrainID"));

if I hardcode TrainID to a specific number and comment ouot that line everything works fine, know why this is happening?

Statement statement = connection.createStatement();
//String departure = "" +availabletimes.getSelectedValue();

                            String sqlQuery = "SELECT TrainID FROM serverdb.trainoperator where `DepartureTime` = '" + booktime + "' and `Departure` = '" + Departures.getSelectedValue() + "' and `Arrival`='" + arrivals.getSelectedValue() + "'";
                           //  System.out.println(sqlQuery);
                            ResultSet rr = statement.executeQuery(sqlQuery);
                            rr.next();
                           // System.out.println(rr.getString("TrainID") + "= TrainID");
                          
                            System.out.println(TrainID);
                            [B]TrainID = "" + (rr.getString("TrainID"));[/B]
                           // TrainID ="1";
                           tixnum = "" + (tickets.getSelectedValue());
if (len ==1)
{
                                

                                out.println("Booking" + " " + tixnum + " " + hop1 + " " + hop2 + "  " + booktime + " " + canceltextdata + " " +TrainID);
                              
}
else if (len==2)
{

   out.println("Booking" + " " + tixnum + " " + hop1 + " " + hop2 + "  " + booktime + " " + canceltextdata + " " + TrainID);

   out.println("Booking2" + " " + tixnum + " " + obj1 + " " + obj2 + "  " + booktime + " " + canceltextdata + " "+TrainID);
                               
}

TrainID is becoming null or something ?

voxpop

Where exactly are you looping and what is len in relation to.

Tar.Aldarion

len is the length of an object. If the object is of length one(ie booking a single trip galway to dublin on a train) then len ==1 is ran and that works fine. This object will contain: sligo galway
If I want to book sligo to galway to dublin, for example, the object will be of length 2 and will contain
sligo galway
galway dublin

Object[] object= PossibleTrips.getSelectedValues();
                   int len = Array.getLength(object);
                   
                      String lol = ""+object[0];
                     
                     StringTokenizer hop = new StringTokenizer(lol);
                      hop1 = hop.nextToken();
                      hop2 = hop.nextToken();

                   
                       if (len == 2)
                     {
                         String lol1 = ""+object[1];
                        
                          StringTokenizer objA = new StringTokenizer(lol1);
                      obj1 = objA.nextToken();
                      obj2 = objA.nextToken();
}

voxpop

Are you looping over the sql if you have more than one trip, ie are you making 2 calls to the database ?
Im still finding it pretty difficult to work out what you are doing, the hop array values dont seem to have an effect on the sql ?

From the code you have shown - TrainID = "" + (rr.getString("TrainID")) is the same no matter if len==1 or len==2. The issues I could guess that are going wrong are :

1) you are closing the connection, hence the resultset get closed;
2) your sql query is not returning anything for the second query (if you are running 2 separate qeuries)
3) your sql query is returning just one record - so you process the first record and are not checking the return of rs.next(), so there may be no other record, hence the error.


On a purley pedantic note - you may be better using String[] over Object[] if you are working with an array of Strings. On an array you can just call array.length to get the length. You should be checking the return bool value of rr.next() and dealing with the result i.e. false = no record.

Tar.Aldarion

Sorry for confusing you, here is the code in order.

if (MyClient != null && out != null && in != null) {

                            try {
Object[] object= PossibleTrips.getSelectedValues();
                     //System.out.print(object[0]);
                    
                   int len = Array.getLength(object);
                   
                      String lol = ""+object[0];
                     System.out.println("what is length:"+len);
                     StringTokenizer hop = new StringTokenizer(lol);
                      hop1 = hop.nextToken();
                      hop2 = hop.nextToken();
System.out.println("hops="+hop1+hop2);
                   
                       if (len == 2)
                     {
                         String lol1 = ""+object[1];
                         System.out.println(lol1+"<-anything there");
                          StringTokenizer objA = new StringTokenizer(lol1);
                      obj1 = objA.nextToken();
                      obj2 = objA.nextToken();
                     }
                     else if (len == 3)
                     {
                     String lol2 = ""+object[2];
                         System.out.println(lol2+"<-anything there");
                     }
//else
//                     {
//                     String lol3 = ""+object[3];
//                         System.out.println(lol3+"<-anything there");
//                     }
//                     String lol1 = ""+object[1];
//                    if (object[1] !=null)
//                     {
//
//                     System.out.println(lol1+"<-anything there");
//                     }
                      
                                //  out.println("HELLO");
                                // out.println("QUIT");
                                // out.println(Departures.getSelectedValue());
//availabletimes.getSelectedValue()
Statement statement = connection.createStatement();
//String departure = "" +availabletimes.getSelectedValue();

                            String sqlQuery = "SELECT TrainID FROM serverdb.trainoperator where `DepartureTime` = '" + booktime + "' and `Departure` = '" + Departures.getSelectedValue() + "' and `Arrival`='" + arrivals.getSelectedValue() + "'";
                           //  System.out.println(sqlQuery);
                            ResultSet rr = statement.executeQuery(sqlQuery);
                            rr.next();
                           // System.out.println(rr.getString("TrainID") + "= TrainID");
                          
                            System.out.println(TrainID);
                           // TrainID = "" + (rr.getString("TrainID"));
                            TrainID ="1";
                           tixnum = "" + (tickets.getSelectedValue());
if (len ==1)
{
                                

                                out.println("Booking" + " " + tixnum + " " + hop1 + " " + hop2 + "  " + booktime + " " + canceltextdata + " " +TrainID);
                               
}
else if (len==2)
{

                                out.println("Booking" + " " + tixnum + " " + hop1 + " " + hop2 + "  " + booktime + " " + canceltextdata + " " + TrainID);
System.out.println("Booking" + " " + tixnum + " " + hop1 + " " + hop2 + "  " + booktime + " " + canceltextdata + " " + TrainID);

                                    



                                out.println("Booking2" + " " + tixnum + " " + obj1 + " " + obj2 + "  " + booktime + " " + canceltextdata + " "+TrainID);
                                 System.out.println("Booking2" + " " + tixnum + " " + obj1 + " " + obj2 + "  " + booktime + " " + canceltextdata + " "+TrainID);
}

I thought TrainID = "" + (rr.getString("TrainID")) is the same no matter if len==1 or len==2 also, that is why i am quite confused!
Maybe I am running 2 queries by accident there somehow.

I'll have a look throught it now with your suggestions, thanks very much!

lynchie

You are calling rr.next(); without checking its return value to check whether the result set has a valid row or not. Calling rr.getString() on an empty resultset would cause that SQLException you are getting.
Also, if the query can return more than one row you should be also performing your logic inside a while(rr.next()) {} loop.
Also, you are not closing your resultset nor statement and have no try catch finally blocks to handle SQLExceptions and close the RS and Statement in the event of an exception

Tar.Aldarion

Thanks.

I know it returns a result set, which can only be one row. I have a catch, it's jsut further along in the code, I should set it to close perhaps!
The thing is that it seems to then be an empty result set when len ==2 and not when len==1, but I dont know why the result set is changing?

Why should I use rr.close() or close the statement btw, new to using this...I'll look it up!
All my stuff seems to work without closeing them. Well apart from this bit!

voxpop

Tar.Aldarion wrote: »

Why should I use rr.close() or close the statement btw, new to using this...I'll look it up!
All my stuff seems to work without closeing them. Well apart from this bit!

When working with database code or sockets or anyting that the jvm cant clean up itself, you should always wrap the code in try/catch/finally. No matter what happens the finally clause will be executed and in here you should close connections to database / sockets /etc. If you dont, you can end up with a resource leak - i.e. thousands of idle connections to the database doing nothing but consuming resource

Tar.Aldarion

Thanks, I'll stick all the cleaning up in a finally, if I ever get this running!
I kind of assumed it would close after the method itself.