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.
I've tried another JDBC driver from Microsoft - "sqljdbc_1.2.2828.100_enu.exe" - there is same incorrect result.
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.
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
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.
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.
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
Would you provide svn# when a fix is done. I could test it.
SVN number is included in the URL: ...rev=735548... and the currently nightly build includes the fix
This issue has been migrated to GitHub: https://github.com/apache/jmeter/issues/2191