[OutGource] Oracle DBMS support

[OutGource] Oracle DBMS support

Forge Component
Published on 2017-11-14 by RFeitor
8 votes
Published on 2017-11-14 by RFeitor

RFeitor has spotted some compatibility issues when installing OutGource in an environment which uses an Oracle DBMS.

I'm opening this thread to address these issues specifically.

First error arose when installing the application:

Log.Timestamp not valid in Oracle

Turns out we can't have a column nammed "Timestamp". RFeitor suggested renaming it to "LoggedTime". I've chosen to rename it to "UnixTimestamp" because the word "Timestamp" actually comes from the Gource log definition and I really don't want to lose that.

I also had to make an upgrade Action which copies values from the previous column "Timestamp" onto the new column "UnixTimestamp" so users who had previously installed OutGource won't lose data (keeping in mind that renaming a column in OutSystems creates a new one rather than renaming it in the DBMS).

Second error happened due to a single Advanced Query in the Action "Private_Cleanup" containing multiple statements instead of a single one. I've added RFeitor's modification onto the project in which he split each statement into individual Advanced Queries.

So this:

Became this:

After scanning the project, I've found another Action "Cache_DeleteAll" which had the same issue and fixed it in a similar fashion.

Lesson learned: better make a single Adv.Query per statement.

Lastly, something odd was happening in the Management screen.

ORA-00932:  inconsistent datatypes: expected - got CLOB
       at  OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl  connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors,  OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB,  Boolean bFirstIterationDone)
       at  OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String  commandText, OracleParameterCollection paramColl, CommandType commandType,  OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32  longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl  orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution,  OracleParameterCollection& bindByPositionParamColl, Boolean&  bBindParamPresent, Int64& internalInitialLOBFS, OracleException&  exceptionForArrayBindDML, Boolean isDescribeOnly, Boolean isFromEF)
       at  Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery,  Boolean fillRequest, CommandBehavior behavior)
       at  Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior  behavior)
       at  OutSystems.HubEdition.DatabaseProvider.Oracle.ExecutionService.ExecutionService.ExecuteReader(IDbCommand  cmd)
       at #hFb.#CFb.ExecuteReader(String  description, Boolean isApplication, Boolean transformParameters, Boolean  skipLog)
       at  OutSystems.Internal.Db.DatabaseAccessProvider`1.ExecuteQuery[T](Command cmd,  GenericRecordList`1 rl, String description, Boolean transformParameters,  Boolean skipLog)
       at  OutSystems.Internal.Db.DatabaseAccessProvider`1.ExecuteQuery[T](Command cmd,  GenericRecordList`1 rl, String description)
       at  ssOutGource.Flows.FlowManagement.ScrnManagement.FuncssPreparation.datasetGetGourceApps(HeContext  heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64&  outParamCount, String qpstSearch)
       at  ssOutGource.Flows.FlowManagement.ScrnManagement.Preparation(HeContext  heContext)
       at ssOutGource.Flows.FlowManagement.ScrnManagement.Page_Load(Object  sender, EventArgs e)
       at  System.Web.UI.Control.OnLoad(EventArgs e)
       at  System.Web.UI.Control.LoadRecursive()
       at  System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint,  Boolean includeStagesAfterAsyncPoint)

RFeitor fixed it by making an Adv.Query specific for Oracle. I've modified it slightly in order to have a single query compatible with both Oracle and SqlServer. I hope it still works! :-)

Again, thanks a lot RFeitor for your time and for the support!