Geek asks and answers

"String data, correct truncation" through ODBC, but not through SQL Server Manager with the same query

I have a LAPP stack on one server, and MSSQL works on another. The former connects to the latter through Microsoft ODBC Driver 1.0 for Linux and usually works well.

One search page of our user does not work 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 into the MS SQL Server Manager query window connected to the same database, replace all the placeholders ?with what my script is switching to $sth->execute(), and run it, this does not lead to an error.

Why is this? (Does this mean an error in the ODBC or SSM driver, or is it just the difference between them?)

Even better, or, I suppose, my ultimate goal: how can I make my script behave like SSM? Currently, the only fix I see is to go through each column (and there are many, this is a union of 5 queries, each of which has many table joins) and find out how long each varchar column is that the query string maybe each one is too long, and then distinguishes all the placeholders in length. That is, I have to make my own truncation and have another code dependency - when our database provider decides to increase the length of the column, for example, then I need to go and reduce the truncation in any script column. Does the ODBC driver "just work" just like SSM does without complaining?

If I turn on tracing, I get one of them for each 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

, cs=... - , , -, , . cs, , , ? , SSM ?

UPDATE: DBD:: ODBC:: FAQ " ?" " " . , , , SQLDescribeParam ( 2, "%" )... , - , ? ?

UPDATE2: : " ODBC- Microsoft SQL Server 1.0 Linux, Microsoft ODBC Driver 11 SQL Server". , ... UPDATE2.5 libmsodbcsql-11.0.so.2270.0 , .

+3
1

bohica, DBD:: ODBC odbc_describe_param, uber-text:

# $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();
...
0

More articles:


All Articles