Strange ADO Connection Problem

[pricerc]

Hi,

I've just spent several hours troubleshooting a problem with a breakout in 'Receive Inventory', where after calling code from a custom menu (which worked fine), it was no longer possible to email invoices out of the 'Sales Order' form.

When the error condition is triggered, the problem manifests when trying to send an invoice via email. Jiwa.exe goes into la-la land for a few minutes, slowly increasing its memory use until finally it crashes with some variety of out-of-memory error, and the email fails to send. And Jiwa needs to be restarted to be usable again.

By a slow process of trial-and-error (in which testing involved making a change to the breakout, running the custom code, and then opening a sales order and trying to email it out and waiting to see whether or not it crashed), I eventually tracked it down to being something to do with my ADO connection.

My code was using JiwaDatabaseObject.ADOConnection (as suggested by Mike somewhere in this forum), to call a stored procedure to test whether or not a custom menu should be enabled, and it was working perfectly. Since I had all but run out of code to delete after dropping the stored procedure (I was testing my ADO connection error handling code), but was still getting the problem, it came down to a few lines, and the ADO connection itself seemed the most likely target.

I changed my code from doing this:

Code: Select all

Set con = DB.ADOConnectionObject

to this:

Code: Select all

Set con = CreateObject("ADODB.Connection")
con.Open DB.ADOConnectionString


and the problem seems to have gone away. I can only assume that calling the stored procedure was somehow leaving the connection in a state that the email code didn't like and wasn't coping with.

For the record, this is a stripped-down, untested version of the problem code. The original has several layers of function call, since I use a function to get an ADO command object for use in several places. Note that I had the problem even when the stored procedure didn't exist, so the name of the sproc is not important, nor the results it would generate if it were valid.

Code: Select all

Dim con
Set con = JiwaDatabaseObject.ADOConnectionObject
con.CursorLocation = 3 ' adUseClient - make results navigable.

Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = con

cmd.CommandType = 4 ' adCmdStoredProc
cmd.CommandText = "SomeBogusSproc"
cmd.Parameters("@SlipID").Value = SlipID
   
Set rs = cmd.Execute()
   
Set rs = Nothing
Set cmd = Nothing
Set con = Nothing


Looking at this code with the benefit of hindsight, I suspect the most likely source of trouble to be changing the cursor mode, since that's the only thing that I change on the connection itself. Although why enabling client-side cursors would break the email, only Mike may be able to answer.

regards,
Ryan

[Mike.Sheen]

I suspect the most likely source of trouble to be changing the cursor mode, since that's the only thing that I change on the connection itself. Although why enabling client-side cursors would break the email, only Mike may be able to answer.

regards,
Ryan

A reasonable assertion - I certainly do not recommend changing any of the connection properties if you expect Jiwa to function normally afterwards.

However, after examining your code, I saw a potential cause aside from that and decided to test it against 06.05.13.

Firstly, I created a stored proc :

CREATE PROCEDURE Test @SlipID CHAR(20) AS
SELECT TOP 1 * FROM IN_Main
GO

GRANT EXECUTE ON Test To JiwaUser
GO

I then opened Jiwa, and opened the Receive Inventory Form.

Under the "Form Loaded" breakout I added this :

FormObject.mRecordCustom(1).Caption = "test"
FormObject.mRecordCustom(1).Visible = True
End Sub

And then under the "Custom Menu Clicked" breakout I added your code, substituting your SP name with my "test" one :

Code: Select all

Dim con
Set con = JiwaDatabaseObject.ADOConnectionObject
con.CursorLocation = 3 ' adUseClient - make results navigable.

Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = con

cmd.CommandType = 4 ' adCmdStoredProc
cmd.CommandText = "Test"
cmd.Parameters("@SlipID").Value = SlipID
   
Set rs = cmd.Execute()
   
Set rs = Nothing
Set cmd = Nothing
Set con = Nothing

End Sub

And then I closed the Receive Inventory Form, reopened it and selected the newly added "test" menu option from the Record Menu.

No error, and I was able to read next, previous, etc. I repeated - again no problem.

Going back to my original suspect cause - I noted you did not close the recordset (ie: rs.close) - and I thought that would be the cause - but as I have proven, this is not the cause of your problem.

Is your stored proc doing anything like changing the transaction isolation level ? If so, that's going to be a problem - I recommend getting the current level, setting it to your desired level and then restoring it to the inital setting before exiting.

If not, please do try with the code and SP I posted above - if the problem still persists, post back here and we'll investigate further.

[pricerc]

Mike,

I will do some more testing later, but a couple of points:

1) I did test closing the recordset at some point, even if it didn't make into my sample code, but it made no difference.

2) I encountered the problem even when the stored proc didn't exist at all, and there was no recordset created (i.e. rs was still Nothing after call to cmd.Execute)

3) You don't mention whether or not you tried emailing an invoice from the Sales Order screen after running your stored procedure, which is the problem I was trying to resolve. I had no problems reported anywhere else in Jiwa, except for emailing out of Sales Orders.

4) My problem is also on 6.5.13 - should have mentioned it initially.

/Ryan