XSODATA Service exception: [260] invalid column name

[former_member221711]

Hi Experts,

I have written a exist procedure to delete data from the database table using XSODATA. On debugging, the procedure just runs fine and data is deleted from the table but while trying to delete a record through the service using POSTMAN i get an error

Service exception: [260] invalid column name.

PROCEDURE

PROCEDURE "XYZ"."ABC_PK.PROCEDURES::SocialDataDeleteCategory" 
					(
						in  ip "XYZ"."ABC_SOCIAL_DATA_CATEGORIES",
						out op "XYZ"."ABC_PK.STRUCTURES::MESSAGE"
					)
					 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
	
	--DEFAULT SCHEMA <default_schema_name>
	--READS SQL DATA AS
	
	
	ls_social_data_id_del   INTEGER;
	
	
BEGIN
/***************************** 
	Write your procedure logic 
 *****************************/
       
    SELECT TOP 1 "SOCIAL_DATA_ID"
	INTO   ls_social_data_id_del	        
	FROM   :ip;






		IF ls_social_data_id_del IS NULL THEN
					
				op  =  SELECT 430 				           	 as HTTP_STATUS_CODE,
					         'Provide social_data_id' 	     as MESSAGE,
				      		 ''           					 as DETAIL 
				       FROM dummy;
				       
		ELSE		       
			    DELETE FROM "XYZ"."ABC_SOCIAL_DATA_CATEGORIES"
				WHERE "SOCIAL_DATA_ID" = :ls_social_data_id_del;
			    
			    COMMIT;
	
	    END IF; 
	
END;

Service:

service {


		"XYZ"."ABC_SOCIAL_DATA_CATEGORIES" as "CATEGORY" with ("SOCIAL_DATA_ID","CATEGORY_ID")
		
		delete using "ABC_PK.PROCEDURES::SocialDataDeleteCategory"
		;


}

Best Regards

Madhusudan

[pfefferf]

Have you checked the server logs already? They should gave you more information regarding the occurred error.

[former_member221711]

Hi Florian,

Sorry for replying late was occupied with some other issues 🙂

Log

