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

[Arjun Reddy]

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 ]

[David O'Meara]

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]

Very well explained David! I couldn't have done better. []

[Arjun Reddy]

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]

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:

// put in our SQL statement
String sql = "INSERT INTO Books VALUES (?, ?, ?, ?, ?, ?, ?)";
PreparedStatement ps = conn.prepare(sql);
// now drop in the values, STARTING AT ONE!!
ps.setXXX(1, b.getID());
ps.setXXX(2, b.getTitle());
ps.setXXX(3, b.getPrice());
ps.setXXX(4, b.getOnSale());
ps.setXXX(5, b.getYear());
ps.setXXX(6, b.getDescription());
ps.setXXX(7, b.getInventory());

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]

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]

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]

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.

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

[Arjun Reddy]

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.