I have a pretty basic C11.1, ABC browse/form procedures for an MS-SQL table. The browse window has an image control that shows the photo (stored in a BLOB of the record). It was all working properly, So, I added NetTalk NetRefresh and NetSetTimeStamp. They all worked as advertised until it didn’t. After disabling the NetTalk templates, the issue remains.
I was testing the record delete feature from different browse procedures and I discovered that the browse with the BLOB record would hang after clicking “Yes” in the “Confirm Delete” message box. After more testing, I learned that records with an image in the BLOB field will hang when being deleted. I can delete any other records as usual. I can also delete the record with the BLOB field if I remove the contents from the BLOB.
It appears that when I try to delete a record with the BLOB that the record (or the table) is locked by the SQL Database Server because MSSS is also prevented from deleting the same record.
I am running out of ideas as to how to find my error that caused this behavior. My SQL and MSSS knowledge is very limited and do not know how to research this issue from the server side if that is the direction to search for the error.
I am not familiar with this specific case but try these tips:
Are you using logout/ commit/ stream/ flush? Try removing them.
Use sp_who2 to find out who is locking who.
What is the isolation level?
Are triggers involved? They can be waiting for a lock on another table.
I would look in the trace file to see what is sent to the server when you retrieve your photo (which is done separately from retrieving the other (normal) part of the record. It might be done with a select blob from wherever for update of blob. That would lock the record for editing, and could maybe prevent you from deleting the record (though I don’t think it should, as the same user, but who knows).
Maybe you need to save the blob first to release the hold, and then you can delete the record?
Thank you for suggesting sample code. I often forget that creating an example code is a good way to debug the problem. I will try the other suggestions first before trying to create an example because I am not sure I can re-create this issue from scratch. This issue started around the same time that the IDE messed up my window resize template settings. I am guessing that the IDE may have done something more than messing up my resizing.
I think I will try understanding what is happening first (as a learning exercise) before trying to recreating this issue. In any case, the code may be too long to post here. I will figure that out when I post the example.
I did notice that when deleting a record with a filled BLOB, there is a Select request (that is suspended). However, when deleting a record with a empty BLOB, the Select request (if one existed) executes without being suspended.
I will try to do a file trace and see if I can find out more.
Using sp_who2, I learned that the MSSQL Driver issues a Select request when the Delete Button is clicked. It then issues a Delete request when the Yes Button is click from the Confirm Message Box. For reason unknown to me, the Select Request is suspended if the BLOB field is not NULL. This blocks the subsequent Delete request and the program “hangs.”
I also learned that if MARS is off, the delete operation works as usual. The driver options in effect was /BusyHandling=2 and /MultipleActiveResultSets=True. The value for /BusyHandling does not seems to matter. As long as /MARS is set to False, Delete will work.
So, I got it working again but it is not a really a fix. I don’t understand why this setting started hanging on delete. I have this with other applications.
Could anyone suggest additional materials on SQL driver strings or explain what these settings do. Every time, I think I figured it out, I found that I am still missing something.
Thank you all for the support and sharing of knowledge.
Maybe this isn’t a fix either, but what if you just set the blobsize to zero and update the record before the DELETE? You could probably just do that in the FileManager object. Then you’d have time to figure out a real fix at your leisure.
That is a clever work-around. Thanks for the suggestion.
At the moment, I just turned off MARS. Because I don’t think I know exactly what I am doing with the SQL driver settings, it appears that it does not make any difference whither it is on or off.
I will revisit this once I actually learn the meanings of these SQL driver settings.
Changing MARS is a big change. I have had it break applications in unexpected places.
Are you deleting the blob on a form? That might issue a select.
Did you try deleting in a mini source application to test if that too issues a select?
Can you post the relevant source and where it ties into r application?
Search in the help for your driver name it has most of the information and search for delete/watch perhaps. It will however will not explain your issue.
Edward, you could always replace the delete with a prop:SQL call. I don’t normally recommend using a band-aide solution without understanding the problem, but you are starting to sound desperate.
If you’re solution is multi-DLL then add this code in the data DLL, otherwise in your EXE.
Go to the global embeds → Global Objects → ABC Objects → File Managers → File Manger for your table
The open the DeleteRecord method and insert the code below before the Parent call.
You need to change UniqueColumnName inside the delete statement and the name of prefix and name of the column. If your unique column is a string datatype then you need to quote the value.
IF Query AND SELF.Throw(Msg:ConfirmDelete) <> Level:Benign
RETURN Level:Cancel
END
SELF.File{Prop:SQL} = 'DELETE ' & SELF.File{Prop:Name} & ' WHERE UniqueColumnName = ' & ATT:UniqueColumnName
IF ERRORCODE() THEN
SELF.SetError(Msg:DeleteFailed)
RETURN(Level:Notify)
ELSE
RETURN(Level:Benign)
END
With a little more work you can make this completely generic by using the file properties to interrogating the file to determine the primary unique column.
No if you meant that I remove the BOLB independently from deleting the whole record. I let the standard ABC template code to remove the record. I did try using different options for “When Called for delete:” in the “Update record on disk” ABC Template. Both “Standard Warning” and “Display Form” cause a Select request before a Delete request to the SQL server. By the way, I should mention that my SQL server is on an AWS VM. My application connects to the server via TCP/IP. Don’t think this could make any difference except speed.
I will have time to read the driver documentation again this weekend and see if I can get more out of it this time. I don’t remember having such a hard time understanding technical documentations when I was younger .
Hi Rick,
Thank you for jumping in to the rescue. Sorry for sounding a bit desperate. I am actually more frustrated then desperate although I have been on this for a good few days now. I am frustrated that I cannot get a better understanding on how all these SQL driver options work and I seem to be bad in Googling for relevant information.
I think for now, I am okay with a workaround. Actually, now I have a choice of 3 workarounds.
With the available time left to complete my project, I may have to live with a workaround until all the features of this project are completed before I can do a deeper dive into this issue again.
To get a better understanding of what Clarion is saying to the DB you can enable a trace. Don’t forget to disable the trace or it will run forever even after a reboot!
The tool is called trace.exe, just select the driver and mark all the checkboxes and hit apply.
.