Full Unicode/Ansi-Support in /testing branch

[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

[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...

[miab3]

@EgonHugeist

Could you add GetResultSet(n) or something similar.

Michal

[EgonHugeist]

miab3,

I don't undrstand you now. ): What do you want to have?

[miab3]

@EgonHugeist

I want to be able to load the DataSet ResultSet indicated by the number.

Michal

[EgonHugeist]

miab3,

Oh yes! Now i understand. Great idea! Patch comes tonight, Michal.

[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

[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?

[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.

[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?

[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.

[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

[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.

[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

[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?