[patch_done] TZStoredProc doesn't return any returnValue/set

[olehs]

As I see, there is no FetchOutParams implementation in ZDbcPostgresqlStatement.

Any chance to get at least OutParam values, or even ResultSets?

[EgonHugeist]

This is right ):

As far as i know was it dropped. There must be a thread where a looooooong discussion was made.

The reason i don't know yet but the project-manager told me this in the past.

Michael

[olehs]

Can't find that thread. Was it dropped for good? )

[EgonHugeist]

olehs,

a good question. I thought i'm to blind to find him. It might be possible that this was done on 6.6?

As far as i know was there an bad update-count involved. Which means if UpdateCount <> then Zeoas tryed to get an resultset. But what exactly the issues had been, i can't tell you. I saw a comment on the bugtracker.

Michael

[olehs]

EgonHugeist,

Ok. Then I'll just leave this patch with my FetchOutParams implementation for TZPostgreSQLCallableStatement here.

I used ResultSet to retrieve values of the first row from output. But to make it work I had to remove rtForwardOnly check in TZPostgreSQLResultSet.MoveAbsolute, because it didn't allow me to navigate resultset.

Is this check really necessary?

[olehs]

Well, that's not all. That patch only works for procedures returning single value or set of values.

To make this all work more patches are required. There are few problems with procedures returning record types etc.

[EgonHugeist]

olehs,

will have look to this. And yes there are many issues to expect. Actually i started to check the API definitions to know: YES a resultset is returned. I think we have to check the statement atributes to be sure we have a UpdatCount or a ResultSet. In my mind could that be the only reason why this support was dropped..

Michael

[olehs]

EgonHugeist,

I thought procedures can have both UpdateCount and ResultSet same time, don't they?

[EgonHugeist]

olehs,

And exactly this, i think, was the point why it was removed. What i mean was another thing. If we can ask the driver which kind of statement we have (ResultSet or not) and all engines do support this then we can add this again. I'm sure only in this case, the poject-manager will accept the patches again. I'm not sure but i think before broke the support they only asket for a resultcount which could be either rows or updatecounts...

Michael

[olehs]

EgonHugeist,

I see. But ASA, Interbase and Oracle drivers do fetch out params. May be Project Manager could accept patches to support ONLY out params (w/o resultsets) for PostgreSQL?

[EgonHugeist]

olehs,

I'm sure he won't have any objections IF the proposed way is save. I'm thinkig here on a C-API call like ASE, Oracle, Interbase do support this. If we get a 100% safe result of the statement-handle which says: Hello i'm a Fetched ResultSet! or Here is my UpdateCount! then we would be happy about supporting this again. Trust me on that.

So my thoughts are vague because i don't know the discussion where it has been decided to broke with this support. But i can add 1 +1, know the actual API and here i see this leak or possible issue.

Michael

[olehs]

EgonHugeist,

OK, forget about it.

Here is reason why we use TZStoredProcedure at all.
We use AutoCommit = True.
I need to execute procedure and get it's returnValue.
TZQuery.ExecSQL commits transaction but doesn't return a value.
TZQuery.Open allows to get out data, but doesn't Commit transaction.
TZStoredProcedure.ExecProc commits transaction and should return a value, but it doesn't in PostgreSQL.
Dead-end.

We used a little hack to make TZStoredProcedure return at least a returnValue, but it's a hack anyway(((

The only way to get what we want is to disable 'beginreq' and AutoCommit.
But after we do that lots of code has to be reviewed.

[EgonHugeist]

olehs,

What's that for a hack? (;

Also had i looked to your patch. Some changes are intersting. The changes on ResultSet.MoveAbsout for example. User miab3 tod me we have a speed decrese in readonly mode. There this function is used. Can you explain me why did you change it?

Michael

[olehs]

EgonHugeist,

What's that for a hack? (;

Just something like
OutputParamValues[0] := GetPlainDriver.GetValue(QueryHandle, 0, 0);
in Statement's update method

Can you explain me why did you change it?

Well, CreateResultSet returns NativeResultSet instead of CachedResultSet when Statement has ResultSetConcurrency=rcReadOnly.
But for some reason NativeResultSet doesn't allow to navigate it if it's type is rtForwardOnly, even to fetch one record. I removed that check, because I couldn't change ResultSet's type from that scope (IZResultSet).

Maybe speed decrease is caused by Native instead of Cached?

[EgonHugeist]

olehs,

Maybe speed decrease is caused by Native instead of Cached?

i think this is axactly the point. The cacheds are fast. FireBird seems to be ultrafast.

@EgonHugeist

I will think about the sample test for nested tables in Oracle.

But I have a different issue.
Why limiting FetchRow works well only for Firebird
worse for Oracle
very bad for MySQL, MSSQL and PostgreSQL
and for SQLite does not work at all.
(It's about the speed for small number of first downloaded records from big table.)

Michal

one of the "right hands" we have did point me to this. Ok all database have different behavoir. And i won't have the time to check this posted issues. Do you have an idea quickly what can be the reason here?

I checked the Move/Fetch resultset funtions of FireBird vs. the others and see amazing differences. But i did not want to play here as long i have to time for this)):

Michael