cosmic_bird
asked on
Working with 2 sql statements
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/tr
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.ge
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.driverN
conn = DriverManager.getConnectio
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.driverN
con = DriverManager.getConnectio
Statement statement = con.createStatement();
statement.executeUpdate("i
}
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());
}
}
}
}
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/tr
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.ge
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.driverN
conn = DriverManager.getConnectio
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.driverN
con = DriverManager.getConnectio
Statement statement = con.createStatement();
statement.executeUpdate("i
}
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());
}
}
}
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Also using a PreparedStatement would be more efficient.
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!
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!
I stand corrected :)
Still be better using a PreparedStatement though.
Still be better using a PreparedStatement though.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
ASKER
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.
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?
> 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.
Where do you get the userid if you don't do a select?
Perhaps the userid used from the select is not valid.
ASKER
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!
>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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> 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.
If nothing is being passed over then the select is maybe not find any rows so nothing is getting inserted.
>>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();
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("ins
statement.setInt(1, user_id);
.
.
.
And you would similarly change the other statement setter to reflect their data type in the database.
.
.
.
ResultSet rs = statement.executeQuery();
int user_id = -1;
// Output the resultset data
if (rs.next()) {
user_id = rs.getInt(1);
}
statement = conn.prepareStatement("ins
statement.setInt(1, user_id);
.
.
.
And you would similarly change the other statement setter to reflect their data type in the database.
ASKER
<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?
<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?
>>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?
Can you expand on this - don't you mean after sending the value it doesn't appear anywhere?
ASKER
you know the way writeUTF
ASKER
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.
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?
>>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?
Also, can you answer this:
Have you tried sending over a literal?
writeUTF("Is there anybody there?");
// ETC
Have you tried sending over a literal?
writeUTF("Is there anybody there?");
// ETC
ASKER
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(
perhaps in order to take data back out from the recordstore I need to reverse the way I input the data in?
I notice that you pass into the recordstore data as follows...
ByteArrayOutputStream baos = new ByteArrayOutputStream();
DataOutputStream outputDataStream = new DataOutputStream(baos);
outputDataStream.writeUTF(
perhaps in order to take data back out from the recordstore I need to reverse the way I input the data in?
You have to read and write in the same order
writeObject
writeUTF
writeChar
readObject
readUTF
readChar
writeObject
writeUTF
writeChar
readObject
readUTF
readChar
ASKER
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?
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?
>>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);
No. If you're doing things like that it would have to be
String j = new String(b);
ASKER
no, that gives a blank insertion
What does this print when you print it?
b.length
b.length
ASKER
it gives an error ->cannot resolve symbol
ASKER
I'm getting the feeling it's something to do witht the sql database - because a simple System.out.println(j) gives an output
In your previous code, just do
byte [] b = (re.nextRecord());
String j = "NULL";
if (b != null && b.length > 1) {
j = new String(b);
}
byte [] b = (re.nextRecord());
String j = "NULL";
if (b != null && b.length > 1) {
j = new String(b);
}
>>because a simple System.out.println(j) gives an output
That's what i was aking you to print!
That's what i was aking you to print!
ASKER
i'm afraid it gave a blank again
Please print the result of printing the value 'j' from the code above (to System.out)
ASKER
it juust gave a what what sotred in the record store -> 5005
OK. Now please show how you're doing the real output
ASKER
I presume you mean output to the server?
void Pref(String url1) throws IOException {
HttpConnection c = null;
DataOutputStream os = null;
try {
c = (HttpConnection)Connector.
c.setRequestMethod(HttpCon
c.setRequestProperty("IF-M
c.setRequestProperty("User
c.setRequestProperty("Cont
c.setRequestProperty("Cont
os = c.openDataOutputStream();
os.writeUTF(j);
os.flush();
}
finally {
if(os != null) {
os.close();
}
if(c != null) {
c.close();
}
}
}
void Pref(String url1) throws IOException {
HttpConnection c = null;
DataOutputStream os = null;
try {
c = (HttpConnection)Connector.
c.setRequestMethod(HttpCon
c.setRequestProperty("IF-M
c.setRequestProperty("User
c.setRequestProperty("Cont
c.setRequestProperty("Cont
os = c.openDataOutputStream();
os.writeUTF(j);
os.flush();
}
finally {
if(os != null) {
os.close();
}
if(c != null) {
c.close();
}
}
}
I mean together with the code we've just been talking about
ASKER
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) {}
b = (re.nextRecord());
j = "NULL";
if (b != null && b.length > 1) {
j = new String(b);
}
}
System.out.println(j);
}catch(Exception ex) {}
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?
>>}catch(Exception ex) {}
Very dangerous - how are you going to know if an exception was thrown?
ASKER
Sorry, i think i'm confused.
>>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
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
ASKER
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.
c.setRequestMethod(HttpCon
c.setRequestProperty("IF-M
c.setRequestProperty("User
c.setRequestProperty("Cont
c.setRequestProperty("Cont
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.openRecordStor
} catch(RecordStoreException
rse.printStackTrace();
}
}
public void close() throws RecordStoreNotOpenExceptio
RecordStoreException {
if (recordStore.getNumRecords
String fileName = recordStore.getName();
recordStore.closeRecordSto
recordStore.deleteRecordSt
} else {
recordStore.closeRecordSto
}
}
public synchronized void addNewStock(String record) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
DataOutputStream outputStream = new DataOutputStream(baos);
try {
outputStream.writeUTF(reco
}
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 RecordStoreNotOpenExceptio
return recordStore.enumerateRecor
}
void Pref(String url1) throws IOException {
HttpConnection c = null;
DataOutputStream os = null;
try {
c = (HttpConnection)Connector.
c.setRequestMethod(HttpCon
c.setRequestProperty("IF-M
c.setRequestProperty("User
c.setRequestProperty("Cont
c.setRequestProperty("Cont
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.openRecordStor
} catch(RecordStoreException
rse.printStackTrace();
}
}
public void close() throws RecordStoreNotOpenExceptio
RecordStoreException {
if (recordStore.getNumRecords
String fileName = recordStore.getName();
recordStore.closeRecordSto
recordStore.deleteRecordSt
} else {
recordStore.closeRecordSto
}
}
public synchronized void addNewStock(String record) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
DataOutputStream outputStream = new DataOutputStream(baos);
try {
outputStream.writeUTF(reco
}
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 RecordStoreNotOpenExceptio
return recordStore.enumerateRecor
}
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"
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"
ASKER
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.
-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.
ASKER
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) {}
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
\t
8-)
btw, what is 'in' here?
>>in = dis.readUTF();
btw, what is 'in' here?
>>in = dis.readUTF();
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.