Jump to content
Changes to the Jaspersoft community edition download ×

Can JasperSoft Studio use MSSql functions with 2 parameters?


mdavidson_2
Go to solution Solved by mdavidson_2,

Recommended Posts

I have a function declared on MSSql Server.  The function works as intended on the MSSql Server.  When I try to call it in a select statement in JasperSoft Studio 6.5, I'm getting syntax errors and no results.  The error is specific to the call to the function.

Function name: dbo.GetPersonTypeDesc( )

The select statment looks like this:

SELECT dbo."Incident"."IncidentID",       dbo."Incident"."IncidentTypeENum",       dbo."Person"."PersonTypeCde",       dbo.GetPersonTypeDesc(Incident.IncidentTypeENum,Person.PersonTypeCde) as 'VALUE'FROM   dbo."Incident"          LEFT INNER JOIN dbo."Person" ON             dbo."Incident"."IncidentID" = dbo."Person"."IncidentID"[/code]

What's the proper syntax to call the procedure?

Link to comment
Share on other sites

  • Replies 4
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

If its a database function, your syntax could be correct. I'm saying could be because I dont know the in/out parameters for the function. 

In order to debug properly, test your function in SSMS first and see if it gives you the required results.

your post is contradictory. have you created a function or procedure ?.

if dbo.GetPersonTypeDesc is a procedure, then the language should be selected as plsql and it's a different syntax

Check here for more info on the execution of MS SQL procedures. https://community.jaspersoft.com/wiki/how-execute-ms-sql-stored-procedure-jasperreports-server

Link to comment
Share on other sites

  • 2 weeks later...
  • Solution

I'm going to post what fixed my issue in case another user ends up with a similar problem:

To reiterate the problem:  I was trying to reference a scalar function declared on a MSSql Server through jasperstudio.  I was getting syntax errors and no dataset would populate in the "data preview". 

What I was doing wrong:  I was focusing on the "syntax error issue".  The problem was not with my syntax referencing the function.  The problem was the data adapter that I was using.  I had used the net.sourceforge.jtds.jdbc.Driver as my adapter to this point.  I was hitting many different databases (Postgres, MySQL, MSSql...) with it without issue.  *BUT*  in order to use the function declared on the MSSql db Server, I needed to create a new data adapter using the com.microsoft.sqlserver.jdbc.SQLServerDriver driver.  NOTE: The "Dataset and query editor dialog" still displayed a syntax error on the line in the select statement that referenced the function, but the data set was sucessfully returned as expected.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...