Itgendid103 with stored procedure : provider InMemory Storage 'incapable' when trying to fetch data

sda · 5 december 2022 om 11:25

Since I have upgraded to 22.0.444 PROD+3699 on november 23th, I have a script automation with a stored procedure that fails with a itgendid103 error.

The script ran fine for almost 2 years and is a pretty long (578 lines).

Any idea what is going wrong given this error ? Should i share my full script here ?

This is the extract of the error:

2022-12-05 06:34:07.602 Information itgengpr065: Ouverture du conteneur de données 'DataDictionaryInMemory'.
2022-12-05 06:34:07.604 Information itgengpr152: Ouverture du conteneur de données 'DataDictionaryInMemory'.
2022-12-05 06:34:09.922 Warning itgendhb173: Contexte: --23/08/21 -- added paging for the salesdocumentlines -- to do...InMemoryStorage;     drop table Newshifts@InMemoryStorage; end;
2022-12-05 06:34:09.953 Error itgencun016: Erreur itgendid103: Le fournisseur 'InMemoryStorage' a renvoyé le statut 'Incapable' lorsqu'il a essayé de récupérer des données sur 'NEWSHIFTS'.
2022-12-05 06:34:11.955 Error itgendid103: ValidationException
   à Invantive.Basics.ValidationException..ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String errorMessage, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId, String url)
   à Invantive.Sql.V1.DataSourceOrFunctionTree.<GetData>d__51.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Sql.V1.ChainedFirehose.PM.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à System.Linq.Lookup`2.Create[TSource](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   à Invantive.Sql.V1.GroupByIterator.TS.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Sql.V1.SelectListIterator.ZS.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.MaterializeSparseArrayIterator.<Iterator>d__1.MoveNext()
   à Invantive.Sql.V1.QueryPlan.JS.MoveNext()
   à Invantive.Data.ExtensionMethods.BOQ`1.COQ.MoveNext()
   à System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   à System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   à Invantive.Sql.V1.TransformToExpression.EvaluateInnerSelectStatementScalar(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, QueryPlan plan, DatabaseColumnDefinitionCollection fields, ISparseArray pSqlVariableRow)
   à LambaExecutePSqlTree186(Closure , GlobalState , ExecutionOptions , IQueryStatePerExecution , ISparseArray , ISparseArray )
   à Invantive.Sql.V1.PSqlStatementTree.<Fetch>d__29.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.QueryPlan.JS.MoveNext()
   à Invantive.Data.ExtensionMethods.BOQ`1.COQ.MoveNext()
   à System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   à System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   à Invantive.Data.ConnectionManager.XG.T(QueryPlan )
   à System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   à System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   à System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   à Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   à Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   à IDH.R.ProcessStatement(GlobalState owner, ExecutionOptions executionOptions, String statement, ResultSet& resultSet, Action`2 fetchMoreRows, Action`9 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification)
   à IDH.R.ProcessStatements(GlobalState owner, ExecutionOptions executionOptions, Action`2 fetchMoreRows, Action`9 executeStatement, IEnumerable`1 statements, String sourceFileName, ScriptingLanguageNextStepSpecification& exitSpecification)
   à IDH.R.B(String[] )
   à Invantive.Producer.QueryEngine.Program.Main(String[] arguments)
   à Invantive.Sql.V1.DataSourceOrFunctionTree.<GetData>d__51.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Sql.V1.ChainedFirehose.PM.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à System.Linq.Lookup`2.Create[TSource](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   à Invantive.Sql.V1.GroupByIterator.TS.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Sql.V1.SelectListIterator.ZS.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.MaterializeSparseArrayIterator.<Iterator>d__1.MoveNext()
   à Invantive.Sql.V1.QueryPlan.JS.MoveNext()
   à Invantive.Data.ExtensionMethods.BOQ`1.COQ.MoveNext()
   à System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   à System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   à Invantive.Sql.V1.TransformToExpression.EvaluateInnerSelectStatementScalar(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, QueryPlan plan, DatabaseColumnDefinitionCollection fields, ISparseArray pSqlVariableRow)
   à LambaExecutePSqlTree186(Closure , GlobalState , ExecutionOptions , IQueryStatePerExecution , ISparseArray , ISparseArray )
   à Invantive.Sql.V1.PSqlStatementTree.<Fetch>d__29.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.QueryPlan.JS.MoveNext()
   à Invantive.Data.ExtensionMethods.BOQ`1.COQ.MoveNext()
   à System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   à System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   à Invantive.Data.ConnectionManager.XG.T(QueryPlan )
   à System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   à System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   à System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   à Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
--- Fin de la trace de la pile à partir de l'emplacement précédent au niveau duquel l'exception a été levée ---
   à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
--- Fin de la trace de la pile à partir de l'emplacement précédent au niveau duquel l'exception a été levée ---
   à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   à Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   à IDH.R.ProcessStatement(GlobalState owner, ExecutionOptions executionOptions, String statement, ResultSet& resultSet, Action`2 fetchMoreRows, Action`9 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification)
2022-12-05 06:34:11.959 Information itgendhb223: Terminer l'exécution du programme avec le code de sortie 3.

forums · 5 december 2022 om 20:05

The cause is probably a change in the dynamic nature of parsing temporary tables. Probably somewhere in a PSQL-block a temporary (in-memory) table NEWSHIFTS is used before it can be interpreted due to creation.

Please include the full script.

sda · 5 december 2022 om 20:37

After some digging I found the code block in cause and it relates to httpdownload@DataDictionary with the method detailed here : How to send multiple key values in Authorization header with httpdownload.

The API enpoint now returns a 404 error.

The error is not caught in the error catch logic :

begin
  select * from httpdownload@DataDictionary (...)
  join jsontable (...)
exception
    when others
    then
    -- do exception work here 
end

My findings so far :

The 404 error returned by the httpdownload@DataDictionary is not caught by the exception handler
It seems there is a change in the httpdownload@DataDictionary method. I tested the API with a cURL query and it works.

I will share my query in a private message, because it contains some confidential access key.

sda · 5 december 2022 om 20:59

It seems it is linked to Itgenclr083 of itgenclr094 (404) niet gevonden Chromium.

I’m trying to upgrade now to 22.0.455

sda · 5 december 2022 om 21:14

my issue was created by the Itgenclr083 of itgenclr094 (404) niet gevonden Chromium.
I confirm that upgrading to 22.0.455 solves the issue.

forums · 7 december 2022 om 07:07

Thanks. Also retested on 22.0.456 and latest BETA. Both display "Caught HTTP error correctly: " using this test code with secret removed.

declare
  l_cnt pls_integer;
begin
  select count(*) 
  into   l_cnt 
  from   httpdownload@DataDictionary 
  ( url => 'https://api.laddition.com/ShiftDocumentsWRONGFOR404?opening_date=2022-11-01 00:00:00&closing_date=2022-11-30 00:00:00'
  , contentType => 'application/json'
  , headers => 'Authorization=Bearer secret' ||  '&'  || 'customerid=15932'
  , diskCache => false
  , method => 'GET'
  ) htp
  join jsontable
  ( 'data.[*]'
    passing htp.contents_char
    columns shift_id                           varchar2 path 'id'
    ,       opening_date                       varchar2 path 'opening_date'
    ,       closing_date                       varchar2 path 'closing_date'
    ,       begin_cash_float                   decimal  path 'begin_cash_float' -- fond de caisse ouverture
    ,       amount_offered_ivat                decimal  path 'amount_offered_ivat' -- total des offerts
    ,       amount_discount_ivat               decimal  path 'amount_discount_ivat' -- total des remises
    ,       amount_total_evat                  decimal  path 'amount_total_evat' -- Total HT
    ,       amount_tax                         decimal  path 'amount_tax' -- Total TVA
    ,       amount_total_wo_discount_ivat      decimal  path 'amount_total_wo_discount_ivat' -- Total CA TTC Brut (hors offerts et remises)
    ,       nb_guest                           int      path 'nb_guest'
    ,       nb_sales_document                  int      path 'nb_sales_document' -- nombre de commandes
    ,       amount_avg_basket                  decimal  path 'amount_avg_basket' -- panier moyen
    ,       amount_avg_sales_document          decimal  path 'amount_avg_sales_document' -- Ticket moyen
    ,       amount_payment_ivariation          decimal  path 'amount_payment_ivariation' -- Total règlements (prenant en compte les écarts renseignées manuellement)
    ,       amount_payment_shift               decimal  path 'amount_payment_shift' -- Total règlements (ventilation CA, normalement égal au CA TTC)
    ,       amount_liquid                      decimal  path 'amount_liquid' -- Montant liquide
    ,       amount_solid                       decimal  path 'amount_solid' -- Montant solide (normalement liquide + solide = CA TTC)
    ,       cancelled_manufacturing            decimal  path 'cancelled_manufacturing' --  Annulation de produits envoyés en cuisine
    ,       cancelled_manufacturing_after_note decimal  path 'cancelled_manufacturing_after_note' --  Annulation de produits après ticket
    ,       cancelled_non_manufacturing        decimal  path 'cancelled_non_manufacturing' -- Annulation de produits hors fabrication cuisine
  ) res
  ;
exception 
  when others 
  then 
    raise_application_error(-20163, 'Caught HTTP error correctly: ' || sqlerrm);
end;

Topic		Antwoorden	Weergaven
Itgenclr000 NullReferenceException Questions mysql , invantive-sql	15	376	11 april 2024
Error itgendhb212: External error in Invantive Data Hub. (out of memory) Questions exact-online , invantive-data-replicator	12	1281	20 september 2023
Itgendid177 message when running a query Wiki (en) invantive-sql	0	320	22 maart 2022
Itgensql160 Row length 127 doesn't equal the field count 64 on row #0. Type 'FirehoseResultSet' Wiki (en) invantive-sql	0	237	23 juli 2022
Querytool Beta 20.1.537 / itgenclr009 / Loop statement broken on the Invantive interpreter Questions invantive-query-tool	2	378	11 april 2024

Itgendid103 with stored procedure : provider InMemory Storage 'incapable' when trying to fetch data

Gerelateerde topics