mORMot Open Source

Bsaidus

Member

From: Algeria

Registered: 2018-12-16

Posts: 50

ORacle issue!!

Hello,
I'm trying to experiment a client/server over http with mormot by publishing my oracle DB over Http.
And I have this issue :

20190930 14340552  +    SynDBZeos.TSQLDBZEOSStatement(07898f30).Prepare
20190930 14340552  +    	SynDBZeos.TSQLDBZEOSConnection(07880f18).Connect to oracle ORADEV for  at 0:
20190930 14340558 DB    		Connected to  using ORADEV 11002000
20190930 14340558  +    		SynDBZeos.TSQLDBZEOSStatement(07899060).Prepare
20190930 14340558  -    		00.002.194
20190930 14340558  +    		SynDBZeos.TSQLDBZEOSStatement(07899060).ExecutePrepared
20190930 14340558 SQL   			SynDBZeos.TSQLDBZEOSStatement(07899060) select sysdate from dual
20190930 14340558  -    		00.004.074
20190930 14340558  -    	00.090.270
20190930 14340558  -    00.090.820
20190930 14340558  +    SynDBZeos.TSQLDBZEOSStatement(07898f30).ExecutePrepared
20190930 14340558 SQL   	SynDBZeos.TSQLDBZEOSStatement(07898f30) select sysdate from dual
20190930 14340558  -    00.003.685
20190930 14340559  +    SynDBZeos.TSQLDBZEOSStatement(07899190).Prepare
20190930 14340559  -    00.000.689
20190930 14340559  +    SynDBZeos.TSQLDBZEOSStatement(07899190).ExecutePrepared
20190930 14340559 SQL   	SynDBZeos.TSQLDBZEOSStatement(07899190) select * from employee
20190930 14340559  -    00.003.119
20190930 14340633 EXC   EZSQLException ("SQL Error: OCI_ERROR: ORA-01406: la valeur de la colonne extraite a été tronquée\n") [] at   $0073F281  CHECKORACLEERROR,  line 1146 of C:/GNU/fpc3tr/Ccr/Zeos/src/dbc/ZDbcOracleUtils.pas  $00748A94  TZORACLERESULTSET__NEXT,  line 1616 of C:/GNU/fpc3tr/Ccr/Zeos/src/dbc/ZDbcOracleResultSet.pas  $00600587  TSQLDBZEOSSTATEMENT__STEP,  line 1177 of C:/GNU/fpc3tr/Ccr/mORMot/SynDBZeos.pas  $005F6804  TSQLDBSTATEMENT__FETCHALLTOBINARY,  line 7229 of C:/GNU/fpc3tr/Ccr/mORMot/SynDB.pas  $005ED8C5  TSQLDBCONNECTION__REMOTEPROCESSMESSAGE,  line 4598 of C:/GNU/fpc3tr/Ccr/mORMot/SynDB.pas  $005FC722  TSQLDBSERVERABSTRACT__PROCESS,  line 328 of C:/GNU/fpc3tr/Ccr/mORMot/SynDBRemote.pas  $0062E39E  THTTPSERVERGENERIC__REQUEST,  line 5849 of C:/GNU/fpc3tr/Ccr/mORMot/SynCrtSock.pas  $0063167F  THTTPAPISERVER__EXECUTE,  line 8933 of C:/GNU/fpc3tr/Ccr/mORMot/SynCrtSock.pas  $0043A725  $004130EE  $77386359  $77D77B74  $77D77B44
20190930 14342901  +    SynDBZeos.TSQLDBZEOSStatement(07899190).ExecutePrepared
20190930 14342901 SQL   	SynDBZeos.TSQLDBZEOSStatement(07899190) select * from employee
20190930 14342901  -    00.000.027
20190930 14342949 EXC   EZSQLException ("SQL Error: OCI_ERROR: ORA-01406: la valeur de la colonne extraite a été tronquée\n") [] at   $0073F281  CHECKORACLEERROR,  line 1146 of C:/GNU/fpc3tr/Ccr/Zeos/src/dbc/ZDbcOracleUtils.pas  $00748A94  TZORACLERESULTSET__NEXT,  line 1616 of C:/GNU/fpc3tr/Ccr/Zeos/src/dbc/ZDbcOracleResultSet.pas  $00600587  TSQLDBZEOSSTATEMENT__STEP,  line 1177 of C:/GNU/fpc3tr/Ccr/mORMot/SynDBZeos.pas  $005F6804  TSQLDBSTATEMENT__FETCHALLTOBINARY,  line 7229 of C:/GNU/fpc3tr/Ccr/mORMot/SynDB.pas  $005ED8C5  TSQLDBCONNECTION__REMOTEPROCESSMESSAGE,  line 4598 of C:/GNU/fpc3tr/Ccr/mORMot/SynDB.pas  $005FC722  TSQLDBSERVERABSTRACT__PROCESS,  line 328 of C:/GNU/fpc3tr/Ccr/mORMot/SynDBRemote.pas  $0062E39E  THTTPSERVERGENERIC__REQUEST,  line 5849 of C:/GNU/fpc3tr/Ccr/mORMot/SynCrtSock.pas  $0063167F  THTTPAPISERVER__EXECUTE,  line 8933 of C:/GNU/fpc3tr/Ccr/mORMot/SynCrtSock.pas  $0043A725  $004130EE  $77386359  $77D77B74  $77D77B44

// Client CODE in DELPHI

// Client CODE 

type
  TForm1 = class(TForm)
    Panel1: TPanel;
    Image1: TImage;
    btnOpen: TButton;
    DBGrid1: TDBGrid;
    ds1: TDataSource;
    DBNavigator1: TDBNavigator;
    ZConnection1: TZConnection;
    ZReadOnlyQuery1: TZReadOnlyQuery;
    procedure FormDestroy(Sender: TObject);
    procedure btnOpenClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }

    Props, Props1: TSQLDBConnectionProperties;
    mystmt, mystmt1: TSQLDBStatement;
    Dst: TDataSet;

  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormDestroy(Sender: TObject);
begin
  if ds1.DataSet <> nil then
    ds1.DataSet.Free;
end;

procedure TForm1.btnOpenClick(Sender: TObject);
begin
  //
  if Props<>nil then
    FreeAndNil(Props);

  Props := TSQLDBWinHTTPConnectionProperties.Create('127.0.0.1:8111','fp','user','pass');
  if props = nil then
  begin
    ShowMessage('fuifhiuzezufh');
    Abort;
  end;
  //
  ds1.DataSet.Free; // release previous TDataSet
  ds1.DataSet := ToDataSet(ds1,Props.Execute('select * from employee',[]));

end;

// Server CODE in LAZARUS ( using ZEOS Dbo 7.2.4 stable)

// Server CODE 
unit umain;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls,
  jsonConf,
  //
  mORMot,
  mORMotDB,
  SynCommons,
  SynDB,
  SynDBRemote,
  SynDBZeos,
  //
  SynLog

  ;

type

  TCntInfos = record
    sHost: String;
    sDB: String;
    iPort: Integer;
    sUser: String;
    sPass: String;
    sLib: String;
  end;

  { TForm1 }

  TForm1 = class(TForm)
    btnStart: TButton;
    btnStop: TButton;
    btnClose: TButton;
    Label1: TLabel;
    Memo1: TMemo;
    procedure btnStartClick(Sender: TObject);
    procedure btnStopClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private

  public
    fInfo: TCntInfos;
    fJCnf: TJSONConfig;
    fFileCfg: String;

    // mORMot vars
    fp: TSQLDBZEOSConnectionProperties;
    hts: TSQLDBServerHttpApi; //TSQLDBServerSockets;  //TSQLDBServerHttpApi
    //Server : TSQLHttpServer;
    //

    // gEneral functions .
    function fReadConfig: Boolean;
    function fStartServer: Boolean;
    function fStopServer: Boolean;
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.FormCreate(Sender: TObject);
begin
  fp := nil;
  Hts:= nil;
  fFileCfg := '';

  // Read Config File .
  if not fReadConfig then begin
    ShowMessage('File not Exists: ' + fFileCfg);
    Application.Terminate;
  end;
  with Memo1.Lines do
  begin
    Add(fInfo.sHost);
    Add(fInfo.sDB);
    Add(IntToStr(fInfo.iPort));
    Add(fInfo.sUser);
    Add(fInfo.sPass);
    Add(fInfo.sLib);
  //end;
  end;

  // Start the server direct
  fStartServer;

end;

procedure TForm1.btnStartClick(Sender: TObject);
begin
  fStartServer;
