Problem with reading data from Oracle database.

ASiwon · 2015-02-13 12:40:21

Hello,

I have problem with reading data from Oracle database. I have following structure:

  TBkWazenieImport = class(TSQLRecord)
  private
    FWagaId: Integer;
    FWagonGranicaObciazenia: Integer;
    FWagonNumer: String;
    FWagonNumerBlad: Boolean;
    FWagonNumerFiltr: String;
    FWagonNumerRodzaj: TBkWagonNumerRodzaj;
    FWagonPobytId: Integer;
    FWagonTaraZBelki: Integer;
    FWagowyDane: String;
    FWazenieCzas: TDateTime;
    FWazenieIdZewnetrzne: Int64;
    FWazenieLadunekNazwa: String;
    FWazenieLadunekNumer: String;
    FWazenieMasaBrutto: Integer;
    FWazenieMasaNetto: Integer;
    FWazeniePosrednie: Boolean;
    FWazenieRodzaj: Byte;
    FWazenieRodzajPotwierdz: Boolean;
    FWazenieSklad: String;
    FWazenieStatus: Integer;
    FWazenieTechnologiczne: Boolean;
    procedure SetWagonNumer(const AValue: String);
  public
    procedure ComputeFieldsBeforeWrite(aRest: TSQLRest; aOccasion: TSQLEvent); override;
  published
    property WagaId: Integer read FWagaId write FWagaId;
    property WagonGranicaObciazenia: Integer read FWagonGranicaObciazenia write FWagonGranicaObciazenia;
    property WagonNumer: String read FWagonNumer write SetWagonNumer;
    property WagonNumerBlad: Boolean read FWagonNumerBlad write FWagonNumerBlad;
    property WagonNumerFiltr: String index 15 read FWagonNumerFiltr;
    property WagonNumerRodzaj: TBkWagonNumerRodzaj read FWagonNumerRodzaj write FWagonNumerRodzaj;
    property WagonPobytId: Integer read FWagonPobytId write FWagonPobytId;
    property WagonTaraZBelki: Integer read FWagonTaraZBelki write FWagonTaraZBelki;
    property WagowyDane: String read FWagowyDane write FWagowyDane;
    property WazenieCzas: TDateTime read FWazenieCzas write FWazenieCzas;
    property WazenieIdZewnetrzne: Int64 read FWazenieIdZewnetrzne write FWazenieIdZewnetrzne;
    property WazenieLadunekNazwa: String index 40 read FWazenieLadunekNazwa write FWazenieLadunekNazwa;
    property WazenieLadunekNumer: String index 6 read FWazenieLadunekNumer write FWazenieLadunekNumer;
    property WazenieMasaBrutto: Integer read FWazenieMasaBrutto write FWazenieMasaBrutto;
    property WazenieMasaNetto: Integer read FWazenieMasaNetto write FWazenieMasaNetto;
    property WazeniePosrednie: Boolean read FWazeniePosrednie write FWazeniePosrednie;
    property WazenieRodzaj: Byte read FWazenieRodzaj write FWazenieRodzaj;
    property WazenieRodzajPotwierdz: Boolean read FWazenieRodzajPotwierdz write FWazenieRodzajPotwierdz;
    property WazenieSklad: String index 5 read FWazenieSklad write FWazenieSklad;
    property WazenieStatus: Integer read FWazenieStatus write FWazenieStatus;
    property WazenieTechnologiczne: Boolean read FWazenieTechnologiczne write FWazenieTechnologiczne;
  end;

