This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes JDBC Connection successful but havin problems with the SQL Statements in java now :( Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Connection successful but havin problems with the SQL Statements in java now :(" Watch "JDBC Connection successful but havin problems with the SQL Statements in java now :(" New topic
Author

JDBC Connection successful but havin problems with the SQL Statements in java now :(

Arjun Reddy
Ranch Hand

Joined: Nov 10, 2007
Posts: 627
Hi Guys,

I am able to establish the JDBC Connection and I am trying to insert some values into the database table from my java code. Now, when I do this

stmnt.executeUpdate("insert into A values(11)"); I am having no problem but when I do this,
stmnt.executeUpdate("insert into A values(b.getInventory())"); which returns the same value I am getting the error:
ORA-00904:"B"."GETINVENTORY":invalid identifier

and if I do this, Taking the value returned by the method into a variable and use it like say,

int WWW=b.getInventory();
stmnt.executeUpdate("insert into A values(WWW)"); I am getting the following error:
ORA-00984:column not allowed here

I am really unable to understand the situation. Can anybody please help?

Thanks.
[ March 10, 2008: Message edited by: Arjun Reddy ]

Be Humble... Be Nice.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Lets talk about what the database sees:

Case 1:
insert into A values(11)
DB: Yep, no problem. Insert 11 into table A

Case 2:
insert into A values(b.getInventory())
DB: What are you talking about, I don't know what getInventory() from table B means

Case 3:
insert into A values(WWW)
DB: What are you talking about, I don't know about column WWW

The point is the DB only see the String that you send, It cannot see Java variables or their values. Hence:

Case 4:
stmnt.executeUpdate("insert into A values("+b.getInventory()+")");
insert into A values(11)
DB: Now you're talking my language.

Does this help?
Abhishek Asthana
Ranch Hand

Joined: Sep 08, 2004
Posts: 146
Very well explained David! I couldn't have done better. []
Arjun Reddy
Ranch Hand

Joined: Nov 10, 2007
Posts: 627
Yes sir.. it's working Thank you.

So basically, the strings in an SQL statement should only contain what the database understands like the statement syntax, table, column names etc.. right?/ Everything else should be added with a + symbol.Sry am a beginner so this is what I have understood. Please correct me if I am wrong.

I am also trying to insert multiple values now. And according to what you have said above, I did this.

stmnt.executeUpdate("INSERT INTO Books VALUES (" + b.getID() +"," + b.getTitle() + "," + b.getPrice() + "," + b.getOnSale() + "," + b.getYear()+ "," + b.getDescription() + "," + b.getInventory()+")");
and it is giving me an Run time exception "Missing Comma". I seem to have included all though.

Thanks.
[ March 11, 2008: Message edited by: Arjun Reddy ]
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

First lets look at what your code would send to the database when the person's title is "Sergent Major"

INSERT INTO Books VALUES (1, Sergent Major, 4.28, TRUE, 2008, The Bees Knees,FALSE)
DB: Huh? Is there supposed to be a comma after 'Sergent'
You: It's suppsed to be a String
DB: You need to put Strings inside single quotes when using databases
You: Oh.

Hence:
stmnt.executeUpdate("INSERT INTO Books VALUES (" + b.getID() +", '" + b.getTitle() + "'," + b.getPrice() + "," + b.getOnSale() + "," + b.getYear()+ ",'" + b.getDescription() + "'," + b.getInventory()+")");

(Note the single quotes around the title and description sent to the DB)

The next step after basic statements is to get into the habit of using a PreparedStatement. These have several advantages which we won't go into here, but it means we can write code like this:



At this stage you'll need to look up the appropriate setXXX methods from the API, but it adds the single quotes for you, and is so much easier to read in the long run.
Arjun Reddy
Ranch Hand

Joined: Nov 10, 2007
Posts: 627
Hi David,

I wish there were points which I can give to you like in other forums and I'd definitely have done that. Thanks a lot.. your explanation is really Nice unlike some people who write long and long paragraphs

Thanks.
Arjun Reddy
Ranch Hand

Joined: Nov 10, 2007
Posts: 627
Um.. Actually am getting the ORA- 00984: Column not allowed here error again!! after I did this:

stmnt.executeUpdate("INSERT INTO Books VALUES (" + b.getID() +", '" + b.getTitle() + "'," + b.getPrice() + "," + b.getOnSale() + "," + b.getYear()+ ",'" + b.getDescription() + "'," + b.getInventory()+")");


This my table in the database:

Column NameData Type
ID CHAR(4)
TITLE VARCHAR2(100)
PRICE NUMBER(5,3)
ONSALE CHAR(2)
YEAR CHAR(4)
DESCRIPTIONVARCHAR2(100)
INVENTORYCHAR(2)
[ March 11, 2008: Message edited by: Arjun Reddy ]
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

there are other character types too, so they also need single quotes.

If you build the query into a string first, you can write the String to logs or System.out to see what it looks like.

Arjun Reddy
Ranch Hand

Joined: Nov 10, 2007
Posts: 627
Yippeeeeeeeee Thanks David.. it worked.. Ahem I've declared getID return type as String too did not notice that before.. THANKS A LOT for ya Patience.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: JDBC Connection successful but havin problems with the SQL Statements in java now :(
 
Similar Threads
ORA-01460: unimplemented or unreasonable conversion requested
HTML Form/Servlet Problem
procedure return resultset
Need to insert 1.5 into a sql colunm on Number(2,2)
error