[49241]{-1}[-1/-1] 2017-02-28 16:52:37.745864 w Logger           SavepointImpl.cpp(02644) : NOTE: BACKUP DATA needed to ensure recoverability of the database
[106897]{318357}[15/58790] 2017-02-28 16:53:32.779154 i TraceContext     TraceContext.cpp(00855) : UserName=MP00420987, ApplicationUserName=MP00420987, ApplicationName=CAPE_PK, ApplicationSource=/CAPE_PK/SERVICES/SRV_CAPE_SOCIAL_DATA_DELETE_CATEGORY.xsodata/CATEGORY(SOCIAL_DATA_ID=27,CATEGORY_ID=137), StatementHash=66e7ec2bc1e431c84a9262c74e790656
[106897]{318357}[15/58790] 2017-02-28 16:53:32.779146 e ceExecute        cePopCustomSql.cpp(01181) : Error-SQL while executing " SELECT "SOCIAL_DATA_ID", "CATEGORY_ID", "CREATED_DATE", "MODIFIED_DATE", "CREATED_BY", "MODIFIED_BY" FROM "MP00420987"."#NEW_CATEGORYType139693469453584" /* 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 */ ": invalid column name: CREATED_DATE: line 1 col 42 (at pos 41)
[104237]{318357}[15/58790] 2017-02-28 16:53:32.783671 e Executor         PlanExecutor.cpp(00862) : plan plan1173@hanaserver:30007 failed with rc 260; invalid column nameinvalid column name: CREATED_DATE: line 1 col 42 (at pos 41) (please check lines: )
[104237]{318357}[15/58790] 2017-02-28 16:53:32.783699 e Executor         PlanExecutor.cpp(00862) : -- returns for plan1173@hanaserver:30007:
[104237]{318357}[15/58790] 2017-02-28 16:53:32.783706 e Executor         PlanExecutor.cpp(00862) : invalid column name: CREATED_DATE: line 1 col 42 (at pos 41) (please check lines: )(260), plan: 7 pops: ceSqlPop pop1(out a), ceConvertDatatypePop pop2(in a, out b), ceLjitPop pop3(in b, out cd), ceConvertDatatypePop pop4(in c, out f), ceConvertDatatypePop pop5(in d, out e), ceCustomCppPop pop6(in ef, out g), ceProjectionPop pop7(in g, out h)
[104237]{318357}[15/58790] 2017-02-28 16:53:32.783730 e Executor         PlanExecutor.cpp(00862) : pop1, 16:53:32.766  +0.013, cpu 1, ceSqlPop, rc 260, invalid column name
[104237]{318357}[15/58790] 2017-02-28 16:53:32.783752 e Executor         PlanExecutor.cpp(00862) : Comm total: 0.000
[104237]{318357}[15/58790] 2017-02-28 16:53:32.783758 e Executor         PlanExecutor.cpp(00862) : Total: 16:53:32.765 to 16:53:32.783 +0.018, cpu 1
[104237]{318357}[15/58790] 2017-02-28 16:53:32.783772 e Executor         PlanExecutor.cpp(00862) : sizes a 0 h 0
[104237]{318357}[15/58790] 2017-02-28 16:53:32.783777 e Executor         PlanExecutor.cpp(00862) : -- end executor returns
[104237]{318357}[15/58790] 2017-02-28 16:53:32.783780 e Executor         PlanExecutor.cpp(00862) : pop1 (rc 260, invalid column name)
pop = executorPy.ceSqlPop() # pop1
pop.setNodeName('IP')
pop.setUseInternalTable()
pop.addViewAttribute('SOCIAL_DATA_ID', datatype=73, intDigits=4, sqlType=42, sqlLength=4)
pop.addViewAttribute('CATEGORY_ID', datatype=73, intDigits=4, sqlType=42, sqlLength=4)
pop.addViewAttribute('CREATED_DATE', datatype=101, intDigits=4, sqlType=63, sqlLength=4)
pop.addViewAttribute('MODIFIED_DATE', datatype=101, intDigits=4, sqlType=63, sqlLength=4)
pop.addViewAttribute('CREATED_BY', datatype=83, sqlType=36, sqlLength=40)
pop.addViewAttribute('MODIFIED_BY', datatype=83, sqlType=36, sqlLength=40)
pop.setLocale('BINARY')
pop.setUserSchema('MP00420987')
pop.addPlanDebugOpDataInfo('XYZ:_SYS_SS_CE_164678_139693257537536:3_INS', callerName='IP', sqlScriptFunctionName='XYZ:CAPE_PK.PROCEDURES::SocialDataDeleteCategory')
pop.setSqlStatement(' SELECT "SOCIAL_DATA_ID", "CATEGORY_ID", "CREATED_DATE", "MODIFIED_DATE", "CREATED_BY", "MODIFIED_BY" FROM "MP00420987"."#NEW_CATEGORYType139693469453584"')
pop.setSchema('MP00420987')
pop.setProcOid(164678)
pop.setExecuteUser('MP00420987')
pop.setScenarioType(512)
pop.setUseParameterizedStatementFlag(0)
pop.addBaseObject('"MP00420987"."#NEW_CATEGORYType139693469453584"')
[104237]{318357}[15/58790] 2017-02-28 16:53:32.783943 e cePlanExec       cePlanExecutor.cpp(07583) : Error during Plan execution of model XYZ:_SYS_SS_CE_164678_139693257537536:3_INS (t -1), reason: invalid column name: CREATED_DATE: line 1 col 42 (at pos 41) (please check lines: )

Table from which i am trying to delete the records

[pfefferf · Accepted solution]

Looks strange. On which HANA version/SPS you are?

Have you tried to remove the "with" statement in the service definition for test purposes, so that the service is not restricted to the SOCIAL_DATA_ID and CATEGORY_ID field?

I could reproduce the error just in case I use the "with" statement in combination with an exit. Either removing the exit or the "with" statement solves that.

I would open a ticket for that, so that SAP can bring some light on that behavior. Maybe it is a bug, because also nothing is written about that in the documentation.

[former_member221711 · Accepted solution]

Hi Florian,

Thanks for helping out .... tried removing the "with" clause & it worked like a charm but its more of a hit & trial here.

Great that you have reported the issue to SAP. It would be of great help if you point to any source for learning xsodata apart from HANA Developer guide.

Best Regards

Madhusudan

[former_member221711]

Thanks for your help..

Apologies posting late, my earlier reply some how did not appear here.

I am on AWS and the version reads as 1.00.110.001447753075.

Removing "with" did work for me as well.

Best Regards

Madhusudan