mORMot Open Source

Hi,
   I try  to implement TsqlRecord descendant which need use some aggregate function, i.e. Get Max(ID). After search the forum, I know I can use ExecuteList to Retrieve the data. But, in this thread http://synopse.info/forum/viewtopic.php?id=1099, ab said,
"More generally, aggregate functions are not available - they are not REST-ready, I suspect. "

The aggregate functions is offen used. Here, I have a idea may resolve this problem simply, but I do'nt know if it's possible to implement it.
The idea is to use view to Retrieve such data, so it mean we need to implement a base class which may call TSQLRecordView. This class can only operate with GET, and have a property to define the view creation.

Now, we may use TSQLRecord to bind some view created in server manually. Here, the problem is how to define view creation automatically.

How about that? Is't possible?

This time, I use mORMot to process some data which charset is Chinese. My Env is Os_Win7_codepage_936/Oracle_11g_charset_AL32UTF8.
I create prop with default codepage param 0, the result is mess character returned. After a few hour of tracing code, finally I change the codepage to cp_utf8 to make my work done. But the problem here still exists.

Follow the code in unit SynDBOracle.pas, when I use default param 0 to create prop, the Class use GetACP to obtain the OS default codepage 936 first. When query data, convert data returned by OCI from codepage 936 to UTF8. So, I use CP_UTF8 to bypass this convertion. From here, I think SynDBOracle may have a wrong concept about codepage and charset.

I have used Oracle since Oracle8. Each time, charset give us a little trouble when starting a new project, because Chinese Charecter is involved mainly. codepage , charset and NLS_LANG make us mad.

To make clear about problem, I'd like to discuss the concept about codepage , charset, which may make things right.

I think, the codepage and NLS_LANG is related to the client tools of Oracle, such as SQL*PLUS, EXP/IMP; but for direct connect, only charset is necessary to concern. Because, when mORMot connect to Oracle DB using OCI, the client means mORMot, the charset used by client  means UTF8. If DB use UTF8, no conversion is needed. If DB use the charset other than UTF8, the conversion is needed. so here mORMot  is nothing related to codepage concept. codepage is related to OS or App.

So, Based on the viewpoint above, CreateWithCodePage should changed to CreateWithCharset; the aCodepage param should change to aCharset. for default charset string, we can query DB to get the charset used by DB, or just set it as 'UTF8' simply.

I do'nt know what descripted here is make things clear, because English isn't my native language.

For Programming with Unicode, oracle have the doc here
http://docs.oracle.com/cd/B28359_01/ser … nicode.htm

By the way, another problem is the error msg returned by OCI display not right, I donot know how mORMot convert this message.

I know all the things discussed here not related to ASCII, because all the charset is superset of ASCII. for the guy not use CJK charset, it is difficult to find such error.

And also codepage/charset list in SynDBOracle may not be right for codepage 936. for codepage, consider following table

Common IANA character set Name      vs.     Oracle character set name
   (Http header & html meta tag)               (NLS_LANG character set part)
 

    UTF-8                                           UTF8
    windows-1250                                EE8MSWIN1250
    windows-1251                                CL8MSWIN1251
    windows-1252                                WE8MSWIN1252
    windows-1253                                EL8MSWIN1253
    windows-1254                                TR8MSWIN1254
    windows-1255                                IW8MSWIN1255
    windows-1256                                AR8MSWIN1256
    windows-1257                                BLT8MSWIN1257
    windows-1258                                VN8MSWIN1258
    windows-936 or GBK                        ZHS16GBK
    Big5                                            ZHT16MSWIN950
    Big5-HKSCS                                  ZHT16HKSCS – Hong Kong extension of big5
    TIS-620                                       TH8TISASCII
    Shift_JIS                                     JA16SJIS
    korean or KS_C_5601-1989             KO16MSWIN949

I just test Orcale DB access. I found AV when db free. I use XE/Ora 11g. Here is a simple code.

var fProps: TSQLDBOracleConnectionProperties;
_sql: RAWUTF8;
Rows: ISQLDBRows;
begin
  fProps:= TSQLDBOracleConnectionProperties.CreateWithCodePage(fOraTNS,
    fOraUser, fOraPass,0);
  fProps.ForcedSchemaName:= fOraUser;
  try
    _sql:='select * from dual';
    Rows:= fProps.Execute(_sql,[],nil) ;
    With Rows Do
    While Step Do
    Begin 
      Memo1.Lines.Add(ColumnString(0));
    End; 
    Rows:=nil;
  finally
    fProps.Free;
  end;

After  fProps.Free ,  AV of OCI in TSQLDBOracleStatement.FreeHandles was fired.

folowing code also have AV problem

  fProps:= TSQLDBOracleConnectionProperties.CreateWithCodePage(fOraTNS,
    fOraUser, fOraPass,0);
  fProps.ForcedSchemaName:= fOraUser;
  try
    _sql:='select * from dual where rownum=?';
    With fProps.Execute(_sql,[1],nil) Do
    While Step Do
    Begin 
      Memo1.Lines.Add(ColumnString(0));
    End; 
  finally
    fProps.Free;
  end;

But the strange thing is  Execute() function, the following code works no problem.

  try
    _sql:='select * from dual where rownum=?';
    Rows:= fProps.Execute(_sql,[1],nil) ;
    With Rows Do
    While Step Do
    Begin 
      Memo1.Lines.Add(ColumnString(0));
    End; 
    Rows:=nil;
  finally
    fProps.Free;
  end;

the param there or empty is differ.

Is there something I missed or a bug?

I downloaded souce (Synopse OpenSource-8195c840e1d682a6)
compile at delphi xe (win7-64bit chinese edition)

some problem found as follows

1.1 Low level common
   -IdemPropName: 1/10 FAILED
   -Soundex: 1/29 FAILED

1.4 Cryptographic routines:
   -Base64: 2/11,994 FAILED

1.6 Synopse PDF
    -TPDFDocumentr: 3/4 FAILED
    -TPdfDocumentGDI: 1/3 FAILED

2.1 Basic classes:
   -TSQLRecord: 4/42 FAILED

2.2 File Based
    Exception ESQLException raised with message unrecognized token "'a??, 1791,1872);"

total assertions failed for all test suits:12/3,097,309

What's the problem about that?