Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Server Connection question

 
Melinda Savoy
Ranch Hand
Posts: 387
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have the following class whereby I am trying to make a database connection to a SQL database that is sitting on my PC:

public class SQLServerTest implements Runnable {
public static void main(String [ ] args){

new SQLServerTest().run();
}

public void run() {
testMS();
}

public void testMS(){
Connection con = null;
String hostname = "localhost"; // current machine
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://"+ hostname +":1433;DatabaseName=esdev;User=test;Password=test");
String tablename = "POI";
String fields = "asset.number, charge.to, hosp.code, item.number";
Statement stmt = con.createStatement();
String query = ("SELECT " + fields + " FROM " + tablename);
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String field = rs.getString(1);
String data = rs.getString(2);
// Do something like System.out.println(mykeyfield);
System.out.println("Field: " + field + " Value: " + data);
}
}
catch (Exception e) {
System.out.println(e.getMessage());
}
finally {
if(con != null){
try { con.close(); }
catch (Exception e) {System.out.println("Error closing connection: " + e.getMessage());
}
}
}
}
}

I am getting the following error that does not have a LINE NUMBER associated with it and I cannot see where my problem is:

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Incorrect syntax near the keyword 'to'.

Any help or direction would be appreciated. Thank you.
 
Layne Lund
Ranch Hand
Posts: 3061
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One reason you don't see the line number that causes the problem is that you are catching the exception and only printing out the message. Instead of

you should do

It looks like this is just a test, so this will probably suffice. However, in production code, you will probably let this exception propogate (i.e. declare it in the throws clause of the method) so that anyone that calls a method that connects to the database can handle the error more appropriately.

With that said, the error isn't with the Java, precisely. This type of error message is from the SQL database itself. It is telling you that there is a syntax error in your SQL query. There is no line number associated with the error message because JDBC does not attach any line number information to the SQL command that is being sent. Even if it did, the database probably wouldn't know what to do with the information.

Anyway, tracking down the error is somewhat problematic because you are concatenating Strings to build it. I suggest that you look at the value of the String variable named query since it contains the SQL query that you are trying to execute. Perhaps you can add a System.out.println() call for this purpose. Find the word, "to" in this query and the syntax error is somewhere before that. You may want to copy and paste this query into the interactive SQL front-end for your database. (Sorry, I'm not familiar with SQL Server, so I don't know what it's called.) Doing so might give you some more information about the SQL syntax error. If you need some eyes to look at it, please post the exact contents of this String and we'll take a look.

By the way, this message board is for general beginner questions. I personally would not consider this a "beginner" question. In fact, we have a forum that is dedicated to JDBC. Please post future questions there.

Good luck and keep coding!

Layne
 
Marilyn de Queiroz
Sheriff
Posts: 9059
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also, using code tags would probably facilitate helpful responses.
 
Melinda Savoy
Ranch Hand
Posts: 387
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Layne,

Thanks so much for the response. I am a newbie in Java and that is the reason I am using this list but I appreciate the advice and will post in the JDBC list henceforth for this this type of question.

I also appreciate you addressing my question here as well. I will try what you've recommended and if I have additional questions I will post.

Thanks again.
 
Merrill Higginson
Ranch Hand
Posts: 4864
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe the word "to" is a keyword in SQL. My suggestion is to change your field name to avoid the use of the word "to". Make it sent-to, or something else.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic