We help IT Professionals succeed at work.

Working with 2 sql statements

cosmic_bird
cosmic_bird asked
on
500 Views
Last Modified: 2012-06-27
Hi, I'd like someone to take a look at the following piece of code.
The objective is to take in a value x from a midlet, then use this value x in a select statement to retrieve another value y from a table in the database. Then finally use this value y in an insert statement.

The problem I'm getting is I get a null value from the insert statement. I know the problem exists because the value i'm working off 'String user_id' is in a while statement. I don't know how to get around this problem. Any ideas?



________________________________________
imports..etc


public class pref extends HttpServlet {


 private final String driverName = "org.gjt.mm.mysql.Driver";
 private final String connURL = "jdbc:mysql://localhost/traffic";
 private static final String CONTENT_TYPE = "text/html";


String user_id;


public void doPost(HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException
{

DataInputStream dis = new DataInputStream(request.getInputStream());

String num = dis.readUTF();

String road1 = dis.readUTF();
String road2 = dis.readUTF();
String time = dis.readUTF();
String kph = dis.readUTF();


Connection conn = null;

try
    {
      Class.forName(this.driverName).newInstance();
      conn = DriverManager.getConnection(this.connURL);
      Statement statement2 = conn.createStatement();

ResultSet rs = statement2.executeQuery
    ("select user_id from usertable where mobileNum = '"+num+"'");
 
 

    // Output the resultset data
   while(rs.next())
    {
user_id  = rs.getString("user_id");
}

      }
    catch (Exception e)
    {
    // return (e.toString());
    }
    finally
    {
      try
      {
        if (conn != null)
          conn.close();  //try to close the db connection
      }
      catch (SQLException sqle)
      {
      //  return (sqle.toString());
      }
    }


Connection con = null;
   
      try
    {
      Class.forName(this.driverName).newInstance();
      con = DriverManager.getConnection(this.connURL);
      Statement statement = con.createStatement();

 
statement.executeUpdate("insert into pref (user_id, road1, road2, time, kph)  values ( '"+user_id+"', '"+road1+"', '"+road2+"', '"+time+"', '"+kph+"')");

 
      }

    catch (Exception e)
    {
    // return (e.toString());
    }
    finally
    {
      try
      {
        if (con != null)
          con.close();  //try to close the db connection
      }
      catch (SQLException sqle)
      {
      //  return (sqle.toString());
      }
    }

}


}
Comment
Watch Question

Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
> he problem I'm getting is I get a null value from the insert statement.

what do u mean by this.
are you sure the select is returning a row?
you should test if user_id is null before inserting.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
If you're using that approach the insert will need to be done using a seperate statement.
Also using a PreparedStatement would be more efficient.

Commented:
no, there's no need using separate statements for SELECT and INSERT.

You can execute as many INSERT/UPDATE/DELETE queries as you need within a single statement. But of course you should use separate statements (or close resultsets manually) when executing multiple SELECT queries. Here we have only ONE select and one insert, so everything is ok.

Regards!
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
I stand corrected :)
Still be better using a PreparedStatement though.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
I would not recommend having a single Connection to your whole servlet!  What would happen if the network forced the connection to close after 5 minutes or something?  You would have to restart your servlet!  I'm sure there is some sort of connection pool you could use which would be a far cleaner option!

Anyway, your original problem, I would say that your best bets are as objects originally suggested (ensure that the value is not null), but also, implement error handling, you have the code:
    catch (Exception e)
    {
    // return (e.toString());
    }
You should NEVER (okay, very, VERY rarely) ignore exceptions, for your application whilst your trying to solve your problem a simple e.printStackTrace() would probably help!  But log it!

Also, what should happen if there are multiple user_id's with the same mobile number?  Or is that a unique constraint on the database?  And is the id a primary key field?  Or can it be set to null?  As you will loop through all of the returned rows, and select the last one (note that the order will not be guaranteed).  Also, put debug statements through your code, for example, look into the log4j logging framework (apache.org), it will help you!

Author

Commented:
Thanks everyone for your replies. My coding is weak, so I'll have to work on it - I try and learn form your tips.

orangehead911 and vk33 thanks for your code. The problem I seem to get with both pieces of your code is that the insert statement doesn't take place at all. Yet if I take out the select statement code the insert does occur. It's like the select statement effects the insert statement.
Could you please tell us what the column data types in the database are?
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
> Yet if I take out the select statement code the insert does occur.

Where do you get the userid if you don't do a select?

Perhaps the userid used from the select is not valid.

Author

Commented:
The userid datatype column is INT.

>Where do you get the userid if you don't do a select?

I temporarily leave it as null.


In the meantime I've discovered that nothing is actually being passed over from the midlet. The idea is to pass a persistantly stored value, but the writeUTF method on the client side seems not do accept the value stored in the MIDP database - I think because that value is orignally stored in ByteArrayOutputStream format. I'll get back to this topic soon once I sort out passing the persistantly stored value over to the sevlet, then I'll accept an answer. Thanks!
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
> In the meantime I've discovered that nothing is actually being passed over from the midlet.

