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 <[email protected]> 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/
