External Stored procedures usage

SrikanthGutha

Hi there

 

I have created a stored procedure using shell script and registered the stored procedure. I can invoke the stored proc but it doesn’t serve my purpose. I am using a global temp table inside the stored proc but stored proc doesn't have access to the data that is in global temp table.

 

Example query - 

1. Open a session in SQLWorkBench and execute the following query. It inserts one row into GlobalTempTable1

 

Insert Into GlobalTempTable1 (CompanyId, StartDate, EndDate, DataVendorID, SortOrder, ContributorID)
Values (23567, '6/20/2016', '6/21/2016',  10, 1, 256)

 

1. Execute the following stored proc in the same session opened in step 1. 

 

select test_proc();

 

But Test_proc() is not able to access the data inserted in step 1

 

Any ideas or working examples on how to use temp tables in Vertica external stored procedure?

 

Thanks in advance!

 

Srikanth

Rahul_Choudhary

Hi 

 

Can you site with an example step you have used to access that data and what error message that you have received?

 

Regards

Rahul Choudhary

SrikanthGutha

Hi Rahul,

 

Thanks for the reply!

 

Sure let me give more details with a better example

 

Step 1 - Open a session in SQLWorkBench

 

I already have a global temp table created in database and the global temp table name is CompanyInput_tbl.

 

In SQLWorkBench, I insert two input rows in to CompanyInput_tbl with the following query 

 

Insert Into CompanyInput_tbl (CompanyId, DataVendorID, ContributorID)
Values (23567, 10, 256)

 

Insert Into CompanyInput_tbl (CompanyId, DataVendorID, ContributorID)
Values (777812, 1, 14)

 

Step 2 - Execute the Test_Proc stored proc which was created already. Test_Proc is an external stored procedure installed and created through a shell script. Here is the query used inside the stored proc - 

 

Select c.companyId, c.companyName, c.companyTicker, c.companyType 

From Company_tbl c

           Inner Join CompanyInput_tbl ci

               On c.companyId = ci.companyId

               and c.dataVendorId = ci.dataVendorId

               and c.contributorId = ci.contributorId

Where ci.companyName is not null

 

Company_tbl is a physical data table.

 

It doesn’t give any error message nor it gives any rows from the stored proc even though there are rows in the data table. CompanyInput_tbl seems to empty inside the stored proc execution. It is not reading the input rows that I inserted in step 1.  

 

Please let me know if any question.

kaurora

From what I can tell it looks like the shell script is setting up another session. 

Did you try with an actual table? Does that work?

eli_revach

Hi,
You dont share your external procedure , i am assuming you running shall script that include vsql command .

If this true , it make sense you dont see data ,as your vsql session is opening new vertica session .

And your temp table data is available only the SQLworkbenk .

Hope i will fing helpful

Thanks.