[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

SQL Syntax Error from JAVA Prepared Statement

I've tested this SQL statement directly from the MySQL command line and had it work, so I'm at a bit of a loss as to what the problem might be.  The code is below.  The 'System.out.println' verified that the expected values are being passed.

Any help is, as always, greatly appreciated.
for(int a=0;a<AL.size();a++)
		{
			System.out.println("Name"+AL.get(a).get(1)+" Avg: "+SiteAvgTBL.get(a).get(0)+" Low: "+SiteAvgTBL.get(a).get(1)+" High: "+SiteAvgTBL.get(a).get(2));
			String Update = "UPDATE FSTable SET Avg='"+SiteAvgTBL.get(a).get(0)+"', Low='"+SiteAvgTBL.get(a).get(1)+"', High='"+SiteAvgTBL.get(a).get(2)+"' WHERE Name='"+AL.get(a).get(1)+"')";
			PreparedStatement UpdatePS = con.prepareStatement(Update);  
		       	UpdatePS.executeUpdate();
		}

Open in new window

0
cdromes
Asked:
cdromes
  • 4
  • 4
  • 2
1 Solution
 
CEHJCommented:
You're not using the PS properly. It you do, you'll find it'll probably work due to quoting problems being ocrrected
0
 
objectsCommented:
need to use a CallableStatement and change your sql

0
 
CEHJCommented:
Your PS string should be
String Update = "UPDATE FSTable SET Avg=?, Low=?, High=? WHERE Name=?";

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
objectsCommented:
ignore that, misread your sql :)

>                         String Update = "UPDATE FSTable SET Avg='"+SiteAvgTBL.get(a).get(0)+"', Low='"+SiteAvgTBL.get(a).get(1)+"', High='"+SiteAvgTBL.get(a).get(2)+"' WHERE Name='"+AL.get(a).get(1)+"')";

get rid of trailing )

                        String Update = "UPDATE FSTable SET Avg='"+SiteAvgTBL.get(a).get(0)+"', Low='"+SiteAvgTBL.get(a).get(1)+"', High='"+SiteAvgTBL.get(a).get(2)+"' WHERE Name='"+AL.get(a).get(1)+"'";

0
 
cdromesAuthor Commented:
The prepared statement?  I basically copied it from the following query three lines above it that worked:

String FSD = "INSERT INTO RecTable (Chr, Name, Locus, Class, Mode, Start, Stop, Position, RRate, Hot, ChrAvg, GenAvg) VALUES ('"+RecombTBL.get(a).get(0)+"','"+RecombTBL.get(a).get(1)+"','"+RecombTBL.get(a).get(2)+"','"+RecombTBL.get(a).get(3)+"','"+RecombTBL.get(a).get(4)+"','"+RecombTBL.get(a).get(5)+"','"+RecombTBL.get(a).get(6)+"','"+TempInt+"','"+RecombTBL.get(a).get(8)+"','"+RecombTBL.get(a).get(9)+"','"+RecombTBL.get(a).get(10)+"','"+RecombTBL.get(a).get(11)+"')";
                  
PreparedStatement FSps = con.prepareStatement(FSD);  
FSps.executeUpdate();

I'm not sure what the issue would be, taking into account that the syntax it works in one place, but not another in the same program.  The quoting has also be checked.
0
 
CEHJCommented:
PreparedStatement is fine
0
 
objectsCommented:
the problem is that query has a opening ( and closing bracket )
when you have modified it you have removed the opening bracket

brackets do not appear to be needed in new query, try what I posted above

0
 
CEHJCommented:
See the following for the correct way to call a PS:

http://www.exampledepot.com/egs/java.sql/InsertPs.html
0
 
cdromesAuthor Commented:
Oh, man.  The deadly trap of copying-and-pasting code snippets for reuse elsewhere bites me in the ass once again.  It was so obvious that I never would've seen it, so many thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now