Devart Forums

I am trying to perform a simple update of a field within a oracle 10g database

using delphi 6 ent and ODAC 5.50


? why does sqlplus require a ; charactor while ODAC Doesn't?

? is thier any suggestions for improved error checking and making the code more bullet proof?

? Didnt I read somewhere an issue with using the net option to do writes
to the database?

PS Thanks for your wonderful product

A beginners guide to datasets and oracle querys / interaction would be
helpful any suggestion for info on the web?




{******************************************************************************}

procedure Build_Query(s : string);
begin
Queryme.Clear;
Queryme.Add('UPDATE');
Queryme.Add('AMASSET');
Queryme.Add('SET');
Queryme.Add('FIELD1 = ' + chr(39) + 'UPDATETHIS' + chr(39));
Queryme.Add('WHERE');
Queryme.Add('ASSETTAG = ' + chr(39) + s + chr(39));

form1.memo1.Clear;
form1.memo1.lines := queryme;
form1.oraquery1.sqlUpdate := queryme;
end;

{******************************************************************************}

procedure Run_Update_Query;
begin
// first insure the dataset is closed
form1.OraDataSource1.DataSet.Close;

// connect
form1.orasession1.server := '10.34.1.146:1521:ATEST';
form1.orasession1.username := 'acugjsr';
form1.orasession1.Password := 'nadayo23';
form1.orasession1.Options.Net := true;
if form1.orasession1.connected true then form1.orasession1.Connect;

form1.OraQuery1.CachedUpdates := true;
try
Begin
form1.oraquery1.Execute;
End;
except
On E : Exception do form1.memo1.Lines.add('Error');
On E : EOraError do form1.memo1.Lines.add('Error Ora');
end; { try except block }

form1.oraquery1.CommitUpdates;

form1.orasession1.disconnect;

end;

{******************************************************************************}

procedure TForm1.Button1Click(Sender: TObject);
begin
Build_Query('YILDIRIM1-1339Z11');

Run_Update_Query;

end; { Procedure }

{******************************************************************************}

ODAC with TOraSession.Options.Net = True does not support national characters in SQL text. You must use parameters. Please see restrictions of Net option in "Working with ODAC Net".

Try to use OraErrorHandler component to change error messages. Please see MasterDetail demo project.
You can handle TOraSession.OnError event and show your message.

ODAC uses common foundations of Borland VCL. You can read about ODAC specific properties in ODAC help. Please see demo projects how to use ODAC components.

Still No Luck

I Reviewed The Working With ODAC Net And Still Recieve The
ORA-01009: missing mandatory parameter
error. I guess my question is
What missing Parameter?
I've been trying varations of the TORAQuery Componet
but still the same error 1009

I've search on the web but cant seem make it through all the Java error dumps and come out with anything usefull

A few places discussed using the FOR clause to limit the number of array elements.

see notes below

Another thing I ran across discussed using paretheses

see notes below


My query is a bit simple

it changes only a single field in a single row

UPDATE AMASSET
SET FIELD1 = 'New Value'
WHERE AMASSET.ASSETTAG = 'YILDIRIM-2-JTXKC11'

I tried setting multiple values but that didnt help

I tried varing the blocking non blocking

When I cut and paste the update stament into SQLPLUS and add a ;
and a commit;

the update runs fine

Any ideas of a possible solution to error 1009, a way to collect more information or an alternate way I might
change the value of a single field programatically

Would help me out alot

Frank Bishop


Notes
--------------

Some Info In Programmer's Guide to the Oracle Precompilers.

--------------------------------------------------------------------------------

Using the FOR Clause
You can use the optional FOR clause to set the number of array elements processed by a SQL statement. The FOR clause is especially useful in UPDATE, INSERT, and DELETE statements. With these statements you might not want to use the entire array (the default). The FOR clause lets you limit the elements used to just the number you need, as the following example shows:

EMP_NAME : name_array(1..100);
SALARY : ORACLE.FLOAT_ARRAY(1..100);
ROWS_TO_INSERT : integer;
...
-- populate the host arrays
ROWS_TO_INSERT := 25; -- set FOR-clause variable
...
EXEC SQL FOR :ROWS_TO_INSERT -- will process only 25 rows
INSERT INTO emp (ename, sal)
VALUES (:EMP_NAME, :SALARY);
The FOR clause must use an integer host variable to count array elements. For example, the following FOR clause is invalid:


