MSSQL Connector issue: How to execute a stored procedure with parameters, Microsoft SQL?

Q&A
1

Hello,

I have some stored procedure at Microsoft SQL Server, it returns a view ('myview') based on parameters passed:

The stored procedure ('requserinfo') has 3 parameters:  @fname; @lname;@locationcode .....

I want to use the procedure to get a user information in the form (something like: FirstName, LastName, Age, Location etc). It is going to be some kind of a search form... I don't have an access to the data source, only can execute the procedure so this is only the way to get the user information....

My current solution is to use MS SQL connector to get the data into the process variable (java.util.List) and then use it in the form to display the table. I tested the query syntax to call a stored procedure with MSSQL Server. Something like:

EXEC [myview].[requserinfo] @fname = N'JOHN',@lname = N'DOE',@locationcode = N'12345';

In the MSSQL query console it works just fine and returns me the data I want.

I have my server/user/password to use it as a connection string, so this should not be a problem.

I use following query in my connector (both versions returns same result):

Version-1:

DECLARE    @return_value int
EXEC    @return_value = [myview].[requserinfo]
        @prenom = N'JOHN',
        @nom = N'DOE',
        @etab = N'12345'
SELECT    'Return Value' = @return_value

Version-2:

EXEC [myview].[requserinfo] @fname = N'JOHN',@lname = N'DOE',@locationcode = N'12345';

 

My OUTPUT operations ('Takes value of') suppose to parse the resultset into a process variable (type: java.util.List). 

So now, when I try to use it in the connector I receive nothing (null as a result). :(

Checking the error message I can see that my query string returns nothing:

 java.lang.NullPointerException: Cannot invoke method getMetaData() on null object


Any thoughts & suggestions on how to use stored procedures with the connectors are welcome!

 Thank you!

1 Like
2

Hello,

Are you using the standard Bonita JDBC Connector?


I don't think this connector can execute a stored procedure. This connector executes an SQL request.

So:

1/ why don't you execute a SQL Request? You can use a view to execute it.

2/ else, why not create your own connector?

Saying that I have another question. You want to exploit this information in a form, isn't? Doing that, you execute your connector in the process and then saved the value in a process variable. You have then two concerns;

* you saved the value in the process. Do you need to keep this information? To archive it? It's better to save only the needed information.

* you don't have any refresh. Let's imagine the connector runs at 10:00 am in the morning. Users access the forms at 4:15 pm. If something changes between, you don't see it, because data are stored in the process.

The alternative is to query the information when you need it, I mean when you display the form. Rest API is the mechanism you look for. When the user accesses the form, you query at this moment your database. And you saved in the process not the complete list, but the information you need (what the user selects for example).

To implement that, you have two possibilities:

1/ develop your own Rest API Extension

2/ use a page, like the Gasoline page. This page branch an external database, then offer a Rest API.

Remarks: if you are aware of a performance issue on your database (in this situation, every time a user accesses the form, you send a request wherein the Connector implementation, you send only one request per case), then you can implement a Rest Api Factory request. The Rest API can cache the information for 1 day, for example, then when other users request the RestAPI, it uses the cached value and not recall the database.

Hope this help,

3

Hello! Thank you for your comment!

Just a few notes:

1) I'm limited to use only that specific view. I don't want to retrieve all the records (many-many records in the source), but just some specific (a stored procedure requires some parameters, as mentioned).

2) I'm able to use simple SELECT in the connector, but NOTHING like stored procedure call works (EXEC ?)

3) I guess REST API would be a better choice, specifically if I need to do a search-like form. Need to find out more on that option.

I will have a look at the examples suggested.

Thanks!