Win a copy of Terraform in Action this week in the Cloud forum!
  • 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:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Updating a Derby database problem

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am trying to update my derby database but it seems that the update isn't working. Mainly the database doesn't update. I have tried various ways but each seems to not update the fields. Below is my code. I have also tried using the "?" with each column entry (example: PROJNAME = ?) along with PreparedStatements setting the PreparedStatement to a JTextField -but that didn't work either. Using the insert statement works fine --just the UPDATE statement doesn't.  The System print (System.out.println)  does print all the entries correctly.  Any help is appreciated. This is my first time using the UPDATE statement --so I am unsure what I'm doing wrong.  I'm using Netbeans 8.2 and Derby.

System Print output:


100 2nd Ave
TBD at CONTRACTING
Joseph Ortega
781726
New York
New York
NORMAL
Hubble Contracting
5208.0
8.88
5670.4704

01/12/2021
01/15/2021
213
125.0



 
Saloon Keeper
Posts: 8779
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
A very brief example of how a prepared statement should be used.


 
Carey Brown
Saloon Keeper
Posts: 8779
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You aren't displaying any exceptions. You should at least do
e.printStackTrace();
 
Marshal
Posts: 26915
82
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's also possible that the SQL (which you have no idea what it is) runs successfully without updating any records. Try this at line 56:


and then examine that value.

 
Bill Melendez
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul:

You're correct. The record shows that 0 rows were updated. Not sure why the update function doesn't work when I have no issues when I insert a new record using the same connection protocol. Any ideas?
 
Carey Brown
Saloon Keeper
Posts: 8779
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Inserts don't have a WHERE clause for one thing.
 
Marshal
Posts: 3727
534
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Try printing out the value of newCRM you are using to verify that it matches an already existing value in the database.
 
Carey Brown
Saloon Keeper
Posts: 8779
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your WHERE clause uses LIKE but you are getting the pattern from
newCRM = jtxtfld_CRMFS.getText();
Does the user know how to make a proper SQL pattern? Do you really need LIKE?
 
Carey Brown
Saloon Keeper
Posts: 8779
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If a user enters in an SQL pattern for the WHERE/LIKE clause then that same pattern will be used to update the field (when you get this working), I don't think you want to be adding '%' to live DB fields.
 
Carey Brown
Saloon Keeper
Posts: 8779
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You are not making proper use of PreparedStatements. You should be using '?' in the SQL and setXXX( N, data ) in the Java code. Not doing so is inviting an SQL injection hack.
 
Carey Brown
Saloon Keeper
Posts: 8779
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your SQL should look like this
All DB professionals I've worked with put SQL keywords in all CAPs and everything else in lower case.

Don't update crmadmin if you are just going to set it back to the same thing you used in the WHERE clause.
 
Bill Melendez
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
All:  I was able to get the UPDATE to work. I did change the format to Field = ?  for each column and used "=" instead of "LIKE" in the where section.


 
Carey Brown
Saloon Keeper
Posts: 8779
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You are calling executeUpdate() twice. But otherwise good.
 
Paul Clapham
Marshal
Posts: 26915
82
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


This should also have a ?
 
Saloon Keeper
Posts: 24595
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul is correct. Use "?" for all variable values, including the selector logic.

One reason to be careful of upper/lower case in database column names is that different databases handle things differently. Often to get the desired results you have to use some sort of quoting mechanism.

Far safer to just stick to lower-case column naming.
 
Bill Melendez
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks all. Just how to I include the selector logic in a search parameter? The variable is designated in the program earlier --hence the use of "doSEARCH" to contain the search parameter previously identified. I use the CRM case numbers as serach parameters. The search CRM number (contained in doSEARCH) is compared to the table record's CRM number.
 
Tim Holloway
Saloon Keeper
Posts: 24595
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


In other words, just like any other parameter in sequence. Adjust as needed.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic