Nested Stored Procedure Support in Vertica
We are migrating our product from Sybase IQ to Vertica. We have lot of stored procedures written in Sybase. As Vertica doesn't support stored procedures, we developed few Java UDx which are working as fine.
We are now facing few problems as we are moving forward with migration, lot of our stored procedures are very complex and nested. Nested as in few of the procedure are calling other procedures.
How can we replicate similar functionality using Java UDx? Say there are two Java UDXs, A and B. How exactly B can be called by A? How code reusability is achieved.
One little additional question along with above. There are several types of procedures, few accepting in, out and inout parameters. How can we write such a UDx in Vertica?
Quick response will be highly appreciated.
Thanks,
Abhishek
Hi!
Disclaimer: it's not an official response(Im not Vertica employee), just an user2user help.
How can we replicate similar functionality using Java UDx? Say there are two Java UDXs, A and B. How exactly B can be called by A? How code reusability is achieved.Unfortunately you can't, but you can open a feature request, or as alternative you can achieve it via JDBC and ODBC.
Disclaimer: it's not an official response(Im not Vertica employee), just an user2user help.
It's very simple. Answer is quite logical. Create your procedure B, register it. Create procedure A and simply call B using select B() using JDBC and get data in resultset.
+2The following mechanism of nesting may work depending on your procedures
select procedureA ( procedureB (col1, col2), col3) from Table T;
this is akin to
select Avg( Sum(c1,c2), c3) from Table T;
select procedureA ( procedureB (col1, col2), col3) from Table T;
this is akin to
select Avg( Sum(c1,c2), c3) from Table T;
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
