Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Syntax Error - Intersect

 
Jonathan Greene
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Everyone,
This is my first post to the forum, thank you in advance for your time and help. I've got an exam scheduling application that's hooking into a SQL server database for a class. I'm trying to run the following command:

statement = connection.createStatement();
statement.executeQuery("select count(instructor_in_both_blocks.inst_id)"
+ "from(select distinct(bsOne.instructor_id) inst_id"
+ " FROM block_staging_one bsOne, block_staging_one bsOneTwo"
+ " where bsOne.schedule_group = " + blockUnderQuestion + "and bsOne.instructor_id = bsOneTwo.instructor_id"
+ "and bsOneTwo.schedule_group =" + comparisonBlock + ") instructor_in_both_blocks;");

Where blockUnderQuestion and comparisonBlock are both simple int's that I'm passing into the SQL Query. When I run this command, NetBeans is throwing the following error:

Apr 23, 2012 10:13:19 AM backend.ERSMain assignScheduleBlock
SEVERE: null
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'bsOneTwo'.

Is there something I'm missing about SQL syntax for java? I can run the exact same syntax in an SQL editor and it returns without issue.

Thanks for your time!

Jonathan
 
Paul Clapham
Sheriff
Posts: 21576
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jonathan, welcome to the Ranch!

I don't know what you mean when you say

I can run the exact same syntax in an SQL editor


because what you posted there is not valid SQL at all. It's Java code. Perhaps the variable it constructs contains valid SQL, but there's no evidence that you have tested that. In other words, look at the SQL and not at the Java code.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
extract the sql statement out into a string.
Then you can debug it, by using system.out.println (or a debugger if you are using an IDE like eclipse or netbeans).
I expect you will find you have made a mistake in constructing your string which isn't easily visible in the code.

As the syntax for sql is exactly the same whether you run it from java or an sql window.
 
Jonathan Greene
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you both for your replies. The syntax was concatenated incorrectly, and it was as simple as that. When I say I took the code and ran it in an SQL editor, I meant I took the SQL piece of the code, and ran it in an SQL editor. I might be a noob to this board, but I understand you can't run java code in SQL.

Thanks for your help.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic