Professional Web Applications Themes

INSERT file to column - Coldfusion Database Access

I have done some due diligence looking for an answer, but I have not found it so here is my post: I have a table t_CustomerFiles, with two columns, CustomerID (INT) and FileData (NTEXT) On a web page the customer is uploading a file and I want to insert the entire file (it is TEXT, not binary) into the table. The following does not work 100% of the time. Sometimes the preservesinglequote fails and the INSERT statement gets terminated. <cffile action="READ" file="D:\customerFile.txt" variable="ReadFile"> <cfquery datasource="#CurDS#" dbtype="#CurDSType#"> INSERT INTO CustomerFiles (CustomerID , FileData ) VALUES(12342, '#preservesinglequotes(ReadFile)#') </cfquery> Is there a way ...

  1. #1

    Default INSERT file to column

    I have done some due diligence looking for an answer, but I have not found it
    so here is my post:

    I have a table t_CustomerFiles, with two columns, CustomerID (INT) and
    FileData (NTEXT)

    On a web page the customer is uploading a file and I want to insert the entire
    file (it is TEXT, not binary) into the table. The following does not work 100%
    of the time. Sometimes the preservesinglequote fails and the INSERT statement
    gets terminated.

    <cffile action="READ" file="D:\customerFile.txt" variable="ReadFile">

    <cfquery datasource="#CurDS#" dbtype="#CurDSType#">
    INSERT INTO CustomerFiles (CustomerID , FileData )
    VALUES(12342, '#preservesinglequotes(ReadFile)#')
    </cfquery>

    Is there a way that I can simply tell Microsoft SQL Server 2000 to "go get
    that file and put it in the database with the CustomerID?"

    Somethig like this:
    <cfquery datasource="#CurDS#" dbtype="#CurDSType#">
    INSERT INTO CustomerFiles (CustomerID , FileData )
    VALUES(12342, GetFile('D:\customerFile.txt'))
    </cfquery>

    (I know that is bogus syntax, but one can only wish upon a star)


    HugoSchmidt Guest

  2. #2

    Default Re: INSERT file to column

    Using a DTS, but you can't call it from CF, you have to use dtsrun (from
    command line).

    With BULK INSERT you have to get all the values to insert in the table inside
    the txt file.

    WRITETEXT, but you have to use preservesinglequotes anyway.

    Regards

    Sojovi Guest

  3. #3

    Default Re: INSERT file to column

    Sojovi wrote:
    > Using a DTS, but you can't call it from CF, you have to use dtsrun (from
    > command line).
    what makes you say that? we do it all the time. you can easily call any DTS job
    from within a sp or even call it via COM.
    PaulH *ACE* Guest

  4. #4

    Default Re: INSERT file to column

    OK, don't get angry, thanks for let me know that. Take it easy, it's a forum, not a class room.

    Regards
    Sojovi Guest

  5. #5

    Default Re: INSERT file to column

    And would be a good idea that you write how we can do it.... if not, why are you posting messages here ?

    Best regards
    Sojovi Guest

  6. #6

    Default Re: INSERT file to column

    Thanks all, I just did a rereplace() instead of the PreserveSingleQuotes() and that worked fine. It may be a patch, but it did the job.
    HugoSchmidt Guest

Similar Threads

  1. Return value of 'serial' column on insert
    By Madison Kelly in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 27th, 06:41 AM
  2. Select after insert to the unique column
    By Bruno Wolff III in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: December 13th, 04:44 PM
  3. Index on Computed Column and ADO Insert
    By Peter Crickman in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 4th, 01:46 AM
  4. Have server get value for column on Insert?
    By Ian Boyd in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 06:42 PM
  5. Which is the best column to insert index...
    By fabriZio in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 02:37 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139