insert update error UpdateBatchSize>1

[dmitryi]

OraQuery.CachedUpdates = True
after inserts or updates call OraQuery.CommitUpdates (or OraQuery.ApplyUpdates):
if OraQuery UpdateBatchSize property = 1 no error
if OraQuery UpdateBatchSize property > 1 is error: "Not found field corresponding parameter "
Delphi 2009
ODAC 6.80.0.48
Oracle Database 10g
(In demo OdacDemo\CachedUpdates, all works in both variants)
Why it occurs and how to correct.
That I do incorrectly.

[bork]

Hello

To reproduce this issue we need more information. Please provide us the query that you are executing in your TOraQuery and the DDL script for creating the tables that are used in your query.

[dmitryi]

OraSession.Options.Direct := True

SQL:

Code: Select all

SELECT * FROM dir_rooms

SQLInsert:

Code: Select all

INSERT INTO DIR_ROOMS
  (ID, DEP__OID, ROOM_NUMB, ROOM_NOTE)
VALUES
  (:ID, :DEP__OID, :ROOM_NUMB, :ROOM_NOTE)
RETURNING
  ID
INTO
  :ID

SQLUpdate:

Code: Select all

UPDATE DIR_ROOMS
SET
  DEP__OID = :DEP__OID,
  ROOM_NUMB = :ROOM_NUMB,
  ROOM_NOTE = :ROOM_NOTE
WHERE
  ID = :Old_ID

error message:

Not found field corresponding parameter DEP__OID

But this error arises in all OraQuery is not dependent from SQL

[bork]

Hello

I asked for the script for creating this table like this:
CREATE TABLE DIR_ROOMS (
...
)

The issue that you described can occur because you have created persistent fields (for example, in the design-time: TOraQuery->Fields Editor), but not all fields were added to persistent fields. It looks like ID, ROOM_NUMB and ROOM_NOTE were added to persistent fields but DEP__OID wasn't. Please check list of persistent fields in your TOraQuery.

[dmitryi]

create table DIR_ROOMS
(
ID INTEGER not null,
DEP__OID INTEGER not null,
ROOM_NUMB VARCHAR2(4),
ROOM_NOTE VARCHAR2(64)
)

[bork]

I cannot reproduce your issue. I executed the following code successfully:

Code: Select all

var
  i: integer;
  MySession: TOraSession;
  MyQuery: TOraQuery;
begin
  MySession := TOraSession.Create(self);
  MySession.Server := 'ORA1110';
  MySession.Password := 'tiger';
  MySession.Username := 'scott';

  try
    MySession.ExecSQL('drop table test_table', []);
  except
  end;

  MySession.ExecSQL('create table test_table ' + #13 +
                      '( ' + #13 +
                      'ID INTEGER not null, ' + #13 +
                      'DEP__OID INTEGER not null, ' + #13 +
                      'ROOM_NUMB VARCHAR2(4), ' + #13 +
                      'ROOM_NOTE VARCHAR2(64) ' + #13 +
                      ')', []);

  MyQuery := TOraQuery.Create(self);
  MyQuery.Session := MySession;
  MyQuery.SQL.Text := 'select * from test_table';

  MyQuery.Close;
  MyQuery.CachedUpdates := true;
  MyQuery.Options.UpdateBatchSize := 10;
  MyQuery.Open;

  for i :=  1 to 10 do
  begin
    MyQuery.Append;
    MyQuery.FieldByName('ID').AsInteger := i;
    MyQuery.FieldByName('DEP__OID').AsInteger := RandomRange(1, 100);
    MyQuery.FieldByName('ROOM_NUMB').AsString := 'a ' + IntToStr(i);
    MyQuery.FieldByName('ROOM_NOTE').AsString := 'b ' + IntToStr(i);
    MyQuery.Post;
  end;

  MyQuery.ApplyUpdates;
end;

Please create a new empty application and execute the same code. And please provide me the result of this code execution. If this is working correctly then please modify this code to reproduce your issue.

[dmitryi]

I can send video where the error is written to an E-mail, size = 464 kb

Can there are additional limitations on usages UpdateBatchSize?

[bork]

Hello

We don't need the video. To help you to fix this issue we should reproduce it at first. Earlier I've posted code that should create a session and query in the run-time and should reproduce your issue. On my computer this code was executed successfully. I ask you to execute the same code:
- If this code is executed on your computer with an error then I should detect the cause: it can be because of the Delphi version, Oracle version, Session settings, etc.
- If this code is executed on your computer successfully then I should find out which settings of your TOraSession or TOraQuery were changed. In this case the fastest way to reproduce this issue is to modify my code so that it reproduces your issue.
So please provide me the result of executing this code. If this code is working correctly then please modify this code to reproduce your issue.

[dmitryi]

Hello

I have created the new application, and execute the same code. This code is executed successfully.

Then I have changed a code:

Code: Select all

var
  i: integer;
  MySession: TOraSession;
  MyQuery: TOraQuery;
begin
  MySession := TOraSession.Create(self);
  MySession.Server := '169.254.254.131:1521:test';
  MySession.Password := 'info';
  MySession.Username := 'info';
  MySession.LoginPrompt := False;
  MySession.Options.Direct := True;

  try
    MySession.ExecSQL('drop table test_table', []);
  except
  end;

  MySession.ExecSQL('create table test_table ' + #13 +
                      '( ' + #13 +
                      'ID INTEGER not null, ' + #13 +
                      'DEP__OID INTEGER not null, ' + #13 +
                      'ROOM_NUMB VARCHAR2(4), ' + #13 +
                      'ROOM_NOTE VARCHAR2(64) ' + #13 +
                      ')', []);

  MyQuery := TOraQuery.Create(self);
  MyQuery.Session := MySession;
  MyQuery.SQL.Text := 'select * from test_table';

  MyQuery.SQLInsert.Text := 'insert into test_table ' + #13 +
                                     '(ID, DEP__OID, ROOM_NUMB, ROOM_NOTE) ' + #13 +
                               'values ' + #13 +
                                     '(:ID, :DEP__OID, :ROOM_NUMB, :ROOM_NOTE)'; 

  MyQuery.Close;
  MyQuery.CachedUpdates := true;
  MyQuery.Options.UpdateBatchSize := 10;
  MyQuery.Open;

  for i :=  1 to 10 do
  begin
    MyQuery.Append;
    MyQuery.FieldByName('ID').AsInteger := i;
    MyQuery.FieldByName('DEP__OID').AsInteger := RandomRange(1, 100);
    MyQuery.FieldByName('ROOM_NUMB').AsString := 'a ' + IntToStr(i);
    MyQuery.FieldByName('ROOM_NOTE').AsString := 'b ' + IntToStr(i);
    MyQuery.Post;
  end;

  MyQuery.ApplyUpdates;
end;

The error appears when I add the MyQuery.SQLInsert.Text

[dmitryi]

There can be a problem in code pages?

[dmitryi]

And my application has worked correctly with UpdateBatchSize=10 when I have deleted the SQLInsert.Text, SQLUpdate.Text

[bork]

Thank you for the information. We have reproduced your issue. We will notify you as soon as we have any result.

[bork]

Hello

We have fixed this bug. This fix was included in ODAC 7.0.0.1.