end;

procedure TForm1.btnStopClick(Sender: TObject);
begin
  fStopServer;
end;

function TForm1.fReadConfig: Boolean;
begin
  Result := False;
  fFileCfg := ChangeFileExt(ParamStr(0), '.json');
  if not FileExists( fFileCfg ) then
    Exit;
  fJCnf :=  TJSONConfig.Create(nil);
  try
    fJCnf.Filename := fFileCfg;
    try
      with fInfo do
      begin
        //sHost := fJCnf.GetValue('/dbCnt/server'  , 'localhost');
        sDB   := fJCnf.GetValue('/dbCnt/db'      , 'employee');
        //iPort := fJCnf.GetValue('/dbCnt/port'    , 3050);
        sUser := fJCnf.GetValue('/dbCnt/user'    , 'root');
        sPass := fJCnf.GetValue('/dbCnt/passwd'  , 'thalla');
        sLib  := fJCnf.GetValue('/dbCnt/library' , 'c:\lib.dll');
      end;
    except
      raise;
    end;
  finally
    FreeAndNil(fJCnf);
  end;

  Result := True;
end;

function TForm1.fStartServer: Boolean;
var
  CStr: String;
begin
  //
  //  Try to publish SQLDatabase with mORMotServer
  //  using Http .
  //  Using Http/Json as transport lyer for requests and responses.
  //
  // Free Http Server.
  if Hts <> nil then FreeAndNil(Hts);
  // Free SQL Sever
  if fp <> nil then FreeAndNil(fp) ;
  // ---------------------------------------------------------------------------------------------
  // Create SQL server connection
  // ---------------------------------------------------------------------------------------------
  //fp := TSQLDBZEOSConnectionProperties.Create(
  //            TSQLDBZEOSConnectionProperties.URI(dFirebird,'127.0.0.1:3050','fbclient.dll',false),
  //            'EMPS', 'sysdba', 'masterkey' );
  // This is a Firebird connection .
  //CStr := fInfo.sHost + ':' + IntToString(fInfo.iPort);
  //fp := TSQLDBZEOSConnectionProperties.Create(
  //            TSQLDBZEOSConnectionProperties.URI(dFirebird, CStr, fInfo.sLib, false),
  //            fInfo.sDB,
  //            fInfo.sUser,
  //            fInfo.sPass );

  // This is to do for Oracle.
  fp := TSQLDBZEOSConnectionProperties.Create(
                TSQLDBZEOSConnectionProperties.URI(dOracle,'',fInfo.sLib),
                fInfo.sDB,
                fInfo.sUser,
                fInfo.sPass);

  // ---------------------------------------------------------------------------------------------
  // Create Http Server and publish the SQL server to the Web
  // ---------------------------------------------------------------------------------------------
  if fp <> nil then
    Hts := TSQLDBServerHttpApi.Create(fp,'fp','8111','user','pass');
  // ---------------------------------------------------------------------------------------------
  //

  //
  // logging
  //with TSynLog.Family do begin
  //  Level := LOG_VERBOSE;
  //  //Level := [sllException,sllExceptionOS];
  //  //HighResolutionTimestamp := true;
  //  //AutoFlushTimeOut := 5;
  //  //OnArchive := EventArchiveSynLZ;
  //  //OnArchive := EventArchiveZip;
  //  //ArchiveAfterDays := 1; // archive after one day
  //end;
  //with TSynLogDB.Family do
  //begin
  //Level := LOG_VERBOSE;
  //PerThreadLog := ptOneFilePerThread;
  //DestinationPath := 'C:\Logs';
  //end;
  with TSQLLog.Family do begin
    Level := LOG_VERBOSE;
    EchoToConsole := LOG_VERBOSE; // log all events to the console
  end;

end;

function TForm1.fStopServer: Boolean;
begin
  if fp <> nil then FreeAndNil(fp);
  if Hts<> nil then FreeAndNil(hts);
end;

end.

Last edited by Bsaidus (2019-09-30 15:09:21)

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: ORacle issue!!

You can try with SynDBOracle instead zeos. And please, do not paste huge code blocks here, use gists instead

Bsaidus

Member

From: Algeria

Registered: 2018-12-16

Posts: 50

Re: ORacle issue!!

You can try with SynDBOracle instead zeos. And please, do not paste huge code blocks here, use gists instead

Yes !!