[DBCleaner] DBCleaner and DataLength

Forge Component
(47)
Published on 11 Mar by Johan den Ouden
47 votes
Published on 11 Mar by Johan den Ouden

Version of DBCleaner 1.8 returns this error using Oracle. 

Error in advanced query GetEspaces in Preparation in EspaceVersionsList in MainFlow in DBCleaner (select {Espace}.*,         {Espace_Version}.*,         {Solution}.*,         {Solution_Version}.*,         {APPLICATION}.*,         {APPLICATION_VERSION}.*,         datalength({Espace_Version}.[OML_File]) / (1024.0 * 1024.0)            from {Espace}     inner join {Espace_Version}  on {Espace_Version}.eSpace_Id = {Espace}.Id        left join {Solution_Version_Reference}  on {Espace_Version}.Id = {Solution_Version_Reference}.Espace_Version_Id    left join {Solution_Version}  on {Solution_Version}.Id = {Solution_Version_Reference}.Solution_Version_Id    left join {Solution} on {Solution}.Id = {Solution_Version}.Solution_Id      left join {APP_VERSION_MODULE_VERSION} on ({Espace_Version}.Id = {APP_VERSION_MODULE_VERSION}.ESPACE_VERSION_ID and {APP_VERSION_MODULE_VERSION}.EXTENSION_VERSION_ID is null)    left join {APPLICATION_VERSION}  on ({APP_VERSION_MODULE_VERSION}.APP_VERSION_ID = {APPLICATION_VERSION}.ID)    left join {APPLICATION}  on ({APPLICATION_VERSION}.APPLICATIONID =  {APPLICA ...): ORA-00904: "DATALENGTH": invalid identifier



Actually this page does not support Oracle or MySQL. There are couple of other unsupported SQLs.

I have a new version and this includes fixes, but I still waiting approval from team owners.


Even Prune Old Emails fails.  Should we expect a fixed version soon?  We  have a lot of clean up to do.

Yes, indeed some of the screens are SQL Server only and are not prepared to run on MySQL or Oracle.

I have accepted Mika's request to join the team so he can share his version of the espace if he so desires.

Thank you Ricardo.  I look forward to seeing what you all come up with :)

Now I added a new version and this is not fully tested with Oracle ( I do not have oracle database). If you can update version and let me know if there is some problems.

Hi Mika - Thank you for the quick turn around for the fixes.  The emails seem to be deleting just fine.  The espace version list still returns an internal error.  If I run the query in a database tool, after replacing the {tablename} with the appropriate tables, i don't get the error.  So maybe it's something OutSystems is doing to the query to cause such an error.

Here is the error:
Error in advanced query GetEspaces in Preparation in Espaces in MainFlow in DBCleaner (select {Espace}.*,      {Module}.[Id],      @Datalength({Espace_Version}.[OML_File]) / (1024.0 * 1024.0),      {Espace_Version}.[Id],      {Espace_Version}.[Version],      {Espace_Version}.[Service_Studio_Version],      {Solution}.[Id],      {Solution}.[Name],      {Solution_Version}.[Version],      {Solution_Version}.[Name],      {APPLICATION}.[NAME],      {APPLICATION_VERSION}.[NAME],      {APPLICATION}.[IS_ACTIVE]       from {Espace}     inner join {Module}  on {Module}.Espace_Id = {Espace}.Id    inner join {Espace_Version}  on {Espace_Version}.eSpace_Id = {Espace}.Id        left join {Solution_Version_Reference}  on {Espace_Version}.Id = {Solution_Version_Reference}.Espace_Version_Id    left join {Solution_Version}  on {Solution_Version}.Id = {Solution_Version_Reference}.Solution_Version_Id    left join {Solution} on {Solution}.Id = {Solution_Version}.Solution_Id      left join {APP_VERSION_MODULE_VERSION} on ({Espace_Version}.Id = {APP_VERSION_MODULE_VERSION}.ESPACE_VERSION_ID and ...): ORA-01722: invalid number



David Zangger wrote:

Hi Mika - Thank you for the quick turn around for the fixes.  The emails seem to be deleting just fine.  The espace version list still returns an internal error.  If I run the query in a database tool, after replacing the {tablename} with the appropriate tables, i don't get the error.  So maybe it's something OutSystems is doing to the query to cause such an error.

Here is the error:
Error in advanced query GetEspaces in Preparation in Espaces in MainFlow in DBCleaner (select {Espace}.*,      {Module}.[Id],      @Datalength({Espace_Version}.[OML_File]) / (1024.0 * 1024.0),      {Espace_Version}.[Id],      {Espace_Version}.[Version],      {Espace_Version}.[Service_Studio_Version],      {Solution}.[Id],      {Solution}.[Name],      {Solution_Version}.[Version],      {Solution_Version}.[Name],      {APPLICATION}.[NAME],      {APPLICATION_VERSION}.[NAME],      {APPLICATION}.[IS_ACTIVE]       from {Espace}     inner join {Module}  on {Module}.Espace_Id = {Espace}.Id    inner join {Espace_Version}  on {Espace_Version}.eSpace_Id = {Espace}.Id        left join {Solution_Version_Reference}  on {Espace_Version}.Id = {Solution_Version_Reference}.Espace_Version_Id    left join {Solution_Version}  on {Solution_Version}.Id = {Solution_Version_Reference}.Solution_Version_Id    left join {Solution} on {Solution}.Id = {Solution_Version}.Solution_Id      left join {APP_VERSION_MODULE_VERSION} on ({Espace_Version}.Id = {APP_VERSION_MODULE_VERSION}.ESPACE_VERSION_ID and ...): ORA-01722: invalid number



Hi David,

use this (I had the same issue):

SyntaxEditor Code Snippet

select {Espace}.*,
    {Module}.[Id],
    dbms_lob.getlength({Espace_Version}.[OML_File]) / (1024.0 * 1024.0),
    {Espace_Version}.[Id],
    {Espace_Version}.[Version],
    {Espace_Version}.[Service_Studio_Version],
    {Solution}.[Id],
    {Solution}.[Name],
    {Solution_Version}.[Version],
    {Solution_Version}.[Name],
    {APPLICATION}.[NAME],
    {APPLICATION_VERSION}.[NAME],
    {APPLICATION}.[IS_ACTIVE]
   
from {Espace} 
  inner join {Module}  on {Module}.Espace_Id = {Espace}.Id
  inner join {Espace_Version}  on {Espace_Version}.eSpace_Id = {Espace}.Id
  
  left join {Solution_Version_Reference}  on {Espace_Version}.Id = {Solution_Version_Reference}.Espace_Version_Id
  left join {Solution_Version}  on {Solution_Version}.Id = {Solution_Version_Reference}.Solution_Version_Id
  left join {Solution} on {Solution}.Id = {Solution_Version}.Solution_Id

  left join {APP_VERSION_MODULE_VERSION} on ({Espace_Version}.Id = {APP_VERSION_MODULE_VERSION}.ESPACE_VERSION_ID and {APP_VERSION_MODULE_VERSION}.EXTENSION_VERSION_ID is null)
  left join {APPLICATION_VERSION}  on ({APP_VERSION_MODULE_VERSION}.APP_VERSION_ID = {APPLICATION_VERSION}.ID)
  left join {APPLICATION}  on ({APPLICATION_VERSION}.APPLICATIONID =  {APPLICATION}.ID)
  
where
  (@IsHideWithoutSolutionVersion = 0 or {Solution}.[Id]  is not null) and
  (@IsHideWithoutAppVersion = 0 or {APPLICATION}.[ID] is not null) and
  (@IsHideCurrentPublishedeSpaceVersion = 0 or {Espace}.[Version_Id] <> {Espace_Version}.[Id]) and
    (
    {Espace}.[Name] like '%' || @Espaces_SearchKeyword || '%' or
    {Espace_Version}.[Service_Studio_Version] like '%' || @Espaces_SearchKeyword || '%' or
    {Solution}.[Name] like '%' || @Espaces_SearchKeyword || '%' or
    {Solution_Version}.[Name] like '%' || @Espaces_SearchKeyword || '%' or
    {APPLICATION}.[NAME]  like '%' || @Espaces_SearchKeyword || '%' or
    {APPLICATION_VERSION}.[NAME] like '%' || @Espaces_SearchKeyword || '%' or
    @Espaces_SearchKeyword = ''
  )
  and ({Espace}.[Id] = @eSpaceId OR @eSpaceId = 0)
  
  Order by @OrderBy