EXEC SQL FOR 25 -- literal not allowed
INSERT INTO emp (ename, empno, sal)
VALUES (:EMP_NAME, :EMP_NUMBER, :SALARY);
The number of array elements processed is determined by comparing the dimension of the smallest host or indicator array with the FOR-clause variable. The lesser value is used.

If the value of the FOR-clause variable is negative, no rows are processed. In this case, a zero is returned to SQLCODE in the SQLCA indicating that Oracle carried out the operation successfully. However, if the value of the FOR-clause variable is zero, no rows are processed, and Oracle issues the error message


ORA-01009: missing mandatory parameter
because zero is treated like an unspecified value.

--------------------------------------------------------------------------------
Some Websphere Stuff
Oracle
Surrounding a query with () causes the Ora-01009 error or an empty result set (113709.RN)
If you are using the Oracle thin driver and the OCI driver, surrounding a query with parentheses ("()") results in the error "ORA-01009: missing mandatory parameter" and an empty result set (no data). This is an Oracle error (917674) and was fixed in the 817.2 fixset. To avoid the problem, either install Oracle fixset 817.2 or avoid surrounding queries with parentheses.

I see your mistake. You assigned oraquery1.sqlUpdate only.

form1.oraquery1.sqlUpdate := queryme;

But when you call form1.oraquery1.Execute you executes SQL text written in form1.oraquery1.SQL. It is empty. Please use oraquery1.SqlUpdate property for update SQL that will be executed during Edit/Post operation. Please see ODAC help for more details about names of parameters in SQLUpdate.

I have reviewed the parameters for sqlupdate and modified my code to
utilize oraquery1.sqlupdate

Simplified the code into 1 procedure

And played around with trying to get parameterized querys to function.
Thinking that may work were the simplier update query doesnt

Also I downloaded and installed the 2.6 version of ora tools but couldnt find the query statement editor tool?

Anyway here is the one routine. Note the section I feel fuzzy about

{******************************************************************************}

procedure TForm1.Button1Click(Sender: TObject);
begin

// Generate The Update Query Programatically
form1.oraquery1.sqlUpdate.Clear;
form1.oraquery1.sqlUpdate.Add('UPDATE AMASSET');
form1.oraquery1.sqlUpdate.Add('SET FIELD1 = ' + chr(39) + '12345' + chr(39));
form1.oraquery1.sqlUpdate.Add('WHERE ASSETTAG = ' + chr(39) + 'YILDIRIM-2-JTXKC11' + chr(39));

// Show Whats Going On (cut and paste to verfiy database connection-operation okay)
form1.memo1.Clear;
form1.memo1.lines := form1.oraquery1.sqlUpdate;

// Connect To The Database
form1.orasession1.server := '10.3.1.146:1521:ACTEST';
form1.orasession1.username := 'acusr';
form1.orasession1.Password := 'nyiso123';
form1.orasession1.Options.Net := true;
if form1.orasession1.connected true then form1.orasession1.Connect;


// THIS IS THE SECTION I NEED SOME HELP WITH

// form1.OraQuery1.Prepare;

// form1.OraQuery1.Active := true;

// first insure the dataset is closed ? why is this needed ?
// form1.OraDataSource1.DataSet.Close;

// form1.OraQuery1.CachedUpdates := true;

form1.oraquery1.Execute;

form1.oraquery1.CommitUpdates;


// Clean Up
form1.orasession1.Disconnect;

end; { Procedure }

{******************************************************************************}


So the sqlupdate is the initial query

the session makes the connection

the datset returns the rows of data

What is the prepare used for

if i use sql plus to make requests for data and

modifications of data how can I do this in ODAC

Frank

PS Thanks again for your very timely help in this matter.

You must write your query to oraquery1.SQL

First I'd like to say thanks for sticking with me on this one.

I dont understand I thought the sqlupdate was used/needed to put the datset into a writable state

So I quickly tried your sugestion. Still no good

In one of the previous fourms I noticed a suggestion to create a fresh
copy of the program so I did this this morning. A few interesting items.

Created a new project

added a orasession componet

added a oraquery componet

added a oradatasource componet

added a button and a memo componet

added OraError to the uses clause and the following code
for the button click event

{******************************************************************************}

procedure TForm1.Button1Click(Sender: TObject);
begin
// Set Up Parameters For Visual Componets
form1.Oradatasource1.DataSet := OraQuery1;

// Next line required to avoid "Not in cached update mode" pop up message
form1.OraQuery1.CachedUpdates := true;
form1.OraQuery1.Session := OraSession1;

