Basic Oracle DB Multi-Tier Project

SHEePYTaGGeRNeP · 2014-02-10 10:26:00

Hello,

I have read some of the SAD Document and searched hours online for solutions and keep getting closer, but it's still not enough.
I do not have a lot of experience of Delphi nor Multi-tier applications.
I started with some DataSnap, but mORMot is a lot better so I had to give that a go.
I am an intern at a Software Engineering company who works with Delphi, but none of them ever worked with mORMot before.

I want to create a simple test project to start working on the real deal, but this is harder than I expected.
I have an Oracle Database and a Server and a Client. I think all the connections work, but the problem I have is that I can't seem to format the data from the database.

On the Server project I have:

    Connection: TSQLDBOracleConnection;
    Model: TSQLModel;
    DataBaseCache: TSQLRestServerDB;
    Server: TSQLite3HttpServer;

and when I click the Test button:

procedure TFormServer.Test(const aName: RawUTF8);
var
  Rows: ISQLDBRows;
  User: Variant;
  JSONBuffer: RawUTF8;
  Statement: TSQLDBOracleStatement;
  SQLTable: TSQLTableJSON;
  Tables: TRawUTF8DynArray;
begin
  Rows := Connection.Properties.Execute('select * from USERS where USERNAME<>?',
    [aName], @User);
  Connection.Properties.GetTableNames(Tables);
  JSONBuffer := Rows.FetchAllAsJSON(true);
  Writeln(UTF8ToString(Rows.FetchAllAsJSON(true)));

I didn't clean up the variables yet, because I tried a whole lot of things such as the ORM approach.

The issue I have is the VARCHAR2 data types in the Oracle Database gave errors, so I somewhat
fixed it by adding 2 lines of code in the SynDBOracle.pas:

            if (ColumnType = ftUTF8) then
              ColumnValueDBForm := SQLCS_NCHAR;

The problem I have now is that the "CLOB" Datatype is giving me this error:
ORA-24806: Incorrect LOB-form.
in the statement:

JSONBuffer := Rows.FetchAllAsJSON(true);

I have no clue how far I messed things up or fixed them.

Help would be much appreciated.

Lorenzo

Last edited by SHEePYTaGGeRNeP (2014-02-10 10:27:56)

ab · 2014-02-10 10:49:32

Are you using the latest 1.18 "unstable" version?

Could you please be a little more specific, and state where you added your 2 lines of code in SynDBOracle.pas?

We use the SynDBOracle.pas unit in production with an Oracle 11.2 database, without any problem about VARCHAR2 fields.
The ORM of mORMot will create NVARCHAR2 kind of columns, but we also have a huge existing DB, using VARCHAR2 columns everywhere, without any issue.

SHEePYTaGGeRNeP · 2014-02-10 10:57:56

I put the 2 lines of code in:

procedure TSQLDBOracleStatement.Prepare(const aSQL: RawUTF8;
  ExpectResults: boolean);

begin
            Check(AttrGet(oHandle, OCI_DTYPE_PARAM, @ColumnValueDBForm, nil,
              OCI_ATTR_CHARSET_FORM, fError), fError);
            Check(AttrGet(oHandle, OCI_DTYPE_PARAM, @ColumnValueDBCharSet, nil,
              OCI_ATTR_CHARSET_ID, fError), fError);
            // Columnvaluedbform := SQLCS_NCHAR;
            if (ColumnType = ftUTF8) then
              ColumnValueDBForm := SQLCS_NCHAR;
            case ColumnValueDBForm of
              SQLCS_IMPLICIT:

ab wrote:

Are you using the latest 1.18 "unstable" version?

I have 1.17 on SynDBOracle, but I updated them manually a couple of times.

