Announcement

Collapse
No announcement yet.

How to insert binary data into SQL Server using SQL_Tools

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to insert binary data into SQL Server using SQL_Tools

    Anyone got a good method of inserting binary data into SQL Server?

    At first thought it is just a string but I would need to escape any single quote marks.

    Is there a more nice way where the binary data goes in untouched?

    I assume I can use VARCHAR(MAX) for small stuff and then what data type is the preferred these days for a .jpg or .mp3 etc...

  • #2
    Don't use any type of character field, use a binary field. (VARBINARY)

    SQL Tools Pro - SQL_UpdateBLOB lets you update a binary field with either a binary string or a file

    If you want to insert it in a new record, do the INSERT of core record data, retrieve the PK and then do an UPDATE for the binary data.



    Comment


    • #3
      SQL_Tools Pro has a SQL_UpdateBLOB function specifically for updating long binary columns in an SQL table. I've used it only once or twice, and am away from my desk and primary development machine (ad therefore, can't get to the help file). If I recall correctly, it's pretty easy -- unlike most SQL-Tools function where you construct something like this:

      Code:
      update mytable set BlobField = 'BlobData' where recnumber = 1
      and pass it to SQL_Tools to execute, the SQL_Blob function can only update the Blob column, which is specified in the function:

      Code:
      SQL_updateBlob(MyDatabase&, TableName$, Columnname$, whereclause$, BlobData$)
      Something like that. Again, it's off the top of my memory, I don't have the help file right at hand.

      Hope this helps
      Real programmers use a magnetized needle and a steady hand

      Comment


      • #4
        Sorry David, somehow I missed this thread.

        Did all of your questions get answered?
        "Not my circus, not my monkeys."

        Comment


        • #5
          Hi All

          Function DB_SAVE_JPG_1 works fine if the column is IMAGE data type.

          I have been reading that IMAGE is deprecated and will be removed in a future version of SQL Server. "Use VARBINARY(MAX) for ALL binary data."
          Neither function works when data type is VARBINARY(MAX)

          Code:
          #COMPILE EXE
          #DIM ALL
          
          #INCLUDE "WIN32API.INC"
          #INCLUDE "C:\SQLTools\SQLT3.INC"
          #LINK    "C:\SQLTools\SQLT3Pro.PBLIB"
          #INCLUDE "DATABASE.INC"
          
          '------------------------------------------------------------------------------
           FUNCTION PBMAIN () AS LONG
          
               DB_INITIALIZE_DATABASE
               DB_OPEN_DATABASE
          
                 DB_SAVE_JPG_1
                 DB_SAVE_JPG_2
          
               DB_CLOSE_DATABASE
          
           END FUNCTION
          '------------------------------------------------------------------------------
           FUNCTION DB_SAVE_JPG_1()AS LONG
          
            LOCAL Result AS LONG
          
             Result = SQL_UpdateBLOB(1, "jpg_test", "JPG", "Unique_ID = 1", "FILE=C:\Basic Source\DB_JPG_SAVE_READ\pix.JPG")
             IF SQL_ErrorPending THEN MSGBOX SQL_ErrorQuickAll
          
           END FUNCTION
          '------------------------------------------------------------------------------
            FUNCTION DB_SAVE_JPG_2()AS LONG
          
             LOCAL FILE_NUMBER  AS LONG
             LOCAL BINARY_DATA  AS STRING
          
             FILE_NUMBER = FREEFILE
          
             OPEN "C:\Basic Source\DB_JPG_SAVE_READ\pix.JPG" FOR BINARY AS FILE_NUMBER
              GET$ FILE_NUMBER, LOF(FILE_NUMBER), BINARY_DATA
             CLOSE FILE_NUMBER
          
             SQL_Statement 1, 1, %SQL_STMT_IMMEDIATE,"UPDATE JPG_TEST SET JPG = '" + BINARY_DATA + "' WHERE UNIQUE_ID = 2"
             IF SQL_ErrorPending THEN MSGBOX SQL_ErrorQuickAll
          
           END FUNCTION

          Comment


          • #6
            Hi David,

            Code:
            > FUNCTION DB_SAVE_JPG_2()AS LONG
            ...
            > GET$ FILE_NUMBER, LOF(FILE_NUMBER), BINARY_DATA
            ...
            > SQL_Statement 1, 1, %SQL_STMT_IMMEDIATE,"UPDATE JPG_TEST SET JPG = '" + BINARY_DATA + "' WHERE UNIQUE_ID = 2"
            That technique won't work. You can't put binary data directly into a SQL statement, which must be 100% text.

            > Neither function works when data type is VARBINARY(MAX)

            Please add SQL_Trace %TRACE_INTERNALS to your DB_SAVE_JPG_1 function, right before the call to SQL_UpdateBLOB, and send the resulting trace file to [email protected]

            (Sorry I keep missing updates to this thread, I'll watch the forum Activity List more carefully.)

            "Not my circus, not my monkeys."

            Comment

            Working...
            X