This week's book giveaway is in the Cloud forum.
We're giving away four copies of The Business Blockchain and have William Mougayar on-line!
See this thread for details.
Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

why do I get this exception

 
ben riches
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello All,
When I click on a table name called 00_BPDCON I get the Exception:
SQL Exception: java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server] Line 1: Incorrent Syntax near '00' .
I don't know why this is happerning?

Rgds
Ben
 
Chris Hall
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Since its a SQLException, the problem lies in the sql statement you are executing, which in this case is select * from {tableName] where 1=0 . Assuming the table name is correct, the 'where 1=0' means you have a field called 1 and you are looking for the records of that field with a value of 0. This really doesn't make sense, and I would guess that is the root of your problem. Make sure you know the structure of the database, and you can try executing the sql statement directly against the database to make sure it works.
 
Wayne L Johnson
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Chris' reply is correct in that you should print out the query as it is being executing, and try executing it directly against the database to verify it.
However the query, "Select * from <tablename> where 1=0" is a perfectly valid query in most databases (I've used it against Oracle, Sybase and MySql) and is a great way to get the table's column definition. It executes very fast and returns no rows, but you still get the MetaData so you can pull out the number of columns, and column names and types.
The error message seems to indicate it doesn't like the table name. However test the SQL directly against your SQL-Server database to make sure that it supports the query.
Finally, and this is a minor issue, but you should probably close your resultset explicitly. Some database don't care, but with some databases you need to close both the resultset and statement. And that should be done in a "finally" clause to ensure they get closed even if an exception gets thrown.
 
mani pallapu
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Chris Hall:
Since its a SQLException, the problem lies in the sql statement you are executing, which in this case is select * from {tableName] where 1=0 . Assuming the table name is correct, the 'where 1=0' means you have a field called 1 and you are looking for the records of that field with a value of 0. This really doesn't make sense, and I would guess that is the root of your problem. Make sure you know the structure of the database, and you can try executing the sql statement directly against the database to make sure it works.
 
ben riches
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So how can I pull all the tablenames out from the database without looking for 1?
Ben
 
Dana Hanna
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DatabaseMetadata has a method for getting column names!
And the 1=0 trick on DB2 for OS/390 takes FOREVER to execute - Don't ask me why!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic