Hi all,
My older version code uses executesqlQuery to execute a SQL code and it returns dictionary value . They used the rule in main interface to save the values to local variables. Now the function is deprecated and we replace it with executeStoredProcedureForQuery to execute SQL and it returns Map structure. But we are getting error while saving that in the main interface.
This is the error we are getting. I have changed the index value as the new structure but still I am getting error. Anyone have idea about how to resolve that?
Thanks,
Kavya
Discussion posts and replies are publicly visible
Can you share the affected code snippet?
Hi Stefan,
The interface have a search button and when I click on that it has to save the value.
a!buttonWidget( label: "Search", style: "NORMAL", value: true, saveInto: { local!search, if( local!refreshSearch, if( local!tabNbr = cons!GOS_ORDER_SEARCH_TAB_RESTRICT, { a!save(ri!gridAvailData, null), a!save(ri!summaryDs, null) }, if( local!tabNbr = cons!GOS_ORDER_SEARCH_TAB_DETAILS, { a!save(ri!gridRestrictData, null), a!save(ri!summaryDs, null) }, if( local!tabNbr = cons!GOS_ORDER_SEARCH_TAB_SUMMARY, { a!save(ri!gridAvailData, null), a!save(ri!gridRestrictData, null), }, {} ) ) ), {} ), { a!save(ri!generatedDocument, null), a!save( local!countRecognizedIds, index(index(index( rule!GOS_getRefBatchOrCpdMetadataByFieldAndValueSQL( fieldName: index( ri!browseInventoryHelper, "searchIdField", {} ), fieldValues: local!searchIdValues, getCountOnly: true ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}),"COUNT",{}) ), a!save( local!restrictData, index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: index( ri!browseInventoryHelper, "searchIdValue", {} ), getCountOnly: false, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: false(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}) ), a!save( local!notificationData, index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: index( ri!browseInventoryHelper, "searchIdValue", {} ), getCountOnly: false, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: true(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}) ), a!save( local!restrictGridSelection.startIndex, 1 ), a!save( local!notificationGridSelection.startIndex, 1 ), a!save( ri!gridRestrictData, rule!GOS_setRestrictionsFromSQL(local!restrictData) ), a!save( ri!gridNotificationData, rule!GOS_setRestrictionsFromSQL(local!notificationData) ), a!save(local!viewAllIds, false), a!save(local!searchUnavail, false), a!save(local!isSummaryView, false), if( local!isRestrictionSearch, { a!save( local!currentDataTabs, cons!GOS_ORDER_SEARCH_TAB_RESTRICT ), a!save( local!restrictData, index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: false, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: false(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}) ), a!save( local!restrictGridSelection.startIndex, 1 ), a!save( ri!gridRestrictData, rule!GOS_setRestrictionsFromSQL(local!restrictData) ), a!save( local!batchRestrictData, index( rule!GOS_getBatchRestrictionsBySearchValues( searchFieldId: "batIdentifier", searchValues: index(ri!gridRestrictData, "batIdentifier", {}), pagingInfo: a!pagingInfo(startIndex:1, batchSize:- 1, sort:{}), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ), getCountOnly: false, getBatIdentifierOnly: false ), "data", {} ) ), if( rule!APN_isEmpty(ri!gridRestrictData), {}, { a!save( ri!gridRestrictData.startDate, index(local!batchRestrictData, "startDate", {}) ), a!save( ri!gridRestrictData.endDate, index(local!batchRestrictData, "endDate", {}) ) } ), a!save( local!countRestricted, if( rule!APN_isEmpty(ri!gridRestrictData), 0, index(index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: true, getBatIdentifierOnly: false, requester: loggedInUser(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}),"COUNT",{}) ) ) }, if( local!isNotificationSearch, { a!save( local!currentDataTabs, cons!GOS_ORDER_SEARCH_TAB_NOTIFICATION ), a!save( local!notificationData, index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: false, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: true(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}) ), a!save( local!notificationGridSelection.startIndex, 1 ), a!save( ri!gridNotificationData, rule!GOS_setRestrictionsFromSQL(local!notificationData) ), a!save( local!batchRestrictData, index( rule!GOS_getBatchRestrictionsBySearchValues( searchFieldId: "batIdentifier", searchValues: index( ri!gridNotificationData, "batIdentifier", {} ), pagingInfo: a!pagingInfo(startIndex:1, batchSize:- 1, sort:{}), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ), getCountOnly: false, getBatIdentifierOnly: false ), "data", {} ) ), if( rule!APN_isEmpty(ri!gridNotificationData), {}, { a!save( ri!gridNotificationData.startDate, index(local!batchRestrictData, "startDate", {}) ), a!save( ri!gridNotificationData.endDate, index(local!batchRestrictData, "endDate", {}) ) } ), a!save( local!countNotification, if( rule!APN_isEmpty( rule!GOS_setRestrictionsFromSQL(local!notificationData) ), 0, index(index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: true, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: true(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}),"COUNT",{}) ) ) }, { a!save( local!tabNbr, cons!GOS_ORDER_SEARCH_TAB_DETAILS ), a!save( local!currentDataTabs, cons!GOS_ORDER_SEARCH_TAB_DETAILS ), a!save( local!gridAvailSelection, local!gridAvailPagingDefault ), local!sqlResult: index(index( rule!GOS_getRefBatchesToSearchBeforeCreationSQL( isExport: true, idField: index( ri!browseInventoryHelper, "searchIdField", {} ), idValue: local!searchIdValues, siteId: index( ri!browseInventoryHelper, "searchSiteIds", {} ), solventId: index( ri!browseInventoryHelper, "searchSolventIds", {} ), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ), minAmount: local!minSearchAmtStd, minConcentration: if( and( not( rule!APN_isBlank( index( ri!browseInventoryHelper, "minConcentration", {} ) ) ), index( ri!browseInventoryHelper, "minConcentration", {} ) <> local!minSearchConcStd ), index( ri!browseInventoryHelper, "minConcentration", {} ), local!minSearchConcStd ), available: true, isSolidOrderType: local!isSolidOrderType, maxAmount: if( or( rule!APN_isBlank(local!maxSearchAmtStd), local!maxSearchAmtStd = 0 ), null, local!maxSearchAmtStd ), maxConcentration: if( or( rule!APN_isBlank(local!maxSearchConcStd), local!maxSearchConcStd = 0 ), null, local!maxSearchConcStd ), oldestSynthDate: index( ri!browseInventoryHelper, "oldestSynthesisDate", {} ), newestSynthDate: index( ri!browseInventoryHelper, "newestSynthesisDate", {} ), chemistName: index( ri!browseInventoryHelper, "chemistName", {} ), minMw: index(ri!browseInventoryHelper, "minMw", {}), maxMw: if( or( rule!APN_isBlank( index(ri!browseInventoryHelper, "maxMw", {}) ), index(ri!browseInventoryHelper, "maxMw", {}) = 0 ), null, index(ri!browseInventoryHelper, "maxMw", {}) ), origin: index(ri!browseInventoryHelper, "origin", {}), concUnit: if( or( index( ri!browseInventoryHelper, "concUnits", {} ) = cons!GOS_REF_UNIT_CONCENTRATION_MGML, index( ri!browseInventoryHelper, "concUnits", {} ) = cons!GOS_REF_UNIT_CONCENTRATION_MM ), index( rule!GOS_getConcUnitIdByConcUnit(ri!browseInventoryHelper.concUnits), "description", {} ), {} ), ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}), local!sqlData: rule!GOS_setRefInvFromSQL(local!sqlResult), a!save(ri!gridAvailData, local!sqlData), a!save( local!countRestricted, index(index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: true, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: false(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}),"COUNT",{}) ), a!save( local!countNotification, index(index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: true, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: true(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}),"COUNT",{}) ) } ) ) }, a!save(local!refreshSearch, false) }, disabled: or( rule!APN_isBlank(local!searchIdValues), and( not( rule!APN_isBlank(local!prelimSearchIdValues) ), rule!APN_isBlank(local!searchIdValues) ), and(local!anyStateFilterCorrect), if( not( rule!APN_isBlank(local!searchIdValuesRaw) ), false, or( if( rule!APN_isBlank(local!searchIdValues), true, false ), and( local!tabNbr = 2, or(rule!APN_isEmpty(local!searchIdValues)) ) ) ) ), submit: false, validate: true, validationGroup: "searchButton" )
There is sub rule for SQL
if( or( rule!APN_isBlank( ri!searchFieldId ), rule!APN_isBlank( ri!searchValues ) ), {}, a!localVariables( local!totalSearchCount: count( ri!searchValues ), local!numberOfIns: tointeger( rounddown( local!totalSearchCount / cons!GOS_SQL_ID_SEARCH_SPLIT, 0 ) ) + 1, local!indexes: enumerate( local!numberOfIns ) + 1, /*GoSRT V3.2 Fix*/ local!userSiteId: if( rule!APN_isBlank( ri!order.recipientUserId ), "", index( rule!GOS_getUserSiteById( ri!order.recipientUserId ), "siteId", 0 ) ), /*GoSRT V3 - Passed Order's project code instead of Users Project Code */ local!requesterProjectIds: if( not( rule!APN_isBlank( ri!requester ) ), touniformstring( index( index( rule!GOS_getProjectIdsByUsername( username: ri!requester, pagingInfo: a!pagingInfo( 1, - 1, {} ) ), "data", {} ), "projectId", {} ) ), {} ), local!usersProjectIds: if( and(not( rule!APN_isBlank( ri!orderProjectId ) ),not( rule!APN_isEmpty( local!requesterProjectIds ) )), if( contains( local!requesterProjectIds, ri!orderProjectId ), ri!orderProjectId, {} ), {} ), local!queryStatement: if( ri!getBatIdentifierOnly, "SELECT BAT_IDENTIFIER ", if( ri!getCountOnly, "SELECT count(BAT_IDENTIFIER) as count ", "SELECT BAT_IDENTIFIER, CPD_IDENTIFIER, PROJECT_ID, RESTRICT_REASON_TIER, RESTRICT_REASON_DESC, RESTRICT_TYPE " ) ) & "FROM GOS_APN_RESTRICT_ACTV_REF_V WHERE " & "(" & joinarray( a!forEach( items:local!indexes, expression:rule!GOS_createIdentifierSearchForSQL( searchIdValues: ri!searchValues, searchIdField: ri!SearchFieldId, index: fv!item, multiplier: cons!GOS_SQL_ID_SEARCH_SPLIT, useSearch: false ) ) ) & ")" & /*"AND (START_DATE<= CURRENT_DATE AND END_DATE>= CURRENT_DATE)"&*/ if( ri!notificationTab = true, {}, if( rule!APN_isBlank( local!usersProjectIds ), {}, { " AND PROJECT_ID <>(" & rule!GOS_transformIdValuesForSQL( local!usersProjectIds ) & ")" } ) ) & if( or( rule!APN_isBlank( ri!stateId ), ri!stateId = cons!GOS_ANY_VALUE ), {}, { " AND RESTRICT_TYPE_ID in (" & if( ri!stateId = cons!GOS_REFERENCE_ID_SOLID, rule!GOS_transformIdValuesForSQL( { cons!GOS_REF_RESTRICT_TYPE_ID_TOTAL, cons!GOS_REF_RESTRICT_TYPE_ID_PARTIAL_SOLIDS } ), if( ri!stateId = cons!GOS_REFERENCE_ID_LIQUID, rule!GOS_transformIdValuesForSQL( { cons!GOS_REF_RESTRICT_TYPE_ID_TOTAL, cons!GOS_REF_RESTRICT_TYPE_ID_PARTIAL_LIQUIDS } ), rule!GOS_transformIdValuesForSQL( { cons!GOS_REF_RESTRICT_TYPE_ID_TOTAL, cons!GOS_REF_RESTRICT_TYPE_ID_PARTIAL_SOLIDS, cons!GOS_REF_RESTRICT_TYPE_ID_PARTIAL_LIQUIDS } ) ) ) & ")" } ) & if( ri!notificationTab = true, { " AND RESTRICT_REASON_TIER in (" & rule!GOS_transformIdValuesForSQL( { cons!GOS_REF_SUB_TYPE_RESTRICT_TIER_5 } ) & ")" }, { " AND RESTRICT_REASON_TIER not in (" & rule!GOS_transformIdValuesForSQL( { cons!GOS_REF_SUB_TYPE_RESTRICT_TIER_4, cons!GOS_REF_SUB_TYPE_RESTRICT_TIER_5 } ) & ")" } ) & if( ri!isSortBatIdentifier, { " ORDER BY BAT_IDENTIFIER" & if( true, " ASC", " DESC" ) }, {} ), local!queryOutput: a!executeStoredProcedureForQuery( dataSource: cons!GOS_SQL_JNDI_NAME, procedureName:cons!GOS_SP_EXECUTE_SQL_QUERY, inputs:{a!storedProcedureInput(name:"SQLQUERY" ,value:local!queryStatement)} ), local!batchIdentifiers: index( index( local!queryOutput, "result", null ), "BAT_IDENTIFIER", null ), local!siteIds: a!forEach( items: local!batchIdentifiers, expression: rule!GOS_getBatchSiteByBatchIdForNotification( fv!item ) ), local!notificationOutput: remove( index( local!queryOutput, "result", null ), wherecontains( tointeger( local!userSiteId ), tointeger( local!siteIds ) ) ),if( ri!siteCheck = true, local!notificationOutput, local!queryOutput ) /*local!queryStatement*/ ) )
Thanks ,
Oh dear .... It's a while ago I saw > 500 lines of code in a saveInto.
The lines 344 and 464 and look suspicious. You cannot store data to a local that way. Use an a!save().
BTW, do you really need to dynamically construct SQL statements in Appian and send them to a stored procedure for execution?
Agreed with this - local!sqlData and local!sqlResult are being invoked in an unsupported way and i'd be surprised if it even worked by accident. I believe the error in the screenshot is likely directly related to these two lines, since they are not a valid save operation.