java.sql.SQLException

[madhoriya22]

Hi,
Here is the query which I am using to get the values from the database:-

  
"SELECT ?, COUNT(*) AS COUNT " +
			"FROM DEFECT_DETAIL " +
			"WHERE TARGET_MILESTONE = ? " +
			"GROUP BY ?";

and here is function throught which I am getting data:-

  
public Hashtable getTargetMilestoneDefectCount(String varColumn, String targetM) {
		Connection con = null;
		ResultSet rs = null;
		String str = null;
		
		Hashtable defectCountList = new Hashtable();
		try {
			 con = new MySqlDAOFactory().getConnection();
			PreparedStatement pStatement = null;
			//passing query to the database
			pStatement = con.prepareStatement( GET_TARGET_MILESTONE_DEFECT_COUNT);
			System.out.println( GET_TARGET_MILESTONE_DEFECT_COUNT);
			
			pStatement.setString(1, varColumn);
			pStatement.setString(2, targetM);
			pStatement.setString(3, varColumn);
			
			rs = pStatement.executeQuery();
			while(rs.next()) {
				System.out.println("Inside while loop");
				
				DefectReportVO defectReportVO = new DefectReportVO();
				
				str = rs.getString(varColumn);
				defectReportVO.setCount(rs.getInt("COUNT"));
				
				if(defectReportVO != null) {
					defectCountList.put(str, defectReportVO);
				}
			}
			rs.close();//closing result set
			pStatement.close();//closing prepared statement

and here is how I am calling this function:-

 Hashtable statusCountList = distinctReportsDAO.getTargetMilestoneDefectCount("STATUS", "v.1.24.00");

Now it is throwing Exception:-

java.sql.SQLException: Column 'STATUS' not found.
		at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
		at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:955)
		at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5436)
		at com.spi.defecttracker.dao.DefectReportDAOImpl.getTargetMilestoneDefectCount(DefectReportDAOImpl.java:846)
		at com.spi.defecttracker.test.testMain.main(testMain.java:52)

i am not able to find what is the problem... STATUS column is there in the table...?

thanks and regards,
madhoriya

[r035198x]

Looks like the status column is not there alright.
Can you see it if you connect to the database from somewhere else e.g from a console/shell?

P.S Nice avatar

[madhoriya22]

P.S Nice avatar

Hi,
Thanks. I had done a lot of hard work in finding that avatar.
note:- size limit provided for the avatar is very small.

Looks like the status column is not there alright.
Can you see it if you connect to the database from somewhere else e.g from a console/shell?

STATUS column is there in the table. Is there anything wrong in the query? I have checked so many times ...... STATUS column is there.

Actually I am calling that function different times for different values, Every time it is giving the same exception...

thanks and regards,
madhoriya.

[r035198x]

Hi,
Thanks.


STATUS column is there in the table. Is there anything wrong in the query? I have checked so many times ...... STATUS column is there.

thanks and regards,
madhoriya.

I don't see anything wrong then. Try changing that count to something else too as in select .... count(*) as somethingElse

[Nepomuk]

Maybe there's a mistake in the definition of the table?

[madhoriya22]

I don't see anything wrong then. Try changing that count to something else too as in select .... count(*) as somethingElse

Hi,
From somewhere i got that replacing ?(mark) with string variable will not substitue the value in query ...?

Is it right...? If it is.. then how i should try this....!!

