Access violation at executing Stored Procedure

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
alexer
Posts: 29
Joined: Tue 26 Dec 2006 14:07

Access violation at executing Stored Procedure

Post by alexer » Wed 23 Jan 2013 10:00

Hi, we tried latest (6.1.2) version of dbexpoda.dll and got access violation at executing stored procedure.
Delphi7+Oracle11g+dbexpoda.dll 6.1.2

Procedure definition:

Code: Select all

create or replace procedure OD_PR_INS_WIRS
   (WIR_ID  number, PLAN_ID  number, WIRDATE date, NAME varchar, "comment" varchar,
    D_SUMMA  binary_double, K_SUMMA  binary_double, EQ_  binary_double, D_AMOUNT  binary_double, K_AMOUNT  binary_double,
    BLANKS varchar, O_STEP  number, DOL  number, CHANGED_BY  number, IS_PLAN CHAR,
    D_BAL_ACC  number, D_VALUE  number, D_REG_1  number, D_REG_2  number, D_REG_3  number, D_REG_4  number, D_REG_5  number, D_REG_6  number,
    K_BAL_ACC  number, K_VALUE  number, K_REG_1  number, K_REG_2  number, K_REG_3  number, K_REG_4  number, K_REG_5  number, K_REG_6  number,NEW_ID out number, WIR_LIST out varchar, D_ID out number,  K_ID out number )
as
...
end FS_OD_PR_INS_WIRS
Procedure call:

Code: Select all

execute procedure PR_INS_WIRS
   ( :WIRDATE, :D_REST, :D_VAL, :D_SUMMA, :D_AMOUNT, :K_REST, :K_VAL, :K_SUMMA, :K_AMOUNT, :NAME, :comment, :EQ_, :O_STEP, :IS_PLAN, :DOL, :LINK, :TAG, :NEW_ID, :WIR_LIST )
Params:

Code: Select all

:WIRDATE = 04.12.2012 13:58:59 <SQLTimeStamp>
:D_REST = 7748855 <FMTBcdField>
:D_VAL = 1 <FMTBcdField>
:D_SUMMA = 0 <FMTBcdField>
:D_AMOUNT = 20 <FMTBcdField>
:K_REST = 7748805 <FMTBcdField>
:K_VAL = 1 <FMTBcdField>
:K_SUMMA = 0 <FMTBcdField>
:K_AMOUNT = 20 <FMTBcdField>
:NAME = Списание ЦБ с баланса (продажа)
:comment = Отчет брокера за 04.12.2012 14:09:17 ( "Банк ВТБ" ОАО )
:EQ_ = (null) <FMTBcdField>
:O_STEP = 7749080 <FMTBcdField>
:IS_PLAN = F <FixedChar>
:DOL = 7749051 <FMTBcdField>
:LINK = (null) <FMTBcdField>
:TAG = (null) <FMTBcdField>
:NEW_ID = 7749081 <FMTBcdField>
:WIR_LIST = (null)
Error:
Access violation at address 023629DD in module 'dbexpoda.dll'. Read of address 02784F8C

With version 6.0.1 procedure works fine.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Access violation at executing Stored Procedure

Post by AlexP » Wed 23 Jan 2013 11:48

Hello,

Please specify the component (TSQLStoredProc, TSQLQuery, etc.), as the provided procedure call code

Code: Select all

execute procedure PR_INS_WIRS 
is incorrect for Oracle.
Or send a small project demonstrating the problem to alexp*devart*com, including the full script for creating the procedure and objects used in it.

alexer
Posts: 29
Joined: Tue 26 Dec 2006 14:07

Re: Access violation at executing Stored Procedure

Post by alexer » Tue 23 Apr 2013 13:03

if Driver=drORACLE then begin // Stupid repeat to correct error in ORACLE Devart dbx driver
counter:=0;
while counter < 10 do begin
try
Result:=inherited ExecProc;
counter:=10;
except
on e:Exception do begin
inc(counter);
if (counter>=10) or (Pos('Access vio',E.Message)=0) then begin
raise
end;
FieldsParams_Preprocess(Driver, DriverName, self, Params);
end;
end;
end;
end else begin
result := inherited ExecProc;
end;

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Access violation at executing Stored Procedure

Post by AlexP » Wed 24 Apr 2013 13:04

Hello,

The given code sample doesn't demonstrate the problem, beside this, multiple procedure call will hardly help solve the problem with AV (if the parameters are not modified). Please send a full example, and DB objects scripts to alexp*devar*com in order for us to reproduce the problem.

alexer
Posts: 29
Joined: Tue 26 Dec 2006 14:07

Re: Access violation at executing Stored Procedure

Post by alexer » Wed 08 May 2013 06:56