EDIT: 12:11 Downloading the Snapshot at the moment.
EDIT: 12:20 Errors everywhere
[dcc32 Error] SQLite3Commons.pas(2313): E2037 Declaration of 'WriteObject' differs from previous declaration
[dcc32 Error] SQLite3Commons.pas(3290): E2003 Undeclared identifier: 'TVarDataDynArray'
[dcc32 Error] SQLite3Commons.pas(5314): E2003 Undeclared identifier: 'Iso8601'
[dcc32 Error] SQLite3Commons.pas(6912): E2005 'TVarDataDynArray' is not a type identifier
[dcc32 Error] SQLite3Commons.pas(7084): E2005 'TVarDataDynArray' is not a type identifier
[dcc32 Error] SQLite3Commons.pas(8107): E2005 'TVarDataDynArray' is not a type identifier
[dcc32 Error] SQLite3Commons.pas(8115): E2005 'TVarDataDynArray' is not a type identifier
[dcc32 Error] SQLite3Commons.pas(8265): E2005 'TVarDataDynArray' is not a type identifier
[dcc32 Error] SQLite3Commons.pas(8274): E2005 'TVarDataDynArray' is not a type identifier
[dcc32 Error] SQLite3Commons.pas(8648): E2137 Method 'AddTyped' not found in base class

Last edited by SHEePYTaGGeRNeP (2014-02-10 11:21:28)

ab · 2014-02-10 12:24:14

You did not update all the units.
Please stay all mORMot units in synch!

The safest (and easiest) is to download http://synopse.info/files/mORMotNightlyBuild.zip

SHEePYTaGGeRNeP · 2014-02-10 12:50:51

Hello,

I finished downloading it and trying to compile the TestSQL3 project, but I get the error:
[dcc32 Error] Classes.pas(1576): E2033 Types of actual and formal var parameters must be identical

constructor TThread.Create(CreateSuspended: Boolean);
begin
  IsMultiThread := true; // for FastMM4 locking, e.g.
  inherited Create;
  FSuspended := CreateSuspended;
  FCreateSuspended := CreateSuspended;
  FHandle := BeginThread(nil, 0, @ThreadProc, Pointer(Self), CREATE_SUSPENDED, FThreadID);        <-- This line
  if FHandle = 0 then
    raise Exception.Create(SysErrorMessage(GetLastError));
  SetThreadPriority(FHandle, THREAD_PRIORITY_NORMAL);
end;

What did I miss?

ab · 2014-02-10 14:45:21

Which version of Delphi are you using?
We just checked with Delphi 6, Delphi 7, Delphi XE3 and Delphi XE4 (the latest for both Win32 and Win64 platforms).

Did you define the LVCL folder and conditional?

SHEePYTaGGeRNeP · 2014-02-10 14:49:53

Thx for the reply Ab,

I use Delphi XE5.
I added the LVCL folder to the Tools - Options - Library Directories.

I don´t think System.Pas changed has it :?

function BeginThread(SecurityAttributes: Pointer; StackSize: LongWord;
  ThreadFunc: TThreadFunc; Parameter: Pointer; CreationFlags: LongWord;
  var ThreadId: TThreadID): THandle;
var
  P: PThreadRec;
begin
  if Assigned(SystemThreadFuncProc) then
    P := PThreadRec(SystemThreadFuncProc(ThreadFunc, Parameter))
  else
  begin
    New(P);
    P.Func := ThreadFunc;
    P.Parameter := Parameter;
  end;

  IsMultiThread := TRUE;

  Result := CreateThread(SecurityAttributes, StackSize, @ThreadWrapper, P,
    CreationFlags, ThreadID);

  { P variable is supposed to be freed by the ThreadWrapper routine.
    If the call to CreateThread fails, then ThreadWrapper will not be called
    and P will not get freed. Check for failure now and free P if required.
  }
  if Result = 0 then
    Dispose(P);
end;

Last edited by SHEePYTaGGeRNeP (2014-02-10 14:54:23)

ab · 2014-02-10 21:38:40

LVCL is an option for Delphi 7 or 2007 only.
It does not mean anything to use it with newer versions, which creates huge executables.

With Delphi XE5, please do NOT add LVCL to your folders, nor define the LVCL conditional.

