Custom OrderClause with SQL Server in Paged Mode

[sderouchie]

I have a custom order clause that I am using in order to sort IP addresses. However, the order is not coming back correctly. When I switch to basic fetchmode, it appears to work correctly, but in Paged mode it doesn't.

Here is what my template looks like:

<DataSource
	tableName="device"
	ID="device"
	dataSourceVersion="1"
	generatedBy="dev current"
	serverType="sql"
>
	<fields>
		<field primaryKey="true" name="dev_id" type="number" hidden="true"></field>
		<field name="dev_displayname" length="128" type="ntext" title="Device Display name"></field>
		<field name="dev_address" length="50" type="ntext" title="Address"></field>
		<field name="dev_ipaddress" length="32" type="ntext" title="Network: IP Address"></field>
	</fields>

	<operationBindings>
		<operationBinding operationType="fetch" >
			<orderClause>
			    #if($dsRequest.sortBy)
				  #if($dsRequest.sortBy == "dev_ipaddress")
					cast(dbo.fnParseString(1,'.',dev_ipaddress) as INT),
         			cast(dbo.fnParseString(2,'.',dev_ipaddress) as INT),
         			cast(dbo.fnParseString(3,'.',dev_ipaddress) as INT),
         			cast(dbo.fnParseString(4,'.',dev_ipaddress) as INT)
				  #else
					$defaultOrderClause
				  #end
				#else
				  dev_displayname
				#end
			</orderClause>
		 </operationBinding>
	</operationBindings>
</DataSource>

I turned on JDBC tracing to see what query was being run:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY x.dev_ipaddress) AS rowID FROM (SELECT TOP 100 PERCENT  device.display_name, device.dev_address, device.dev_ipaddress
											 ORDER BY 
			    				  					cast(dbo.fnParseString(1,'.',dev_ipaddress) as INT),
         			cast(dbo.fnParseString(2,'.',dev_ipaddress) as INT),
         			cast(dbo.fnParseString(3,'.',dev_ipaddress) as INT),
         			cast(dbo.fnParseString(4,'.',dev_ipaddress) as INT)
				  							) x) y WHERE y.rowID BETWEEN 1 AND 75

I'm not sure why the order by is needed twice, but it is doing the custom order by in one case, and overriding it with the non-customized order by and giving back the wrong order.

[Isomorphic]

What you're seeing there is a query to report the row count and not the final query that returns data.

Instead of looking at JDBC tracing, just look at the server-side log, which reports the SQL used as well. If you need more help, post the log here (always post this for a server-side issue).

[sderouchie]

I don't think that is the query to report the row count -- that query actually returns the data, not just a count. From the JDBC log, the query just before this one looks to be the query to get the row count:

SQLServerStatement:241 Executing (not server cursor) SELECT COUNT(*) FROM device

I'll get some server side logs, but they are not showing the query -- they only show the row count query. I'll post these shortly.

[sderouchie]

I've attached the trace output that was produced when retrieving the data.

[Isomorphic]

The default log settings we ship with the SDK will log the actual SQL query and several other important troubleshooting logs that are missing from this output.

Please restore the default settings (found in log4j.isc.config.xml in your SDK) and send us the log with those settings enabled.

[sderouchie]

Sorry, I didn't realize someone had changed our default logging configuration. I've attached the log.

[Isomorphic]

This is high on the list for an engineer to look into, but we are currently focusing closely on getting the 3.1 release out. Please bear with us for a couple of days.

Thanks,
Isomorphic Software Support

[Isomorphic]

Also, as a quick possible workaround - it looks like you've set sqlPaging:"sqlLimit". The SQL that is required to implement this in Oracle (that complex outer query involving ROWCOUNT) may be interfering with your custom sort clause.

So you might be able to to correct the problem by setting sqlPaging:"jdbcScroll" on your <operationBinding>.

[Isomorphic]

What version / date of the framework are you using? It looks like we made changes to support this combination of sqlLimit / SQL Server / custom orderClause earlier this year (3.1 and greater only). Can you try this query with a recent 3.1 or 4.0 build and let us know if you still have the same problem?

[sderouchie]

This is the version I am using:

Isomorphic SmartClient Framework (v8.2p_2012-11-15/PowerEdition Deployment 2012-11-15)

[Isomorphic]

OK, again please try with a recent build. For older versions you'd need to use jdbcScroll mode as described previously.