unit Unit1;
//TEST FOR EXECUTION OF SIMPLE ORACLE 11G PROCEDURE on DELHI 7 on WINDOWS XP
//PROCEDURE CREATED BY USER 'META'
//
//CREATE OR REPLACE PROCEDURE DIC_FU_META_DATA_VERSION (VERSION OUT VARCHAR ) AS
//begin
// VERSION:= 'V02.20';
//end DIC_FU_META_DATA_VERSION;
///
//grant execute on DIC_FU_META_DATA_VERSION to PUBLIC;


interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, sqlexpr, db, DBXpress, StdCtrls, Buttons;

type
TForm1 = class(TForm)
BitBtn1: TBitBtn;
procedure BitBtn1Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;




implementation
uses QForms;

const
MetaDataVersion='V02.20';

var SQLConnection:TSQLConnection;


function CheckMetaDataVersion(metabase:TSQLConnection):string;
var StoredProc:TSQLStoredProc;
P :TParam;
begin
StoredProc:=TSQLStoredProc.Create(nil);
try
StoredProc.SQLConnection:=metabase;
StoredProc.StoredProcName:='META.DIC_FU_META_DATA_VERSION';
StoredProc.ExecProc;
result := StoredProc.ParamByName('VERSION').AsString;
if MetaDataVersion <> result then begin
Application.MessageBox( 'Proplem with parameter access', 'Look', [smbOK]);

end;
finally
StoredProc.Free
end;
end;


{$R *.dfm}
procedure TForm1.BitBtn1Click(Sender: TObject);
var s:string;
begin
try
S:=CheckMetaDataVersion(SQLConnection);
Application.MessageBox( 'ОК PROCEDURE CALL:'+S, 'Look', [smbOK]);
except
on e:Exception do begin
Application.MessageBox( e.Message, 'Look', [smbOK]);
raise;
end;
end;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
SQLConnection := TSQLConnection.Create(nil);

SQLConnection.DriverName:='DevartOracle';
SQLConnection.GetDriverFunc :='getSQLDriverORA';
SQLConnection.LibraryName :='dbexpoda.dll';
SQLConnection.VendorLib :='OCI.DLL';

SQLConnection.Params.Values['User_Name'] := 'SYSDBA'; // not schema owner!
SQLConnection.Params.Values['Password'] := 'keymaster';
SQLConnection.Params.Values['DataBase'] := 'ORADATA';
SQLConnection.Params.Values['Schema'] := 'META';
SQLConnection.ConnectionName := 'ORAMETA';
SQLConnection.LoginPrompt := TRUE;
try
SQLConnection.Connected := True;
except
on e:Exception do begin
Application.MessageBox( e.Message, 'Look', [smbOK]);
raise;
end;
end;
end;


initialization

end.



It is the problem to demonstrate the errors on other procedure call because the error raised in huge program complex having 10MB source code.

I partly resolve the problem by calling of procedure several times (up to 16). But the procedure DIC_FU_META_DATA_VERSION don't work anyway.

Is it posible to reconstruct driver source in a way that instead of message "Access violation" i should view message like this "Can't assign value for parameter XXXX of procedure YYYY".
Is it posible to add in driver source more diagnostic code and information to view the problem.

Is it posible to send me driver sorces with additional debug code or anything else.

The resolvation of the problem critial for me.

Thank you in advance.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Access violation at executing Stored Procedure

Post by AlexP » Wed 08 May 2013 10:27

Hello,


We have checked your code and procedure on Delphi 7 (build 8.1) + Oracle 11 (Server 11.2.0.1.0, Client 11.2.0.3.0) + Win XP & Win 7 - and we couldn't reproduce AV. Please specify your exact versions of IDE, Oracle (Client and Server), and an e-mail, to which we can send you a compiled exe file in order for you to check our build on your software

alexer
Posts: 29
Joined: Tue 26 Dec 2006 14:07

Re: Access violation at executing Stored Procedure

Post by alexer » Wed 08 May 2013 12:13

I have Delphi 7 (build 8.1) + Oracle 11 (Server 11.2.0.2.0, Client 11.2.0.2.0) + Win XP.

Error raised after n call of procedure. The error have not stable nature.

Error raised in module SqlExpr procedure TCustomSQLDataSet.GetOutputParams on call Check(FSQLCommand.getParameter(i+1, 0, RecBuffer, Params.Size, IsNull)) for output parameter.

To stable error raising I modify the test project by including the loop for 1000 call of same procedure:

unit Unit1;
//TEST FOR EXECUTION OF SIMPLE ORACLE 11G PROCEDURE
//
//CREATE OR REPLACE PROCEDURE DIC_FU_META_DATA_VERSION (VERSION OUT VARCHAR ) AS
//begin
// VERSION:= 'V02.20';
//end DIC_FU_META_DATA_VERSION;
///
//grant execute on DIC_FU_META_DATA_VERSION to PUBLIC;


interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, sqlexpr, db, DBXpress, StdCtrls, Buttons;

type
TForm1 = class(TForm)
BitBtn1: TBitBtn;
procedure BitBtn1Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;




implementation

