- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Link Copied
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
what does the interface to &Connection_Open in module msado28 look like?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
More likely a bug in the data in the COM object you are applying the wizard to...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
![](/skins/images/C4F7392A5C68ACA60290C9576F107044/responsive_peak/images/icon_anonymous_message.png)
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page