Testing `varchar(max)` retrieval

[wibeasley]

Hi @zozlak (and @r2evans), I'm responding to #4 and #5. I'm happy to help with your request to test. Before I get started, can you confirm that this is what you'd like me to do:

1. Within SSMS, create a fresh table in a fresh database with at least one varchar(max) column.
2. Within R, populate that column and send it to SQL Server.
   • I'll write a random characters to each cell. How many characters would you like to me to write? 2^10? 2^16?
   • Which function should I use for this? RODBCext::sqlExecute()?
3. Within R, retrieve the column and see if it matches R's in-memory copy of the data.frame.
   • Which function should I use for this? RODBC::sqlGetResults()?

Since these tests can be run on Travis (b/c SQL Server isn't yet supported for Ubuntu), I'll put the tests in a different folder than /tests/testthat.

BTW, if you need help in the future, consider asking @nutterb if he has time. He's a sharp guy and a fan of RODBCext.

[zozlak]

Thanks for help!

Yes, I would like you to perform a test as you described. Only a few remarks:

• To insert the data, please use sqlExecute(handle, "INSERT INTO yourTestTable VALUES (?)", dataFrameWithSampleData)
• For data retrieval, please use sqlExecute(handle, "SELECT * FROM yourTestTable", NULL, TRUE)
• When retrieving the data it is supposed to retrieve only first 255 characters. This means:
  • When checking if inserted and retrieved data match, limit yourself to 255 characters (or to the length of retrieved strings)
  • It would be nice to check if inserted values match (especially if they were not trimmed) using some other method (in worst case by looking directly into the database).
• I think there is no point in adding such tests to the repository as:
  • They can not be easily run by others (they depend on your local ODBC configuration).
  • They are so simple, that it is not a big deal to write the testing code once more.

[zozlak]

And if you wonder why varchar(max) will retrieve only 255 characters and varchar(anyNumber), e.g. varchar(100000) will retrieve up to 100k characters, the answer is:

• For varchar(max) the Ms SQL ODBC driver reports length of 0. This is obviously wrong so the question is can we guess the right length easily?
• When writing data we can substitute it by our data maximum length. That is a very good and safe guess as in worst case (data too long for a database to handle) the ODBC driver will automatically cut them to the supported length and let us know about it.
• But when reading data we do not have any good guess for the data length. Internally the ODBC function reading data requires us to pass a buffer and its length, so we must assume a fixed value. In RODBC this default was set to 255 and RODBCext keeps it.

[nutterb]

I had a two week long ordeal with this issue as I was starting to work with RODBCext. When I finally understood that it was an issue in the SQL Server driver ( specifically, I think it is the SQL Server 11.0 Native Client driver) I had to find a way to work around it.

My solution comes in there policies

1. Never use Varchar (max) in my databases ( though I understand this may not be realistic for existing databases. I'm just fortunate to have control over such matters)
2. Use varchar (8000) if I really need a long character string. More often than not, this will suffice.
3. If varchar (8000) isn't enough, use a varbinary (max). This requires some finagling to convert your character string to a binary string, but it isnt terribly difficult to do and SQL Server does handle the varbinary (max) type pretty well.

Again, this doesn't really help with established databases. If you really need to get out a varchar(max), I think you will have to do some ugly contortions with a stored procedure.

I can run these tests on my instance of SQL Server as well. We have s couple of differect drivers installed on our machines that don't always behave the same way).

[wibeasley]

@zozlak, I believe this is what you wanted me to test, and that it confirms your suspicions. Notice I tried with the more recent ODBC driver too: 13.1.

The results can be viewed in this mark down. Tell me if you'd like to me to try something else. https://github.com/wibeasley/RODBCext/blob/master/tests/manual/stitched-output/test-varchar.md

@nutterb, if you're recreating something in the future, notice the .sql script to create the table, in addition to the .R file to run the tests. If you create your own DSN an point it to that test database, I think you should be able to replicate everything. I'll create a PR now, so it's more unified in the master branch.

[zozlak]

The 8000 characters test copying only 7999 characters looks a little suspicious (like I forgot to allocate one more byte in the buffer for the terminating \0 character [C internals]).

I will check it, probably on Sunday.

If I will introduce any changes, I will ask you to run tests again and the final result will be the 0.2.7 release ("a correct support for string data types reporting 0 length")

[nutterb]

Just ran these on my configuration under three drivers. Results were identical to @wibeasley

dsn_11_0 <- "rodbcext-test-11-0" #ODBC Driver 6.01.7601.17514; SQLSRV32.DLL 20-NOV-2010
dsn_odbc_11 <- "rodbcext-test-odbc-for-sql-11" #ODBC Driver 2014.120.4219.00; MSODBCSQL11.DLL 21-DEC-2015
dsn_sql_server <- "rodbcext-test-sql-server" # ODBC Driver 2011.110.3000.00; SQLNCLI11.DLL 20-OCT-2012

[zozlak]

I checked the RODBCext part and it seems that buffers' length is set correctly in RODBCext.

This suggests that the "last character being lost for columns longer then 255 chars" error is caused by the RODBC (the data retrieval step is performed by RODBC's functions).

To be investigated when I will have a few days of free time (#8).

@nutterb , @wibeasley Thank you for your help!