Can I update DataSet using FR script?

Mick.pl

I have a simple query (a component based on TfrxCustomQuery): select tNum from tmpTable
And I need to insert a row into that table. I'm trying

qryTest.Open;
qryTest.DataSet.Insert;
qryTest.FieldByName('tNum').AsInteger := 12345;
qryTest.DataSet.Post;
qryTest.Close;

but the above has no efect on my table, I mean I have no error message but no row is inserted.
In fact my issue is a bit more complex. I need to insert a row with a column of BlobType (existing JPG file).
I tried to use another query with params like:

insert into tmpTable (tPicture) values :APict

and I specified [:APict] as a param of that query. But then I couldn't find what a type for param should be used (Blob, Grafic, etc) and what script variables I shoud use to:

1) LoadFromFile / LoadFromStream that picture,
2) use this variable as the value of [:APict] param to the query.


Maybe someone has any experience with using FR script for such not typical cases?

Regards
Mick

technisoft

My first guess would be that the frxQuery and other data access component are one-way, i.e. they have the ability to read data but not write.
My second guess is that, depending on database engine used, the update needs to be committed and that the write happens in a different transaction to the read. So, even if write succeeds, read will not see it until the read transaction is restarted, which may possibly undo the write in the sense that it just reads the original data without the insert that happened afterwards.

Mick.pl

Thank you both for your replies :-)

- 1. I won't try to force TfrxCustomQuery to append/insert rows into a table the way Delphi programmers do and frxClass says about :-(
- 2. Topic 6372 doesn't say a word about inserting rows into a table, just says about getting data from DataSet :-(


So I come back to my first try. And it was like below.

qryInsert (TfrxCustomQuery) with SQL insert into tmpTable (tBlob) select :AMyPicture
Parameter of this query :AMyPicture is to pass a picture (JPG) loaded from a file.
I put a TfrxPictureView (Visible := false) somewhere on a band, and use Picture1.Picture.LoadFromFile( JPGFileName).
Then I want to do qryInsert.ExecSQL.
What is an operation (ExecSQL) I used to work with thousands times with a SUCCESS for inserting or updating database tables.
And my question is: what a type for query parameter I should use to force FR engine to recognize Picture1.Pitcure properly?
Should I use Bytes, VarBytes, Blob, Graphic,TypedBinary, HugeBlob, Variant or any other ?
Or should I forget that FR engine is able to pass properly such a data (picture) as a parameter to TfrxCustomQuery :-(

Regards
Mick

PS.
I've solved my problem reading JPG file byte by byte and then updating a table.
But that is SO inefficiently and takes minutes when a file is bigger, even if I use some tricks and analyze where there is #0 and other chars :-(

Mick.pl

I've got an answer to my ticket I'd sent to FR Team.
It is NOT possible to insert (update) blob field into a database table directly from FastReport script using query with SQL property like
insert into MyTable (Picture) values :APic, where :APic is a parameter of Blob type.

It is necessary to use Delphi (or other) to send a graphic file (like it was in my issue) to a parameter of TfxCustomQuery.

Regards
Mick

technisoft

I think that is understandable and logical. Imagine a standalone end-user reporting tool which would allow the user to insert or modify data in the database. Your data integrity would be zero.

Mick.pl

technisoft wrote: »

I think that is understandable and logical. Imagine a standalone end-user reporting tool which would allow the user to insert or modify data in the database. Your data integrity would be zero.

Technisoft

I can't agree with you this time [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> It is not a user who may update database using a report but it is ITS author who plans the report "activity" at desing time (our end users do[/img]NOT have an access to designer).
FR offers a TfrxCustomQuery, and we can use ANY SQL database statement in that query we need including update and insert.

And we've been doing that for years with a success [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> The simplest case is to help user with filling dialog parameters. I mean to save some of them in a database and when user run the same report again some of dialog controls are filled with values the user used for last time. Regards Mick[/img]