Page 33 of 44
Posted: 11.09.2012, 21:03
by miab3
@olehs, @EgonHugeist
It seems that now the procedures in MySQL work just as well as the FireBird and PostgreSQL.
Zeos7_branches_ testing_r1776
http://svn.code.sf.net/p/zeoslib/code-0 ... s/testing/
compiles and runs on (I have tested):
- D2006,
- D2007 -> C++,
- DXE2 32/64 -> C++32,
- Lazarus Win 1.1/fpc 2.7.1 32/64,
- Lazarus Lin 1.1/fpc 2.7.1 64 on Debian 64-bit (LMDE 201204).
Michal
Posted: 11.09.2012, 22:35
by EgonHugeist
miab3,
It seems that now the procedures in MySQL work just as well as the FireBird and PostgreSQL.
these are good news again, Michal!
Multiple resultsets and cursors also occurs in other servers
MSSQL, PostgreSQL
Seems again like a PostgreSQL job for Oleg. But i think this is a little bit more complicated to get a generic way from RefCoursor to ResultSets. This is also an Oracle issue (also pipelined references).
But like nested tables I would left it for later.
Disagree, Michal. We did broke with the TDataSetField/TNestedDataSets because the FPC doesn't support any way to get an improvement. Delphi does support such field since D6+. Also are no Pointer or Interface-Fields supported on the FPC.
I did the job with the multiple resultsets now. You can fetch them @runtime with these TZStoredProc routines:
Code: Select all
procedure FirstResultSet;
procedure PreviousResultSet;
procedure NextResultSet;
procedure LastResultSet;
But at designtime the IS NO way to do it, Michal. I return allways only the ReturnValue or the In/InOut values of the last call of the stored procedure... Also did i prepare some tests which are running fine...
Posted: 12.09.2012, 08:34
by miab3
@EgonHugeist
Could you add GetResultSet(n) or something similar.
Michal
Posted: 12.09.2012, 10:17
by EgonHugeist
miab3,
I don't undrstand you now. ): What do you want to have?
Posted: 12.09.2012, 10:42
by miab3
@EgonHugeist
I want to be able to load the DataSet ResultSet indicated by the number.
Michal
Posted: 12.09.2012, 11:14
by EgonHugeist
miab3,
Oh yes! Now i understand. Great idea! Patch comes tonight, Michal.
Posted: 12.09.2012, 11:23
by miab3
@EgonHugeist
Maybe something like this:
Code: Select all
uses ... ,math;
...
function TZMySQLCallableStatement.GetResultSet(var i:integer; cur:boolean): IZResultSet;
begin
if FResultSets.Count = 0 then
Result := nil
else
begin
i:= Min(i,FResultSets.Count -1);
if cur then FActiveResultset := i;
Result := IZResultSet(FResultSets[i]);
end;
end;
Michal
Posted: 12.09.2012, 12:09
by EgonHugeist
miab3,
I do understand what you want to do. But i woudn't go this way. That makes the impression you can allways use an index x. I propose to raise an exception E:'List index out of bounds' like all other List objects do it. Force the users to a clean code. What do you think?
Posted: 12.09.2012, 16:38
by olehs
EgonHugeist,
miab3,
Sorry, I was too busy last week and didn't follow last changes.
Michael, I can see you did a great job with multiple result sets, this is really cool!
I've been thinking about overloaded functions we were talking before, and can't see a good way to deal with them. The problem is mostly in Ado driver, because it is a universal driver and has no instruments to work with overloads. We just can't say what kind of database we have on the other side of Ado connection and how it works with overloads.
Seems again like a PostgreSQL job for Oleg. But i think this is a little bit more complicated to get a generic way from RefCoursor to ResultSets. This is also an Oracle issue (also pipelined references).
I actually didn't know about such usage of REFCURSORs in PG. I agree with you - it's not the same as MySQL's m-resultsets. Besides, there are limitations on transaction control when working with RefCursors (they must be fetched before transaction is closed).
Well, need to think about it.
Posted: 12.09.2012, 21:45
by EgonHugeist
miab3,
i've added SetResultSet (naming conflict? I thought we SET another active ResultSet? so i used Set.. instead of Get..) and ResultSetCount: Integer to get an Index behavior.
Hope this is ok for you too. This is actually only supported for MySQL, Michal.
olehs,
Sorry, I was too busy last week
No appologies needed. We do the job for free. Waiting is quiet normal!
I've been thinking about overloaded functions we were talking before, and can't see a good way to deal with them. The problem is mostly in Ado driver, because it is a universal driver and has no instruments to work with overloads. We just can't say what kind of database we have on the other side of Ado connection and how it works with overloads.
Ok, Oleg. So let's think about possible solutions. Just open ZPropertyEditor.pas. There iv'e added some lines to decide if a Catalog/Schema/Objectname is valid for Stored procedures. Connection.DbcConnection.GetMetadata.GetDatabaseInfo.SupportsCatalogsInProcedureCalls
These informations about the Database do exist but i must admit i don't know a line where they are used except the Quoting handling. In my mind should this handling be used also for the TZTable component and a property is missing (which is also depending to sever-versions) for AddEscapeCharToWildcards functions. Some object need to be escape others don't. I know about some threads where people have problems with this escaping of object names. It seems this behavior changes with the Server releases too.
If we can ask the DatabaseInfo for SupportsCatalogs/SchemasInProcedureCalls + (missing) SupportsOverloadsInProcedureCalls + Overload-Delimiter + OverloadDefault then we could simply extract that overload (which should be placed allways at least, right?). Also can we create an overload of SplitQualifiedObjectName to with an overload handling. Or we do avoid the Quoting for such Protocols if the DatabaseInfo says: Nope, please son't do it.
All i want to say is that the DatabasInfo should return the informations we need to have an automation for that.
Bevore going on with such thougth's i would like to hear other proposals...
REFCURSORs
Yes Oleg this is an issue i see on Oracle too. But on Oracle we have also the pipelined data issue, Mark did show me. Here i think do we need either an direct pglib call to return "something" or we have to genereate a select statement. A black new hole for me too. But i was also thinking we can't manage the Multiple resultsets and now it works fine..
So lets sleep a night about it, reading some examples/documentations (Michal, you can help use here too) and see what we can do.
What do you think?
Posted: 13.09.2012, 09:49
by EgonHugeist
olehs,
Just a litte info inbetween:
https://forums.oracle.com/forums/thread ... dID=886365
This is nice Oracle blog about the handling/usage of a refcursor. After reading this i can clearly see what must be done to get this running.. Not an easy job(if we decide to do it). We have to go the same sequence i did it on MySQL.
BUT you can define xRefCursors as Inout/Out/return values. And we have to fetch/cache all Data because the reference will be closed after the first access. So we do run again into the TDataSetField/TNestedDataSet issue.
What do you think about? Should we add a stUnsupported(Readonly stringfield, Value: 'Unsupported Type'), stPointer SQLType which will be set for Compilers who can't handle that? And open the support? I allready had a discussion according such fields with Mark and Ludo.
However it can be done but it can make a lot of trouble also. The best solution is that the user declares his procedure/function/variable statments by him selves with the TZSQLQuery and fetches the data too.
Posted: 13.09.2012, 11:35
by miab3
EgonHugeist, olehs,
Maybe before we take for Oracle improve in MSSQL-ADO problem with the names of procedures:
[dbo].[ABTEST;1]
instead:
[dbo].[ABTEST];1
And that:
Code: Select all
begin
ZStoredProc1.StoredProcName:='[dbo].[ABTEST];1';
ZStoredProc1.Params[1].Value:=50;
ZStoredProc1.Params[2].Value:=100;
ZStoredProc1.Params[3].Value:='abc';
ZStoredProc1.ExecProc;
Edit1.Text:=ZStoredProc1.Params[4].AsString+' '+ZStoredProc1.Params[5].AsString;
end;
Results in:
'600 a'
instead
'600 abcabc'
As for ORACLE,
Here's my patch to the ORACLE adds two new types BINARY_FLOAT and BINARY_DOUBLE:
Code: Select all
--- D:/svn/zeosbtb/src/plain/ZPlainOracleConstants.pas Tue Jul 24 17:18:20 2012
+++ D:/Program Files/Borland/BDS/4.0/Libs/Zeos/src/my/ZPlainOracleConstants.pas Sun Sep 02 12:04:04 2012
@@ -420,6 +420,8 @@
SQLT_RID = 11 ;
SQLT_DAT = 12 ;
SQLT_VBI = 15 ;
+ SQLT_BFLOAT = 21 ;
+ SQLT_BDOUBLE = 22 ;
SQLT_BIN = 23 ;
SQLT_LBI = 24 ;
_SQLT_PLI = 29;
@@ -429,6 +431,8 @@
SQLT_LVB = 95 ;
SQLT_AFC = 96 ;
SQLT_AVC = 97 ;
+ SQLT_IBFLOAT = 100;
+ SQLT_IBDOUBLE = 101;
SQLT_CUR = 102;
SQLT_RDD = 104;
SQLT_LAB = 105;
--- D:/svn/zeosbtb/src/dbc/ZDbcOracleResultSet.pas Sun Sep 09 09:14:10 2012
+++ D:/Program Files/Borland/BDS/4.0/Libs/Zeos/src/my/ZDbcOracleResultSet.pas Sun Sep 09 09:31:48 2012
@@ -262,6 +262,8 @@
CurrentVar.ColType := stLong;
end
end;
+ SQLT_BFLOAT, SQLT_BDOUBLE, SQLT_IBFLOAT, SQLT_IBDOUBLE:
+ CurrentVar.ColType := stDouble;
SQLT_INT, _SQLT_PLI:
CurrentVar.ColType := stInteger;
SQLT_LNG, SQLT_LVC:
--- D:/svn/zeosbtb/src/dbc/ZDbcOracleUtils.pas Thu Aug 09 00:25:53 2012
+++ D:/Program Files/Borland/BDS/4.0/Libs/Zeos/src/my/ZDbcOracleUtils.pas Sun Sep 02 12:04:04 2012
@@ -522,7 +522,7 @@
Result := stString
else if (TypeName = 'NCHAR') or (TypeName = 'NVARCHAR2') then
Result := stString
- else if TypeName = 'FLOAT' then
+ else if (TypeName = 'FLOAT') or (TypeName = 'BINARY_FLOAT') or (TypeName = 'BINARY_DOUBLE') then
Result := stDouble
else if TypeName = 'DATE' then {precission - 1 sec, so Timestamp}
Result := stTimestamp
See if it works well.
Michal
Posted: 13.09.2012, 11:54
by EgonHugeist
miab3,
i mered you patch manually. Better is a *.patch file. The best practice is to overwrite the files in your tortoise folder then click the rootfolder->Create a patch, zip it, attach it. Then i have a file which i can simply apply, Michal.
R1788
Actually i've no ADO test running ): i've still the issue with MSSQL and Sybase too. But i'll have a look tonight.
Posted: 13.09.2012, 12:41
by miab3
EgonHugeist,
Thanks,
Referring to ORACLE BINARY_FLOAT and BINARY_DOUBLE
The binary floating-point numbers support the special values infinity and NaN (not a number).
http://www.oraxcel.com/cgi-bin/yabb2/Ya ... 1231160384
http://docs.oracle.com/cd/B19306_01/ser ... ons177.htm
I do not know whether it can be easily implemented with ZEOS?
Referring to procedures MSSQL.
It would be nice to although the simplest of them work.
It is interesting if the procedures can work in FreeTDS?
Michal
Posted: 13.09.2012, 14:04
by EgonHugeist
miab3,
if commited a patch for the ADO naming issue. R1791 Can you test it?
The trunked return values i haven't testet. -> tonight if i get these Severs running too.
Accordingly the two new type for oracle i must admit i have no idea yet NaN can be handled as NULL. But infinity is a problem! An infinity value can't be handled here, right? The bigest type we have is the stBigDecimal with extendend precision. It might be better to use this type instead of stDouble?