File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Beginning Java and the fly likes SQL Server Connection question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » Beginning Java
Bookmark "SQL Server Connection question" Watch "SQL Server Connection question" New topic
Author

SQL Server Connection question

Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 387

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

Joined: Dec 06, 2001
Posts: 3061
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


Java API Documentation
The Java Tutorial
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9053
    
  12
Also, using code tags would probably facilitate helpful responses.


JavaBeginnersFaq
"Yesterday is history, tomorrow is a mystery, and today is a gift; that's why they call it the present." Eleanor Roosevelt
Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 387

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

Joined: Feb 15, 2005
Posts: 4864
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.


Merrill
Consultant, Sima Solutions
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: SQL Server Connection question