|
From: <to...@si...> - 2020-01-22 21:48:02
|
Good morning,
I have been working on scripting processes for customers of mine and ran
into what appears to be a problem in tdbc when performing INSERT INTO
<tblname>(<column>, <column>, ...) VALUES(<value>, <value>, ...);
I was successful building the table using the tdbc::odbc and doing
INSERT rows as above if I _hard-code_ the values.
Instead, if I use VALUES(':value1', ':value2', ...) using single-quotes
around character fields, none around numeric fields and attempt to bind
variables into the value fields, the process breaks with the following
SQL Server messages:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary
data would be truncated.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has
been terminated.
(executing the statement)
If I do not use the single-quotes around the character fields, it fails
instead with:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String data, right
truncation.
(executing the statement)
Yes, I have verified that the data values being inserted are equal to or
less in length than the column definitions in the table. In fact to
ensure there would not be any problems, all columns were deliberately
created larger than necessary. And as I stated previously, if I
hard-code the values into a SQL statement an
use tdbc::odbc to insert the data, it works fine. Here is a example of
the hard-code module I ran:
package require tdbc::odbc
tdbc::odbc::connection create db_conn "DRIVER={ODBC Driver 17 for SQL
Server};DATABASE=<dbname>;SERVER=<svrnm>;Uid=<usrid>;Pwd=<usrpwd>;"
set eDocsPut [db_conn prepare { INSERT INTO <tblnm>(col1, col2, col3,
col4, col5, col6, col7) VALUES('XXXX', 2362401134, 000000002,
'20130102040038_edocs_d130101_t064907_1', 2362401134000, 00000016750,
00000032749);}]
$eDocsPut execute
$eDocsPut close
db_connn close
The above code successfully inserts the row into the table. The
following code however, does not.
package require tdbc::odbc
tdbc::odbc::connection create db_conn "DRIVER={ODBC Driver 17 for SQL
Server};DATABASE=<dbname>;SERVER=<svrnm>;Uid=<usrid>;Pwd=<usrpwd>;"
set eDocsPut [db_conn prepare { INSERT INTO <tblnm>(col1, col2, col3,
col4, col5, col6, col7) VALUES(':col1', :col2, :col3, ':col4', :col5,
:col6, :col7);}]
set col1 "XXXX"
set col2 2362401134
set col3 000000002
set col4 "20130102040038_edocs_d130101_t064907_1"
set col5 2362401134000
set col6 00000016750
set col7 00000032749
$eDocsPut execute
$eDocsPut close
db_connn close
Returning the row inserted by the hard-coded process (which also works
successfully using tdbc::odbc!) we get:
col1 {XXXX } col2 2362401134 col3 2 col4
{20130102040038_edocs_d130101_t064907_1 } col5 2362401134000
col6 16750 col7 32749
Any help you could provide is Greatly appreciated!
|