Ben Jann wrote:
Thanks, Joseph. I wonder whether this change in behavior between Stata
10 and Stata 11 was intended. I guess not.
--------------------------------------------------------------------------------
The change in behavior comes as a surprise to me, too.
I did some more experimentation today. The workaround that I suggested
yesterday (manually create the table beforehand with a VARCHAR() datatype)
works for the desktop database program that I happened to have used for
illustration (Microsoft Access).
But it *doesn't* work for a client-server RDBMS (Microsoft SQL Server). The
only kludge that I got to work for this is version control, i.e., prefixing the
Stata command with 10.1 (see below).
I know that Stata 11 has changed the insertion algorithm in order to speed up
-odbc insert- operations, but this new behavior doesn't seem likely to be an
intentional trade-off.
So, I'm guessing that you guessed correctly, and that this is something for
tech support.
Joseph Coveney
. version 11.0
.
. clear *
. set more off
.
. local DDL CREATE TABLE Sandbox.dbo.Strings ( ///
> release_nr CHAR(4) NOT NULL, ///
> string_column VARCHAR(10) NOT NULL ///
> );
. odbc exec("`DDL'"), dsn(Sandbox)
.
. quietly set obs 3
. generate str4 release_nr = "11.0"
. generate str string_column = "a"
. quietly replace string_column = string_column + "bc" in 2/l
. quietly replace string_column = string_column + "de" in l
. generate byte space_index = strpos(string_column, " ")
. list s*, noobs abbreviate(15)
+-----------------------------+
| string_column space_index |
|-----------------------------|
| a 0 |
| abc 0 |
| abcde 0 |
+-----------------------------+
.
. odbc insert release_nr string_column, table(Strings) ///
> dsn(Sandbox) insert sqlshow
INSERT INTO Strings (release_nr, string_column) VALUES (?, ?)
.
. quietly replace release_nr = "10.1"
. version 10.1: odbc insert release_nr string_column, table(Strings) ///
> dsn(Sandbox) insert sqlshow
INSERT INTO Strings (release_nr, string_column) VALUES ('10.1', 'a')
INSERT INTO Strings (release_nr, string_column) VALUES ('10.1', 'abc')
INSERT INTO Strings (release_nr, string_column) VALUES ('10.1', 'abcde')
.
. local query SELECT * FROM Strings ///
> WHERE string_column LIKE '% ';
. odbc exec("`query'"), dsn(Sandbox)
1. +------------------------------------------------------------------+
|release_nr | 11.0 |
|string_column | a |
+------------------------------------------------------------------+
2. +------------------------------------------------------------------+
|release_nr | 11.0 |
|string_column | abc |
+------------------------------------------------------------------+
.
. odbc load, table(Strings) dsn(Sandbox) clear
. generate byte space_index = strpos(string_column, " ")
. list, noobs abbreviate(15) sepby(release_nr)
+------------------------------------------+
| release_nr string_column space_index |
|------------------------------------------|
| 11.0 a 2 |
| 11.0 abc 4 |
| 11.0 abcde 0 |
|------------------------------------------|
| 10.1 a 0 |
| 10.1 abc 0 |
| 10.1 abcde 0 |
+------------------------------------------+
.
. odbc exec("DROP TABLE Strings;"), dsn(Sandbox)
.
. exit
end of do-file
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
