Page 1 of 1
insert update error UpdateBatchSize>1
Posted: Wed 14 Jul 2010 07:14
by 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.
Posted: Thu 15 Jul 2010 08:06
by 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.
The additional information
Posted: Fri 16 Jul 2010 06:29
by dmitryi
OraSession.Options.Direct := True
SQL:
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
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
Posted: Fri 16 Jul 2010 08:42
by 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.
Posted: Fri 16 Jul 2010 08:55
by dmitryi
create table DIR_ROOMS
(
ID INTEGER not null,
DEP__OID INTEGER not null,
ROOM_NUMB VARCHAR2(4),
ROOM_NOTE VARCHAR2(64)
)
Posted: Mon 19 Jul 2010 09:39
by 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.
Posted: Mon 19 Jul 2010 12:15
by 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?
Posted: Wed 21 Jul 2010 14:54
by 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.
Posted: Thu 22 Jul 2010 06:25
by 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
Posted: Thu 22 Jul 2010 06:31
by dmitryi
There can be a problem in code pages?
Posted: Thu 22 Jul 2010 06:42
by dmitryi
And my application has worked correctly with UpdateBatchSize=10 when I have deleted the SQLInsert.Text, SQLUpdate.Text
Posted: Fri 23 Jul 2010 14:59
by bork
Thank you for the information. We have reproduced your issue. We will notify you as soon as we have any result.
Posted: Wed 20 Oct 2010 13:23
by bork
Hello
We have fixed this bug. This fix was included in ODAC 7.0.0.1.