Access violation at executing Stored Procedure

[alexer]

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]

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]

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]

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]

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]

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]

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]

1) My e-mail [email protected]
2) The error raised only for all procedures which have output params with type varchar

[AlexP]

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]

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

[AlexP]

hello,

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

[alexer]

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]

Hello,

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

[alexer]

Can you send the letters back to the same address [email protected] ? (including the previous address)
Can you duplicate the last letter (with application) to the address [email protected] ?

[alexer]

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.