none
Working with varchar(max) RRS feed

  • Question

  • I'm not sure where to post this as it relates to issues with T-SQL, ADO.Net & VB.Net.  So, here goes.

     

    I want to use the varchar(max) datatype to store large ASCII files for batch processing and archival purposes, as opposed to network storage as it contains personally identifiable information.

     

    So I created this table:

    CREATE TABLE [dbo].[submission_files_table](
     [fice_code] [char](6) NULL,
     [academic_year] [char](4) NULL,
     [term] [char](1) NULL,
     [extension] [char](3) NULL,
     [description] [varchar](50) NULL,
     [file_create_date] [smalldatetime] NULL,
     [file_load_date] [smalldatetime] NULL,
     [submission_file] [varchar](max) NULL
    ) ON [PRIMARY]

     

    And I populated it with 26+ MB test data file using this T-SQL command.

    INSERT [dbo].[submission_files_table] (

                    [fice_code],

                    [academic_year],

                    [term],

                    [extension],

                    [description],

                    [file_create_date],

                    [file_load_date],

                    [file]

    ) SELECT 

                    /* char(6) */ '001108',

                    /* char(4) */ '2008',

                    /* char(1) */ '1',

                    /* char(3) */ 'dat',

                    /* varchar(50) */ 'Fall 2007 Submission',

                    /* smalldatetime */ '2007-10-25 13:2:3.670',

                    /* smalldatetime */ '2007-10-25 13:2:3.670',

                    'uaf071.dat'

    FROM OPENROWSET(BULK 'e:\uaf071.dat', SINGLE_CLOB) [file]

    And it inserted the row, after a few seconds that would be appropriate for a large file.

     

    I tried to retrieve the data using a T-SQL command like that in the MSDN article 'Working with Large Value Types'

    SELECT submission_file
    FROM [dbo].[submission_files_table]
    WHERE [fice_code] = '001108'

    But, all I got was the name of the file 'uaf071.dat'

     

    This is similar to the example comand in the MSDN document

    SELECT DocumentSummary
    FROM Production.Document
    WHERE DocumentID = 3;

    Where they retrieved the actual contents of the string in the data table.

     

    So, I created a VB.Net project with this code (also from the MSDN document)

            With cmd
                .CommandText = "select submission_file from submission_files_table where fice_code = '001108'"
                .CommandType = CommandType.Text
                .Connection = cn
            End With

            Dim reader As SqlClient.SqlDataReader
            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            While reader.Read()
                Dim buffer As SqlTypes.SqlChars = reader.GetSqlChars(0)
            End While

    But, the buffer variable only contains the same data as the T-SQL select statement - uaf071.dat.

     

    What am I doing wrong?  And is using the varchar(max) datatype even advisable as a way to get the sensitive data off the network?

     

    Friday, October 26, 2007 8:21 PM

Answers

  •  

    Your insert is doing what has been asked, that is to insert the string 'uaf071.dat'.

     

    I think you want something like:

     

    INSERT [dbo].[submission_files_table] (

                    [fice_code],

                    [academic_year],

                    [term],

                    [extension],

                    [description],

                    [file_create_date],

                    [file_load_date],

                    [file]

    ) SELECT 

                    /* char(6) */ '001108',

                    /* char(4) */ '2008',

                    /* char(1) */ '1',

                    /* char(3) */ 'dat',

                    /* varchar(50) */ 'Fall 2007 Submission',

                    /* smalldatetime */ '2007-10-25 13:2:3.670',

                    /* smalldatetime */ '2007-10-25 13:2:3.670',

                    myFile

    FROM OPENROWSET(BULK 'e:\uaf071.dat', SINGLE_CLOB) As Temp(myFile)

    Monday, October 29, 2007 6:03 PM

All replies

  • Isn't there anyone from Microsoft who can answer this question?

     

    Monday, October 29, 2007 1:22 PM
  •  

    Your insert is doing what has been asked, that is to insert the string 'uaf071.dat'.

     

    I think you want something like:

     

    INSERT [dbo].[submission_files_table] (

                    [fice_code],

                    [academic_year],

                    [term],

                    [extension],

                    [description],

                    [file_create_date],

                    [file_load_date],

                    [file]

    ) SELECT 

                    /* char(6) */ '001108',

                    /* char(4) */ '2008',

                    /* char(1) */ '1',

                    /* char(3) */ 'dat',

                    /* varchar(50) */ 'Fall 2007 Submission',

                    /* smalldatetime */ '2007-10-25 13:2:3.670',

                    /* smalldatetime */ '2007-10-25 13:2:3.670',

                    myFile

    FROM OPENROWSET(BULK 'e:\uaf071.dat', SINGLE_CLOB) As Temp(myFile)

    Monday, October 29, 2007 6:03 PM
  • That was indeed my problem, thanks for your input.

     

    Any thoughts on the advisablity of storing these large text files inside the database as opposed to on the network?

    Monday, October 29, 2007 7:16 PM