If nothing is being passed over then the select is maybe not find any rows so nothing is getting inserted.
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>but the writeUTF method on the client side seems not do accept the value stored in the MIDP database

Make sure you're sending linefeeds

writeUTF(value);
writeUTF("\r\n");
out.flush();

You need to make some changes int he code i posted:

.
.
.
               ResultSet rs = statement.executeQuery();
               int user_id = -1;

               // Output the resultset data
               if (rs.next()) {
                    user_id = rs.getInt(1);
               }
               statement = conn.prepareStatement("insert into pref (user_id, road1, road2, time, kph)  values (?,?,?,?,?)");
               statement.setInt(1, user_id);
.
.
.

And you would similarly change the other statement setter to reflect their data type in the database.

Author

Commented:
<Make sure you're sending linefeeds

<writeUTF(value);
<writeUTF("\r\n");
<out.flush();

Yeah I'm doing exactly that. I even passed the persistent value into a textbox on the midlet and used the getString method
eg   String x = tb.getString    to make sure that I am passing a string ot the writeUTF method eg...  writeUTF(x);

and btw if I say     form.append(x)  it gives an output to the form, so there definetly is a value there, I'm just mistified why it's not going over.
Here's the code snipet from the midlet anyway,

/there's only on value in the recordstore
RecordEnumeration re = db.enumerate();
if(re.hasNextElement()) {
 j =  new String(re.nextRecord());
}

:
:
:
os.writeUTF(j);


Just wondering if it's anything to do with bytes?


CERTIFIED EXPERT
Top Expert 2016

Commented:
>>but the writeUTF method on the client side seems not do accept the value stored in the MIDP database

Can you expand on this - don't you mean after sending the value it doesn't appear anywhere?

Author

Commented:
you know the way writeUTF

Author

Commented:
You know the way writeUTF is used to output data to the server, when used with a persistently stored record it passes over a blank value.
CERTIFIED EXPERT
Top Expert 2016

Commented:
Sorry - don't understand that. I'm trying too see what might be happening:

>>I'm just mistified why it's not going over.

How do you know it *hasn't* gone over? Simply because you're not seeing it?
CERTIFIED EXPERT
Top Expert 2016

Commented:
Also, can you answer this:

Have you tried sending over a literal?

writeUTF("Is there anybody there?");
// ETC

Author

Commented:
yeah, a literal goes over ok.

I notice that you pass into the recordstore data as follows...

ByteArrayOutputStream baos = new ByteArrayOutputStream();
DataOutputStream outputDataStream = new DataOutputStream(baos);

outputDataStream.writeUTF(baos);

perhaps in order to take data back out from the recordstore I need to reverse the way I input the data in?
CERTIFIED EXPERT
Top Expert 2016

Commented:
You have to read and write in the same order

writeObject
writeUTF
writeChar


readObject
readUTF
readChar

Author

Commented:
I've changed my code around in the midelt as follows..



try {
RecordEnumeration re = db.enumerate();
if(re.hasNextElement()) {
Byte [] b = (re.nextRecord());
String j = b.toString();

}

|
|
os.writeUTF(j);

and I get the follwoing insert intot e db on the server side....[B@d590dbc
so something does go over. Surely String j = b.toString(); should work, i.e should send over text?
 

CERTIFIED EXPERT
Top Expert 2016

Commented:
>>Surely String j = b.toString(); should work

No. If you're doing things like that it would have to be

String j = new String(b);

Author

Commented:
no, that gives a blank insertion
CERTIFIED EXPERT
Top Expert 2016

Commented:
What does this print when you print it?

b.length

Author

Commented:
it gives an error   ->cannot resolve symbol

Author

Commented:
I'm getting the feeling it's something to do witht the sql database - because a simple System.out.println(j) gives an output
CERTIFIED EXPERT
Top Expert 2016

Commented:
In your previous code, just do

      byte [] b = (re.nextRecord());
             String j = "NULL";
      if (b != null && b.length > 1) {
           j = new String(b);
      }

CERTIFIED EXPERT
Top Expert 2016

Commented:
>>because a simple System.out.println(j) gives an output

That's what i was aking you to print!

Author

Commented:
i'm afraid it gave a blank again
CERTIFIED EXPERT
Top Expert 2016

Commented:
Please print the result of printing the value 'j' from the code above (to System.out)

Author

Commented:
it juust gave a what what sotred in the record store -> 5005
CERTIFIED EXPERT
Top Expert 2016

Commented:
OK. Now please show how you're doing the real output

Author

Commented:
I presume you mean output to the server?


void Pref(String url1) throws IOException {
HttpConnection c = null;


DataOutputStream os = null;

try {

c = (HttpConnection)Connector.open(url1);
c.setRequestMethod(HttpConnection.POST);
c.setRequestProperty("IF-Modified-Since", "20 Oct 2001 16:19:14 GMT");
c.setRequestProperty("User-Agent","Profile/MIDP-1.0 Configuration/CLDC-1.0");
c.setRequestProperty("Content-Language", "en-CA");
c.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");

os = c.openDataOutputStream();

os.writeUTF(j);


os.flush();
}
 finally {

if(os != null) {
os.close();
}
if(c != null) {
c.close();
}
}

}

CERTIFIED EXPERT
Top Expert 2016

Commented:
I mean together with the code we've just been talking about

Author

Commented:
if(re.hasNextElement()) {


 

b = (re.nextRecord());
             j = "NULL";
    if (b != null && b.length > 1) {
        j = new String(b);
    }
}
System.out.println(j);

}catch(Exception ex) {}

CERTIFIED EXPERT
Top Expert 2016

Commented:
So far i've seen everything apart from what i wanted - the code together!

>>}catch(Exception ex) {}

