Is it possible to inform the size of the VARCHAR in a Namedparameter?

Asked

Viewed 169 times

6

I’m working with a tuning of an SQL Server database. Through the query below, I can see how the bank executed some of my heaviest queries.

SELECT TOP 30 deqt.TEXT AS Query,
              last_execution_time
FROM   sys.dm_exec_query_stats deqs
       CROSS APPLY sys.Dm_exec_sql_text(deqs.sql_handle) deqt
       CROSS APPLY sys.Dm_exec_query_plan(deqs.plan_handle) deqp
WHERE  ( deqt.TEXT LIKE '%SELECT AV.ID,%'
         OR deqt.TEXT LIKE '%SELECT ''DEPARTMENT'' AS NODE,%'
         OR deqt.TEXT LIKE '%SELECT COUNT(P.COD) FROM _PEDIDOS P%WHERE P.CONCLUIDO = 1  AND P.COD_LOJA in ( @P0 ) AND P.STATUS_PARCEIRO =  @P1%' )
         --1 = 1
       AND deqt.TEXT NOT LIKE 'select top 30%'
       AND last_execution_time > Getdate() - 1
ORDER  BY 1

The problem according to the DBA is that the query input parameter was of a different type from the column. So I changed the Java code to the following:

   final MapSqlParameterSource params = new MapSqlParameterSource()
    params.addValue('SKU', sku, Types.VARCHAR)
    params.addValue('ORGANIZATION_ID', organizationId, Types.BIGINT)

    List<AttributeValue> attributeValues = jdbcTemplate.query(SELECT_ATTRIBUTE_VALUES, params, { ResultSet rs, int idx ->

However, VARCHAR(8000) arrives, and the column is 255. Is there any way to inform this? someone knows?

java sql-server jdbc groovy

  • This java code of yours is incomplete, starting with semicolons, but the fact that the last line is truncated doesn’t help either. It gets easier if you provide a [mcve].

  • the rest of the code is not important, because I won’t post all my domain classes here. has no semicolon because I use Groovy. the important thing is the MapSqlParameterSource

1 answer

0


I decided to cast in SQL.

WHERE field = CAST(:FIELD AS VARCHAR(255))

Browser other questions tagged java sql-server jdbc groovy

You are not signed in. Login or sign up in order to post.