SHEePYTaGGeRNeP · 2014-02-11 08:27:04

I removed them, but now I cannot compile TestSQL3:
[dcc32 Error] SynSQlite3Static.pas(227): E1026 File not found: 'sqlite3fts3.obj'
[dcc32 Error] SynSQlite3Static.pas(1029): E2065 Unsatisfied forward or external declaration: 'sqlite3_close'
[dcc32 Error] SynSQlite3Static.pas(1168): E2065 Unsatisfied forward or external declaration: 'sqlite3_initialize' and a lot more.

Do I just copy sqlite3fts3.obj from the LVCL folder or what is the deal?

ab · 2014-02-11 08:42:38

Your local setup is just broken.
Please remove all your files and follow instructions either in the Readme.txt file, or in the SAD 1.18 pdf.

See http://synopse.info/fossil/wiki?name=Get+the+source
Get the source from http://synopse.info/files/mORMotNightlyBuild.zip
Get the .obj from http://synopse.info/files/sqlite3obj.7z

SHEePYTaGGeRNeP · 2014-02-11 08:59:41

I just forgot to copy the sqlite3.obj files
I'll let you know it everything is working.

SHEePYTaGGeRNeP · 2014-02-13 10:02:43

Hey Ab, so I created a new project, very basic.
I want to retreive data from the Oracle Database from within the Server.

I followed one of the example from project 4 and some others, but it gives me an error.

program ServerProject;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  System.SysUtils,
  mORMot,
  mORMotSQLite3,
  SynDBOracle,
  SynDB,
  UserDataUnit in 'UserDataUnit.pas';

var
  Model: TSQLModel;
  Connection: TSQLDBOracleConnection;
  Rows: ISQLDBRows;
  Result: UTF8String;
  User: Variant;

begin
  try
    Connection := TSQLDBOracleConnection.Create
      (TSQLDBOracleConnectionProperties.Create('SQL', 'devdb', 'dev', 'mmp'));
    Writeln('Connected to Oracle Database.');
    Connection.Properties.ThreadSafeConnection;
    Model := CreateSampleModel;
    try
      Rows := Connection.Properties.Execute
        ('SELECT * FROM USERS WHERE USERNAME<>?', ['PCMASTERRACE'], @User);
      Result := Rows.FetchAllAsJSON(false);
    finally
      Model.Free;
    end;
    { TODO -oUser -cConsole Main : Insert code here }

  finally
    Connection.Free;
  end;

end.

The problem is I get the message:
Unhandled type for property: USERNAME, which is a VARCHAR2 in the Oracle Database.
When it reaches

aModel := CreateSampleModel;

The screenshot is of an old project, but same UserDataUnit.
http://i.imgur.com/4eiDRKh.png

This is located in the "UserDataUnit.pas"

function CreateSampleModel: TSQLModel;
begin
  result := TSQLModel.Create([TSQLUserRecord]);
end;

Help ?

Last edited by SHEePYTaGGeRNeP (2014-02-13 10:03:28)

ab · 2014-02-13 10:37:54

You are mixing ORM and SQL in the same code.
All this is a bit messy.

Ensure you read the "6.1. ORM is not Database" paragraph in the SAD 1.18 pdf.

What is your TSQLUserRecord type definition?
You should set the "index ... " to in the TSQLUserRecord type definition for VARCHAR2 fields, otherwise it will expect a CLOB content.

SHEePYTaGGeRNeP · 2014-02-13 11:00:36

ab wrote:

You are mixing ORM and SQL in the same code.
All this is a bit messy.

I only need to move data from the database that is not locally on the PC to the client via a server, so how do I do this?
I probably don't need the UserDataUnit class then, I guess?

There is only the sample "External DB perfomance" which has loads of code I don't need?

ab wrote:

What is your TSQLUserRecord type definition?
You should set the "index ... " to in the TSQLUserRecord type definition for VARCHAR2 fields, otherwise it will expect a CLOB content.

You mean this one?

  TSQLUserRecord = class(TSQLRecord)

