2

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?

tomaz
  • 493
  • 4
  • 13

2 Answers2

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

geisterfurz007
  • 5,292
  • 5
  • 33
  • 54
Franz Schwab
  • 11
  • 1
  • Thanks for the tips. I've solved the problem by using the `DBI::sqlInterpolate()` function. – tomaz Sep 13 '19 at 11:49
0

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.

tomaz
  • 493
  • 4
  • 13