const
MetaDataVersion='V02.20';

var SQLConnection:TSQLConnection;


function CheckMetaDataVersion(metabase:TSQLConnection):string;
var StoredProc:TSQLStoredProc;
P :TParam;
begin
StoredProc:=TSQLStoredProc.Create(nil);
try
StoredProc.SQLConnection:=metabase;
StoredProc.StoredProcName:='META.DIC_FU_META_DATA_VERSION';
StoredProc.ExecProc;
result := StoredProc.ParamByName('VERSION').AsString;
if MetaDataVersion <> result then begin
Application.MessageBox( 'Error in parameter value', 'Look');

end;
finally
StoredProc.Free
end;
end;


{$R *.dfm}
procedure TForm1.BitBtn1Click(Sender: TObject);
var s:string; i:integer;
TD:TTransactionDesc;
begin
TD.TransactionID := 1;
SQLConnection.StartTransaction(TD);
try
for i:=1 to 1000 do begin
try
S:=CheckMetaDataVersion(SQLConnection);
except
on e:Exception do begin
Application.MessageBox( PChar(e.Message + ' i=' + inttostr(i)), 'Look');
raise;
end;
end;
end;
Application.MessageBox( PChar('Success execution: '+S), 'Look' );
finally
SQLConnection.Commit(TD);
end
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
SQLConnection := TSQLConnection.Create(nil);

SQLConnection.DriverName:='DevartOracle';
SQLConnection.GetDriverFunc :='getSQLDriverORA';
SQLConnection.LibraryName :='dbexpoda.dll';
SQLConnection.VendorLib :='OCI.DLL';

SQLConnection.Params.Values['User_Name'] := 'SYSDBA'; // not schema owner!
SQLConnection.Params.Values['Schema'] := 'META';
SQLConnection.LoginPrompt := TRUE;

SQLConnection.Params.Values['Password'] := 'keymaster';

SQLConnection.Params.Values['DataBase'] := 'ORADATA';

SQLConnection.ConnectionName := 'ORAMETA';
try
SQLConnection.Connected := True;
except
on e:Exception do begin
Application.MessageBox( PChar(e.Message), 'Look');
raise;
end;
end;
end;


initialization

end.

alexer
Posts: 29
Joined: Tue 26 Dec 2006 14:07

Re: Access violation at executing Stored Procedure

Post by alexer » Wed 08 May 2013 13:13

1) My e-mail a.n.ermakov@mail.ru
2) The error raised only for all procedures which have output params with type varchar

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Access violation at executing Stored Procedure

Post by AlexP » Fri 10 May 2013 12:16

Hello,

I have sent you an application build to the specified e-mail (your login, password, etc. are used). Please try to execute the application (the procedure is called by click), and let us know the result.

alexer
Posts: 29
Joined: Tue 26 Dec 2006 14:07

Re: Access violation at executing Stored Procedure

Post by alexer » Mon 13 May 2013 08:07

Hello.
When I click the BitBtn1, I got a message "OK PROCEDURE CALL:V02.20".

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Access violation at executing Stored Procedure

Post by AlexP » Mon 13 May 2013 11:14

hello,

I have sent you a new application, in which this procedure is run in a loop (with no errors).

alexer
Posts: 29
Joined: Tue 26 Dec 2006 14:07

Re: Access violation at executing Stored Procedure

Post by alexer » Mon 13 May 2013 12:48

Now writes at startup: "SQL Server Error: ORA-12154: TNS:can not resolve the specified connection identifier".
When press the button says the same.
Can you sent a source code with the application?

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Access violation at executing Stored Procedure

Post by AlexP » Mon 13 May 2013 13:52

Hello,

I have sent you an application, the source code of which you had provided. Please check it again.

alexer
Posts: 29
Joined: Tue 26 Dec 2006 14:07

Re: Access violation at executing Stored Procedure

Post by alexer » Mon 13 May 2013 14:42

Can you send the letters back to the same address tva.fansy@mail.ru ? (including the previous address)
Can you duplicate the last letter (with application) to the address tva.fansy@mail.ru ?
Last edited by alexer on Mon 03 Jun 2013 07:59, edited 1 time in total.

alexer
Posts: 29
Joined: Tue 26 Dec 2006 14:07

Re: Access violation at executing Stored Procedure

Post by alexer » Mon 13 May 2013 15:21

Hello.
I checked. Compiled application gives you the same mistake as our:
"Access violation at addess 013629C3 in module 'dbexpoda.dll'. Read of address 0175C000 i=35"
The procedure gave a crash on call 35.
The next time click on BitBtn1:
"Access violation at addess 013629C3 in module 'dbexpoda.dll'. Read of address 0175C000 i=11"
The procedure gave a crash on call 11.
The next time:
"Access violation at addess 013629C3 in module 'dbexpoda.dll'. Read of address 0175C000 i=13"
The procedure gave a crash on call 13.

Post Reply