File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Error in retrieving data using java from Column whose name is a reserved word in SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error in retrieving data using java from Column whose name is a reserved word in SQL " Watch "Error in retrieving data using java from Column whose name is a reserved word in SQL " New topic
Author

Error in retrieving data using java from Column whose name is a reserved word in SQL

Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
Hi Guys
I struck at some point of my developement. I have a table from where i am pulling the data, But the table has one column whose name is a reserved word. when i am trying to use that column name i am getting an SQL exception. MISSING EXPRESSION, Here is my code; Any help would greatly appriciated.


[CODE]

try {

st = db.prepareStatement(FamilyLookExtraResults);
st.setString(1,progKit);

rs = st.executeQuery();

while (rs.next()) {

progLookupVO=new ProgrammingLookUpVO();
progLookupVO.setProgKit(rs.getString("PROGKIT"));
progLookupVO.setXcvrNO(rs.getString("XCVR_NO"));
progLookupVO.setCategory(rs.getString("CATEGORY"));
progLookupVO.setFileName(rs.getString("FILE_NAME"));
progLookupVO.setFilePath(rs.getString("FILE_PATH"));
progLookupVO.setType(rs.getString("TYPE"));
progLookupVO.setComment(rs.getString("COMMENT"));
progLookupVO.setVersion(rs.getString("VERSION"));
progLookupVO.setCreationDateExtra(rs.getString("CREATE_DT"));


progVOList.add(progLookupVO);

}


private static final String FamilyLookExtraResults="select PROGKIT, XCVR_NO,CATEGORY, FILE_NAME, FILE_PATH, TYPE , COMMENT , VERSION, CREATE_DT from PROG_EXTRA where PROGKIT= ? ";

here COMMENT is a column name which is a reserved word.
when i ran the with same query with 'COMMENT' or "COMMENT" in SQLDeveloper it is working fine. But i am not sure how to make this happen in Java

Thanks
Kumar
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You can escape the reserved word, though how you do this will be database specific. Or you can use the ResuultSet methods that let you get a value by its column index. Make sure you specify all the columns you want in the order you want them in your select clause if you go this way.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
Thankyou very much Paul,
i tried to use the column index, but still having the issue

java.sql.SQLException: ORA-00936: missing expression

as the select query is not executing as it has COMMENT colun specified on it. My question is how to pass the query to DB

select PROGKIT, XCVR_NO,CATEGORY, FILE_NAME, FILE_PATH, TYPE ,COMMENT, VERSION, CREATE_DT from PROG_EXTRA where PROGKIT= :1

This is the query it genated when i debug.
if i change this to::

select PROGKIT, XCVR_NO,CATEGORY, FILE_NAME, FILE_PATH, TYPE ,'COMMENT', VERSION, CREATE_DT from PROG_EXTRA where PROGKIT= :1

i.e ' COMMENT' and used
progLookupVO.setComment(rs.getString(7));

it isgiving me COMMENT as the value under COMMENT column, but in the db it is null for that particular id.

Thanks
Kumar
Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
Resolved!!!

I used a escape sequence to the query as :::
select PROGKIT, XCVR_NO,CATEGORY, FILE_NAME, FILE_PATH, TYPE ,\"COMMENT\", VERSION, CREATE_DT from PROG_EXTRA where PROGKIT= ? ";

Thanks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Error in retrieving data using java from Column whose name is a reserved word in SQL
 
Similar Threads
Getting 0 rows returned when selecting with null values
need help :: multiple rows update without primary key
Problem Passing Parameter values to action
Displaying a table with in a table with DWR data
Problem in hiding the row of data