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   Fri, 27 Nov 2009 01:36:50 +0900

Ben Jann wrote:

I have a problem with -odbc- in Stata 11. When using -odbc insert- to
write data into an SQL table then strings get padded with white space.
For example, string

 "abc"

will be stored as

 "abc  "

if the variable is str5. This behavior makes it very difficult to work
with variables that contain strings of varying length.

In Stata 10, using the exact same commands, the strings were stored
without the added white space.

Does anyone know how to make Stata 11 not add the white space?

--------------------------------------------------------------------------------

I hadn't realized that the behavior changed between Stata releases.  

The only thing that I could suggest is to create the table manually, specifying 
VARCHAR(), and then use -odbc insert . . ., insert-, instead of having Stata 
create the table on-the-fly in an -odbc insert . . ., create- command, which 
appears to specify CHAR() with the length of the Stata string variable.  See 
the example below for details.

Joseph Coveney

. version 11.0

. clear *

. set more off

. 
. set obs 1
obs was 0, now 1

. generate str5 string_column = "abc"

. 
. local source examples.oreilly.com/9780596527600/ChapterSamples/Chapter01/

. copy http://`source'Bobblehead.accdb F:\Database1.accdb

. 
. local dsn MS Access Database;DBQ=Database1.accdb;DefaultDir=F:\;

. local query SELECT string_column, LEN(string_column)

. local query `query' AS string_length FROM Test;

. 
. /* Stata creates table */
. odbc insert, table(Test) dsn("`dsn'") create sqlshow

CREATE TABLE Test (string_column CHAR(5))
INSERT INTO Test (string_column) VALUES (?)

. odbc exec("`query'"), dsn("`dsn'")

    1. +------------------------------------------------------------+
       |string_column | abc                                         |
       |string_length | 5                                           |
       +------------------------------------------------------------+



. odbc exec("DROP TABLE Test;"), dsn("`dsn'")


. 
. /* Manually create table beforehand */
. local DDL CREATE TABLE Test (string_column VARCHAR(10) NOT NULL);

. odbc exec("`DDL'"), dsn("`dsn'")


. 
. odbc insert, table(Test) dsn("`dsn'") insert

. odbc exec("`query'"), dsn("`dsn'")

    1. +------------------------------------------------------------+
       |string_column | abc                                         |
       |string_length | 3                                           |
       +------------------------------------------------------------+



. 
. erase F:\Database1.accdb

. 
. 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