<div>
you aren't using host variables at all 
 
adoquery.SQL.Text := 
&nbsp; 'UPDATE MYTABLE SET FIELD1 = FIELD1 + 1 WHERE FIELD2 = :PARAM'; 
adoquery.ParamByName('PARAM').AsString := 'A'; 
 
this is host variables !
</div>

<div>
You should do something like this 
 
with ADOQuery1 do begin 
&nbsp; Close; 
&nbsp; SQL.Clear; 
&nbsp; SQL.Add('UPDATE MYTABLE'); 
&nbsp; SQL.Add('SET FIELD1 = (SELECT FIELD1 FROM MYTABLE WHERE FIELD2 = 'A') + 1'); 
&nbsp; SQL.Add('WHERE FIELD2 = 'A''); 
&nbsp; EXECSQL; 
end; 

</div>

<div>
if you really want it like (i advise against): 
mind the quotes ! (all single) 
 
with ADOQuery1 do begin 
&nbsp; Close; 
&nbsp; SQL.Clear; 
&nbsp; SQL.Add('UPDATE MYTABLE'); 
&nbsp; SQL.Add('SET FIELD1 = (SELECT FIELD1 FROM MYTABLE WHERE FIELD2 = ''A'') + 1'); 
&nbsp; SQL.Add('WHERE FIELD2 = ''A'''); 
&nbsp; EXECSQL; 
end;
</div>

<div>
Yes Geert, 
i'm not using host variables, and i don't want to use them! 
Are they required? 
 
I've tested the query suggested by you in the first reply, thank you for the simplified query, it works perfectly. 
 
I don't know why the query was wroten in this way... 
 
The same error is still remain (SQL0302). 
Regards
</div>

<div>
thanks tankergoblin, 
i'm already using single quotes.
</div>

<div>
not using host variables will slow down your database 
it can process 3x less queries
</div>

<div>
what are the types of the columns and the actual values you are trying to insert ? 
 
if you have a column of Varchar(20) and you add a string which is longer (40) this may happen ... 

</div>

<div>
Geert, 
i don't know how to use host variables within delphi (i've used them with cobol.. ). 
Can you post a code example with the same type of query?
</div>

<div>
i guess you missed my second comment ?
</div>

<div>
Geert, 
finally i've found the problem.... 
 
The column specified in the where clause was a different type (decimal). 
I've spent a whole day thanks to a &quot;non sense&quot; error returned by DB2, i'm so angry... 
 
By the way i'm very grateful to you for the simplified query, the points are yours. 
Best Regard. 
 
P.S.: 
Can you give me anyway the delphi code example to use host variables? 

</div>

<div>
i'll just copy some snippets of how i do it with parameters = same as host variables 
 
with QryUpd do 
&nbsp; begin 
&nbsp; &nbsp; SQL.Text := 
&nbsp; &nbsp; &nbsp; 'UPDATE MYTABLE SET FIELD1 = FIELD1 + 1 WHERE ID = :ID'; 
&nbsp; &nbsp; ParamByName('ID').AsInteger := fId; 
&nbsp; &nbsp; ExecSQL; 
&nbsp; end; 

<pre><code class="code-10 nolinks" id="code-265823"> fPriceFieldsName.Clear;
 aQry := TADoQuery.Create(nil);
 try
 aQry.Connection := fConn;
 aQry.SQL.Text :=
 'SELECT * FROM PRICESCONFIG '+
 'WHERE TABLENAME = :TABLENAME '+
 ' AND PRICEGROUP = :PRICEGROUP '+
 ' AND CURRENCYID = :CURRENCYID '+
 'ORDER BY SORTPOS, FIELDNAME ';
 aQry.Prepare;
 aQry.ParamByName('TABLENAME').AsString := fTableName;
 aQry.ParamByName('PRICEGROUP').AsInteger := fGroupId;
 aQry.ParamByName('CURRENCYID').AsInteger := fCurrencyId;
 aQry.Open;
 if not aQry.IsEmpty then
 while not aQry.Eof do
 begin
 fPriceFieldsName.Add(aQry.FieldByName('FIELDNAME').AsString);
 aQry.Next;
 end;
 aQry.Close;
 finally
 FreeAndNil(aQry);
 end;
 