In the 24 line of the method(see post #1) it is throwing the exception, It means it not finding that valued in the query.. Am I right?

thanks and regards,
madhoriya.

[r035198x]

Hi,
From somewhere i got that replacing ?(mark) with string variable will not substitue the value in query ...?

Is it right...? If it is.. then how i should try this....!!

...

thanks and regards,
madhoriya.

I doubt that. What happens if you use the index (1) to get the value in that column?

[madhoriya22]

I doubt that. What happens if you use the index (1) to get the value in that column?

Hi,
tried ur way.. It is givign strange results...My query supposed give results like this:-

 
'STATUS'	count
ASSIGNED 5// this should be
NEW		  21// the Hashtable 
REOPENED 1// contents

But what through methods the content of Hashtable are:-

 
{STATUS=com.spi.defecttracker.vo.DefectReportVO@c1b531}

and count it returnin is 27. which is total count for that column.

thanks and regards,
madhoriya

[madhoriya22]

Hi,
tried ur way.. It is givign strange results...My query supposed give results like this:-

 
'STATUS'	count
ASSIGNED 5// this should be
NEW		 21// the Hashtable 
REOPENED 1// contents

But what through methods the content of Hashtable are:-

 
{STATUS=com.spi.defecttracker.vo.DefectReportVO@c1b531}

and count it returnin is 27. which is total count for that column.

thanks and regards,
madhoriya

Hi,
Still I am not able to do it....... Is that right ...... that ?(marks) in query are only replaced by values supplied for column..... not by column Names.

[r035198x]

Hi,
Still I am not able to do it....... Is that right ...... that ?(marks) in query are only replaced by values supplied for column..... not by column Names.

What happens if you hardcode the colum name value in the query.

[madhoriya22]

What happens if you hardcode the colum name value in the query.

Hi,
Then there is no problem.... But I have to use this query different times for different column name values.

[r035198x]

Hi,
Then there is no problem.... But I have to use this query different times for different column name values.

So it's the ? thing then.
Use normal string concatenation for it then.

[madhoriya22]

So it's the ? thing then.
Use normal string concatenation for it then.

Hi,
I have tried that also. But what's happenin that when i declaring query like;-

//This string definition is outside the method.
private static final String GET_COLUMN_COUNT = "SELECT " + varColumn +
			", COUNT(*) AS COUNT " +
			"FROM DEFECT_DETAIL " +
			"WHERE TARGET_MILESTONE = ? " +
			"GROUP BY " +varColumn);

and passing it to

 
pStatement = con.prepareStatement( GET_COLUMN_COUNT );

it is not working. But when I am directly passing the whole query like:-

 
pStatement = con.prepareStatement( "SELECT " + varColumn +
			", COUNT(*) AS COUNT " +
			"FROM DEFECT_DETAIL " +
			"WHERE TARGET_MILESTONE = ? " +
			"GROUP BY " +varColumn);

It is working. But I have not to do it this way. I have to first declare query and then pass it to prepareStatemen t(actually my class structure is like that and i have to follow that).

[r035198x]

Hi,
I have tried that also. But what's happenin that when i declaring query like;-

//This string definition is outside the method.
private static final String GET_COLUMN_COUNT = "SELECT " + varColumn +
			", COUNT(*) AS COUNT " +
			"FROM DEFECT_DETAIL " +
			"WHERE TARGET_MILESTONE = ? " +
			"GROUP BY " +varColumn);

and passing it to

 
pStatement = con.prepareStatement( GET_COLUMN_COUNT );

it is not working.....

Does it give a syntax error when you do it like that?

[madhoriya22]

Does it give a syntax error when you do it like that?

Hi,
Ya !! but i removed that syntax error.. This is how i done it...I declare a static variable in class..

 
private static String columnName = null;
[b]//query is ::[/b]
private static final String GET_COLUMN_COUNT = "SELECT " + columnName +
			", COUNT(*) AS COUNT " +
			"FROM DEFECT_DETAIL " +
			"WHERE TARGET_MILESTONE = ? " +
			"GROUP BY " +columnName;

In this variable i assigned the value which is passed to method and pass the query to the prepareStatemen t.

public Hashtable getColumnCount(String varColumn, String targetM) {
		Connection con = null;
		ResultSet rs = null;
		String str = null;
 
		columnName = varColumn;[b]// Here I am assigning the value[/b]
		System.out.println("column Name:\t"+columnName);
 
		Hashtable defectCountList = new Hashtable();
		try {
			 con = new MySqlDAOFactory().getConnection();
			PreparedStatement pStatement = null;
			//passing query to the database
			pStatement = con.prepareStatement( GET_COLUMN_COUNT);

But Still the valued is not passed to the query instead it is taking null.........