"String data, right truncation" via ODBC but not via SQL Server Manager with the same query

I have a LAPP stack on one server and MSSQL running on another. The former connects to the latter via Microsoft's ODBC Driver 1.0 for Linux and generally works well.

One search page of ours fails with the following error if the query string is too long:

Couldn't execute statement: [unixODBC][Microsoft][SQL Server Native Client 11.0]String data, right truncation (SQL-22001)

However, if I copy the query, paste it in a MS SQL Server Manager query window connected to the same database, replace all the ? placeholders with exactly what my script passes to $sth->execute(), and run it, it does not produce an error.

Why is this? (Does this indicate a bug in the ODBC driver, or SSM, or just a difference between the two?)

Better yet, or I suppose my end goal, would be: how can I get my script to behave like SSM does? Currently the only fix I can see is to go through every single column (and there are many, it's a union across 5 queries each of which has many table joins) and find out what the length of every varchar column is that the query string might every be too long for, and then differentiate all the placeholders by length. I.e. I have to do my own truncation and have yet another code dependency--when our database vendor decides to lengthen a column, for example, then I have to go and lessen the truncation in any script the column appears in. Can't the ODBC driver "just work" the same way SSM does, without complaining?

If I enable tracing, I get one of these per placeholder:

-dbd_bind_ph=rebind_param
+rebind_param 7 '%aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa%' (size svCUR=43/SvLEN=48/max=0) svtype:8, value type:1, sql type:0
+get_param_type(7f22ca950e88,7)
  bind 7 '%aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...' value_len=43 maxlen=47 null=0)
  bind 7 value_type:1 VARCHAR cs=20 dd=0 bl=43
-rebind_param
+dbd_bind_ph(7f22ca950e88, name=8, value='%aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa%', attribs=, sql_type=0(unknown), is_inout=0, maxlen=0
  First bind of this placeholder

It seems the cs=... is what varies and seems to correspond to the maximum placeholder size I could get away with in that context. Is there a way to access that cs value so I can limit the value before I bind it, but dynamically so that I don't have these extra code dependencies? Perhaps that's what SSM does automagically?

UPDATE: I found in the DBD::ODBC::FAQ under "Why am I getting errors with bound parameters?" under "data truncated error" a description more or less of this problem. Is it really true that I'd have to loop through every parameter to fix it to the length returned by SQLDescribeParam (minus 2, so I can add '%' signs for searching at the start and end)...but that that's not even guaranteed to work if I use any kind of join because it may get the length wrong? What's wrong with this picture?

UPDATE2: I just found here that, "If you are using the Microsoft SQL Server ODBC Driver 1.0 for Linux, you should upgrade to the Microsoft ODBC Driver 11 for SQL Server." Off to try that...UPDATE2.5 on the latest libmsodbcsql-11.0.so.2270.0 driver, it also exhibits this behaviour.

Answers


Thanks to bohica, DBD::ODBC now exports a function odbc_describe_param that exposes the necessary information for a workaround for the uber-text-search situation:

# $sth is an already-prepared statement
for (1..(scalar keys %{$sth->{ParamTypes}})) {
    my @help = $sth->odbc_describe_param($_);
    if ($help[1]) { # like SQLDescribeParam API - i.e., type, size, decimal digital and nullable
        $sth->bind_param($_, '%' . substr($s, 0, $help[1] - 2) . '%');
    } else { # 0 = false = unlimited length allowed
        $sth->bind_param($_, '%' . $s . '%');
    }
}
$sth->execute();
...

Need Your Help

Altering lazy-loaded object's private variables

c# nhibernate accessibility

I'm running into an issue with private setters when using NHibernate and lazy-loading. Let's say I have a class that looks like this:

How to connect to a Google account from shell

shell gmail wget google-account

I would like to connect from shell to a Google account in order to simulate navigation from this account with something like wget.