topic Re: Help with using a SQL stored procedure to pull data using JSL in Discussions

Help with using a SQL stored procedure to pull data using JSL

PleaseRecycle — Mon, 05 Aug 2024 22:01:40 GMT

Good evening,

I'm a beginner to JSL. I think suspect that I have a syntax error or something.

I'm trying to run an SQL stored procedure from a JSL script. My database connections seem to work correctly (see when the query is a select statement). However, I can't get the stored procedure to run and give me the data table. Below I've listed:

1) the query formula that works (the select statement) and

2) the three query formulas that won't run my stored procedure.

Thank you for taking a look. --Birdi (JMP 17.0.0)

// HELP:  The select statement works but the stored procedure doesn't.  
//Does anyone have an idea as to what I'm missing?
 
// I can run the select statement, but I could use some help on running the stored procedure.
//query="SELECT * FROM [dbo].[tblCost_Outliers]"; // works
query = "exec dbo.procCost_PM_Birdi"; // doesn't work
//query="begin dbo.procCost_PM_Birdi; end;"; // doesn't work
//query = "dbo.procCost_PM_Birdi"; // doesn't work
 
 
//create connection
dbc = Create Database Connection(
	"DSN=*****;UID=*****;PWD=*****;APP=JMP;WSID=*****;DATABASE=*****;"
);
 
//get result
Execute SQL( dbc, query ); 
 
//tidyup
Close Database Connection( dbc );

What does that mean to use <JSL> to insert JSL code? Yep, I'm new. Thank you for your patience.

Re: Help with using a SQL stored procedure to pull data using JSL

pmroz — Fri, 01 Dec 2023 14:41:42 GMT

I'm not sure what database you're using. I use Oracle which has different syntax, but there are some general principles that may be helpful:

Execute SQL cannot return table data from a call to stored procedure directly.
If your stored procedure creates a table, make a second call to retrieve that data.

This works in Oracle:

execproc = "begin myuser.my_oracle_proc; end";
query    = "SELECT * FROM myuser.mytable";
 
// Run the procedure to load the table tblCost_Outliers
Execute SQL( dbc, execproc ); 

// Retrieve the table into the variable dt
dt = Execute SQL( dbc, query ); 
 
//tidyup
Close Database Connection( dbc );

Maybe JMP tech support can help you with the correct syntax for calling a procedure for your database.

Re: Help with using a SQL stored procedure to pull data using JSL

Dahlia_Watkins — Thu, 07 Dec 2023 15:40:37 GMT

See https://community.jmp.com/t5/Discussions/SQL-query-to-ODBC-datasource-SQL-server-with-temporary-tables/m-p/457582#M70262. This will help you with this issue since you are using SQL Server.