D,
I some times think with ADO (and COM for that matter) in VCL type wrappers
there
are strange timing issues. I have a db panel component that builds its
editor on Active & LayoutChanged and
Close then Open of the ADODataSet caused all sorts of strange behaviour
(like TFields transmogrifying mid ActiveChanged proc) I think after the
dataset opened
it moved things around in the recordset, it was almost as though it was
catching up. I couldnt debug it
because at low speed it never happened. Eventually I had to replace the
Close/Open with a requery (which caused other probs as it wiped out dynamic
recordset prop i was setting in AfterOpen). I also saw something like this
in some COM
code for using WinFAx
If you are getting this strange intermittent behavour (this sounds really
bad) but try spreading a few 'delay loops'
around
ps You are not going mad it's only COM
HTH
Neven
----- Original Message -----
From: Donovan J. Edye <[EMAIL PROTECTED]>
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
Sent: Friday, November 17, 2000 5:07 PM
Subject: [DUG]: [Q] ADO / MDAC 2.6 / SQL7 Strangeness....
> G'Day,
>
> Env: Delphi 5.01, Informal MDAC 2.6 patch installed, MDAC 2.6, SQL7 SP2
>
> I seem to be one of the lucky ones (not!!!) that "lost" all sensible error
> reporting once upgraded to MDAC 2.6. I am now faced with hex values for
> errors and not too friendly error strings. That aside. I have the
following
> stored procedure (See below). What is strange about this is that I can run
> it heaps of times in the query analyzer and get no errors. However
> occasionally / sporadically I get the error "OLE Error 80040E14" which is
> "DB_E_ERRORSINCOMMAND". If I execute the stored procedure again on excatly
> the same record. Then it succeeds. Calls to this stored procedure after
the
> initial failure work OK. Swicthing to another stored procedure and then
back
> again may cause this stored procedure to fail, but may not. Added to this
> the above behaviour is not restricted to this procedure alone but
seemingly
> random other stored procedures.
>
> I thought I read that things could get a little problematic if you had a
> return value from a stored procedure as well as output paramaters, but
there
> are stored procedures that have no output paramaters and just return
values,
> and they fail too. Irrespective of whether I am using .Open or .ExecProc
> this appears to happen.
>
> I apologise that this is so long and complex, but I am really stumped on
> this one. Can anyone shed any light on this for me?
>
> TIA
>
>
> To set up my stored procedure I use the following Delphi code:
>
> procedure TDM.SQL7_Init_StoredProcParams(var Use_StoredProc :
> TADOStoredProc; The_StoredProc : TAppStoredProcs);
>
////////////////////////////////////////////////////////////////////////////
> ////
> // PURPOSE: Reads the information from the global variable that contains
all
> the
> // stored procedure information. It then uses this information to create
> // the paramaters for the suppied stored procedure
> const
> PROC_NAME = 'TDM.SQL7_Init_StoredProcParams';
> ERR_MSG = 'I was unable to initialise the paramaters';
> var
> c : SmallInt;
> begin
> try
> with Use_StoredProc, AppStoredProcsInfo[The_StoredProc] do
> begin
> Use_StoredProc.Close;
> file://Assign Name
> Use_StoredProc.ProcedureName := ProcName;
> file://Create all the paramaters
> Parameters.Clear;
> Parameters.CreateParameter('RETURN_VALUE', ftInteger,
> pdReturnValue, 0, null);
> for c := Low(Params) to High(Params) do
> begin
> Parameters.CreateParameter(Params[c].ParamName,
> Params[c].DataType, Params[c].Direction, Params[c].Size, null);
> end;
> end;
> except
> on E : Exception do
> Handle_AppExceptions(UNIT_NAME, PROC_NAME, ERR_MSG, E.Message,
> FALSE);
> end;
> end;
>
> And to call them:
>
> function SQL7_Open_StoredProc(Use_DB : TADOConnection; var The_StoredProc
:
> TADOStoredProc;
> RaiseException : boolean = TRUE) : integer;
>
////////////////////////////////////////////////////////////////////////////
> ////
> // PURPOSE: Opens the specified stored procedure
> var
> sMsg : string;
> begin
> file://Default behavior
> result := 0;
> with The_StoredProc do
> begin
> Close;
> Connection := Use_DB;
> Prepared := TRUE;
> Open;
> file://Return the error code if we can
> if The_StoredProc.Parameters.FindParam('RETURN_VALUE') <> nil then
> result :=
> The_StoredProc.Parameters.ParamByName('RETURN_VALUE').Value;
> file://Now raise an exception
> if result <> 0 then
> begin
> file://Only raise the exception should the user want to
> if not RaiseException then Exit;
> if Use_DB.Errors.Count = 0 then
> begin
> sMsg := 'An error occurred while executing the stored
procedure
> [' + The_StoredProc.ProcedureName + ']';
> raise ESQL7.Create(sMsg);
> end else
> begin
> sMsg := 'An error occurred while executing the stored
procedure
> [' + The_StoredProc.ProcedureName + ']';
> sMsg := sMsg + 'SQL Server reported the error as: [' +
> IntToStr(Use_DB.Errors[0].NativeError) + Use_DB.Errors[0].Description +
']';
> raise ESQL7.Create(sMsg);
> end;
> end;
> end;
> end;
>
> function SQL7_Exec_StoredProc(Use_DB : TADOConnection; var The_StoredProc
:
> TADOStoredProc;
> RaiseException : boolean = TRUE) : integer;
>
////////////////////////////////////////////////////////////////////////////
> ////
> // PURPOSE: Allows a stored procedure to be executed
> var
> sMsg : string;
> begin
> file://Default behavior
> result := 0;
> with The_StoredProc do
> begin
> Close;
> Connection := Use_DB;
> Prepared := TRUE;
> ExecProc;
> file://Return the error code if we can
> if The_StoredProc.Parameters.FindParam('RETURN_VALUE') <> nil then
> result :=
> The_StoredProc.Parameters.ParamByName('RETURN_VALUE').Value;
> file://Now raise an exception
> if result <> 0 then
> begin
> file://Only raise the exception should the user want to
> if not RaiseException then Exit;
> if Use_DB.Errors.Count = 0 then
> begin
> sMsg := 'An error occurred while executing the stored
procedure
> [' + The_StoredProc.ProcedureName + ']';
> raise ESQL7.Create(sMsg);
> end else
> begin
> sMsg := 'An error occurred while executing the stored
procedure
> [' + The_StoredProc.ProcedureName + ']';
> sMsg := sMsg + 'SQL Server reported the error as: [' +
> IntToStr(Use_DB.Errors[0].NativeError) + Use_DB.Errors[0].Description +
']';
> raise ESQL7.Create(sMsg);
> end;
> end;
> end;
> end;
>
>
> CREATE PROCEDURE [dbo].[sp_AddRundownSlug] @ProgramID TProgramID, @DayID
> TDayID,
> @TimeOfDayID TTimeOfDayID,
> @NewSlugID TSlugID OUTPUT AS
> DECLARE @Err Int
> DECLARE @NextSlugID TSlugID
> DECLARE @NewSlugElementID TSlugElementID
>
> -- Get the next slug ID
> BEGIN TRANSACTION
> SELECT @NextSlugID = (SELECT Max(SlugID) + 1 FROM tblSlugs
> WHERE ProgramID = @ProgramID AND
> DayID = @DayID AND
> TimeOfDayID = @TimeOfDayID)
>
> -- NBNBNB This is temporary and is for the news system specific
> implementation
> -- Start slugs at 10
> SELECT @NextSlugID = IsNull(@NextSlugID, 10)
>
> -- Add the slug
> INSERT INTO tblSlugs (ProgramID, DayID, TimeOfDayID, SlugID, Description)
> VALUES (@ProgramID, @DayID, @TimeOfDayID, @NextSlugID, 'New Slug')
> SELECT @Err = @@ERROR
> IF @Err <> 0 GOTO error
>
> -- Now add the slug element
> EXEC sp_AddRundownElement @ProgramID, @DayID, @TimeOfDayID, @NextSlugID,
> @NewSlugElementID
> SELECT @Err = @@ERROR
> IF @Err <> 0 GOTO error
>
> -- Assign the element id to the output paramater
> SELECT @NewSlugID = @NextSlugID
>
> COMMIT TRANSACTION
> -- Return Success
> RETURN @Err
>
> /* Error Handling */
> error:
> ROLLBACK TRANSACTION
> RETURN @Err
> GO
> ------------------------------------------------------------------------
> --Donovan [[EMAIL PROTECTED]]
> Donovan J. Edye [www.edye.wattle.id.au]
> Namadgi Systems, Delphi Developer [www.namsys.com.au]
> Voice: +61 2 6285-3460 Fax: +61 2 6285-3459
> TVisualBasic = Class(None);
> Heard just before the 'Big Bang': "...Uh Oh...."
> ------------------------------------------------------------------------
> GXExplorer [http://www.gxexplorer.org] Freeware Windows Explorer
> replacement. Also includes freeware delphi windows explorer components.
> ------------------------------------------------------------------------
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe delphi"
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
