Hi I am executing a stored procedure I am getting its result when I run it from dbeaver with my desired parameters correctly, whereas for the same procedure I am getting null resultant when I try to execute procedure from code.
Below are the code calls
<code>procedurequery = entityManager.createNamedStoredProcedureQuery("UserManagementUsersReportModel.generate");
procedurequery.setParameter("START_DATE", formattedStartDate);
procedurequery.setParameter("END_DATE", formattedEndDate);
procedurequery.setParameter("DEF", parameters.get("def"));
procedurequery.setParameter("ABC", parameters.get("abc"));
List<Report> ReportList = procedurequery.getResultList();
<code>procedurequery = entityManager.createNamedStoredProcedureQuery("UserManagementUsersReportModel.generate");
procedurequery.setParameter("START_DATE", formattedStartDate);
procedurequery.setParameter("END_DATE", formattedEndDate);
procedurequery.setParameter("DEF", parameters.get("def"));
procedurequery.setParameter("ABC", parameters.get("abc"));
List<Report> ReportList = procedurequery.getResultList();
</code>
procedurequery = entityManager.createNamedStoredProcedureQuery("UserManagementUsersReportModel.generate");
procedurequery.setParameter("START_DATE", formattedStartDate);
procedurequery.setParameter("END_DATE", formattedEndDate);
procedurequery.setParameter("DEF", parameters.get("def"));
procedurequery.setParameter("ABC", parameters.get("abc"));
List<Report> ReportList = procedurequery.getResultList();
Moreover using entity as below:
<code>@NamedStoredProcedureQuery(
name = "Report.generate",
procedureName = "REPORT",
@StoredProcedureParameter(mode = ParameterMode.IN, name = "CREATED_START_DATE", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "CREATED_END_DATE", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "DEF", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "ABC", type = String.class),
resultClasses = Report.class
<code>@NamedStoredProcedureQuery(
name = "Report.generate",
procedureName = "REPORT",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "CREATED_START_DATE", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "CREATED_END_DATE", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "DEF", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "ABC", type = String.class),
},
resultClasses = Report.class
)
</code>
@NamedStoredProcedureQuery(
name = "Report.generate",
procedureName = "REPORT",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "CREATED_START_DATE", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "CREATED_END_DATE", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "DEF", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "ABC", type = String.class),
},
resultClasses = Report.class
)
I even got the parameters printed they are exactly the same I am using to run procedure from dbeaver
Parameter: ABC, Value: null
Parameter: CREATED_START_DATE, Value: 05-May-15
Parameter: DEF, Value: 1,2,79
Parameter: CREATED_END_DATE, Value: 08-May-24
Below is the query I am using in stored procedure
SELECT ulv.*, cau.CREATED_BY, cau.CREATED_DATE from CORE.DEF ulv
SET @SqlQuery = @SqlQuery + ' ulv.id IN (' + REPLACE(@DEF, ',', ',') + ')';
SET @SqlQuery = @SqlQuery + ' AND cau.abc IN (' + REPLACE(@ABC, ',', ',') + ')';
<code>SET @SqlQuery = '
SELECT ulv.*, cau.CREATED_BY, cau.CREATED_DATE from CORE.DEF ulv
left join CORE.ABC cau
on cau.id = ulv.id
where
'
IF @DEF IS NOT NULL
BEGIN
SET @SqlQuery = @SqlQuery + ' ulv.id IN (' + REPLACE(@DEF, ',', ',') + ')';
END
IF @ABC IS NOT NULL
BEGIN
SET @SqlQuery = @SqlQuery + ' AND cau.abc IN (' + REPLACE(@ABC, ',', ',') + ')';
END
</code>
SET @SqlQuery = '
SELECT ulv.*, cau.CREATED_BY, cau.CREATED_DATE from CORE.DEF ulv
left join CORE.ABC cau
on cau.id = ulv.id
where
'
IF @DEF IS NOT NULL
BEGIN
SET @SqlQuery = @SqlQuery + ' ulv.id IN (' + REPLACE(@DEF, ',', ',') + ')';
END
IF @ABC IS NOT NULL
BEGIN
SET @SqlQuery = @SqlQuery + ' AND cau.abc IN (' + REPLACE(@ABC, ',', ',') + ')';
END
Just to clarify if I remove these params in procedures and just run the query without where filters it gives me correct result in code and dbeaver both
Unable to understand whats wrong with the query params, same are giving result from dbeaver while not from code