Bug 46522 - Incorrect "Response data" in JDBC sample when column names are missing
Incorrect "Response data" in JDBC sample when column names are missing
Status: RESOLVED FIXED
Product: JMeter
Classification: Unclassified
Component: Main
2.3.2
PC Windows XP
: P2 normal (vote)
: ---
Assigned To: JMeter issues mailing list
:
Depends on:
Blocks:
  Show dependency tree
 
Reported: 2009-01-13 11:18 UTC by Yaroslav
Modified: 2009-01-24 05:00 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Yaroslav 2009-01-13 11:18:27 UTC
My DB is "MSSQL Server 2005"
I'm using "sqljdbs4.jar" as ODBC driver.

My table has a few field and a few data lines.

When I am using a simple select - ResponseData are shown correctly in a JDBC sample, for instance,

#1>SELECT visittypeid, visitname, version from storeshop.visittypes;
"Response data" tab is showing correct data as expected:

visittypeid	typename	version
1	      Secret Shop	   1
2	      Blitz (Jun-Nov)      1
3	      Training	           1
4	      Casual Visit         1
5	      Blitz (2008 Holiday) 1

When I'm using little bit "fancy" SELECT, the ResponseData are incorrect, for instance,

#2>SELECT 'visittypeid=' + CAST(visittypeid AS VARCHAR), 'typename='  + typename, 'version=' + CAST(version AS VARCHAR) from storeshop.visittypes;
		
version=1	version=1	version=1
version=1	version=1	version=1
version=1	version=1	version=1
version=1	version=1	version=1
version=1	version=1	version=1

ER(ExpectedResult):

visittypeid=1   typename=Secret Shop        version=1
visittypeid=2   typename=Blitz (Jun-Nov)    version=1
...

I used to use a similar "technique" with MySql - I didn't have any problem there.
The #2 SELECT inside MSSQL is working fine, as expected.

So, I do not exactly, is it Jmeter "display" issue or "sqljdbs4.jar" issue.
Would you provide me "mssql" ODBC driver which you are using to verify the "issue" again.
You can attach the jar-file to this report - I guess it is ~0.5MB, if it is OK for you Or send directly to my email Or let me know when I can get "correct" one.
Comment 1 Yaroslav 2009-01-13 13:38:08 UTC
I've tried another JDBC driver from Microsoft - "sqljdbc_1.2.2828.100_enu.exe" - there is same incorrect result.
Comment 2 Sebb 2009-01-13 14:58:33 UTC
Since JMeter works fine with MySQL (and with Apache Derby where I tried a similar SQL statement), the problem seems to be related purely to the MS JDBC drivers - i.e. it is not a problem with JMeter.

If you can show that the select statement works OK with a stand-alone JDBC program, please re-open and attach details.
Comment 3 Yaroslav 2009-01-13 16:57:55 UTC
Sorry, I'm not a JAVA developer and don't have Java standalone environment. I will try to use a Beanshell sample for it, but for know I've tried couple more drivers - from JNETDirect.com and INETSoftware.de.

Results are the same as for native Microsoft JDBC (as in Description).

So, 99.99% looks like - it is a Jmeter problem (if those guys don't use a source-code from MS).

Reopened
Comment 4 Yaroslav 2009-01-14 09:35:01 UTC
Hi Sebb2,
As you asked I did some research.

I've found some tricky staff to get an expected result - need to use a "field's alias" - so, the next statement will be working as expected:

#3>select 'visittypeid=' + CAST(visittypeid AS VARCHAR) AS a, ('typename='  + typename) as b, ('version=' + CAST(version AS VARCHAR)) AS c from storeshop.visittypes;

Sorry, I was wrong about the result in MySql (see Description) - I'm using there fields' aliases.

But I've also found another fanny staff - if I'm using in my java code field-numbers (because fields' aliases are not defined in SQL statement) then it works!!!, so the next Beanshell code shows it:
-------
import java.sql.*;

Class.forName("com...");
Connection conn = DriverManager.getConnection ("jdbc:...." );

String sql="select 'visittypeid=' + CAST(visittypeid AS VARCHAR), 'typename='  + typename, 'version=' + CAST(version AS VARCHAR) from storeshop.visittypes";

Statement db_statement = conn.createStatement();
ResultSet result = db_statement.executeQuery (sql);
while (result.next() )
{
	System.out.println ("by filed's # ===>  " + result.getString(1) + "   " + result.getString(2) + "    " + result.getString(3));
}
-------
result:
by filed's # ===>  visittypeid=1   typename=Secret Shop    version=1
by filed's # ===>  visittypeid=2   typename=Blitz (Jun-Nov)    version=1
by filed's # ===>  visittypeid=3   typename=Training    version=1
by filed's # ===>  visittypeid=4   typename=Casual Visit    version=1
by filed's # ===>  visittypeid=5   typename=Blitz (2008 Holiday)    version=1

So, we have a case when SQL statement does not provide fields' aliases in positions where expression are used - then we have the problem.
But using fields' numbers in Jmeter would fix the problem. ;)

I think that the "dedicated" restriction (using aliases' names in SQL statement) should be placed in Jmeter manual if the "issue" will not be fixed in Jmeter soon.
Comment 5 Sebb 2009-01-15 08:45:47 UTC
Thanks for the extra info - that has confirmed it is a bug in JMeter.

The problem is that the column name is being used to store and look up the data, so if the name is non-unique (e.g. all blanks, as happens for the expressions) then the wrong data may be displayed.

Note that using aliases is not guaranteed to work either ... 

Just try using duplicate aliases - e.g. a,b,a instead of a,b,c - and some of the wrong information will be displayed.
Comment 6 Sebb 2009-01-18 13:13:45 UTC
Fixed in SVN:

URL: http://svn.apache.org/viewvc?rev=735548&view=rev
Log:
Bug 46522 - Incorrect "Response data" in JDBC sample when column names are missing
Comment 7 Yaroslav 2009-01-23 11:53:46 UTC
Would you provide svn# when a fix is done. I could test it.
Comment 8 Sebb 2009-01-24 05:00:48 UTC
SVN number is included in the URL:

...rev=735548...

and the currently nightly build includes the fix