Very dangerous - how are you going to know if an exception was thrown?

Author

Commented:
Sorry, i think i'm confused.
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>Sorry, i think i'm confused.

a. We need to see the code you're using. So far all that's come out is unconnected snippets

b. Don't have empty catch blocks - print the stack trace. Unless you put a comment into the catch block saying why you're ignoring it

Author

Commented:
ok, I'll post the important section of the midlet code..
void Pref(String url1) throws IOException {
HttpConnection c = null;


DataOutputStream os = null;



try {




c = (HttpConnection)Connector.open(url1);
c.setRequestMethod(HttpConnection.POST);
c.setRequestProperty("IF-Modified-Since", "20 Oct 2001 16:19:14 GMT");
c.setRequestProperty("User-Agent","Profile/MIDP-1.0 Configuration/CLDC-1.0");
c.setRequestProperty("Content-Language", "en-CA");
c.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");


os = c.openDataOutputStream();



os.writeUTF(j);


os.flush();

}
 finally {

if(os != null) {
os.close();
}
if(c != null) {
c.close();
}
}

}




else if(screen == prefset)
    {
      
if (c == submitCommand)
{


                  try {
RecordEnumeration re = db.enumerate();
if(re.hasNextElement()) {
b = (re.nextRecord());

}

         j = new String(b);

}
catch(Exception ex) {}


try {
Pref(url1);
}catch(IOException e) {}




The following code concerns the record store itself...

import javax.microedition.rms.*;
import java.util.Enumeration;
import java.util.Vector;
import java.io.*;

public class StockDB {

   RecordStore recordStore = null;
       
   public StockDB() {}

   public StockDB(String fileName) {
      try {
        recordStore = RecordStore.openRecordStore(fileName, true);
      } catch(RecordStoreException rse) {
        rse.printStackTrace();
      }
   }

   public void close() throws RecordStoreNotOpenException,
                               RecordStoreException {
        if (recordStore.getNumRecords() == 0) {
            String fileName = recordStore.getName();
            recordStore.closeRecordStore();
            recordStore.deleteRecordStore(fileName);
        } else {
            recordStore.closeRecordStore();
        }
    }


   public synchronized void addNewStock(String record) {
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      DataOutputStream outputStream = new DataOutputStream(baos);
      try {
          outputStream.writeUTF(record);
      }
      catch (IOException ioe) {
          System.out.println(ioe);
          ioe.printStackTrace();
      }
      byte[] b = baos.toByteArray();
      try {
          recordStore.addRecord(b, 0, b.length);
      }
      catch (RecordStoreException rse) {
          System.out.println(rse);
          rse.printStackTrace();
      }
    }

    public synchronized RecordEnumeration enumerate() throws RecordStoreNotOpenException {
       return recordStore.enumerateRecords(null, null, false);
    }


CERTIFIED EXPERT
Top Expert 2016

Commented:
Sorry that's simply not coherent and full of empty catch blocks until we get to the StockDB class, where i guess the received data are being written in addNewStock. It's not clear *how* they're being received though.

A question - can you explain (without the aid of too much technical language) what you're attempting to do here? Something along the lines of:

" i want to send the bar got from the foo over a wireless connection and put it in the database"

Author

Commented:
Sorry I should be more clear.

-I have one value stored in the the small db on the mobile phone.

-I want to send this data over to a servlet which uses sql to store the value in a database.

-I can successfully store records in the mobile's recordstore and I can successfully retreive records from the recordstore. i know thi sbecasue i can view the output form the recordstore on a form or textfield whatever.

-I can successfully send data over to a servlet - for example data from a textfield, and stored this in a tabel in a Database.

-Even though all lines of functionality and comunication are working I  can't seem to send the data extracted from the recordstore to the servlet. Or more precisely when I try and store this value in the databsae it's stored as a blank, so I presume it hasn't successfully been pass over to the sevlet.

Hope that's a bit clearer..


By the way i'm only trying this technique  so I can uniquely identify different users. The value stroed on the phone will be their mobile number. Alternatively I could use sessions. Anyway that's another story.

Author

Commented:
I got it working; the following piece of code did the trick

try {
RecordEnumeration re = db.enumerate();
if(re.hasNextElement()) {
b = (re.nextRecord());
}
      ByteArrayInputStream bais = new
ByteArrayInputStream(b);
DataInputStream dis = new
DataInputStream(bais);
in = dis.readUTF();

                  }catch(Exception ex) {}

Glad I could be of assistance! :-)

\t
CERTIFIED EXPERT
Top Expert 2016

Commented:
8-)

btw, what is 'in' here?

>>in = dis.readUTF();
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.