[Data Extractor] Is the Data Extractor supported on an Oracle Stack

Forge Component
(10)
Published on 16 Jan by Carlos Sousa
10 votes
Published on 16 Jan by Carlos Sousa

I am trying to use the Data Extractor from the forge. We are running on P11. Thats seems not to work. We are using Oracle. Has anyone used the Data Extractor on P11 in combination with a .NET/Oracle Stack and had positive results ?

Hi Ronnie,

Can you show us more details like log message, prints...?

I am still trying and get my grip on it.

At the moment I was able to export a static entity and an entity without foreign keys. So at least simple tables seem to work on Oracle/.NET/P11

Importing multiple tables that have no relation with each other in 1 snapshot work too. If however I try this with multiple tables that have relations with each other then I get an error message telling me "Error extracting Table Data. Specified cast is not valid.". It's one table with foreign keys to 3 static tables and a foreign key to its own (table A with child records can have as parent 1 table A record. Recursion).

It's a large table with many attributes of the following types

Long Integer, DateTime, Text, Currency, Decimal, 4 foreign key identifiers, Boolean, Date.

It has protect rules on all 4 foreign key identifiers

Trying to import only the table A without the tables where it has foreign keys to also fails. with the same error.

On environment Oracle/.NET/P11

Trying another table B with foreign keys however succeeds.

Data types in this table are 

Long Integer, Text, 3 foreign key identifiers but no recursion, Date, Text, DateTime, Long Integer. 2 FK's having a protect rule and 1 FK having a delete rule.

Trying another table B with 2 of the related tables also succeed. Table B is has however also a FK to table A fails again.

Removing the FK from table A that is pointing to itself (Recursion) is not solving the problem. It still fails. So the recursion might not be the problem.




I get the following logging for a simple table C (with some FK's) 

General:

16-10-2019 14:58:14(System)
1OMLProcessor.ExternalAuthentication_ValidateUserCredentials took 469 msSLOWEXTENSION
16-10-2019 14:58:14DataExtractor
(Data Extractor)
20OS: OutSystems.NssDataExtractor_Ext.Schemas.SchemaOracle.GetIndexesStatements took 797 msSLOWSQL
16-10-2019 14:58:13DataExtractor
(Data Extractor)
20OS: OutSystems.NssDataExtractor_Ext.Schemas.SchemaOracle.GetIndexesStatements took 797 msSLOWSQL
16-10-2019 14:58:12DataExtractor
(Data Extractor)
20OS: OutSystems.NssDataExtractor_Ext.Schemas.SchemaOracle.GetIndexesStatements took 859 msSLOWSQL
16-10-2019 14:58:11DataExtractor
(Data Extractor)
20OS: OutSystems.NssDataExtractor_Ext.Schemas.SchemaOracle.GetIndexesStatements took 829 msSLOWSQL
16-10-2019 14:58:11DataExtractor
(Data Extractor)
20DataExtractor_Ext.DataExtractor_Extract took 3561 msSLOWEXTENSION
16-10-2019 14:57:46DataExtractor
(Data Extractor)
20Query RichWidgets.ApplicationSwitcher.Preparation.GetUserApplications.List took 750 ms


Error:


Error Detail
Back to Log
Id:
8bbe060a-ef40-41a9-9e63-b760008bad71
Time of Log:
16-10-2019 14:58:14
Request Key:53ad47d9-6f60-4485-94d0-07058de6ec0f
eSpace:
DataExtractor
Tenant:
Users
User:

Session Id:
No session
Server:
VTL1A202
Module:

Message:


Error extracting Table Data. Specified cast is not valid.


Environment InformationeSpaceVer: 0 (Id=27125, PubId=0, CompiledWith=11.0.212.0)
RequestUrl: http://127.0.0.1/DataExtractor/_TimerHandler.asmx (Method: POST)
AppDomain: /LM/W3SVC/1/ROOT/DataExtractor-614-132156854838552456
FilePath: C:\...\PS\running\DataExtractor.2110902764\
ClientIp: 127.0.0.1
Locale: en-US
DateFormat: dd-MM-yyyy
PID: 4120 ('w3wp', Started='10/10/2019 1:13:44 PM', Priv=4576Mb, Virt=2124782Mb)
TID: 99
Thread Name:
.NET: 4.0.30319.42000
Stack:
Error extracting Table Data. Specified cast is not valid.
   at ssDataExtractor.RssExtensionDataExtractor_Ext.MssDataExtractor_Extract(HeContext heContext, Int32 inParamExtractId, String inParamExtractName, RecordList inParamTableList, DateTime inParamStartDateTime, DateTime inParamEndDateTime, Int32 inParamMaximumFieldSize, Boolean inParamExtractTableStructure, Boolean inParamExtractTableIndexes, Boolean inParamExtractDDL, String& outParamFilePath, String& outParamFileSize, String& outParamHostname, String& outParamDuration)
   at ssDataExtractor.Actions.ActionTimer_CreateSnapshot(HeContext heContext)


I have one table that fails when it contains data but succeeds when it is empty. It has the following structure.


CREATE TABLE "OSUSR_WUB_ACTIVIT2" (
"ID" NUMBER(20, 0),
"ADUSER" VARCHAR2(200),
"DATETIME" TIMESTAMP(6),
"ACTIVITYSTATUSID" NUMBER(10, 0),
"XXXXSTATUSID" NUMBER(10, 0),
"XXXXID" NUMBER(20, 0),
"YYYYSTATUSID" NUMBER(10, 0),
"YYYYTYPEID" NUMBER(10, 0),
"USERFULLNAME" VARCHAR2(200),
"ADDITIONALINFO" VARCHAR2(2000),
"YYYID" NUMBER(20, 0),
"ORIGINALTASKID" NUMBER(20, 0)
)


Could it be that some data is causing problems and is it possible to investigate this ?

So empty it succeeds but with the following data inside it already fails.

ActivityLog.
Id
ActivityLog.
ADUser
ActivityLog.
DateTime
ActivityLog.
ActivityStatusId
ActivityLog.
InvoiceStatusId
ActivityLog.
InvoiceId
ActivityLog.
TastStatusId
ActivityLog.
TaskTypeId
ActivityLog.
UserFullName
ActivityLog.
AdditionalInfo
1877774ronv16-10-2019 16:08:041335753421Ronnie Verheij | KrampInvoice - Assigned to (Ronnie Verheij | Kramp)
1877775ronv16-10-2019 16:08:531335753400Ronnie Verheij | KrampInvoice - Assigned to (Ronnie Verheij | Kramp)
1877776ronv16-10-2019 16:08:5325335753411Ronnie Verheij | KrampTask 'Approve invoice' added to Ronnie Verheij | Kramp (ronv)  with finished before 2019-10-18


Last test:

* Drop the table from the database

* Compiled the eSpace again so that the table is recreated without unused columns

* DataExtract on the empty newly created table => Success

* Add 2 rows in the table.

* DataExtract on the table with 2 rows => Fails

* Conclusion => The DataExtract only partly works on Oracle/.NET/P11. So we can't use it. Rating 1

Processing Upload...

I added some try/catches and found that a 0 value for an Int64 and Decimal is a DBNull. The conversion of this values went wrong in the original DataExtractor. The added code is now better working on Oracle. The poor thing is that in original code doesn't contain clean conversions for all types.

Please check, modify and publish this new working DataExtractor logic.