this structure is registered to the model in the following way:

  AModel.AddTable(TBkWazenieImport);
  VirtualTableExternalMap(AModel, TBkWazenieImport, AConnectionProp, cTbWagWazeniaImporty).
    MapField('ID', cFdWWIId).
    MapField('WagaId', cFdWWIWagaId).
    MapField('WagonGranicaObciazenia', cFdWWIGranicaObciazenia).
    MapField('WagonNumer', cFdWWINrWagonu).
    MapField('WagonNumerBlad', cFdWWINrWagonuBlad).
    MapField('WagonNumerFiltr', cFdWWINrWagonuFiltr).
    MapField('WagonNumerRodzaj', cFdWWINrWagonuRodz).
    MapField('WagonPobytId', cFdWWIWagonId).
    MapField('WagonTaraZBelki', cFdWWITaraZBelki).
    MapField('WagowyDane', cFdWWIWagowy).
    MapField('WazenieCzas', cFdWWICzasWazenia).
    MapField('WazenieIdZewnetrzne', cFdWWIWazenieZewnId).
    MapField('WazenieLadunekNazwa', cFdWWITowarNazwa).
    MapField('WazenieLadunekNumer', cFdWWITowarNumer).
    MapField('WazenieMasaBrutto', cFdWWIMasaBrutto).
    MapField('WazenieMasaNetto', cFdWWIMasaNetto).
    MapField('WazeniePosrednie', cFdWWIPosrednie).
    MapField('WazenieRodzaj', cFdWWIRodzajWaz).
    MapField('WazenieRodzajPotwierdz', cFdWWIPotwierdz).
    MapField('WazenieSklad', cFdWWISklad).
    MapField('WazenieStatus', cFdWWIStatus).
    MapField('WazenieTechnologiczne', cFdWWITechnolog);

cFd* values are constants with correct field names in the database. Now if I'm trying to read record in the such way:

Result := TBkWazenieImport.Create(ServerStruct.DbClient, 13934)

then everything works fine. But if I'm trying to read record in the such way:

    Result := TBkWazenieImport.Create(ServerStruct.DbClient, 'WagaId = ? AND WazenieIdZewnetrzne = ?',
      [WazenieDane.WagaId, WazenieDane.WazenieId])

then no record is retrieved. Additionaly in the log file the following problem is reported:

20150213 13123735 + TSQLRestServerDB(04DC5650).0066741F
20150213 13124440 + TSQLDBOracleConnection(02EE3130).007650FD
20150213 13124440 - 00.000.002
20150213 13124440 + TSQLDBOracleConnection(02EE3130).00764940
20150213 13124446 SQL TSQLDBOracleStatement(04D67340) SELECT NLS_CHARSET_ID(PROPERTY_VALUE) FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='NLS_CHARACTERSET'
20150213 13124446 DB TSQLDBOracleStatement(04D67340) 1 row(s) in 2.31ms
20150213 13124446 info TSQLDBOracleConnection(02EE3130) Connected to localhost as system with oci.dll rev. 10.2.0.1, codepage 65001 (873/AL32UTF8)
20150213 13124446 SQL TSQLDBOracleStatement(04D67340) ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD-HH24:MI:SS'
20150213 13124446 DB TSQLDBOracleStatement(04D67340) 0 row(s) in 424us
20150213 13124446 SQL TSQLDBOracleStatement(04D67340) alter session set NLS_NUMERIC_CHARACTERS = ". "
20150213 13124446 DB TSQLDBOracleStatement(04D67340) 0 row(s) in 457us
20150213 13124446 - 00.095.292
20150213 13124446 EXC ESQLDBOracle ("TSQLDBOracleStatement error: ORA-00923: FROM keyword not found where expected") at 00763B9C stack trace API 006046C0 006046E8
20150213 13124446 ERROR "ESQLDBOracle(02F98400)":"TSQLDBOracleStatement error: ORA-00923: FROM keyword not found where expected" stack trace API 00606104 0076A20F 0076548E 006B2DE3 006B291C 00770B15 0076FAFD 00663AB1 00662CAB 00667587 00699175 0065E439 0065C5A3 0065859F 0064D041 0076054F 007600DA 00760E0F 00686AF3
20150213 13124446 EXC ESQLDBOracle ("TSQLDBOracleStatement error: ORA-00923: FROM keyword not found where expected") at 00763B9C stack trace API 006046C0 006046E8
20150213 13124446 SQL TSQLDBOracleConnection(02EE3130) select WWI_ID as RowID,WWI_ZDW_ID as WagaId,WWI_GRANICA_OBC as WagonGranicaObciazenia,WWI_NR_WAGONU as WagonNumer,WWI_NR_WAGONU_BLAD as WagonNumerBlad,WWI_NR_WAGONU_FILTR as WagonNumerFiltr,WWI_NR_WAGONU_RODZ as WagonNumerRodzaj,WWI_WE_ID as WagonPobytId,WWI_TARA_Z_BEL as WagonTaraZBelki,WWI_WAGOWY as WagowyDane,WWI_CZAS_WAZENIA as WazenieCzas,WWI_WAZENIE_ID as WazenieIdZewnetrzne,WWI_TOWAR_NAZWA as WazenieLadunekNazwa,WWI_TOWAR_NUMER as WazenieLadunekNumer,WWI_MASA_BRUTTO as WazenieMasaBrutto,WWI_MASA_NETTO as WazenieMasaNetto,WWI_POSREDNIE as WazeniePosrednie,WWI_RODZAJ_WAZ as WazenieRodzaj,WWI_POTWIERDZ as WazenieRodzajPotwierdz,WWI_SKLAD as WazenieSklad,WWI_STATUS as WazenieStatus,WWI_TECHNOLOGICZNY as WazenieTechnologiczne from U_EKS.T_WAGONY_WAZENIA_IMPORTY where rownum<=1 and WWI_ZDW_ID=?OOR? and WWI_WAZENIE_ID=?OOR?
20150213 13124446 DB TSQLDBOracleStatement(04D67240) 0 row(s) in 99.98ms
20150213 13124446 EXC ESQLDBOracle ("TSQLDBOracleStatement error: ORA-00923: FROM keyword not found where expected") at 00763B9C stack trace API 006046C0 006046E8
20150213 13124446 srvr GET root ERROR=400 (Bad Request)
20150213 13124446 - 07.181.810
20150213 13124446 ERROR TSQLRestClientDB(02F75A60) GET root returned 400 Bad Request with message { "ErrorCode":400, "ErrorText":"Bad Request" } stack trace API 00605E0E 0065E5AB 0065C5A3 0065859F 0064D041 0076054F 007600DA 00760E0F 00686AF3