// Connect To The Database
form1.orasession1.server := '10.3.3.146:1521:ACPOD';
form1.orasession1.username := 'a56sr';
form1.orasession1.Password := 'nwero123';
form1.orasession1.Options.Net := true;
if form1.orasession1.connected true then
form1.orasession1.Connect;

// Generate The Update Query Programatically
// ( Doesnt Work Memory Acess Violation )
form1.oraquery1.SQL.Clear;
form1.oraquery1.SQL.Add('UPDATE AMASSET');
form1.oraquery1.SQL.Add('SET FIELD1 = ' + chr(39) + '12345' + chr(39));
form1.oraquery1.SQL.Add('WHERE ASSETTAG = ' + chr(39) + 'YILDIRIM-2-JTXKC11' + chr(39));

(*
// Generate The Select Query Programatically ( Works Good )
form1.oraquery1.SQL.Clear;
form1.oraquery1.SQL.Add('SELECT * FROM AMASSET');
form1.oraquery1.SQL.Add('WHERE ASSETTAG = ' + chr(39) + 'YILDIRIM-2-JTXKC11' + chr(39));
*)

// Show User Whats Going To Happen
form1.memo1.Clear;
form1.memo1.lines := form1.oraquery1.SQL;

// Run The SQL Update Code
try
form1.oraquery1.Execute;
except
On E : EOraError do
form1.memo1.Lines.add(inttostr(E.ErrorCode) + ' ' + E.Message);
end; { try except block }

try
form1.oraquery1.CommitUpdates;
except
On E : EOraError do
form1.memo1.Lines.add(inttostr(E.ErrorCode) + ' ' + E.Message);
end; { try except block }

(*
// Show The User The Results For Select Query
form1.memo1.Lines.add('Serial Number : ' + Uppercase(VarToStr(OraDataSource1.DataSet.FieldValues['SERIALNO'])));
form1.memo1.Lines.add('Asset Tag : ' + Uppercase(VarToStr(OraDataSource1.DataSet.FieldValues['FIELD1'])));
*)

// Clean Up
form1.orasession1.Disconnect;

end; { Procedure }

{******************************************************************************}

by swapping the (* *) blocks for the // Generate The Update Query Programatically section I was able to demonstate the ability to read from the database yet not to write to it

{******************************************************************************}

Output From Select Read Test

SELECT * FROM AMASSET
WHERE ASSETTAG = 'YILDIRIM-2-JTXKC11'
Serial Number : JTXKC11
Asset Tag : 12345

{******************************************************************************}

Output From Update Write Test

pop up message
Access violation at address 0046F44E in
module Project1.exe Read of address 00000000

{******************************************************************************}


The ORA-01009 error seems to have disappeared ?

A cut and paste test of the memo componet works
fine in sqlplus if I add a ; at the end press enter

then type commit; and press enter



SQL*Plus: Release 10.1.0.2.0 - Production on Wed Aug 10 10:18:20 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL> UPDATE AMASSET
2 SET FIELD1 = '12345'
3 WHERE ASSETTAG = 'YILDIRIM-2-JTXKC11';

1 row updated.

SQL> commit;

Commit complete.

SQL>


Frank Bishop

Ihave made some process on the problem

One of the oracle DBA'a suggested that the ORA-01009 error
may be a result of trouble with the newline or carrige return statements
between the portions of the query

so

for the read I use Oraquery.SQL

form1.oraquery1.SQL.Add('SELECT * FROM AMASSET');
form1.oraquery1.SQL.Add('WHERE ASSETTAG = ' + chr(39) + 'YILDIRIM-2-JTXKC11' + chr(39));


And for the write Oraquery.SQLUpdate

but in the write I change the query to be a single line

form1.oraquery1.SQL.Add('UPDATE AMASSET SET FIELD1 = ' + chr(39) + '12345' + chr(39) + ' WHERE ASSETTAG = ' + chr(39) + 'YILDIRIM-2-JTXKC11' + chr(39));

This resolved my problem

The issue was the lack of a space before the WHERE in the update query
making '12345'WHERE part of the query

Making the change like this

form1.oraquery1.SQL.Add('UPDATE AMASSET ');
form1.oraquery1.SQL.Add('SET FIELD1 = ' + chr(39) + '12345' + chr(39));
form1.oraquery1.SQL.Add(' WHERE ASSETTAG = ' + chr(39) + 'YILDIRIM-2-JTXKC11' + chr(39));

