Using Tcl with tdbc over MS SQL on Win x64
We have been using the tclodbc package to run queries against MS SQL Server for several years. Now we need to switch to x64 process and tclodbc won't load because there is no x64 version.
We started looking into alternatives and tried tdbc, but we are having problems with character fields passed as parameters. I created a test table with 2 integer columns and 2 varchar columns (50 and 100 long).
I try to insert records and the following happens: 1. If I insert records containing only 2 integer parameters everything works fine - 5 records are inserted (see code below). 2. If I insert records containing only one character, then 1 record is inserted and then the script crashes with the following error: "[Microsoft] [ODBC Driver 11 for SQL Server] String data, correct truncation" 3. If I pass two character parameters, or an integer parameter and a character parameter, then nothing will be inserted - I get the same error as above.
I have not tested other datatypes, but it looks like there is something wrong with the character parameters (I also tried to use nvarchar and char columns).
Here is my code:
package require tdbc::odbc
set con "Driver=\{ODBC Driver 11 for SQL Server\};server=server;database=database;Intergrated Security=True;Trusted_Connection=Yes"
tdbc::odbc::connection create db $con
set insert_cmd [db prepare {INSERT INTO dbo.testing_tdbc (f50,f100) VALUES(:f50,:f100) } ]
$insert_cmd paramtype f50 char 50
$insert_cmd paramtype f100 char 100
foreach fint [list 1 2 3 4 5] {
set fi1 $fint
set fi2 [expr {$fi1*2}]
set f50 "${fi2}_${fi1}"
set f100 $f50
$insert_cmd execute
}
$insert_cmd close
db close
I've tried the same thing with the ODBC 13 Driver for SQL Server - all with the same result.
source to share
[Microsoft] [ODBC Driver 11 for SQL Server] String Data, Right Truncation
As you probably know, this error is caused by inserting a row longer than the declared precision of the column.
According to the tdbc :: statement, the syntax for $stmt paramtype
:
$stmt paramtype ?direction? type ?precision? ?scale?
So I'm not really sure what you mean by passing f50
like direction
here (which should be one of:. in, inout, out
I think you can just omit the call $stmt paramtype
and go directly to execution $insert_cmd
using, for example $stmt execute ?dict?
:
set insert_cmd [db prepare {INSERT INTO dbo.testing_tdbc (f50,f100) VALUES(:f50,:f100) } ]
foreach fint {1 2 3 4 5} {
set value [expr {$fint*2}]_$fint
set bindings [dict create f50 $value f100 $value]
$insert_cmd execute $bindings
}
$insert_cmd close
db close
source to share