Insert a Blob via a SQL Script

insert a BLOB via a sql script?

For testing, you can insert literal hex bytes or use the RAWTOHEX(string) function, as shown below.

create table a(id integer, item blob);
insert into a values(1,'54455354');
insert into a values(2, RAWTOHEX('Test'));
select UTF8TOSTRING(item) from a;
TEST
Test

Addendum: For loading BLOB fields from a file, FILE_READ(fileNameString) may be a useful alternative.

insert into a values(3, FILE_READ('file.dat'));

Insert BLOB from a file into a sql script to embed H2 database

From FILE_READ documentation:

File names and URLs are supported. To read a stream from the
classpath, use the prefix classpath:

Seems that the use of a relative path it's not possible; then a possible solution is to include the file with the desired binary content in the classpath and access it using classpath: in FILE_READ. This way you can deploy it in any other machine without worries about the absolute paths.

By code using RunScript

So if before perform your test you setup the DB running the script by code using something like:

RunScript.execute(conn, new FileReader("yourScript.sql"));

Then add the logo.png as a resource of your project this way you can refer it inside the script using classpath: notation: FILE_READ('classpath:/your/package/resource/logo.png').

Using RunScript from command line tool

If you use the command line tool, you can create a .jar to package your resources, e.g resource.jar and add it to classpath in your cmd:

java -cp h2*.jar;resource.jar org.h2.tools.RunScript -url jdbc:h2:~/test -script yourScript.sql

Then as the previous case in your script you can refer your binary file using FILE_READ('classpath:/your/package/resource/logo.png')

Hope it helps,

How to insert a blob into a database using sql server management studio

You can insert into a varbinary(max) field using T-SQL within SQL Server Management Studio and in particular using the OPENROWSET commmand.

For example:

INSERT Production.ProductPhoto 
(
    ThumbnailPhoto, 
    ThumbnailPhotoFilePath, 
    LargePhoto, 
    LargePhotoFilePath
)
SELECT ThumbnailPhoto.*, null, null, N'tricycle_pink.gif'
FROM OPENROWSET 
    (BULK 'c:\images\tricycle.jpg', SINGLE_BLOB) ThumbnailPhoto

Take a look at the following documentation for a good example/walkthrough

Working With Large Value Types

Note that the file path in this case is relative to the targeted SQL server and not your client running this command.

Can you embed blob data in a script for sql server?

Assuming you are referring to a field of type BINARY / VARBINARY / IMAGE, you should be able to just specify the Hex Bytes such as:

0x0012FD...

For example:

INSERT INTO TableName (IDField, BlobField) VALUES (1, 0x0012FD);

You just need to get that string of hex digits from the file. If you already have such a value in the DB already, then just select that row and field in SSMS, and copy / paste the value from the cell (in "Results to Grid" mode) into your SQL script.

You can also wrap long lines using a backslash as follows:

INSERT INTO TableName (IDField, BlobField) VALUES (1, 0x0012FD\
12B36D98\
D523);

If wrapping via back-slash, be sure to start each new line at the first position as the entire thing is treated as a continuous string. Hence, indenting lines that come immediately following a back-slash would then have spaces between the hex digits, which is not valid. For example:

INSERT INTO TableName (IDField, BlobField) VALUES (1, 0x0012FD\
  12B36D98\
D523);

equates to:

0x0012FD  12B36D98D523

How can I insert into a BLOB column from an insert statement in sqldeveloper?

Yes, it's possible, e.g. using the implicit conversion from RAW to BLOB:

insert into blob_fun values(1, hextoraw('453d7a34'));

453d7a34 is a string of hexadecimal values, which is first explicitly converted to the RAW data type and then inserted into the BLOB column. The result is a BLOB value of 4 bytes.

Writing blob file into database by using the sql statement insert into

Try something like that:

FADSQuery.SQL.Add('Insert Into '+DBName+'(BlobData) values (:BlobData)');
FADSQuery.ParamByName('BlobData').LoadFromStream(BinaryStream, ftBlob); 

Cannot insert BLOB data into table

Where is the database installed? Is it on your computer? Because, directory has to reside on the database server (or be accessible to it - if it is elsewhere, use UNC to create it). "C:\temp" looks like it is your own PC, so - it might not work.

As of privileges: directory (as an Oracle object) is owned by SYS, and SYS has to grant privileges to user(s) that will be using it, such as

grant read, write on directory myimages to jtruant;

[EDIT: here's a working example]

This is my directory:

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
EXT_DIR

Creating a table:

SQL> create table job_resumes2
  2    (resume_id       number,
  3     first_name      varchar2(25),
  4     last_name       varchar2(25),
  5     profile_picture blob);

Table created.

Running a procedure:

SQL> declare
  2    src_lob  bfile := bfilename('EXT_DIR', 'robco.jpg');
  3    dest_lob blob;
  4  begin
  5    insert into job_resumes2
  6      (resume_id, first_name, last_name, profile_picture)
  7    values
  8      (1, 'John', 'M', empty_blob())
  9      returning profile_picture into dest_lob;
 10
 11    dbms_lob.open(src_lob, dbms_lob.lob_readonly);
 12    dbms_lob.loadfromfile( dest_lob => dest_lob,
 13                           src_lob  => src_lob,
 14                           amount   => dbms_lob.getlength(src_lob) );
 15    dbms_lob.close(src_lob);
 16
 17    commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

Is there anything there?

SQL> select dbms_lob.getlength(profile_picture) from job_resumes2;

DBMS_LOB.GETLENGTH(PROFILE_PICTURE)
-----------------------------------
                               6427

SQL>

Seems to be OK. Try to follow my steps and see what happens.

Inserting BLOB data in DB2 using SQL Query

1) You could use LOAD or IMPORT via ADMIN_CMD. In this way, you can use SQL to call the administrative stored procedure that will call the tool. Import or Load can read files and then put that in a row.

You can also wrap this process by using a temporary table that will read the binary data from the file, insert it in the temporary table, and then return it to be from the table.

2) You can create an external stored procedure or UDF implemented in Java or C, that will read the data, and then insert that in the row.

Create Blobs with Filename and Data from SQL Rows

This is a pretty simple feat natively in ADF:

1. Use a Lookup Activity to get the row results of your Database Table
2. Use a For Each to Iterate over each row from the Lookup
3. Within the For Each have a Copy Data activity that copies from DB (as a passthrough really) to Blob Storage

Here are screenshots with comments to illustrate the exact steps and proof that it works:

1st, here is my mock table that is similar to your example, I have a pretend FileID, and Data field

2nd, I do a lookup on that table, see the details and output below

3rd, we put in the For Each activity so that we can perform an action or group of actions per item in the array object

4th, we go into the For each and create a Copy Data activity with a Source of Azure SQLDB and a Sink of Azure Blob

Source Details:

Sink Details:

And here is what happens in my Blob Storage when I run this pipeline:

Let me know if you need anything else or have any questions, hope this helps! Remember to like, love, and follow :)

---

Related Topics