Doesnt seem to work

so either 1 line
or somthing like this

form1.oraquery1.SQLUpdate.Add(
'UPDATE' + chr(13) + chr(10) +
'AMASSET'+ chr(13) + chr(10) +
'SET FIELD1 = ' + chr(39) + '12345' + chr(39) + chr(13) + chr(10) +
'WHERE ASSETTAG = ' + chr(39) + 'YILDIRIM-2-JTXKC11' + chr(39));

seems to do the trick

Thanks for helping me solve this problem

Kudo's to the ODAC Forum Team

Oracle - You have some work to do on your error messages

PS any thoughts or info on how the OraQuery.SQL.Add or OraQuery.SQLUpdate.Add methods function let me know

I'll check back on the thread in a few days

I have alot of work implementing database writes into my project

Thanks Again

Frank Bishop

Special Thanks To Paul for not giving up

Your example is correct, but form1.oraquery1.CommitUpdates must be used with CachedUpdates=True in pair with ApplyUpdates,RestoreUpdates,CommitUpdates,CancelUpdates and cannot be used in your example. Please see ODAC help. I think you want to write form1.orasession1.Commit. Correct this line and you will receive working example.

Thanks

I noticed that although the code runs without exceptions the database is not being changed

Looks like I have some reading and tweaking to do!

I'll look a bit closer first thing in the morning and get back to you.

initially I changed the

form1.oraquery.commitupdates;

to

form1.session1.commit;

but it didnt appear to take

I need to look closer at the code and

do a few more tests when I can concentrate a bit better.

Stay Tunned to the thread

Fank

Well I seem to be more lost than ever

I tried a number of approches

I know this is not right but just so you can see

{ I connect to the database }

// Build The Query
form1.oraquery1.SQLUpdate.Clear;
form1.oraquery1.SQLUpdate.Add('UPDATE AMASSET SET FIELD1 = ' + chr(39) + form1.edit1.text + chr(39) + ' WHERE ASSETTAG = ' + chr(39) + 'YETTER1' + chr(39));

// Show User Whats Going To Happen
form1.memo1.Clear;
form1.memo1.lines := form1.oraquery1.SQLUpdate;

// Run The SQL Update Code

form1.Oraquery1.Execute;

form1.OraQuery1.CachedUpdates := true;

form1.OraQuery1.ApplyUpdates;

form1.OraQuery1.CommitUpdates;



form1.OraSession1.ApplyUpdates;

form1.OraSession1.Commit;


(* adding this generates an error
try
form1.OraQuery1.DataSource.DataSet.Close;
except
On E : Exception do form1.memo1.Lines.add(E.Message);
end; { try except block }
*)


{ I disconnect fom the database }

The problem is that I can read from the database

but can't write to the database

(if I cut and paste the update SQL code and follow it with a commit in sqlplus It works okay )

Frank

The code below works

Any suggestions on

Cleaning it up

Or Adding more bullet profing

Are appreciated - but after all this

I think we both want to put this thread to bed.


{******************************************************************************}
// Write

procedure TForm1.Button2Click(Sender: TObject);
begin
get_random_string(random_string_value);

// Connect To The Database
form1.orasession1.server := '10.3.1.146:1521:ACTEST';
form1.orasession1.username := 'acusr';
form1.orasession1.Password := 'nyiso123';
form1.orasession1.Options.Net := true;
if form1.orasession1.connected true then form1.orasession1.Connect;

Oraquery1.ReadOnly := false;

// Build The Query
form1.oraquery1.SQLUpdate.Clear;
form1.oraquery1.SQLUpdate.Add('UPDATE AMASSET SET FIELD1 = '
+ chr(39) + random_string_value + chr(39) + ' WHERE ASSETTAG = ' + chr(39)
+ 'YETTER1-8RMSV21' + chr(39));

// Show User Whats Going To Happen
form1.memo1.Clear;
form1.memo1.lines := form1.oraquery1.SQLUpdate;

form1.OraQuery1.CachedUpdates := true;
form1.OraQuery1.Open;
form1.OraQuery1.Edit;

form1.OraSession1.StartTransaction;
try
form1.OraQuery1.ApplyUpdates;
form1.OraSession1.Commit;
except
form1.OraSession1.Rollback;
raise;
end;
form1.OraQuery1.CommitUpdates;


// Clean Up
form1.Orasession1.Disconnect;

end;
{******************************************************************************}