cancel
Showing results for 
Search instead for 
Did you mean: 

Error: sap dbtech jdbc 1301

Former Member
0 Kudos

Hi,

Please someone could help !!!

Step 1  : 

CREATE FUNCTION ABCD.scale(xobjid nvarchar)

RETURNS TABLE (FRM INT, TOO INT, objid1 nvarchar) LANGUAGE SQLSCRIPT AS

BEGIN

      --declare xobjid1 nvarchar(8);

      --xobjid1 := :xobjid;

    RETURN SELECT FRM, TOO, :xobjid as objid1 FROM ABCD.CUSTOMERCONNECTIONS;

END;

Step 2 :

CREATE PROCEDURE ABCD.ARRAY_AGG_TEST()

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

   DECLARE idd NVARCHAR ARRAY;

   DECLARE n,x Integer;

   DECLARE idd1 NVARCHAR;

   tab = select ID from ABCD.CUSTOMER;

   idd := ARRAY_AGG(:tab.ID);

   n := CARDINALITY(:idd);

   --select :n as card from dummy;

   --output := :idd;

   --rst = UNNEST(:idd);

   --SELECT * FROM :rst;

for x in 1 .. :n do

       idd1 := :idd[1];

       select * from ABCD.scale(:idd1);

       --select FRM, TOO, objid1 from I050447.scale('1A');

end for;

END;

Step 3: Then do call: 

CALL ABCD.ARRAY_AGG_TEST();

I get the following error:

Could Not Execute 'Call ABCD.ARRAY_AGG_TEST()' in 268 ms.

SAP dbtech jdbc 1301 numeric or value error: ABCD.ARRAY_AGG_TEST(): (range 3)



Thanks Much,

Abhishek

Accepted Solutions (0)

Answers (1)

Answers (1)

rindia
Active Contributor
0 Kudos

Hi Abhishek,

This has to be create from my end and to see.

Could you please send the DDL for the tables CUSTOMER & CUSTOMERCONNECTIONS.

If the table columns are huge then send the data size and types of columns which you used.

Regards

Raj

Former Member
0 Kudos

CREATE COLUMN TABLE "ABCD"."CUSTOMER" ("ID" NVARCHAR(10),

       "NAME" NVARCHAR(10),

       "WEIGHT" INTEGER CS_INT) UNLOAD PRIORITY 5 AUTO MERGE

CREATE COLUMN TABLE "ABCD"."CUSTOMERCONNECTIONS" ("FRM" INTEGER CS_INT,

       "TOO" INTEGER CS_INT,

       "WEIGHT" INTEGER CS_INT) UNLOAD PRIORITY 5 AUTO MERGE

rindia
Active Contributor
0 Kudos

oops, I am getting error while creating procedure at the line DECLARE idd NVARCHAR ARRAY; I am on HANA revision 58. Minimum revision 60 is required for Array I guess.

Former Member
0 Kudos

Yes, SP06.

vivekbhoj
Active Contributor
0 Kudos

Hi Abhishek,

I tried your code and its working fine for me.

I am on HANA rev66

On which HANA version are you?

Regards,

Vivek

vivekbhoj
Active Contributor
0 Kudos

With SPS06 only write operations on Array are supported.

http://help.sap.com/hana/Whats_New_SAP_HANA_Platform_Release_Notes_en.pdf

Regards,

Vivek

Former Member
0 Kudos

Hi Vivek,

I am on Rev 67.

This step itself gives me error, if you run:

select * from ABCD.scale(:idd1);

Thanks Much,

Abhishek

vivekbhoj
Active Contributor
0 Kudos

I copy pasted your code to create tables and procedure was created successfully, can't attach screenshot now:

CREATE PROCEDURE ARRAY_AGG_TEST1()

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

   DECLARE idd NVARCHAR ARRAY;

   DECLARE n,x Integer;

   DECLARE idd1 NVARCHAR;

   tab = select ID from CUSTOMER1;

   idd := ARRAY_AGG(:tab.ID);

   n := CARDINALITY(:idd);

   --select :n as card from dummy;

   --output := :idd;

   --rst = UNNEST(:idd);

   --SELECT * FROM :rst;

for x in 1 .. :n do

       idd1 := :idd[1];

       select * from scale1(:idd1);

       --select FRM, TOO, objid1 from I050447.scale('1A');

end for;

END;

Statement 'CREATE PROCEDURE ARRAY_AGG_TEST1() LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN DECLARE idd ...'

successfully executed in 136 ms 502 µs  (server processing time: 135 ms 211 µs) - Rows Affected: 0

Regards,

Vivek

vivekbhoj
Active Contributor
0 Kudos

Call Procedure also runs successfully:

CALL ARRAY_AGG_TEST1();

Statement 'CALL ARRAY_AGG_TEST1()'

successfully executed in 13 ms 809 µs  (server processing time: 12 ms 294 µs) - Rows Affected: 0

Former Member
0 Kudos

Hi Vivek,

Please try by inserting two/three rows of values in table.

Then it throws error. Also, the procedure gets created successfully.

If you run the statement: select * from scale1(:idd1); inside procedure it throws error but outside it executes successfully.

Thanks Much,

Abhishek

vivekbhoj
Active Contributor
0 Kudos

Hi Abhishek,

Yeah after inserting data it is giving same error as mentioned by you

The problem is you are taking nvarchar as input to function scale and it is not able to process

    idd1 := :idd[1];

But if you take any integer as input it works fine:

CREATE FUNCTION scale2 (val INT) RETURNS TABLE (FRM INT, WEIGHT INT)

LANGUAGE SQLSCRIPT AS

BEGIN

RETURN SELECT FRM, :val * WEIGHT AS WEIGHT FROM CUSTOMERCONNECTIONS1;

END;

CREATE PROCEDURE ARRAY_AGG_TEST1()

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

   DECLARE idd integer ARRAY;

   DECLARE n,x Integer;

   DECLARE idd1 integer;

   tab = select WEIGHT from CUSTOMER1;

   idd := ARRAY_AGG(:tab.WEIGHT);

   n := CARDINALITY(:idd);

       idd1 := :idd[1];

       select * from scale2(:idd1);

END;

call ARRAY_AGG_TEST1() works fine and gives output

There is no need to use for loop as you are taking idd[1],  using for loop it gives 5 output results.

Regards,

Vivek

vivekbhoj
Active Contributor
0 Kudos

Can you please tell what are you trying to achieve with it?

Former Member
0 Kudos

Hi Vivek,

I am trying to achieve is:

  • i have a column which is not an integer, hence have used nvarchar.
  • Want to save the entire column as input variable for the next statement and traverse through it.

Thanks Much,

Abhishek