procedure TFormCurrency.FetchUpdate(Field: string; Value: Variant);
begin
 with QryUpd do
 begin
 SQL.Text :=
 Format('UPDATE CURRENCIES SET %s = :VALUE WHERE CURRENCYID = :ID' , [Field]);
 Prepare;
 ParamByName('ID').AsInteger := fCurrencyId;
 if VarIsNull(Value) then
 ParamByName('VALUE').Value := Null
 else
 case VarType(Value) of
 varString: ParamByName('VALUE').AsString := Value;
 varDouble: ParamByName('VALUE').AsFloat := Value;
 varInteger, varShortInt: ParamByName('VALUE').AsInteger := Value;
 end;
 ExecSQL;
 end;
end;
</code></pre>
</div>

<div>
Thanks Geert, i'll use these snippets in the future, for performance tuning. 
Best Regards. 
 

</div>

<div>
<div class="content wysiwyg-content">
Hi, 
i'm developing an NT service using delphi2006 and ADO to execute an update on DB2/AS400. 
 
I'm using a TADOQuery object and when i try to execute the following query: 
 
UPDATE MYTABLE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
SET FIELD1 = (SELECT FIELD1 FROM MYTABLE WHERE FIELD2 = 'A') + 1 
WHERE FIELD2 = 'A' 
 
I get the error code SQL0302, that mean something like &quot;The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use&quot;. 
 
Of course the same query executed from other applications like dbvisualizer works fine, without any problem. 
Moreover, i'm not using host variables, only fixed values in the SET and WHERE clauses. 
 
My problem is very urgent, any help is welcome.. 
Regards
</div>
</div>

Hi, 
i'm developing an NT service using delphi2006 and ADO to execute an update on DB2/AS400.

I'm using a TADOQuery object and when i try to execute the following query:

UPDATE MYTABLE           
SET FIELD1 = (SELECT FIELD1 FROM MYTABLE WHERE FIELD2 = 'A') + 1 
WHERE FIELD2 = 'A' 

I get the error code SQL0302, that mean something like "The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use".

Of course the same query executed from other applications like dbvisualizer works fine, without any problem.
Moreover, i'm not using host variables, only fixed values in the SET and WHERE clauses.

My problem is very urgent, any help is welcome..
Regards

UPDATE problem with Delphi/DB2/AS400

Operating systems perform basic tasks, such as recognizing input from the keyboard, sending output to the display screen, keeping track of files and directories on the disk, and controlling peripheral devices such as disk drives and printers. For large systems, the operating system makes sure that different programs and users running at the same time do not interfere with each other. The operating system is also responsible for security, ensuring that unauthorized users do not access the system. Operating systems provide a software platform on top of which other programs, called application programs, can run.

Operating Systems

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

Delphi is the most powerful Object Pascal IDE and component library for cross-platform Native App Development with flexible Cloud services and broad IoT connectivity. It provides powerful VCL controls for Windows 10 and enables FMX development for Windows, Mac and Mobile. Delphi is your choice for ultrafast Enterprise Strong Development™. Look for increased memory for large projects, extended multi-monitor support, improved Object Inspector and much more. Delphi is 5x faster for development and deployment across multiple desktop, mobile, cloud and database platforms including 32-bit and 64-bit Windows 10.

Delphi

COBOL (Common Business-Oriented Language) is a programming language with English-like syntax. It is readable but verbose compared to other programming languages. CODASYL (Conference on Data Systems Languages) created COBOL in 1959 based on design work from Grace Hopper. It was standardized in 1968. COBOL is still present in large-scale batch processing and transaction processing where legacy applications are being used, usually in government, financial, and administrative systems.