How to Insert BLOB (image) into SQL Server database using XBasic code ?

[ferryk17]

Hi all,
I have a picture in my webroot , then I want to convert it into blob and insert into SQL table (SQL Server)

dim logo_path as c = "test.png"
dim logo as b = file.to_blob(logo_path)

dim sql as c = <<%str%
INSERT INTO test (date_time_stored,logo) VALUES
(GETDATE(),{logo})
%str%
sql = evaluate_string(sql)

Then I use a standard sql function to execute the SQL command, but it return false. When I remove the logo field, it success. Is there another ways to insert blob ?

[ferryk17]

Re: How to Insert BLOB (image) into SQL Server database using XBasic code ?

nevermind, I found it.

1. we need to convert the blob file with encode64.
2. we need to put '{logo}' on sql command.

[Sarah Mitchell]

Re: How to Insert BLOB (image) into SQL Server database using XBasic code ?

Hello Ferry!

Are you using SQL::Arguments? You can do this very easily with SQL::Arguments. For example:

dim logo as b = file.to_blob("test.png")

dim sql as c =<<%sql%
INSERT INTO test (date_time_stored,logo) VALUES
(GETDATE(), :logo)
%sql%

dim cn as SQL::Connection
dim args as SQL::Arguments

args.set("logo", logo);

' open the connection to the database
if (cn.open("::Name::conn") <> .f.) then
    ' execute the SQL statement
    if (cn.execute(sql,args) <> .f.) then
        ' succeeded
    else
        ' failed. Check cn.callResult.text for more information
    end if
else
    ' failed. The Named AlphaDAO connection string does not exist or the connection could not be opened.
    ' Check cn.callResult.text for more information
end if

Using SQL::Arguments offers many benefits, including taking all the work out of merging data in to your SQL queries and preventing SQL injection attacks. I highly recommend using SQL::Arguments.

Check out Using Xbasic With SQL Tables to learn more.