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/
