Statalist


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

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


From   Ben Jann <ben.jann@gmail.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: RE: Problem with -odbc insert- in Stata 11: padded strings
Date   Fri, 27 Nov 2009 10:18:44 +0100

Thanks, Joseph. I wonder whether this change in behavior between Stata
10 and Stata 11 was intended. I guess not.
ben

On Thu, Nov 26, 2009 at 5:36 PM, Joseph Coveney <jcoveney@bigplanet.com> wrote:
> 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/
>

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