TOracleConnection + TSQLQuery - How to get RowsAfftected?
lazarus / 16-Databases

----------------------------------------------------------------------
dailton · 2011-04-28 19:08

Hi,

I'm trying to get TSQLQuery.RowsAfftected without success. I have
found someone answer saying that TOracleConnection.RowsAfftected was
not implemented. Is it true?

I modified the source code as someone sugested but I was not able to
compile. Could anyone help?

PS: it's important to know RowsAfftected to decide doing COMMIT or
ROLLBACK...

Thanks in advance

Dailton Menezes

----------------------------------------------------------------------
eny · 2011-04-28 19:17

Quote from: dailton on April 28, 2011, 09:08:53 pmPS: it's important
to know RowsAfftected to decide doing COMMIT or ROLLBACK...Hm, rather
unusual...
It's better to determine the rows that need updating beforehand (via a
SELECT).
And for inserts: Oracle's own error handling will tell you if it's
necessary to do a commit or rollback.

----------------------------------------------------------------------
dailton · 2011-04-28 19:48

Thanks for your reply...

The point is : i'm converting an old application that was written in
Delphi 5/BDE. This is a SQL Browser application where the user can
enter any SQL command and then submit, including DML.

For example: it's possible to submit a delete command and after that,
knowing RowsAffected, decide to do a COMMIT or ROLLBACK.

Someone sugested to modify sqldb.pp and implement
TOracleConnection.RowsAfftected(...) but i was not capable of
compiling...

What do you think about it?

Thanks.

Dailton Menezes

----------------------------------------------------------------------
Lacak2 · 2011-04-29 05:15

Quote from: dailton on April 28, 2011, 09:08:53 pmI have found someone
answer saying that TOracleConnection.RowsAfftected was not
implemented. Is it true?

Yes it is true ;-)
I made attempt to implement it and also reported bug:
http://bugs.freepascal.org/view.php?id=16885

Can you please test it and report any errors here ?
(for me it compiled, when I wrote it ... see bug report, there is now
diff, which you must apply to fcl-
db/src/sqldb/oracle/oracleconnection.pp)

----------------------------------------------------------------------
dailton · 2011-04-29 11:55

Again, thanks for your reply.

Could you teach me step by step how to compile
C:\lazarus\fpc\2.4.2\source\packages\fcl-
db\src\sqldb\oracle\oraconnection.pp. That's my problem...

Thank you in advance.

Dailton Menezes

----------------------------------------------------------------------
dailton · 2011-04-30 01:14

Hello Guys,

It did work...

What was done?

 1) First I changed the source code in
C:\lazarus\fpc\2.4.2\source\packages\fcl-
db\src\SQLdb\oracle\oracleconnection.pp as explained in the article
http://bugs.freepascal.org/view.php?id=16885

 2) Next I compiled the unit oracleconnection.pp by using the IDE
itself (menu Run | Build File)

 3) Next I copied the three files generated (oracleconnection.o,
oracleconnection.ppu and oracleconnection.rst) to the directory
C:\lazarus\fpc\2.4.2\units\i386-win32\fcl-db

 4) To test, I used the following fragmented code :

       OracleConnection1.open;
       SQLTransaction1.StartTransaction;
       SQLQuery1.ExecSQL;

       if SQLQuery1.RowsAffected > 0 then
       begin
          case MessageDlg(format('%d Rows Affected. Commit?
Yes/No',[SQLQuery1.RowsAffected]),
                                   mtConfirmation,[mbYes,mbNo],0) of
            mrNo : SQLTransaction1.Rollback;
            mrYes: SQLTransaction1.Commit;
          end;
       end
       else begin
          MessageDlg('No Rows Affected.',mtInformation,[mbOk],0);
          SQLTransaction1.Commit; // Commit Anyway
       end; 


Regards

Dailton Menezes

----------------------------------------------------------------------
Lacak2 · 2011-05-02 05:26

Thank you Dailton for testing and reporting result

----------------------------------------------------------------------
dailton · 2011-05-03 14:08

Hi Lacak2,

The TOracleConnection.RowsAffected did work but i've got something new
and strange. For the first time if I execute a simple query using the
modified TOracleConnection  (ex: select sysdate from dual)
i´ve got the oracle error:

OracleConnection1: ORA-01062: unable to allocate memory for define
buffer

But if I execute 'select * from any_table' for the first time
everything works fine and the 'select sysdate from dual' begins to
work normally (after that). I'm running Lazarus 0.9.30/Fpc2.4.2 under
windows 7 x32 accessing Oracle 10g through client version 10.

I didn't detect this problem before, because, after compiling the
modified TOracleConnection I run immediately an 'update' to check is
the problem of RowsAffected was solved and, in this way, the problem
do not appear.

So I'm concluding that the patch is not yet 100% safe.

Do you have any idea?

Dailton Menezes

----------------------------------------------------------------------
Lacak2 · 2011-05-04 05:56

Hm, this is strange.

Code which was added by my patch is invoked only when you use (call)
RowsAffected method of TSQLQuery. So if you does not use this method
then other behavior should not be affected.

Can you test your program with original unpatched version of
oracleconnection.pp ?
I expect, that you will get same error.

I do not have Oracle database, so I can not test it.

----------------------------------------------------------------------
dailton · 2011-05-04 14:32

Lacak2,

Thanks for your reply.

1) I returned to the old files : oracleconnection.o,
oracleconnection.ppu and recompiled my project and everything went
fine (rowsafftected stopped working but the strange error disappeared)

2) Maybe the way that I used to recompile the oracleconenction.pp via
IDE may have caused the problem. What do you think? You didn't answer
so I tried by myself.

new info: additionally I'm using crosscompile to WIN64. The modified
oracleconnection.pp worked fine under W2008 X64  (i mean :
rowsaffected ok and no strange error)

Thanks

Dailton Menezes

----------------------------------------------------------------------
Lacak2 · 2011-05-06 06:59

Quote from: dailton on May 04, 2011, 04:32:26 pm1) I returned to the
old files : oracleconnection.o, oracleconnection.ppu and recompiled my
project and everything went fine (rowsafftected stopped working but
the strange error disappeared)
Strange.
So I expect, that your "strange error" appears after you use
RowsAffected ... it is so ? (not before use or not when you do not use
RowsAffected at all) ?

Quote from: dailton on May 04, 2011, 04:32:26 pm2) Maybe the way that
I used to recompile the oracleconenction.pp via IDE may have caused
the problem. What do you think? You didn't answer so I tried by
myself.
I do not think, that this may be reason

Quote from: dailton on May 04, 2011, 04:32:26 pmnew info: additionally
I'm using crosscompile to WIN64. The modified oracleconnection.pp
worked fine under W2008 X64  (i mean : rowsaffected ok and no strange
error)
Hm, I do not know what it means ???
May be that there is some other 32/64 bit related problem ?
Can somebody else comment on this ?

----------------------------------------------------------------------
dailton · 2011-05-06 18:41

Hi,

I did not call the method RowsAffected explicitly but simply ran the
Query.open nor started any transaction. if I run an update for the
very first time, the problem do not appear but if a run a simple
'select sysdate from dual' the error appears. Important: if a run for
the very first time 'select * from mytable' the next 'select sysdate
from dual' will not fail... believe it or not...

Please see exactly the code i'm using :

procedure TfrmSimpleBrowser.menuExecuteClick(Sender: TObject);
var
  Cmd : string;
