about blob column

profh · 2012-07-03 02:52:16

i retrieve blob data from database using TSQLDBStatement,but the Query.TotalRowsRetrieved is always zero:

var
  OleDBConnectionProperties:TOleDBConnectionProperties;
  SQLDBConnection:TSQLDBConnection;
  Query: TSQLDBStatement;
......

  OleDBConnectionProperties := TOleDBConnectionProperties.Create('','','','');
  OleDBConnectionProperties.ConnectionString := myrdbmstr;

  try
    SQLDBConnection := OleDBConnectionProperties.NewConnection;
  except
    OleDBConnectionProperties.Free;
    exit;
  end;

  try
    Query := SQLDBConnection.NewStatement;
  except
    SQLDBConnection.Disconnect;
    SQLDBConnection.Free;
    OleDBConnectionProperties.Free;
    exit;
  end;

  try
    Query.Execute('select blobfield from mytable where id=100',true);
    aParams.Resp := Query.ColumnBlob(0);
  except
    Query.Free;
    SQLDBConnection.Disconnect;
    SQLDBConnection.Free;
    OleDBConnectionProperties.Free;
    aParams.Resp := '';
    exit;
  end;

ab · 2012-07-03 08:50:34

You need to call the "Step" method before accessing the columns, as stated by the documentation.

profh · 2012-07-03 09:11:13

i add step(true) or step(false) before "aParams.Resp := Query.ColumnBlob(0);",it is just the same problem.

i think 'select blobfield from mytable where id=100' makes the mistake,but it is really a correct SQL statement.

mpv · 2012-07-03 11:19:14

Is you query return rows?
try this code:

if Query.Step then
aParams.Resp := Query.ColumnBlob(0)
else
// no rows return

TotalRowsRetrieved increased only after Step() call - see TOleDBStatement.Step source.

profh · 2012-07-03 12:09:50

if Query.Step then
aParams.Resp := Query.ColumnBlob(0)
else
// no rows return

i check that Query.Step returns false.

but,'select blobfield from mytable where id=100' does return one row, and "blobfield" is text type of Mysql.

ab · 2012-07-03 12:36:59

Is it a BLOB/binary or a TEXT column?

If it is a TEXT, you would rather use Query.ColumnUTF8().

It will also depend on your OleDB provider.

profh · 2012-07-03 13:30:19

yes, it is a TEXT column,i use Query.ColumnUTF8(), and i also got nothing.

IMHO,Query.Execute('select blobfield from mytable where id=100',true) returns empty,
but 'select blobfield from mytable where id=100' does return one row.

ab · 2012-07-03 13:43:06

You should better use

Query := OleDBConnectionProperties.Execute('select blobfield from mytable where id=100',[]);

or

