Error in Passing parameter to Procedure in v ertica

malar_gopal1

Hi , My Concept is , I just want to selecting name for the parameter I just pass it on run time using external procedure. 1)I have created a sh file. It runs perfectly. 2)Created the Procedure also which calls the .sh file. 3)When I run the procedure I get error. I showed below. CaN any one help me out. .sh file: ---------- vsql -c "select name from test where id='read id; echo $id'"; output: ------- [dbadmin@cch1utbia7 malar]$ sh q.sh 1 name ------ a (1 row) Create Procedure: ------------------ create procedure a17(arg1 int) as 'q.sh' language 'external' user 'dbadmin'; CREATE PROCEDURE Executing Procedure: -------------------- dbadmin=> select a17(1); INFO 4427: Procedure reported: Could not execute procedure, errno=8 ERROR 4424: Procedure execution error: exit status=1 Regards, Malar

Navin_C

Hello Malar, First of all Vertica External procedures are not like other database procedures. The procedures in vertica don't give output. They can give output in 0 or 1 0 stands for procedure executed successfully 1 stands for procedure not executed successfully Secondly, Every shell script which is used for External procedure should end with exit 0 as per Vertica Documents The file should end with exit 0, and exit 0 must reside on its own line. This naming convention instructs Vertica to return 0 when the script succeeds.

malar_gopal1

Hi Navin , Thanks for the reply . Yes as u said Exit 0 is must. When I run a procedure with out any argument I get the output with exit 0 status. FYI: create procedure a14() as 'p1.sh' language 'external' user 'dbadmin'; CREATE PROCEDURE dbadmin=> select a14(); INFO 4427: Procedure reported: id | name | salary ----+------+-------- 1 | a | 12 2 | b | 12 3 | c | 12 (3 rows) a14 ----- 0 (1 row) But I gte an error when I run a procedure with argument . Can U pls tell me where I went wrong (in the previous post). Or How to achieve this goal . Thanks, Malar