Ben Jan <[email protected]> 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?
It would be helpful to know the exact driver Ben is using and the database he
is connecting to. I believe that the problem is driver specific and does not
affect all databases/drivers. If Ben will contact me off-list, I will work
through this with him and we can report back to the list with what we find.
Stata 11 uses parameterized inserts for speed, whereas Stata 10.1 and
before constructed SQL 'INSERT' statements. The use of parameterized
inserts should not cause the problem Ben is seeing. However, as Joseph
Coveney pointed out, Ben may be able to use -version 10.1- as a workaround
for now. From -help version-:
VERSION 10.1
5. odbc insert will insert data by constructing an SQL insert
statement and will not use parameterized inserts.
For those interested, below are my logs from running a test on
this problem using 3 different databases/drivers on both Windows
and Linux:
===========================MYSQL=========================
. set odbcmgr unixodbc
. local dsn "mysql_cert"
. local user "user(cert)"
. local pass "password(cert)"
. local temp_table "test"
. local query SELECT teststr, LENGTH(teststr)
. local query `query' AS string_length FROM test;
.
. version 10.1
. clear
. input str5 teststr
teststr
1. "abc"
2. "a"
3. "b"
4. end
.
. capture odbc exec("DROP TABLE `temp_table'"), dsn("`dsn'") `user' `pass'
. odbc insert, dsn("`dsn'") table("`temp_table'") create `user' `pass'
. odbc exec("`query'"), dsn("`dsn'") `user' `pass'
1. +-----------------------------------------------------------------+
|teststr | abc |
|string_length | 3 |
+-----------------------------------------------------------------+
2. +-----------------------------------------------------------------+
|teststr | a |
|string_length | 1 |
+-----------------------------------------------------------------+
3. +-----------------------------------------------------------------+
|teststr | b |
|string_length | 1 |
+-----------------------------------------------------------------+
. clear
. odbc load, dsn("`dsn'") table("`temp_table'") `user' `pass' noquote
. gen byte space_index = strpos(teststr, " ")
. list
+--------------------+
| teststr space_~x |
|--------------------|
1. | abc 0 |
2. | a 0 |
3. | b 0 |
+--------------------+
.
. version 11.0
. clear
. input str5 teststr
teststr
1. "abc"
2. "a"
3. "b"
4. end
.
. capture odbc exec("DROP TABLE `temp_table'"), dsn("`dsn'") `user' `pass'
. odbc insert, dsn("`dsn'") table("`temp_table'") create `user' `pass'
. odbc exec("`query'"), dsn("`dsn'") `user' `pass'
1. +-----------------------------------------------------------------+
|teststr | abc |
|string_length | 3 |
+-----------------------------------------------------------------+
2. +-----------------------------------------------------------------+
|teststr | a |
|string_length | 1 |
+-----------------------------------------------------------------+
3. +-----------------------------------------------------------------+
|teststr | b |
|string_length | 1 |
+-----------------------------------------------------------------+
. clear
. odbc load, dsn("`dsn'") table("`temp_table'") `user' `pass' noquote
. gen byte space_index = strpos(teststr, " ")
. list
+--------------------+
| teststr space_~x |
|--------------------|
1. | abc 0 |
2. | a 0 |
3. | b 0 |
+--------------------+
===========================MYSQL=========================
=======================SQLSERVER=========================
. set odbcmgr unixodbc
.
. local dsn "sqlserver_cert"
. local user "user(cert)"
. local pass "password(cert)"
. local temp_table "test"
. local query SELECT teststr, LEN(teststr)
. local query `query' AS string_length FROM test;
.
. version 10.1
. clear
. input str5 teststr
teststr
1. "abc"
2. "a"
3. "b"
4. end
.
. capture odbc exec("DROP TABLE `temp_table'"), dsn("`dsn'") `user' `pass'
. odbc insert, dsn("`dsn'") table("`temp_table'") create `user' `pass'
. odbc exec("`query'"), dsn("`dsn'") `user' `pass'
1. +-----------------------------------------------------------------+
|teststr | abc |
|string_length | 3 |
+-----------------------------------------------------------------+
2. +-----------------------------------------------------------------+
|teststr | a |
|string_length | 1 |
+-----------------------------------------------------------------+
3. +-----------------------------------------------------------------+
|teststr | b |
|string_length | 1 |
+-----------------------------------------------------------------+
. clear
. odbc load, dsn("`dsn'") table("`temp_table'") `user' `pass' noquote
. gen byte space_index = strpos(teststr, " ")
. list
+--------------------+
| teststr space_~x |
|--------------------|
1. | abc 4 |
2. | a 2 |
3. | b 2 |
+--------------------+
.
. version 11.0
. clear
. input str5 teststr
teststr
1. "abc"
2. "a"
3. "b"
4. end
.
. capture odbc exec("DROP TABLE `temp_table'"), dsn("`dsn'") `user' `pass'
. odbc insert, dsn("`dsn'") table("`temp_table'") create `user' `pass'
. odbc exec("`query'"), dsn("`dsn'") `user' `pass'
1. +-----------------------------------------------------------------+
|teststr | abc |
|string_length | 3 |
+-----------------------------------------------------------------+
2. +-----------------------------------------------------------------+
|teststr | a |
|string_length | 1 |
+-----------------------------------------------------------------+
3. +-----------------------------------------------------------------+
|teststr | b |
|string_length | 1 |
+-----------------------------------------------------------------+
. clear
. odbc load, dsn("`dsn'") table("`temp_table'") `user' `pass' noquote
. gen byte space_index = strpos(teststr, " ")
. list
+--------------------+
| teststr space_~x |
|--------------------|
1. | abc 4 |
2. | a 2 |
3. | b 2 |
+--------------------+
=======================SQLSERVER=========================
========================ACCESS===========================
. local dsn "access_cert"
. local user "user(cert)"
. local pass "password(cert)"
. local temp_table "test"
. local query SELECT teststr, LEN(teststr)
. local query `query' AS string_length FROM test;
.
. version 10.1
. clear
. input str5 teststr
teststr
1. "abc"
2. "a"
3. "b"
4. end
.
. capture odbc exec("DROP TABLE `temp_table'"), dsn("`dsn'") `user' `pass'
. odbc insert, dsn("`dsn'") table("`temp_table'") create `user' `pass'
. odbc exec("`query'"), dsn("`dsn'") `user' `pass'
1. +-----------------------------------------------------------------+
|teststr | abc |
|string_length | 5 |
+-----------------------------------------------------------------+
2. +-----------------------------------------------------------------+
|teststr | a |
|string_length | 5 |
+-----------------------------------------------------------------+
3. +-----------------------------------------------------------------+
|teststr | b |
|string_length | 5 |
+-----------------------------------------------------------------+
. clear
. odbc load, dsn("`dsn'") table("`temp_table'") `user' `pass' noquote
. gen byte space_index = strpos(teststr, " ")
. list
+--------------------+
| teststr space_~x |
|--------------------|
1. | abc 4 |
2. | a 2 |
3. | b 2 |
+--------------------+
.
. version 11.0
. clear
. input str5 teststr
teststr
1. "abc"
2. "a"
3. "b"
4. end
.
. capture odbc exec("DROP TABLE `temp_table'"), dsn("`dsn'") `user' `pass'
. odbc insert, dsn("`dsn'") table("`temp_table'") create `user' `pass'
. odbc exec("`query'"), dsn("`dsn'") `user' `pass'
1. +-----------------------------------------------------------------+
|teststr | abc |
|string_length | 5 |
+-----------------------------------------------------------------+
2. +-----------------------------------------------------------------+
|teststr | a |
|string_length | 5 |
+-----------------------------------------------------------------+
3. +-----------------------------------------------------------------+
|teststr | b |
|string_length | 5 |
+-----------------------------------------------------------------+
. clear
. odbc load, dsn("`dsn'") table("`temp_table'") `user' `pass' noquote
. gen byte space_index = strpos(teststr, " ")
. list
+--------------------+
| teststr space_~x |
|--------------------|
1. | abc 4 |
2. | a 2 |
3. | b 2 |
+--------------------+
========================ACCESS===========================
NOTE: I also tried running the Access do-file in Stata 10 and got the same
results as the above log-file. I was using Access Driver version
6.00.6001.18000 and connecting to an Access 2007 file.
Kevin Crow
StataCorp
*
* 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/
