Has anyone seen this error before when running a SQL Select?
I went to the webpage suggested by the error, but essentially just found a bunch of people complaining to Microsoft to “fix the bug.” All I’m doing is the following:
Storing this SQL in a constant
Connecting to a MS SQL Server
After connection, running a SQLSelect on the database with this query
Attempting to receive a RecordSet back to parse.
I run about 25 other queries to the same database on the same server in the same fashion via threads and timers, but this particular one is causing an issue. One would assume then that it’s the SQL query itself, however when running it in SQL Management Studio I have no issues.
Problem is these seem to be settable in code in C# but I don’t think they’re settable in Xojo when using the MS SQL Plugin. I’m trying to avoid requiring ODBC in an app but it doesn’t seem possible with this bug here
With the release of newer SQL Server and Xojo, is there any XOJO native solution using the XOJO SQL plugin? After coding for many months towards the goal of Full Text Searches, any suggestions would be helpful. Thanks.
We use Monkeybread’s SQL Plugin using ODBC to connect to MSSQL databases as this is the only solution that does everything we want. This is what we found with each of the options that we looked at:
MSSQLDatabase Xojo Plugin
This plugin does not deal with Large Data Objects. We cannot read and write successfully to these fields using this plugin.
ODBCDatabase Xojo Plugin
This plugin does not handle Lock Timeouts correctly. When a lock timeout occurs, no error is raised and the recordset contains whatever data has successfully been read up to the timeout. It does read and write BLOBs correctly.
Monkeybread SQL Plugin using DBLibrary
This plugin does not handle lock timeout errors from the recordsets correctly. To be clear, we think this is a problem with Microsoft’s SQL client, not Monkeybread’s plugin.
Monkeybread SQL Plugin using ODBC
Lock timeouts are raised correctly. It does handle BLOBs but you must set the dtb.Option(“ODBCAddLongTextBufferSpace”) = “false”
Both Monkeybread plugins require that all recordsets are closed before disconnecting from the database. It’s also important not to open multiple recordsets at the same time against one database connection. This causes “unpredictable” behaviour. Again this is a Microsoft issue, not a Monkeybread problem.
These findings are from about a year ago so may have been updated.
I’ve found a work-around for this issue while still using the MSSQLDatabase connector. The key is to use the CONVERT() function inside of SQL Server statement. CAST() does not work.
Dim sCommand As String = _
"SELECT wo.wonumber, wo.vehicleid " + _
", CONVERT( VARCHAR(2000), woli.ItemCode) AS ItemCode " + _
", CONVERT( VARCHAR(2000), woli.ItemType) AS ItemType " + _
"FROM WorkOrders wo " + _
" LEFT JOIN WOLineItems woli ON wo.WorkOrderId = woli.WorkOrderId " + _
"WHERE wo.CreatedOn > '2017-01-01' " + _
"ORDER BY wo.woNumber, woli.ItemCode"
In my case, I don’t have any control over the table structures as it is vertical market product that we’ve purchased. I’m just trying to create better reporting options using Xojo Web. I hope this helps someone out. It’ll probably be me, sometime in the future when I go looking for a solution to this problem that I’ve already solved way back in 2017. Take care.
[quote=352141:@Kevin J Cully]I’ve found a work-around for this issue while still using the MSSQLDatabase connector. The key is to use the CONVERT() function inside of SQL Server statement. CAST() does not work.
Kevin - thank you! This is the solution that I was looking for. I appreciate this “out of the box” solution vs. just throwing up your hands and going to a plugin or ODBC.