Problem:
With an MS SQLServer database, when I do a WHERE query on a value from a binary(32) column, and use a value obtained from an earlier query, I have to prefix the value in the WHERE query with 0x .
That's not nice, because what I want is to be able to use the value as it was returned from the earlier query.
Explanation:
In a SQL server database I have table KNT_0104_Zaak_Werksoort that has 5 columns,
one of which is KLM_Zaak_Werksoort_HKey with type binary(32) .
https://learn.microsoft.com/en-us/sql/t-sql/data-types/binary-and-varbinary-transact-sql?view=sql-server-ver16
states:
binary [ ( n ) ]
Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.
I do first a query that returns values from the first row,
and I store the value of KLM_Zaak_Werksoort_HKey in slim variable firstKey .
Next I want to check that a query with a WHERE clause on that value of firstKey ,
returns a result from matching row 1.
I find that in the WHERE clause, I need to prefix the value for the firstKey, with 0x .
Otherwise the second query won't match any row.
!* query first row
!|SQLCommand |StartupDatabase|SELECT TOP 1 * FROM BV.KNT_0104_Zaak_Werksoort|
|KLM_Zaak_Werksoort_HKey?|MD_GeldigVan? |MD_GeldigTot? |MD_LaadDatum? |MD_Bron? |
|$firstKey= |=~/.*/ |=~/.*/ |=~/.*/ |=~/.*/ |
*!
!* where query
!|SQLCommand |StartupDatabase|SELECT MD_GeldigVan FROM BV.KNT_0104_Zaak_Werksoort WHERE KLM_Zaak_Werksoort_HKey = 0x%KLM_Zaak_Werksoort_HKey%|
|KLM_Zaak_Werksoort_HKey|MD_GeldigVan? |
|$firstKey |=~/.*/ |
*!
I looked in the jdbcslim code, at where the value that is assigned to firstKey, is obtained :
six42.fitnesse.jdbcslim.SQLCommand convertRSIntoTable :
while (rs.next()) {
oneRow = new ArrayList();
for (int i = 1; i <= columnCount; i++) {
>>> oneRow.add(rs.getString(i));
if (Properties().isDebug()) System.out.println("Row:" + i + ":" + rs.getString(i));
}
appendOrExtendRow(resultTable, oneRow, rowCount++, extend, summaryOnly);
}
I see that rs is an object of type
com.microsoft.sqlserver.jdbc.SQLServerResultSet
and from the ms sqlserver jdbc driver (mssql-jdbc-11.2.1.jre11.jar) .
Question:
Should the getString() method from SQLServerResultSet add the prefix 0x .
Of should method convertRSIntoTable from SQLCommand add the prefix ,
by using something more advanced than getString() ?