Statalist


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

st: Re: Problem with -odbc insert- in Stata 11: padded strings


From   "Joseph Coveney" <jcoveney@bigplanet.com>
To   <statalist@hsphsun2.harvard.edu>
Subject   st: Re: Problem with -odbc insert- in Stata 11: padded strings
Date   Sat, 28 Nov 2009 02:03:34 +0900

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/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index