parameterized query with long string

Question

I have a parametrized SQL query that I want to execute from (local) R on Exasol database as described here: https://db.rstudio.com/best-practices/run-queries-safely/#parameterized-queries.

with tab as 
   (select 
      t.*, 
      position(value in ?) as pos
      from MY_TABLE t
   )
select * from tab where pos > 0;

The value that is passed to ? is a (long) string. When this string is 2000 characters long or less, everything works fine. When I increase it to 2001 characters, I get an error:

Error in result_bind(res@ptr, as.list(params)) : 
nanodbc/nanodbc.cpp:1587: 40001: [EXASOL][EXASolution driver]GlobalTransactionRollback 
msg: data exception - string data, right truncation. (Session: 1640027176042911503) 

I guess the source of the problem is that my parameter is recognized as CHAR and not as VARCHAR. The Exasol User Manual states: "The length of both types is limited to 2,000 characters (CHAR) and 2,000,000 characters (VARCHAR), respectively".

Is there any way to cast ? to VARCHAR?

Answer 1

If you establish your db connection via ODBC you could try having a look at these parameters:
MAXPARAMSIZE and DEFAULTPARAMSIZE.

Probably, if you set DEFAULTPARAMSIZE to a higher value in the odbc config:
https://docs.exasol.com/connect_exasol/drivers/odbc/using_odbc.htm?Highlight=varchar

Answer 2

The problem above has been present when I tried using the first suggested method for running parametrized queries described in tutorial here: https://db.rstudio.com/best-practices/run-queries-safely/. This first approach uses a combination of functions dbSendQuery() and dbBind().

My problem with long strings has been solved when I switched to the second (less safe) method which uses the sqlInterpolate() function.