• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL

 
Ranch Hand
Posts: 455
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi. I am having a couple of problems with SQL.
First Problem:
Here's my SQL statement:


Why can't this SQL statement bring in a record with an apostrophe in it??(i.e- Mervyn's). Keeps telling me "Incorrect Syntax".
Second Problem:

There is a record I have in SQL that has an "S" in this column. But it doesn't seem to recognize it. I printed to standard out to verify that the value is an "S" and it is. The datatype in SQL for this column is a char.

Am I going crazy?? I must be missing something.
Any help on these issues would be much appreciated!
Thanks in advance!
[ April 04, 2002: Message edited by: Jennifer Sohl ]
[ April 04, 2002: Message edited by: Jennifer Sohl ]
 
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
The easiest way to solve your first problem is to use a PreparedStatement rather than a Statement - it will take care of the special characters for you (sample code available by request)
Your second problem is a common Java mistake, you are testing equality of the java objects, not their values. You need rs.getString(5).equals("S") rather than rs.getString(5) == "S".
Dave
 
Jennifer Sohl
Ranch Hand
Posts: 455
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the reply! Well, DUH!! I can't believe that I didn't notice I was trying to compare objects rather than values. Thanks for pointing that out.
If it's not too much trouble, could you post some sample code to show me how to do the Prepared Statements? I am not yet familiar with them. Thanks again for your help!
 
Ranch Hand
Posts: 3244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Jennifer
Prepared statemtns are basically precompiled SQL statements that can be used to speed up processing of the queries when just a few of the parameters of the statement are going to change.
You create them the same as you do a regular Statment object except that you have to include the SQL text in the constructor:
PreparedStatement pStmt = conn.prepareStatement(“SELECT * FROM employees WHERE lname = ?” ;) ;
The question mark represents the parameter to be supplied later. You can have multiple parameters and then later, when you start working with them, they are all referenced with an index starting at 1 (the first parameter is 1 the second is 2 and so on).
Now when you go to use it you have to:
1 clear any existing parameters
--- pStmt.clearParameters( );
2 set the values of any parameters in the statement
--- pStmt.setString(1, “O'Connor” ;) ;
3 execute the statement as normal
--- pStmt.executeQuery();
There are multiple setXXX() methods that all take a parameter index as their first argument and the value being assigned as the second. To assign null, use the setNull() method the first arg would be the parameter index the second is the type of the argument that is being set to null, like this:
pStmt.setNull(2, Types.INTEGER);
Then you just process the results as normal.
hope that helps you out, if you need more details let me know...
[ April 05, 2002: Message edited by: Dave Vick ]
 
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

Originally posted by Dave Vick:
You can have multiple parameters and then later, when you start working with them, they are all referenced with an index starting at 1 (the first parameter is 1 the second is 2 and so on).


This is definitely worth stating twice...
 
Jennifer Sohl
Ranch Hand
Posts: 455
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello. Me again. I am having another problem with this darn database stuff.
Here is my code:

(mu is a JComboBox);
I have printed 'mString' to std out, and it shows exactly what is in the table. I can't figure out why I keep getting a NullPointerException on the line "while(rs.next)"?
I am stumped.
Could someone help me out... Again?
Sorry for being a pest. Thanks in advance!
 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You aren't fetching the executeQuery into the result Set. See below,
ResultSet rs = stmt.executeQuery("SELECT UNITID FROM MARKETINGUNITS WHERE DESCRIPTION = '" + mString + "'");
And you will be fine.
 
Jennifer Sohl
Ranch Hand
Posts: 455
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
AHA. I see that very clearly now. I must be having a bad week to make such stupid mistakes.
Thanks for helping me out!!
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic