Problem with Oracle Cursors

jvillasantegomez · 2013-11-28 16:31:06

Hi

The implementation of oracle cursors seems to be broken.

I don't know the cause, but when you do Result := cursor.FetchAllAsJSON(true); it returns empty records that are not on my dataset.

I I do a select only, it works great, but with cursors it returns a json with some records empty.

Any help??

jvillasantegomez · 2013-11-29 16:04:47

I'm posting some code to get some context;

Here's a simple table with only one record:

CREATE TABLE MyTable
(
  TEXTO  VARCHAR2(4000 BYTE)
)

Here's the oracle procedure

PROCEDURE DUMMY(
    P_cursor OUT SYS_REFCURSOR
) IS
  BEGIN
    OPEN P_cursor FOR
	SELECT TEXTO FROM MyTable;
END dummy;

And here's the delphi calling code

var
  query: RawUTF8;
  stmt: ISQLDBStatement;
  cursor: ISQLDBRows;
begin
  query :=  'BEGIN SOME_PKG.DUMMY(P_cursor => ?); END;';
  stmt := Props.NewThreadSafeStatementPrepared(query, false);
  stmt.BindCursor(1);
  stmt.ExecutePrepared;
  cursor := stmt.BoundCursor(1);     
  Result := cursor.FetchAllAsJSON(true);
end;

Even with only one record on the table called MyTable, this is what cursor.FetchAllAsJSON(true) is returning:

[
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "" },
  { "TEXTO": "Some texto" }
]

As can be seen, the only record that should be returned was the last one ({"TEXTO": "Some texto"} as it is the only record on the MyTable table, but somehow mormot is returning lots of empty records.

Any help would be much appreciated!!!

jvillasantegomez · 2013-12-02 16:05:25

Is anyone really using mORmot on a production environment??

I (sadly) find it so buggy that i don't think anyone is using it for a real production server with a real database, but i could (hope) be wrong.

ab · 2013-12-02 18:21:38

mORMot works very well with Oracle, with very good stability and amazing performances.
We use it daily on production, with very good feedback from our end user customers.

We use Oracle with mORMot on a production environement, but WITHOUT any cursor.
This "cursor" extensions was never tested on production.
It was clear from the beginning that since we do not need it here, we would try to make an implementation in the code, but "cursor feature" debugging and testing should be up to the end-users.

If you want us to help, do not only describe the symptoms, but try to debug and find out what is wrong.
This is how Open Source works, do not forget it.

jvillasantegomez · 2013-12-02 18:38:09

I really want to help here and i been debugging this when my time permits it, but as i said before, i'm not very good at delphi, just trying to publish some rest services on delphi to work on a javascript client at work.

Anyways, i'll keep trying debuggin it to see if i can find the culprit. In the meantime, if i use ZEOS do you think that it would handle cursors?

For what is worth i'm not blaming anyone here, i just think that if you don't support oracle cursors you just can't call your framework as working with oracle, because it crearly doesn't. The SAD clearle said that oracle is a supported database, but as you can see mormot doesn't support oracle well, cursors, functions, are standard oracle stuff that you simply don't support.

On the other hand, i think that it is very important to know, from a user standpoint, if any lib/framework really works on production... academic discussions are great, but some people just need to get shit done fast. Any framework out there worth would have a sesion on it's documentation pointing out production uses, you can find things like http://builtwithlaravel.com/, http://builtwithbootstrap.com/, http://backbonejs.org/#examples, builtwith.angularjs.org/... etc... This is an important point for deciding if using the framework is worth the time invested learning it, as important for working people as good documentation and good community support.

Being from Cuba i don't really have a chance to work on the open source community, but as you said, it is better to contribute than to blame... I'm really doing my best, i'm posting what would be my own lack of knowledge with the hope that others could help too.

Anyways, great framework...

Last edited by jvillasantegomez (2013-12-02 18:49:56)

ab · 2013-12-03 10:33:34

Thanks for the feedback... from Cuba!

We have to remember that mORMot is about abstraction above the database engine used.
You can switch from one back-end to another, just by changing a class.
This is the magic of ORM.

But it also implies separation of concern.
Putting some business logic in stored procedures is clearly a break of the LOGICAL n-Tier architecture we try to implement with mORMot.
In mORMot design principles, DB are mainly about CRUD process (you can even use a non RDBMS backend, like our in-memory TObjectList storage, or in the close future a NoSQL engine like MongoDB).
This is why we do not use cursors or such on production, and do not have much time nor resource to internally debug it.

Of course, if you have legacy code, you can use existing stored procs and cursors.
In this case, you may have to help debugging our implementation, or use ZEOS or other low-level libraries (like FireDAC) which support Oracle cursors.
But using our SynDBOracle layer for the main Oracle database CRUD access (i.e. all SELECT/INSERT/UPDATE/DELETE statement generated by the ORM or manually written) will always be faster and much more integrated.

jvillasantegomez · 2013-12-03 18:10:34

Ok, this is a hack.

I changed line 2792 of SynDBOracle from:

fRowCount := (fInternalBufferSize - ColCount shl 4) div RowSize;

to

fRowCount := 0;

Now cursors are returning (number of actual records in DB) + 1 and that one empty record i slice it on the client end.
This is a super hack until anyone finds a better solution.

The problem seems to be with the fRowCount variable, since we're returning cursors, but i'm not really sure.
I'm really trying to push mormot forward at work, they won't buy into it (yet) but today i found a time to debug this a little, if anyone finds a better solution please write here...

I'll keep on this
until then... best regards.

Last edited by jvillasantegomez (2013-12-03 18:18:20)

ab · 2013-12-03 19:48:19

Thanks to your feedback, I suspect we found something to fix, without reducing the performance, nor need to "slice the empty record" on the client end.
See http://synopse.info/fossil/info/a509f1da80
and http://synopse.info/fossil/info/0626dd1111

Is it working for you?

jvillasantegomez · 2013-12-03 20:47:35

IT IS WORKIN GREAT NOW!!

Thanks for your commit, I knew that you would do a much better job than me.

//==================================

A question: Any reason why I'm receiving this error when i try to post a new thread?

An error was encountered
Error: Could not connect to smtp host "mail.gandi.net" (110) (Connection timed out).

I found another thing about oracle (it will drive me mad), made some changes and it is working now, i wanted to post the solution (hack until you review it) for future users of the framework.

best regards...

ab · 2013-12-03 21:19:41

There is some obscure DNS error on my server box...

It should work now.

Thanks a lot for the feedback!
I'm happy it is working as expected now.

mpv · 2014-07-31 11:55:13

AB, please - apply my patch about Make TSQLDBOracleStatement tolerant to Oracle warnings described here http://synopse.info/fossil/tktview/e9ca … 3f1a87f910 to SynDBOracle. Actually this is VERY critical bug as for me.....

ab · 2014-07-31 20:10:47

I did forgot about this error.

It should be fixed by now.
See http://synopse.info/fossil/info/5cceb081a

Thanks for the report and patch!

mORMot Open Source

#1 2013-11-28 16:31:06

Problem with Oracle Cursors

#2 2013-11-29 16:04:47

Re: Problem with Oracle Cursors

#3 2013-12-02 16:05:25

Re: Problem with Oracle Cursors

#4 2013-12-02 18:21:38

Re: Problem with Oracle Cursors

#5 2013-12-02 18:38:09

Re: Problem with Oracle Cursors

#6 2013-12-03 10:33:34

Re: Problem with Oracle Cursors

#7 2013-12-03 18:10:34

Re: Problem with Oracle Cursors

#8 2013-12-03 19:48:19

Re: Problem with Oracle Cursors

#9 2013-12-03 20:47:35

Re: Problem with Oracle Cursors

#10 2013-12-03 21:19:41

Re: Problem with Oracle Cursors

#11 2014-07-31 11:55:13

Re: Problem with Oracle Cursors

#12 2014-07-31 20:10:47

Re: Problem with Oracle Cursors

Board footer