begin
  try

    // Close the query before changing the sql text

    SQLQuery1.Close;

    // Set SQL text extracting from user's interface (memo)

    if memoSQL.SelLength <> 0 then
       SQLQuery1.SQL.Text := memoSQL.SelText
    else SQLQuery1.SQL.Text := memoSQL.Text;

    // Get Param's if any. Show a form to define the param's

    if SQLQuery1.Params.Count > 0 then begin
      frmQryParams.Query := SQLQuery1;
      if frmQryParams.ShowModal = mrCancel then exit;
    end;

    // Open Oracle DB

    OracleConnection1.open;
    Screen.Cursor := crSQLWait;

    // Check to see if it is SELECT

    Cmd := Trim(Words(trim(SQLQuery1.SQL.Text),1,' '));
    if CompareText(Cmd,'Select')=0 then begin
       // Yes it's a Select so just open it without transaction
       SQLQuery1.Open;
       Screen.Cursor := crDefault;
    end
    else begin
       // It's not a Select (no cursor) so start transaction

       SQLTransaction1.StartTransaction;
       SQLQuery1.ExecSQL;
       Screen.Cursor := crDefault;

       // check if there are rows affected?

       if SQLQuery1.RowsAffected > 0 then begin
          case MessageDlg(format('%d Rows Affected. Commit?
Yes/No',[SQLQuery1.RowsAffected]),
                          mtConfirmation,[mbYes,mbNo],0) of
            mrNo : SQLTransaction1.Rollback;
            mrYes: SQLTransaction1.Commit;
          end;
          end
       else begin
          MessageDlg('No Rows Affected.',mtInformation,[mbOk],0);
          SQLTransaction1.Commit;
       end;
    end;

  except  // trouble on the way...
    On E:Exception do begin
       if SQLTransaction1.Active then
          SQLTransaction1.Rollback;
       Screen.Cursor := crDefault;
       MessageDlg(E.Message,mtError,[mbOk],0);
    end;
  end;
end;  

Thanks

----------------------------------------------------------------------
Lacak2 · 2011-05-09 05:54

Quote from: dailton on May 06, 2011, 08:41:17 pmI did not call the
method RowsAffected explicitly but simply ran the Query.open nor
started any transaction.
ok, then patch (which implements RowsAffected) can not affect "Open"
in any way.
but then I do not understand, why "strange error" disappeared when you
use old oracleconnection.pp ?
Can you do a test, that you remove code which adds patch and recompile
oracleconnection.pp again and test what happens ?

Quote from: dailton on May 06, 2011, 08:41:17 pm if I run an update
for the very first time, the problem do not appear but if a run a
simple 'select sysdate from dual' the error appears.
so you run 'select sysdate from dual' as 1st query and errors appears
just when you run this 1st query? (not later when you run another
update query and RowwsAffected comes into play?)

Quote from: dailton on May 06, 2011, 08:41:17 pmImportant: if a run
for the very first time 'select * from mytable' the next 'select
sysdate from dual' will not fail... believe it or not...
I believe, but without working Oracle database I can not test and
reproduce ... If you can do a small test program which when run shows
this error then you can fill bug report into bug tracker

----------------------------------------------------------------------
dailton · 2011-05-09 20:05

Hi,

I did the test you suggested:

1) I used the original OracleConnection.pp (without patch) and
recompiled via IDE
2) Next I moved OracleConnection.o and OracleConnection.ppu to the
directory C:\lazarus\fpc\2.4.2\units\i386-win32\fcl-db
3) Next I recompiled my project and run and the error raises again. So
we may conclude that the process of recompiling the
OracleConnection.pp (via IDE) may be causing the problem.

Thanks

Dailton Menezes

----------------------------------------------------------------------
Lacak2 · 2011-05-10 09:42

Thanks.
So patch does not causes this error.

----------------------------------------------------------------------
dailton · 2011-05-10 15:09

Lacak2,

Could you or anybody else tell me the correct way of compiling? The
best way, maybe, via make?

Thanks

Dailton Menezes

----------------------------------------------------------------------
Lacak2 · 2011-05-11 07:28

I did it always only via IDE ... sorry I can not serve you in this
case.
But IMH there should not be any difference ...