What do you mean by set the index?

I'm sorry for knowing so little, have never worked wit Delphi, nor N-Tier architecture.

Looking forward to your answer.

Last edited by SHEePYTaGGeRNeP (2014-02-13 11:07:26)

ab · 2014-02-13 13:16:15

Please read "9.3. ORM Integration" paragraph in the SAD 1.18 pdf.

What is your FULL definition of TSQLUSerRecord?

SHEePYTaGGeRNeP · 2014-02-13 13:28:23

unit UserDataUnit;
/// it's a good practice to put all data definition into a stand-alone unit
// - this unit will be shared between client and server

interface

uses
  mORMot;

type
  /// here we declare the class containing the data
  // - it just has to inherits from TSQLRecord, and the published
  // properties will be used for the ORM (and all SQL creation)
  // - the beginning of the class name must be 'TSQL' for proper table naming
  // in client/server environnment


  // EDIT FILTERSETTINGS = UTF8STRING

  TSQLUserRecord = class(TSQLRecord)
  private
    // NUMBER
    fRECEIVERID, fAPPLICATIONS, fUSERRIGHTS, fUSERFUNCTIONS, fDEFAULTMEDIAID,
      fSTARTMEDIAID, fLEVELSMEDIAMENU, fSTARTINCOMINGID, fLEVELSINCOMINGMENU,
      fSTARTDOCUMENTID, fLEVELSDOCUMENTMENU, fSTARTCATEGORYID,
      fLEVELSCATEGORYMENU, fSTARTPROGRAMID, fSTARTPROGRAMMENU,
      fLEVELSPROGRAMMENU, fLEVELSCONTACTMENU: Integer;
    // VARCHAR2(60 CHAR)
    fUSERNAME, fUPASSWORD: UTF8String;
    // NUMBER
    fCAPACITY_MA, fCAPACITY_DI, fCAPACITY_WO, fCAPACITY_DO, fCAPACITY_VR,
      fCAPACITY_ZA, fCAPACITY_ZO, fDEFAULTPOOL, fPRIVATEPOOL, fALLOWEDMEDIA,
      fISACTIVE: Integer;
    // Date
    fMODIFIED: TDate;
    // NUMBER(1,0)
    fDELETED: Integer;
    // NUMBER
    fDEFFOLDERID: Integer;
    // VARCHAR2(60 CHAR)
    fEMAILADDRESS: UTF8String;
    // VARCHAR2(120 CHAR)
    fHOMEPAGE: UTF8String;
    // VARCHAR2(255 CHAR)
    fCONNECTMESSAGE: UTF8String;
    // NUMBER
    fDEFLOCATIONID, fUSEROPTIONS: Integer;
    // NUMBER(38,0)
    fRECEIVERRIGHTS, fMESSAGERIGHTS, fFILERIGHTS, fTASKRIGHTS: Integer;
    // CLOB
    fFILTERSETTINGS: UTF8String;
    // NUMBER
    fDELETEDBY, fMESSAGELIMIT: Integer;
    // NUMBER(38,0)
    fEXPORT_SELECT: Integer;
    // TIMESTAMP (6)
    fOUTLOOK2CS, fCS2OUTLOOK: TTime;
    // NUMBER(1,0)
    fSHOWAGENDAREJECT, fNOTIFYTASKCREATOR: Integer;
    // DATE
    fLASTPASSWORDCHANGE: TDate;
    // VARCHAR2(60 CHAR)
    fMD5PASSWORD: UTF8String;
    // VARCHAR(30 CHAR)
    fPINCODE: UTF8String;
    // VARCHAR(60 CHAR)
    fCOOKIE: UTF8String;
  published
    property RECEIVERID: Integer read fRECEIVERID write fRECEIVERID;
    property APPLICATIONS: Integer read fAPPLICATIONS write fAPPLICATIONS;
    property USERRIGHTS: Integer read fUSERRIGHTS write fUSERRIGHTS;
    property USERFUNCTIONS: Integer read fDEFAULTMEDIAID write fDEFAULTMEDIAID;
    property STARTMEDIAID: Integer read fSTARTMEDIAID write fSTARTMEDIAID;
    property LEVELSMEDIAMENU: Integer read fLEVELSMEDIAMENU
      write fLEVELSMEDIAMENU;
    property STARTINCOMINGID: Integer read fSTARTINCOMINGID
      write fSTARTINCOMINGID;
    property LEVELSINCOMINGMENU: Integer read fLEVELSINCOMINGMENU
      write fLEVELSINCOMINGMENU;
    property STARTDOCUMENTID: Integer read fSTARTDOCUMENTID
      write fSTARTDOCUMENTID;
    property LEVELSDOCUMENTMENU: Integer read fLEVELSDOCUMENTMENU
      write fLEVELSDOCUMENTMENU;
    property STARTCATEGORYID: Integer read fSTARTCATEGORYID
      write fSTARTCATEGORYID;
    property LEVELSCATEGORYMENU: Integer read fLEVELSCATEGORYMENU
      write fLEVELSCATEGORYMENU;
    property STARTPROGRAMID: Integer read fSTARTPROGRAMID write fSTARTPROGRAMID;
    property STARTPROGRAMMENU: Integer read fSTARTPROGRAMMENU
      write fSTARTPROGRAMMENU;
    property LEVELSPROGRAMMENU: Integer read fLEVELSPROGRAMMENU
      write fLEVELSPROGRAMMENU;
    property LEVELSCONTACTMENU: Integer read fLEVELSCONTACTMENU
      write fLEVELSCONTACTMENU;

    property USERNAME: UTF8String read fUSERNAME write fUSERNAME;
    property UPASSWORD: UTF8String read fUPASSWORD write fUPASSWORD;

    property CAPACITY_MA: Integer read fCAPACITY_MA write fCAPACITY_MA;
    property CAPACITY_DI: Integer read fCAPACITY_DI write fCAPACITY_DI;
    property CAPACITY_WO: Integer read fCAPACITY_WO write fCAPACITY_WO;
    property CAPACITY_DO: Integer read fCAPACITY_DO write fCAPACITY_DO;
    property CAPACITY_VR: Integer read fCAPACITY_VR write fCAPACITY_VR;
    property CAPACITY_ZA: Integer read fCAPACITY_ZA write fCAPACITY_ZA;
    property CAPACITY_ZO: Integer read fCAPACITY_ZO write fCAPACITY_ZO;
    property DEFAULTPOOL: Integer read fDEFAULTPOOL write fDEFAULTPOOL;
    property PRIVATEPOOL: Integer read fPRIVATEPOOL write fPRIVATEPOOL;
    property ALLOWEDMEDIA: Integer read fALLOWEDMEDIA write fALLOWEDMEDIA;
    property ISACTIVE: Integer read fISACTIVE write fISACTIVE;

    property MODIFIED: TDate read fMODIFIED write fMODIFIED;
    property DELETED: Integer read fDELETED write fDELETED;
    property DEFFOLDERID: Integer read fDEFFOLDERID write fDEFFOLDERID;
    property EMAILADDRESS: UTF8String read fEMAILADDRESS write fEMAILADDRESS;
    property HOMEPAGE: UTF8String read fHOMEPAGE write fHOMEPAGE;
    property CONNECTMESSAGE: UTF8String read fCONNECTMESSAGE
      write fCONNECTMESSAGE;

    property DEFLOCATIONID: Integer read fDEFLOCATIONID write fDEFLOCATIONID;
    property USEROPTIONS: Integer read fUSEROPTIONS write fUSEROPTIONS;

    property RECEIVERRIGHTS: Integer read fRECEIVERRIGHTS write fRECEIVERRIGHTS;
    property MESSAGERIGHTS: Integer read fMESSAGERIGHTS write fMESSAGERIGHTS;
    property FILERIGHTS: Integer read fFILERIGHTS write fFILERIGHTS;
    property TASKRIGHTS: Integer read fTASKRIGHTS write fTASKRIGHTS;

    property FILTERSETTINGS: UTF8String read fFILTERSETTINGS
      write fFILTERSETTINGS;
    property DELETEDBY: Integer read fDELETEDBY write fDELETEDBY;
    property MESSAGELIMIT: Integer read fMESSAGELIMIT write fMESSAGELIMIT;
    property EXPORT_SELECT: Integer read fEXPORT_SELECT write fEXPORT_SELECT;

    property OUTLOOK2CS: TTime read fOUTLOOK2CS write fOUTLOOK2CS;
    property CS2OUTLOOK: TTime read fCS2OUTLOOK write fCS2OUTLOOK;

    property SHOWAGENDAREJECT: Integer read fSHOWAGENDAREJECT
      write fSHOWAGENDAREJECT;
    property NOTIFYTASKCREATOR: Integer read fNOTIFYTASKCREATOR
      write fNOTIFYTASKCREATOR;
    PRoperty LASTPASSWORDCHANGE: TDate read fLASTPASSWORDCHANGE
      write fLASTPASSWORDCHANGE;
    property MD5PASSWORD: UTF8String read fMD5PASSWORD write fMD5PASSWORD;
    property PINCODE: UTF8String read fPINCODE write fPINCODE;
    property COOKIE: UTF8String read fCOOKIE write fCOOKIE;

  end;

  /// an easy way to create a database model for client and server
