Re: [Tcl-tdbc] Issue with tdbcodbc using ODBC Driver 17 for SQL Server

[Tom G. <Tom...@si...>]

Ok, I can hear the laughter already!

I figured out my own issue this morning after a fresh cup of coffee and 
good night sleep.  The format of my SQL statement was not valid.  After 
some small change to the code, it is working fine today.

Sorry for the knee-jerk reaction of the "issue" in tdbc!  You Tcl 
developers rock!

On 1/22/2020 3:29 PM, to...@si... wrote:
>
> 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!
>
>
>
> _______________________________________________
> Tcl-tdbc mailing list
> Tcl...@li...
> https://lists.sourceforge.net/lists/listinfo/tcl-tdbc