Query := OleDBConnectionProperties.Execute('select blobfield from mytable where id=?,[100]);

profh · 2012-07-03 14:00:55

it does not work,and also returns nothing.

the OleDB Provider i use is:mysql-connector-odbc-5.1.11-win32.msi.

ab · 2012-07-03 19:06:50

Did you try with ado?

Did you try with syndbexplorer?

profh · 2012-07-03 23:26:01

ado and syndbexplorer are the same,
i try syndbexplorer ,if i use "select * from mytable where id = 100",it returns one row with the blobfield empty. if i use 'select blobfield from mytable where id=100',it makes a mistake and popup a error window. the error window just like this:

Error

OLEDB Error 80040E21 - ?? OLE DB
???????????,????? OLEDB ????????????

Exception class:EOleDBException

profh · 2012-07-03 23:30:00

i use HeidiSQL 5.0.0.3272 and execute 'select blobfield from mytable where id=100',it can return one row with blobfield content,no error displays.

ab · 2012-07-04 05:40:25

AFAIK HeidiSQL does not use any OleDB provider, but has direct access to the MySQL client library.

That is why I asked about direct ADO access (via your OleDB provider), and also to try with our SynDBExplorer.

The 80040E21 exception is about an unhandled feature of the used OleDB provider.
It sounds like a known limitation of the provider used. See http://www.mysqltalk.org/image-vp435971.html

In order to fix your problem, you'd have to try another OleDB provider (one non using ODBC, but direct MySQL access), like http://cherrycitysoftware.com/ccs/provi … MySQL.aspx or others.

A direct ODBC connection could be a better solution.
The unit is not yet finished. See http://synopse.info/fossil/finfo?name=SynDBODBC.pas

profh · 2012-07-04 08:00:00

thanks,
perhaps i should use zeoslib at the server side as i used to.

ab · 2012-07-04 08:33:34

ZeosLib has indeed a direct connector to MySQL.
Could be a better choice in your particular case!

profh · 2012-07-04 09:17:55

how long shall the SynDBODBC.pas be finished? is it sutable for my case?
i really want use less third commponent.

thanks again.

profh · 2012-07-04 10:09:49

i use sqlite3 and mysql at the same time.

and i used synpdf before,but it can not process chinese font from my emf file,so i pause now, and i shall pick up it later.

i post here before: http://synopse.info/forum/viewtopic.php?id=42

ab · 2012-07-04 10:21:24

Direct link to MySQL could make even better sense that the ODBC link.
But I have no MySQL installation at hand, and no need nor time to do it in the close future.
Any contribution is welcome!

About SynPDF, handling font fallback is far from trivial...
Could better be fixed by setting a font explicitly having all chinese glyphs.

profh · 2012-07-04 11:31:01

while using ZeosLib,another question is about json:

how to translate between ZeosLib's Query and json?

profh · 2012-07-06 06:45:44

here is my code refer syndb,

unit uzjsonquery;

interface

uses Classes,SysUtils, db,ZConnection,ZSqlProcessor,ZDataset,
    SQLite3, SynCommons,syndb,synoledb,SQLite3Commons;

type
  TZJsonQuery = class(TZQuery)
  private
  protected
    procedure ColumnsToJSON(WR: TJSONWriter);
    function  FetchAllToJSON(JSON: TStream; Expanded: boolean): PtrInt;
  public
    function  FetchAllAsJSON(Expanded: boolean; ReturnedRowCount: PPtrInt=nil): RawUTF8;
  end;

implementation

procedure TZJsonQuery.ColumnsToJSON(WR: TJSONWriter);
var col: integer;
    blob: RawByteString;
    tmpstream:TMemoryStream;
begin
  if WR.Expand then WR.Add('{');

  for col := 0 to FieldCount - 1 do begin
    if WR.Expand then
      WR.AddFieldName(Fields[col].FieldName); // add '"ColumnName":'
    if Fields[col].IsNull then
      WR.AddShort('null') else
    case Fields[col].DataType of
      db.ftUnknown:     WR.AddShort('null');
      ftSmallint, ftInteger, ftWord,ftLargeint: WR.Add(Fields[col].AsInteger);
      ftFloat:WR.Add(Fields[col].AsFloat);
      db.ftCurrency: WR.AddCurr64(Fields[col].AsCurrency);
      db.ftDate, db.ftTime, db.ftDateTime: begin
        WR.Add('"');
        WR.AddDateTime(Fields[col].AsDateTime);
        WR.Add('"');
      end;
      ftMemo,ftstring,ftWideString: begin
        WR.Add('"');
        WR.AddJSONEscape(pointer(Fields[col].AsString));
        WR.Add('"');
      end;
      db.ftBlob: begin
        tmpstream := TMemoryStream.Create;
        try
          TBlobField(Fields[col]).SaveToStream(tmpstream);
          tmpstream.Position := 0;
          WR.WrBase64(tmpstream.Memory,tmpstream.Size,true);
        finally
          tmpstream.Free;
          tmpstream := nil;
        end;
      end;
      else raise ESQLDBException.CreateFmt('%s: Invalid ColumnType() %s',
        [ClassName,'']);
    end;
    WR.Add(',');
  end;
  WR.CancelLastComma; // cancel last ','
  if WR.Expand then WR.Add('}');
end;

function TZJsonQuery.FetchAllToJSON(JSON: TStream; Expanded: boolean): PtrInt;
var W: TJSONWriter;
    col: integer;
begin
  result := 0;
  W := TJSONWriter.Create(JSON,Expanded,false);
  try
    SetLength(W.ColNames,FieldCount);//columncount
    for col := 0 to FieldCount-1 do
      W.ColNames[col] := FieldDefList.FieldDefs[col].Name;
    W.AddColumns;
    if Expanded then W.Add('[');
     
    First;
    while (not Eof) do
    begin
      ColumnsToJSON(W);
      W.Add(',');
      inc(result);

      Next;
    end;
    W.CancelLastComma; // cancel last ','
    W.Add(']');
    if not Expanded then W.Add('}');
    W.Add(#10);
    W.Flush;
  finally
    W.Free;
  end;
end;

function TZJsonQuery.FetchAllAsJSON(Expanded: boolean;
                            ReturnedRowCount: PPtrInt=nil): RawUTF8;
var Stream: TRawByteStringStream;
    RowCount: PtrInt;
begin
  Stream := TRawByteStringStream.Create;
  try
    RowCount := FetchAllToJSON(Stream,Expanded);
    if ReturnedRowCount<>nil then ReturnedRowCount^ := RowCount;
      
    result := Stream.DataString;
  finally
    Stream.Free;
  end;
end;

it is ok for me, and i hope it is usful for someone else.

ab · 2012-07-06 08:32:01

The whole idea is good.
Thanks for sharing the code!

I suspect you'll have issues with UTF-8 encoding. You are mixing RawUTF8/UTF-8 buffers and plain String type.
Accents will probably be lost in the process.

profh · 2012-07-06 08:42:38

i see, and i will check it.

thanks a lot.

ab · 2012-07-06 08:47:16

By the way, I'm working on font fall-back for SynPDF.

I hope that it will be able to easily handle Chinese characters soon, without the need to set the explicit font.

profh · 2012-07-06 08:54:51

u are the greatest.

ab · 2012-07-10 09:59:34

See http://blog.synopse.info/post/2012/07/1 … generation

mORMot Open Source

#1 2012-07-03 02:52:16

about blob column

#2 2012-07-03 08:50:34

Re: about blob column

#3 2012-07-03 09:11:13

Re: about blob column

#4 2012-07-03 11:19:14

Re: about blob column

#5 2012-07-03 12:09:50

Re: about blob column

#6 2012-07-03 12:36:59

Re: about blob column

#7 2012-07-03 13:30:19

Re: about blob column

#8 2012-07-03 13:43:06

Re: about blob column

#9 2012-07-03 14:00:55

Re: about blob column

#10 2012-07-03 19:06:50

Re: about blob column

#11 2012-07-03 23:26:01

Re: about blob column

#12 2012-07-03 23:30:00

Re: about blob column

#13 2012-07-04 05:40:25

Re: about blob column

#14 2012-07-04 08:00:00

Re: about blob column

#15 2012-07-04 08:33:34

Re: about blob column

#16 2012-07-04 09:17:55

Re: about blob column

#17 2012-07-04 10:09:49

Re: about blob column

#18 2012-07-04 10:21:24

Re: about blob column

#19 2012-07-04 11:31:01

Re: about blob column

#20 2012-07-06 06:45:44

Re: about blob column

#21 2012-07-06 08:32:01

Re: about blob column

#22 2012-07-06 08:42:38

Re: about blob column

#23 2012-07-06 08:47:16

Re: about blob column

#24 2012-07-06 08:54:51

Re: about blob column

#25 2012-07-10 09:59:34

Re: about blob column

Board footer