Folks,

Is it possible to import images (.jpg) automatically through a stored procedure to a BLOB field in a table?  I've tried the following but don't believe OpenRowSet is supported:

INSERT INTO xPhotos (xPhoto1)
SELECT BulkColumn from Openrowset(Bulk 'D:\Users\jelway\Pictures\superbowl.jpg',Single_Blob)

If anyone can let me know if this is possible and/or provide the proper syntax, that would be HUGELY appreciated.

Thanks in advance

dunk7


The bad news is that the function isn't implemented within ElevateDB. The good news is that ElevateDB is extendable and you can write your own external function using Delphi to achieve what you want.


If you want to go down that route and need a bit of help writing the function let me know.

Roy Lambert [Team Elevate]

dunk7

I can understand your desire to access a file-system file in a SQL statement but that is sort-of "outside the box" of EDB's usual operations, partly for security reasons I would guess, but also for simple desire to minimize complexity.

Any stored procedure to insert a BLOB / File also needs to know about things like primary keys, varchar fields used to store file-names or descriptions etc. As how these are set up vary so much from developer to developer EDB doesn't try to fix them with a single syntax.

Within a Delphi EXE it is easy to do as you request:

var
 SQLStr: String;
 Q: EDBQuery;
 aJPG: TJPEGImage;
 aBMP: TBitmap;
begin
 Q:= TEDBQuery.Create(self);
 //code to set session & database for the query here.
 aJPG:= TJPEGImage.Create;
 aBMP:= TBitmap.create;
 try
   aBMP.LoadFromFile('C:\photos\Filename.jpg');
   aJPG.Assign(aBMP);
   SQLStr:= ' INSERT INTO xPhotos ("Photo1") VALUES (:Blob) ';
   Q.SQL.Text:= SQLStr;
   Q.Prepare;
   InsertQ.ParamByName('Blob').Assign(J);
   Q.ExecSQL;
   Q.Unprepare;
   Q.Close;
 finally
   aJPG.Free;
   aBMP.free;
 Q.Free;
 end;
end;
 
Rolling the above code into an external Module or DLL is less difficult than it seems.

You need to build a DLL using the sample code Tim has put in the binaries section of the newsgroup, this DLL will surface a number of parameters (such as "FileName", "DatabaseName", "TableName").

You will then register this new external module in your database using the "create module" statement, and place the DLL in the path detailed in this statement.

After that you can call the module's functionality from any SQL Script using the form:

Call MyNewFunction('FileName', 'DatabaseName', 'TableName');

and it will do the work you desire.

Adam Brett wrote:

dunk7

I can understand your desire to access a file-system file in a SQL statement but that is sort-of "outside the box" of EDB's usual operations, partly for security reasons I would guess, but also for simple desire to minimize complexity.

Any stored procedure to insert a BLOB / File also needs to know about things like primary keys, varchar fields used to store file-names or descriptions etc. As how these are set up vary so much from developer to developer EDB doesn't try to fix them with a single syntax.

Within a Delphi EXE it is easy to do as you request:

var
 SQLStr: String;
 Q: EDBQuery;
 aJPG: TJPEGImage;
 aBMP: TBitmap;
begin
 Q:= TEDBQuery.Create(self);
 //code to set session & database for the query here.
 aJPG:= TJPEGImage.Create;
 aBMP:= TBitmap.create;
 try
   aBMP.LoadFromFile('C:\photos\Filename.jpg');
   aJPG.Assign(aBMP);
   SQLStr:= ' INSERT INTO xPhotos ("Photo1") VALUES (:Blob) ';
   Q.SQL.Text:= SQLStr;
   Q.Prepare;
   InsertQ.ParamByName('Blob').Assign(J);
   Q.ExecSQL;
   Q.Unprepare;
   Q.Close;
 finally
   aJPG.Free;
   aBMP.free;
 Q.Free;
 end;
end;
 
Rolling the above code into an external Module or DLL is less difficult than it seems.

You need to build a DLL using the sample code Tim has put in the binaries section of the newsgroup, this DLL will surface a number of parameters (such as "FileName", "DatabaseName", "TableName").

You will then register this new external module in your database using the "create module" statement, and place the DLL in the path detailed in this statement.

After that you can call the module's functionality from any SQL Script using the form:

Call MyNewFunction('FileName', 'DatabaseName', 'TableName');

and it will do the work you desire.


WOW...Thanks so much Adam!