This week's book giveaway is in the General Computing forum.
We're giving away four copies of Raising Young Coders: A Parent’s Guide to Teaching Programming at Home and have Cassandra Chin on-line!
See this thread for details.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

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

 
Ranch Hand
Posts: 629
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Very well explained David! I couldn't have done better. []
 
Arjun Reddy
Ranch Hand
Posts: 629
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 629
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 629
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 629
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic