CODEWITHSUNDEEP
javaspring-jdbcsimplejdbccall
user3434028
user3434028

Reputation: 11

alternate way to execute stored proc using simplejdbccall

Unable to capture a readable sql on execution of stored procedure using SimpleJdbcCall. The SQL Server profiler for my execution will show a lengthy SQL which is not readable as the value is not next to the parameter. Is there any other way I can execute the stored procedure preferably using spring-jdbc which creates readable sql something similar to shown below under Expected?

I tried to build SimpleJdbcCall using useInParameterNames and/or withoutProcedureColumnMetaDataAccess and/or not-declaring-input-parameters with no positive result

My DAO:

SimpleJdbcCall call = new SimpleJdbcCall(dataSource)
            .withProcedureName("UpdateSomethingProc");
 final SqlParameterSource in = new MapSqlParameterSource()
                .addValue(parm1, null)
                .addValue(parm2, null)
                .addValue(parm3, null)
                .addValue(parm4, "1")
                .addValue(parm5, "987654321")
...
                .addValue(parm21, null);
                .addValue(parm22, null);
call.execute(in);

Stored Proc Definition:

CREATE      PROCEDURE [dbo].[UpdateSomethingProc] 
@parm1  varchar(16) = NULL,
@parm2  integer     = NULL,
@parm3  varchar(16) = NULL,
@parm4  char(3) ,
@parm5  char(18),
@parm6  T_DBID,
@parm7  char(3) ,
@parm8  char(1) ,
@parm9  char(4) ,
@parm10 smallint    = 0,
@parm11 char(4)     = NULL,
@parm12 char(4)     = NULL,
@parm13 smallint    = NULL,
@parm14 smallint    = NULL,
@parm15 smallint    = NULL,
@parm16 smallint    = NULL,
@parm17 smallint    = NULL,
@parm18 char(4)    = NULL,  
@parm19 char(4)    = NULL,  
@parm20 char(4)     = NULL,
@parm21 smallint    = 0,        
@parm22 smallint    = 0

Expected: Legacy application using C++ ends up with following SQL in the SQL Server Profiler which is more readable as it shows parameter name and respective value.

exec dbo.UpdateSomethingProc @param1='0x9',@parm2='1',@parm3='987654321',@parm4='ABC',@parm5='000',@parm6='1',@parm7='505B',@parm8='0',@parm9='999',@parm10='',@parm11='0',@parm12='0',@parm13='0',@parm14='0',@parm15='0',@parm16='2019/11/11 23:54:35',@parm17='HUB',@parm18='0'

Actual: New application using Spring Boot ends up with following SQL in the SQL Server Profiler. As you notice, it is hard to read which parameter has which value. Especially, when troubleshooting. 

exec sp_executesql N'EXEC dbo.UpdateSomethingProc @P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15, @P16, @P17, @P18, @P19, @P20, @P21  ',N'@P0 nvarchar(4000),@P1 int,@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 smallint,@P10 nvarchar(4000),@P11 nvarchar(4000),@P12 smallint,@P13 smallint,@P14 smallint,@P15 smallint,@P16 smallint,@P17 nvarchar(4000),@P18 nvarchar(4000),@P19 nvarchar(4000),@P20 smallint,@P21 smallint',NULL,NULL,NULL,N'1',N'987654321',N'ABC',N'000',N'2',N'505B',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N'2019-11-12T00:14:29.859Z',NULL,NULL,NULL,NULL

Upvotes: 0

Views: 1594

Answers (1)

misterManager
misterManager

Reputation: 1174

This is the way that just about any ORM is going to call out to a stored proc (or any other parameterized sql statement for that matter). It is going to ensure that values are explicitly passed and safe from sql injection etc.

I am not familiar with JDBC but it looks like it has the concept of "prepared statements". If the formatting of the sql is especially important to you, I would use a prepared statement.... pseudo code:

query = connection.preparedStatment('exec sp_MyProc @param1=?, @param2=?');

query.supplyValue(1, value1);
query.supplyValue(2, value2);

query.execute

bear in mind this will result in a similar statement to the one you are already seeing but you will have a slight bit more control of how verbose it is.

Upvotes: 1

Related Questions