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   kcrow@stata.com (Kevin H. Crow)
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: Re: Problem with -odbc insert- in Stata 11: padded strings
Date   Mon, 30 Nov 2009 14:32:43 -0600

Ben Jan <ben.jann@gmail.com> 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/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index