The ElevateDB Version 2 manual says...

"Most of the time you can simply use the general TField AsString and AsVariant properties to update a
BLOB or CLOB column in the same fashion as you would any other column. Both of these properties
allow very large strings or binary data to be stored in a BLOB or CLOB column."

Could someone provide a sample of Delphi XE Unicode source demonstrating how to use AsVariant to store binary data?

Basically, I have an array of doubles I would like to store in and retrieve from a BLOB field but would like to avoid creating a blob stream as we have up till now.

Thanks!

I should have mentioned that I'm using a TEDBQuery and a parameterized SQL statement (SELECT, INSERT and UPDATE) so that's why I posted this BLOB question to the ElevateDB SQL forum.

Is there no easy way to INSERT or UPDATE a BLOB field using a parameterized query?

Is the only way... to open a dataset, position the cursor on a record, put the dataset in dsEdit mode, update the BLOB field and post... as in the examples in the documentation?

David


I didn't respond before because I've never used .AsVariant to load a LOB column. I either use .AsString or .LoadFromStream.

Using .AsString is easy just use it as you would any other parameter in a query.

If you're not sure of the syntax to use in your query reverse engineer the database with data and examine the script produced.

Roy Lambert [Team Elevate]

Hey Roy!

Thank you for the reply!

I looked at the reverse engineered insert statement but it didn't actually answer the question.

Here is a sample scenario...

I have a variable "d" of type array[0..0] of Double, "i" of type integer for the count of items in the array and a "query" of type TEDBQuery that is connected to the default session and current open database.

I have the following incomplete code... can you fill in for the ???

GetMem(d, SizeOf(Double) * i);
try
 { assume I have filled the double array with values here... }
 query.SQL.Clear;
 query.SQL.Add('insert into "datatable" (ID, DATA) values (:ID, :DATA)');
 query.ParamByName('ID').ParamType := ptInputOutput;
 query.ParamByName('DATA').AsString := ???
 query.ExecSQL;
 result := query.ParamByName('ID').AsInteger;
finally
 FreeMem(d);
end;

Next question will be getting the data using a select statement... finding out how large the stored array is so I can allocate the double array in memory and then translate the stored value back to that array of doubles in memory.

Any suggestions?

David


>I looked at the reverse engineered insert statement but it didn't actually answer the question.

I don't have anything like you're trying to do so before I post anything else can you post ONE of the insert statements so I see how Tim does it.

Roy Lambert [Team Elevate]

This is an insert similar to what I'm trying to accomplish but with an array of integers instead of doubles.  The value "48" is the count of integer items stored in the blob field that follows it.

EXECUTE IMMEDIATE 'INSERT INTO "DataTable" VALUES (0,
                 2,
                 1,
                 0,
                 3,
                 1,
                 48,
                 X''01000000000000000000000012000000010000000000000000000000
0400000001000000000000000000000013000000'',
                 TIMESTAMP ''2009-07-24 13:32:07.0879'',
                 TIMESTAMP ''2010-05-06 09:15:31.0331'')';

Here is an example of an insert where the 9th column ("495") represents the number of doubles in the array stored in the 19th column.

EXECUTE IMMEDIATE 'INSERT INTO "DoubleData" VALUES (1,
                 1,
                 4,
                 NULL,
                 0,
                 0,
                 NULL,
                 NULL,
                 495,
                 6643,
                 6890,
                 0.5,
                 28.4366,
                 330.9244,
                 77.3596864646465,
                 43.7315728444391,
                 1E30,
                 '''',
                 X''091B9E5E293B4640A4703D0AD7EB4A401D38674469274E40197'',
                 NULL,
                 TIMESTAMP ''2011-03-04 14:50:40.0329'',
                 NULL)';

David


Reading the manual, and looking at that example you'll first need to convert your array of whatever into a byte string, then preface with X" and finish it with another " (or convert into an array of TEDBByte and call the TEDBEngine.BinaryToSQLStr method).

I would be very tempted to use a CLOB, convert the data into a string list and then just use parambyname().AsString. If you stored the array dimensions in the first item of the stringlist it would be easy to write a couple of utility routines - one to convert to the stringlist and the second to convert from it.

Going back to your original post why do you want to get away from creating a blob stream - speed, writing lines of code or.....

Roy Lambert [Team Elevate]

David,

For future reference, if you send me an email directly I'll be sure to hop
over here and answer something, especially if you need an answer quickly.  I
check the newsgroups only occasionally lately while I'm working on some new
big features.  Plus, the Team Elevate guys are all very good with ElevateDB
and DBISAM, so they do pretty well without me.

Back to your question, here is the solution:

var
  DoubleArray: array of Double;
  TempString: AnsiString;
begin
  SetLength(DoubleArray,10);
  DoubleArray[0]:=100;
  DoubleArray[9]:=200;
  SetLength(TempString,(Length(DoubleArray)*SizeOf(Double)));
  Move(DoubleArray[0],TempString[1],Length(TempString));
  { assume I have filled the double array with values here... }
  with EDBQuery1 do
     begin
     SQL.Text:='insert into david (ID, DATA) values (:ID, :DATA)';
     Prepare;
     ParamByName('ID').ParamType:=ptInputOutput;
     ParamByName('DATA').AsBlob:=TempString;
     ExecSQL;
     end;
end;

TParams are a bit weird in that they don't directly support binary values
like byte arrays, so you have to use an AnsiString in a similar way to a
PChar for a memory buffer.

--
Tim Young
Elevate Software
www.elevatesoft.com