Retrieve a blob using mORMot

Thomas-Acia · 2015-08-27 12:02:14

Hi,

I had to do a project in order to create and print receipts. So I used mORMot + Mustache + WPTools + ZEOS.
I am at the end of my project. But I have a little problem with a blob. In fact, I sent my templates (created with mustache) on a PgSQL database. And now I would like to retrieve these blob using mORMot. On my database, the type is "bytea". So my question is : how can I retrieve the blob as text (because my template is html/css). Wich type can I use ?

Thank you

ab · 2015-08-27 12:39:50

Retrieve the blob as a RawByteString, if you can.

Thomas-Acia · 2015-08-27 12:48:51

  TSQLticket_template = class(TSQLRecord)
  private
      flibelle_template :RawUTF8;
      fbin :RawByteString;
      fblbid, fid_type_rapport :Integer;
  published
    property libelle_template: RawUTF8 Read flibelle_template Write flibelle_template;
    property bin: RawByteString Read fbin Write fbin;
    property blbid: Integer Read fblbid Write fblbid;
    property id_type_rapport: Integer Read fid_type_rapport Write fid_type_rapport;
  public
  end;

procedure TTicketClient.LoadTemplate2(idTemplate: Integer);
var
   aTemplate: TSQLticket_template;
begin
    aTemplate := TSQLticket_template.CreateAndFillPrepare(CTicket.Database, 'id = ?', [idTemplate]);
try
    while aTemplate.FillOne do
    begin
      FTemplate.Text := aTemplate.bin;
    end;
finally
    aTemplate.Free;
 end;

 end;

I have that. But It seems like my FTemplate.text is empty

ab · 2015-08-27 13:36:32

For a BINARY blob, do not use RawByteString, but TSQLRawBlob published property.
Then get the blob explicitly: it is not retrieve by default with other fields.
Use TSQLRest.RetrieveBlob method instead.

See http://synopse.info/files/html/Synopse% … l#TITLE_59
and search for the BLOB keyword in the documentation.

But in your case, you want to store only text, I guess.

So use a plain RawUTF8 field, without any "index ###" length attribute.
It would be stored as a CLOB in th external DB.
And you have nothing special to do.

Thomas-Acia · 2015-08-27 13:50:55

procedure TTicketClient.LoadTemplate2(idTemplate: Integer);
var
   aTemplate: TSQLticket_template;
begin
    aTemplate := TSQLticket_template.CreateAndFillPrepare(CTicket.Database, 'id = ?', [idTemplate]);
try
    while aTemplate.FillOne do
    begin
      TSQLRest.RetrieveBlob(TSQLticket_template,idTemplate, ??? ,aTemplate.bin);
    end;
finally
    aTemplate.Free;
 end;

 end;

Something like that ?

Last edited by Thomas-Acia (2015-08-27 13:56:47)

Thomas-Acia · 2015-08-27 13:56:29

ab wrote:

For a BINARY blob, do not use RawByteString, but TSQLRawBlob published property.
Then get the blob explicitly: it is not retrieve by default with other fields.
Use TSQLRest.RetrieveBlob method instead.
See http://synopse.info/files/html/Synopse% … l#TITLE_59
and search for the BLOB keyword in the documentation.
But in your case, you want to store only text, I guess.
So use a plain RawUTF8 field, without any "index ###" length attribute.
It would be stored as a CLOB in th external DB.
And you have nothing special to do.

Yes, it's only a template which contains a text.

Thomas-Acia · 2015-08-27 14:27:00

I don't know know to fill all the arguments.
TSQLRecordClass = TSQLticket_template
aID = idTemplate
const BlobFieldName : RawUTF8 = ??
BlobData : I think = aTemplate.bin

ab · 2015-08-27 15:37:51

Please follow http://synopse.info/files/html/Synopse% … l#TITL_146 for external database definition of TSQLRecord classes.

