AUTOINVIKE will not execute more than once

[David_B_18]

I'm having a problem with executing a call to the Execute method of a Connection object more than once.  The first call executes fine but any subsequent calls just cause the program to stop execution.  The point the program stops is the "$$STATUS = AUTOINVIKE($OBJECT, 6, invokeargs)" statement in the $Connection_Execute function.  The $Connection_Execute function is contained in a module called "msado28" which was created with the Module Wizard.  The commandtext for the Execute method is merely a call to a SQL Server stored procedure called Storetest which takes one string parameter and has been tested within SQL Server Management Studio.

The main program code is as below:

program storetest
 use msado28
 implicit none
 
 integer(int_ptr_kind()) COMcnn
 character*120, parameter::COMcnnstr='Provider=SQLOLEDB; Data Source=mySQLServer; Initial Catalog=myDB; Trusted_Connection=Yes'
 integer(4) COMrtnstatus
 integer(int_ptr_kind()) COMexestatus
 type(variant) rst
 integer*4 i

 integer(4),parameter::adConnectUnspecified = -1
 integer(4),parameter::adExecuteNoRecords = 128

 call COMInitialize(COMrtnstatus)
 call &Connection_Open(COMcnn,COMcnnstr,'myUserID','myPassword',adConnectUnspecified)

 do i=1,10
  COMexestatus=$Connection_Execute(COMcnn,"Exec Storetest 'this is a test'",rst,adExecuteNoRecords,COMrtnstatus)
 enddo

 call $Connection_Close(COMcnn,COMrtnstatus)
 COMrtnstatus=COMReleaseObject(COMcnn)
 call COMUninitialize()

end program

I've expanded the range of the loop to include the COMInitialize and COMUninitialize but that didn't help.
 

Anyone have any ideas why the Execute method hangs on the second pass through the loop?

Thanks

 

[andrew_4619]

what does the interface to &Connection_Open in module msado28 look like?

[David_B_18]

subroutine $Connection_Open($Object, ConnectionString, UserID, Password, Options, $Status)
 implicit none
 integer(int_ptr_kind()), intent(in) :: $Object
 character(len=*), intent(in) :: ConnectionString
 character(len=*), intent(in) :: UserID
 character(len=*), intent(in) :: Password
 integer(4), intent(in) :: Options
 integer(4), intent(out) :: $Status

 integer(4) $$status
 integer(int_ptr_kind()) invokeargs

 invokeargs=autoallocateinvokeargs()

 call autoaddarg(invokeargs, '$ARG1', ConnectionString, AUTO_ARG_IN, VT_BSTR)
 call autoaddarg(invokeargs, '$ARG2', UserID, AUTO_ARG_IN, VT_BSTR)
 call autoaddarg(invokeargs, '$ARG3', Password, AUTO_ARG_IN, VT_BSTR)
 call autoaddarg(invokeargs, '$ARG4', Options)

 $$Status = autoinvoke($object, 10, invokeargs)
 if ($$Status == DISP_E_EXCEPTION) call $$DisplayError(invokeargs)
 if (present($Status)) $Status = $$Status

 call autodeallocateargs(invokeargs)
end subroutine


integer(int_ptr_kind()) function $Connection_Execute($Object, CommandText, RecordAffected, Options, $Status)
 implicit none
 integer(int_ptr_kind()), intent(in) :: $Object
 character(len=*), intent(in) :: CommandText
 type (variant), intent(out) :: RecordsAffected
 integer(4), intent(in) :: Options
 integer(4), intent(out), optional :: $Status
 integer(int_ptr_kind()), volatile :: $Return

 integer(4) $$status
 integer(int_ptr_kind()) invokeargs

 invokeargs=autoallocateinvokeargs()

 call autoaddarg(invokeargs, '$RETURN', $Return, AUTO_ARG_OUT, VT_DISPATCH)
 call autoaddarg(invokeargs, '$ARG1', CommandText, AUTO_ARG_IN, VT_BSTR)
 call autoaddarg(invokeargs, '$ARG2', RecordsAffected, AUTO_ARG_OUT)
 call autoaddarg(invokeargs, '$ARG3', Options)

 $$Status = autoinvoke($object, 6, invokeargs)
 if ($$Status == DISP_E_EXCEPTION) call $$DisplayError(invokeargs)
 if (present($Status)) $Status = $$Status
 
 $Connection_Execute = $Return

 call autodeallocateargs(invokeargs)
end function

I have just noticed in the immediate window (don't know why I didn't notice before) an error message:

"First-chance exception at 0x011383bc in storetest.exe: 0xC0000005: Access violation reading location 0x00000000."

Thanks

[David_B_18]

Ignore that error message.  It must have been left over from something else I did.  It's not appearing now, even though the code is still refusing to execute the $$Status = autoinvoke($object, 6, invokeargs) line of code the second time through.

[David_B_18]

OK, I've found a work-around:

If I comment out the two lines:

 call autoaddarg(invokeargs, '$ARG2', RecordsAffected, AUTO_ARG_OUT)
 call autoaddarg(invokeargs, '$ARG3', Options)

the code loops through fine.

Why?...
 

[jimdempseyatthecove]

Check to see if API requires the CommandText arg line to include the additional command line arguments (as opposed to your above code having them as additional autoaddarg args). This may be an O/S dependent thing/quirk.

Jim Dempsey

[David_B_18]

Apparently not, as the code does run when commenting out both lines.  The offending line is the one with the RecordsAffected argument.  Commenting out just this one line also allows the code to run through.

Perusing the Microsoft documentation on the Execute method of the Connection object (https://msdn.microsoft.com/en-us/library/windows/desktop/ms675023%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396) I learned the RecordsAffected parameter is actually a Long (when using VB) and is used to return the number of records returned - not the records themselves.  So, I redefined the rst variable to integer(4) and the RecordsAffected parameter to integer(4), intent(out).

That seemed to fix it.

Is this a bug in the Module Wizard?

[andrew_4619]

More likely a bug in the data in the COM object you are applying the  wizard to...

[David_B_18]

Per conversation with "app4619" I've changed the type of RecordsAffected to the IFWIN module definition corresponding to integer(4); i.e. integer(long) - many Thanks, Andrew.

This should be the final resolution on this issue.