function CreateSampleModel: TSQLModel;

implementation

function CreateSampleModel: TSQLModel;
begin
  result := TSQLModel.Create([TSQLUserRecord]);
end;

end.

ab · 2014-02-13 13:43:11

Some issues, which are clearly stated in the PDF doc:

1. You have to specify that the string property has a maximum length, to be recognized as VARCHAR2 with Oracle.

    property USERNAME: UTF8String index 60 read fUSERNAME write fUSERNAME;
    property UPASSWORD: UTF8String index 60 read fUPASSWORD write fUPASSWORD;

2. UTF8String is not the right type.
Please use RawUTF8 type instead.

SHEePYTaGGeRNeP · 2014-02-13 13:54:31

Yes, it worked.

Love you.

mORMot Open Source

#1 2014-02-10 10:26:00

Basic Oracle DB Multi-Tier Project

#2 2014-02-10 10:49:32

Re: Basic Oracle DB Multi-Tier Project

#3 2014-02-10 10:57:56

Re: Basic Oracle DB Multi-Tier Project

#4 2014-02-10 12:24:14

Re: Basic Oracle DB Multi-Tier Project

#5 2014-02-10 12:50:51

Re: Basic Oracle DB Multi-Tier Project

#6 2014-02-10 14:45:21

Re: Basic Oracle DB Multi-Tier Project

#7 2014-02-10 14:49:53

Re: Basic Oracle DB Multi-Tier Project

#8 2014-02-10 21:38:40

Re: Basic Oracle DB Multi-Tier Project

#9 2014-02-11 08:27:04

Re: Basic Oracle DB Multi-Tier Project

#10 2014-02-11 08:42:38

Re: Basic Oracle DB Multi-Tier Project

#11 2014-02-11 08:59:41

Re: Basic Oracle DB Multi-Tier Project

#12 2014-02-13 10:02:43

Re: Basic Oracle DB Multi-Tier Project

#13 2014-02-13 10:37:54

Re: Basic Oracle DB Multi-Tier Project

#14 2014-02-13 11:00:36

Re: Basic Oracle DB Multi-Tier Project

#15 2014-02-13 13:16:15

Re: Basic Oracle DB Multi-Tier Project

#16 2014-02-13 13:28:23

Re: Basic Oracle DB Multi-Tier Project

#17 2014-02-13 13:43:11

Re: Basic Oracle DB Multi-Tier Project

#18 2014-02-13 13:54:31

Re: Basic Oracle DB Multi-Tier Project

Board footer