On Tue Nov 11 13:50:28 2014, july@imperia.bg wrote:
Show quoted text> Hi Martin,
>
> Here is the patch file:
> ----------------------------
>
> --- dbdimp2.c 2014-11-11 14:08:26.739154650 +0200
> +++ dbdimp.c 2014-11-11 15:29:08.419093790 +0200
> @@ -4177,6 +4177,25 @@
> * sloi = -500100 ps=0 sqlt=-3 (SQL_VARBINARY)
> * sloi = 4001 ps=0 sqlt=-9 (SQL_WVARCHAR) <--- this one fails
> without above
> */
> + if (phs->sql_type == SQL_VARBINARY && phs->param_size == 0
> + && phs->requested_type == 0 && imp_dbh->driver_type == DT_DONT_CARE
> + && strstr(imp_dbh->odbc_driver_name, "libmsodbcsql-11")
> + == imp_dbh->odbc_driver_name
> + && strcmp(imp_dbh->odbc_dbms_name, "Microsoft SQL Server") == 0 ) {
> + /*
> + printf("Driver name: %s\n", imp_dbh->odbc_driver_name);
> + printf("Driver type: %d\n", imp_dbh->driver_type);
> + printf("ODBC DBMS: %s\n", imp_dbh->odbc_dbms_name);
> + printf("Request type: %d\n", phs->requested_type);
> + printf("Param size: %d\n", phs->param_size);
> + # Driver name: libmsodbcsql-11.0.so.2270.0
> + # Driver type: 0
> + # ODBC DBMS: Microsoft SQL Server
> + # Request type: 0
> + # Param size: 0
> + */
> + column_size = 0;
> + }
>
> /*printf("sloi = %d ps=%d sqlt=%d\n", strlen_or_ind,
> phs->param_size, phs->sql_type);*/
>
>
> My /etc/odbc.ini config:
> ----------------------------
>
> [VirtualODBC]
> Description = VirtualODBC
> Driver = /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
> Database = imperia_i10
> Server = 10.29.0.184,1433
> AutoTranslate = No
>
>
> My /etc/odbcinst.ini config:
> ----------------------------
>
> [VirtualODBC]
> Description=Microsoft ODBC Driver 11 for SQL Server
> Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
> Threading=1
> UsageCount=1
>
>
> The test script:
> ----------------------------
>
> #!/usr/bin/perl
>
> use strict;
> use warnings;
> use DBI;
> use Storable qw (thaw);
>
> my ($dsn, $user, $pwd) = ($ARGV[0], $ARGV[1], $ARGV[2]);
>
> print "Connecting to the ODBC database.\n";
> my $h = DBI->connect("dbi:ODBC:$dsn", $user, $pwd,
> {
> AutoCommit => 0,
> PrintError => 1,
> RaiseError => 1,
> });
>
> eval {
> local $h->{'PrintError'} = 0;
> local $h->{'RaiseError'} = 0;
> $h->do(q/drop table metavarbin/);
> $h->commit;
> };
>
> print "Create 'metavarbin' test table.\n";
> $h->do(<<EOF);
> CREATE TABLE "metavarbin" (
> "doc_id" INTEGER,
> "meta_name" VARCHAR (255),
> "meta_value" VARBINARY(MAX),
> "meta_idx" INTEGER,
> "from_ver" BIGINT,
> "to_ver" BIGINT,
> PRIMARY KEY("doc_id", "meta_name", "meta_idx", "from_ver", "to_ver"));
> EOF
> $h->commit;
>
> $h->do(<<EOF);
> CREATE INDEX "metavarbin_idx" ON "metavarbin"("doc_id");
> EOF
> $h->commit;
>
> my $sth = $h->prepare('INSERT into metavarbin VALUES (?, ?, ?, ?, ?, ?)');
>
> my @args = (1234, 'name', " " x 8001, 0, '1234567890', '1234567899');
>
> print "Insert VARBINARY data over 8k.\n";
> $sth->execute(@args);
>
> # Changing the data to prevent errors due PK constraint.
> $args[0] = $args[0] . '1';
> print "Insert VARBINARY data up to 8k.\n";
> $args[2] = " " x 8000;
> $sth->execute(@args);
>
> $args[0] = $args[0] . '1';
> print "Insert VARBINARY data over 8k again.\n";
> $args[2] = " " x 8001;
>
> #use DBI ':sql_types';
> #$sth->bind_param(3, undef, {TYPE => SQL_VARBINARY});
>
> $sth->execute(@args);
>
> $h->commit;
>
>
> # $ perl odbc.pl VirtualODBC user password
>
> I've spent some time writing this script since it wasn't obvious what is
> actually happening. In our software we (re)use prepared statements for
> performance reasons. The problem is related to the 8000 size limit, but
> it does not affect the first execute() call, but rather the second one
> that exceed the 8k limit.
>
> I've tried all types of bind_param variations to overcome and rebind the
> params for the second call, but no luck.
>
> Additionally, in our software we use "bytes" over "utf-8", so we have
> disabled every conversion setting to utf that exists, but it seems this
> is not related to this problem after all, at least this is still
> reproducible with the perl script.
>
> Another issue I'm not sure what is the origin - if I uncomment the
> 'bind_param' lines at the end of the script I still receive the
> "[Microsoft][ODBC Driver 11 for SQL Server]Invalid precision value
> (SQL-HY104)" which probably means I don't know how to properly bind, by
> hand, that problematic column (VARBINARY(MAX)) with or without my patch.
> Probably that is another bug or some kind of knowledge lack on my side.
>
> Regards,
> Julian Lishev
Wow Julian, I wish everyone who reported an issue did as much work to help identify the problem.
Since my first reply I've tried to reproduce your problem with our MS SQL Server driver and could not so in the hope you would provide a reproducible script I've downloaded and installed the MS driver you are using. I'll look into your solution and also the issues you have when uncommented the bind params at the end of the script.
Hopefully, I'll be in a position to provide a new version you can try by the weekend.
BTW, the driver you are using is the same codebase as the Windows MS native client driver so I've already fixed the issue of the workarounds in DBD::ODBC for MS SQL Server not working with the linux driver.
Any column which uses MAX is a PITA because the driver does not report a size for the parameter. This seems to be the case that MS added max support to MS SQL Server before realising it didn't fit into ODBC too well.
Rhanks again for the detailed info - I'll get back to you soon.
Martin
--
Martin J. Evans
Wetherby, UK