Thomas-Acia · 2015-08-28 07:07:23

 
  TSQLticket_template = class(TSQLRecord)
  private
      flibelle_template :RawUTF8;
      fbin :TSQLRawBlob;
      fblbid, fid_type_rapport :Integer;
  published
    property libelle_template: RawUTF8 Read flibelle_template Write flibelle_template;
    property bin: TSQLRawBlob Read fbin Write fbin;
    property blbid: Integer Read fblbid Write fblbid;
    property id_type_rapport: Integer Read fid_type_rapport Write fid_type_rapport;
  public
  end;

I think my TSQLRecord definition is right. I have a TSQLRawBlob. And then I don't know which args to use

ab · 2015-08-28 07:17:29

Please read the doc about how to define properties.

If your template is plain text, do not use a TSQLRawBlob, but a RawUTF8.

TSQLticket_template = class(TSQLRecord)
  private
      flibelle_template :RawUTF8;
      fbin : RawUTF8;
      fblbid, fid_type_rapport :Integer;
  published
    property libelle_template: RawUTF8 index 50 Read flibelle_template Write flibelle_template; // note the INDEX 50 here to create a VARCHAR(50)
    property bin: RawUTF8 Read fbin Write fbin; // no INDEX here to create a VARCHAR(max) = CLOB
    property blbid: Integer Read fblbid Write fblbid;
    property id_type_rapport: Integer Read fid_type_rapport Write fid_type_rapport;
  public
  end;

Thomas-Acia · 2015-08-28 07:20:49

Ah !!!! And to retrieve it ??

Last edited by Thomas-Acia (2015-08-28 07:25:19)

ab · 2015-08-28 07:27:39

It would be retrieve as part of any TSQLticket_template instance, just like other fields.
No need to have a separated request.

Thomas-Acia · 2015-08-28 07:32:51

So do you think I can do that :

FTemplate.Text := aTemplate.bin;

Edit : When I do that: my text value is :
￰PGh0bWw+DQoNCjxzdHlsZSB0.....

Last edited by Thomas-Acia (2015-08-28 07:38:50)

ab · 2015-08-28 11:49:27

I guess your code are mixing things up.

Your database is not empty, and/or has data from various TSQLRecord layouts.

Thomas-Acia · 2015-09-02 12:51:52

Finally, I used a TSQLRawBlob and the method : RetrieveBlob(). It works perfectly.
I have a last question. I sometimes have a problem with my server :

I think it's a problem with the PostgreSQL connection, maybe a timeout ??

Edit : I saw the ConnectionTimeOutMinutes method in the doc, I tried it, without success.

Last edited by Thomas-Acia (2015-09-02 12:53:03)

ab · 2015-09-02 13:50:22

... or an invalid SQL statement which let the Server release the connection?

I'm no PostgreSQL expert, perhaps some other may help....

mORMot Open Source

#1 2015-08-27 12:02:14

Retrieve a blob using mORMot

#2 2015-08-27 12:39:50

Re: Retrieve a blob using mORMot

#3 2015-08-27 12:48:51

Re: Retrieve a blob using mORMot

#4 2015-08-27 13:36:32

Re: Retrieve a blob using mORMot

#5 2015-08-27 13:50:55

Re: Retrieve a blob using mORMot

#6 2015-08-27 13:56:29

Re: Retrieve a blob using mORMot

#7 2015-08-27 14:27:00

Re: Retrieve a blob using mORMot

#8 2015-08-27 15:37:51

Re: Retrieve a blob using mORMot

#9 2015-08-28 07:07:23

Re: Retrieve a blob using mORMot

#10 2015-08-28 07:17:29

Re: Retrieve a blob using mORMot

#11 2015-08-28 07:20:49

Re: Retrieve a blob using mORMot

#12 2015-08-28 07:27:39

Re: Retrieve a blob using mORMot

#13 2015-08-28 07:32:51

Re: Retrieve a blob using mORMot

#14 2015-08-28 11:49:27

Re: Retrieve a blob using mORMot

#15 2015-09-02 12:51:52

Re: Retrieve a blob using mORMot

#16 2015-09-02 13:50:22

Re: Retrieve a blob using mORMot

Board footer