aspose file tools*
The moose likes JDBC and the fly likes doubt regarding default values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Reply locked New topic
Author

doubt regarding default values

rakhi sinha
Ranch Hand

Joined: Mar 26, 2012
Posts: 147
database.java



These fields are retrieved from servlet that is p1.java



when I click on submit button of p1.java database.java is called .Program is running well but there is only one problem...when all fields are filled in i1,i2,i3,c1,c2,c3,q1,q2and q3.program show no errors but when i filled only one field i1,c1 and q1 then it shows error how to remove this error(SQLException caught: ORA-00936: missing expression ).If i want to fill only one field.
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1457
    
    6

Rakhi,

Use PreparedStatement, it avoids SQL injection.
e.g.


http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html


Swastik
rakhi sinha
Ranch Hand

Joined: Mar 26, 2012
Posts: 147
Swastik Dey wrote:Rakhi,

Use PreparedStatement, it avoids SQL injection.
e.g.


http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html


I am using prepared statement but it is not working
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1457
    
    6

What exactly do you mean by not working? Does it raise any exception? Look at the exception stack trace.
rakhi sinha
Ranch Hand

Joined: Mar 26, 2012
Posts: 147
rakhi sinha wrote:
Swastik Dey wrote:Rakhi,

Use PreparedStatement, it avoids SQL injection.
e.g.


http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html


I am using prepared statement but it is not working

sorry,I am using this



[Edited to shorten the lines in the CODE tag -- Martin Vajsar]
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1457
    
    6

Does the table has only 5 fields, and everywhere you are using pst1.setString. Are all the fields in the table are of character type?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Rakhi, I've a few notes on this:

- Show us the exception and stack trace you're getting. Otherwise we'can only guess what's happening. See also the link ItDoesntWorkIsUseless.

- You're not using PreparedStatement and you'll be vulnerable to SQL injection attacks if you'll continue this way.

- Though the INSERT ALL command should work, I'd suggest to split it into three separate INSERT INTO statements. If you use update batching with prepared statements, it will be even more effective than the INSERT ALL statement you're using now.

- Your statement contains three column lists in the INTO P section. The firts two contain column QU, while the last has column QUA at this place. This seems to be a typo. However, since the column names are rather cryptic, I can't know for sure.
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1457
    
    6

As Martin says, I also feel that PreparedStatement with batch update should be a better solution.
Piyush Mangal
Ranch Hand

Joined: Jan 22, 2007
Posts: 196
You need to use parameterized sql with PreparedStatement.


rakhi sinha
Ranch Hand

Joined: Mar 26, 2012
Posts: 147
Martin Vajsar wrote:Rakhi, I've a few notes on this:

- Show us the exception and stack trace you're getting. Otherwise we'can only guess what's happening. See also the link ItDoesntWorkIsUseless.

- You're not using PreparedStatement and you'll be vulnerable to SQL injection attacks if you'll continue this way.

- Though the INSERT ALL command should work, I'd suggest to split it into three separate INSERT INTO statements. If you use update batching with prepared statements, it will be even more effective than the INSERT ALL statement you're using now.

- Your statement contains three column lists in the INTO P section. The firts two contain column QU, while the last has column QUA at this place. This seems to be a typo. However, since the column names are rather cryptic, I can't know for sure.


QUA was written by mistake..it is QU ......
My problem is this....
if i want enter only one of these values q1,q2 and q3 then database shoe the exception SQLException caught: ORA-00936: missing expression in p1.java
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1457
    
    6

Are you still using statement or you have changed it to PreparedStatement? If changed show us that part of the code once again?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

To debug this issue: write the text of the query (variable query) into a log file. Try to run that text in SQL*plus or similar tool, and possibly post it here. Most probably some of the parameters you stuff into it causes syntax errors (and demonstrates you've serious SQL injection issues in your code).

To turn that into production-quality code: follow the advice I've already given here (execute a PreparedStatement using simple INSERT INTO ... VALUES three times in a row - or any other number of times you actually need).
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

we need to see the sql where you are only trying to insert 1 value.
Are you using the same statement but only setting one of the variables?
rakhi sinha
Ranch Hand

Joined: Mar 26, 2012
Posts: 147
Piyush Mangal wrote:You need to use parameterized sql with PreparedStatement.





when i use prepared statement no value is inserted in the database
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

rakhi sinha wrote:
Piyush Mangal wrote:You need to use parameterized sql with PreparedStatement.





when i use prepared statement no value is inserted in the database


2 points
1: are all the columns strings, even the quantity and price, as prepared statement has methods for each datatype.
2: It must be throwing an exception if it isn't inserting anything at all, can you post the stack trace.
rakhi sinha
Ranch Hand

Joined: Mar 26, 2012
Posts: 147
Wendy Gibbons wrote:
rakhi sinha wrote:
Piyush Mangal wrote:You need to use parameterized sql with PreparedStatement.





when i use prepared statement no value is inserted in the database


2 points
1: are all the columns strings, even the quantity and price, as prepared statement has methods for each datatype.
2: It must be throwing an exception if it isn't inserting anything at all, can you post the stack trace.


i have done some mistake but now it is inserting values into database but now there is another problem that i have mentioned in my prepared statement problem
http://www.coderanch.com/t/573865/JDBC/java/prepared-statement

Please help me out to solve this problem..thanks in advance
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

as you have started a new thread locking this one
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: doubt regarding default values