Executed SQL query has correct table and field values, but for first field the word RowId is used. But it is impossible to use the word RowId in the Oracle queries. This word is added to the query in function TSQLRestStorageExternal.AdaptSQLForEngineList - for condition Field=0 is always value 'RowId' used.

And finally is it a bug or I'm doing something wrong?

best regards
Adam Siwon

ab · 2015-02-13 14:46:25

This is a bug when targeting Oracle.

Should be fixed by http://synopse.info/fossil/info/272e1d0937

Thanks for the feedback!

ASiwon · 2015-02-15 20:05:20

I have one question yet. Is it possible to check in code that while query execution some exception occured? When it was a bug in mORMot then after creation of instance of the class TSQLRecord then only empty record was created - exactly like in situation when record was not found. Sometime I don't know record exist or not in the database. But if an error occur I want to just know about it - not only from log files.

ab · 2015-02-16 07:20:21

If the query fails due to wrong parameters, the TSQLRecord.ID is indeed 0, just like if there is no result on a valid query.
But did you try TSQLRestClientURI.LastErrorCode/LastErrorMessage/LastErrorException ?

ASiwon · 2015-12-02 18:28:09

Hi ab!

On the client side LastError properties works perfectly. Now after few months I found the same problem but on the server side. Here I have only pointer to TSQLRestServer class instance. In this class there is no information about errors which occurs while model class instance creation. How can I check information about errors for TSQLRestServer instance on server side?

Last edited by ASiwon (2015-12-02 21:22:59)

mORMot Open Source

#1 2015-02-13 12:40:21

Problem with reading data from Oracle database.

#2 2015-02-13 14:46:25

Re: Problem with reading data from Oracle database.

#3 2015-02-15 20:05:20

Re: Problem with reading data from Oracle database.

#4 2015-02-16 07:20:21

Re: Problem with reading data from Oracle database.

#5 2015-12-02 18:28:09

Re: Problem with reading data from Oracle database.

Board footer