DBImage: how to use?

[BigChimp]

Well, I did say I'd look into the code to see what's causing the problems & I never said the problem couldn't be at my end.... though yes, I was quite frustrated at this...

Thanks a lot though for looking into it & finding the problem, that is very satisfying!

[taazz]

This is my new years resolute, Try to help everyone that attaches a working example project in Lazarus forums.
Kidding aside it is one of my goals for this year.

[BigChimp]

Well, thanks a lot for that - you helped me out a lot!

[BigChimp]

FYI, could now test
http://bugs.freepascal.org/view.php?id=25453
   0025453: [Patch] Support images using DBImage OnDBImageRead event
both with regular Lazarus DBImage (file extension+BLOB) as well as
Delphi style (just the BLOB, fixed file format, in my case jpg).
for LazReport.

Works beautifully.

[taazz]

actually the delphi style is the same as lazarus style the dbimage adds a couple of bytes in front that describe the image that follows but i think its a not string based. Well it has been more than 10 years the last time I looked at that code so things might have change in the mean time.

[BigChimp]

The Full_Name field is since it is a computed field and not a physical one you must remove it from the update query.

This wasn't documented (the fact that sqldb can't correctly deal with computed by fields, that is)

Added
docs
http://wiki.lazarus.freepascal.org/Firebird_in_action#Attempted_update_of_read-only_column_.2F_COMPUTED_BY_fields
official docs request
http://bugs.freepascal.org/view.php?id=25545

Had a look at modifying sqldb's ibconnection but the performance impact would probably be too big...


Thanks

[taazz]

In some cases it might be updatable eg have trigger that parses the full_name to first,midle and last names and update them instead. It has been seen in the wild for phone numbers though not names. The safest bet would be to do nothing and let the end user handle it.

[BigChimp]

taazz, no a computed field as the term is specified by Interbase/Firebird is statically defined in Firebird DDL and not calculated by triggers (well perhaps internally, but that's Firebird's problem, not ours ; ); see Interbase 6 Language Reference (SQL reference) page 76:
"Computed columns are based on an expression. Column values are computed each time the table is accessed."

Yes, you could generate the same effect with a trigger but that would be a regular field filled by a trigger, which is different.

[taazz]

the other way around, you use the trigger to break the computed value to their respected fields not combine them in to one. The user types in +01555675987 and this breaks up in the trigger to
country code = 001, area code = 555, phone number  = 675987 and the read only field is ignored. Granted this is mostly used with updatable views not tables but you get the point.

[BigChimp]

In some cases it might be updatable eg have trigger that parses the full_name to first,midle and last names and update them instead.

I'm assuming with it you mean "computed by" field, say full_name.
Firebird will not accept any updates to full_name at all, unless you resort to updatable views etc, yes. In that case, Firebird will presumably report a NULL rdb$fields.rdb$computed_source (see patch in link below) as that applies to the edit: column in the underlying table, not the one in the view.

I don't really see any mechanism except updatable views where this kind of thing could work though...

Admittedly I haven't tested this because:
1. As said, performance constraints will likely render my patch useless
2. The patch as is doesn't seem to run. Suggestions for improvements welcome... Probably me again messing with pointers that $(deity) hadn't intended for mere mortals... ehrm the likes of me.

See
http://bugs.freepascal.org/view.php?id=25513

Thanks.

[taazz]

1) the readonly status is present when you first create the fields/fielddefs as well there is no need to run a new query on the server just before you commit just make sure that the fields. Readonly property is set when the fields are created and you are done.
2) A New property is needed to give the user a choice to leave out read only fields from the update-insert etc or not, just in case the underline object is not a table but a view and readonly fields needs to be included so the triggers would work.

In any case it is up to the user to select how s/he want the query to behave a blanket type of property, that will enable/disable the inclusion of the readonly fields to the final sql, would be very useful but not required at this point.
to put it simple : One must know what one is doing with his/her  fields before one is allowed to insert/update anything to a production database. So the current behavior is in my book desired and if well documented should not be changed.

Just my .002 cents.

[BigChimp]

1) the readonly status is present when you first create the fields/fielddefs as well there is no need to run a new query on the server just before you commit just make sure that the fields. Readonly property is set when the fields are created and you are done.

AddFieldDefs is run once for each query preparation - moving it somewhere else may be doable but has to take into account that Firebird is a multi-user db. A table we just used may now have an additional field etc.
Not likely, but possible.

2) A New property is needed to give the user a choice to leave out read only fields from the update-insert etc or not, just in case the underline object is not a table but a view and readonly fields needs to be included so the triggers would work.

Assumptions here - unless you checked what updatable views return in the column I mentioned.

In any case it is up to the user to select how s/he want the query to behave a blanket type of property, that will enable/disable the inclusion of the readonly fields to the final sql, would be very useful but not required at this point.

Well, having the db layer provide sensible defaults is IMO important. If the user knows what he's doing he's free to deviate from those defaults.
Cf the way insertsql etc get generated: they are provided for the user but the user can change them.

to put it simple : One must know what one is doing with his/her  fields before one is allowed to insert/update anything to a production database. So the current behavior is in my book desired and if well documented should not be changed.

Agreed. However, you may be coming from an Interbase/Delphi background where it is obvious that these kinds of fields are not set read-only by the db layer. I and others don't - but as you say, if properly documented, it certainly works.

Just my 2 cents

[taazz]

1) the readonly status is present when you first create the fields/fielddefs as well there is no need to run a new query on the server just before you commit just make sure that the fields. Readonly property is set when the fields are created and you are done.

AddFieldDefs is run once for each query preparation - moving it somewhere else may be doable but has to take into account that Firebird is a multi-user db. A table we just used may now have an additional field etc.
Not likely, but possible.

erm I do get the feeling that I am missing something important or I've lost all eloquence and reson ( not that I had much to begin with). I'm only stating that placing the checks of read only fields in the addfielddefs procedure instead of the updaterecord procedure is the prefered way to handle things. I never said to move the addfieldsdefs from its current position to somewhere else. at least that is what I get from your statement that you understood.

2) A New property is needed to give the user a choice to leave out read only fields from the update-insert etc or not, just in case the underline object is not a table but a view and readonly fields needs to be included so the triggers would work.

Assumptions here - unless you checked what updatable views return in the column I mentioned.

No no assumptions don't look the firebird only case what ever you do on the sqlquery component is global to all databases.

In any case it is up to the user to select how s/he want the query to behave a blanket type of property, that will enable/disable the inclusion of the readonly fields to the final sql, would be very useful but not required at this point.

Well, having the db layer provide sensible defaults is IMO important. If the user knows what he's doing he's free to deviate from those defaults.
Cf the way insertsql etc get generated: they are provided for the user but the user can change them.

Agreed! I'm only saying that it already provides sensible defaults.

to put it simple : One must know what one is doing with his/her  fields before one is allowed to insert/update anything to a production database. So the current behavior is in my book desired and if well documented should not be changed.

Agreed. However, you may be coming from an Interbase/Delphi background where it is obvious that these kinds of fields are not set read-only by the db layer. I and others don't - but as you say, if properly documented, it certainly works.

Just my 2 cents

Not really my main line of data server was MSSQL from 6 to 2015 firebird in comparison is on the constipated side of the fence if it did not had the wonderful embedded server as a single dll to share with your application I would have never used it to begin with, not when postgreSQL is a few clicks away and MSSQL express literally covers any none enterprise use I had so far on windows environments with out problems. 

PS. Still haven't read the link you provided about calculated fields on firebird, probably will not before Monday either.

[BigChimp]

erm I do get the feeling that I am missing something important or I've lost all eloquence and reson ( not that I had much to begin with).

Me likewise

I'm only stating that placing the checks of read only fields in the addfielddefs procedure instead of the updaterecord procedure is the prefered way to handle things. I never said to move the addfieldsdefs from its current position to somewhere else. at least that is what I get from your statement that you understood.

More or less, yes. Glad that's sorted out.

No no assumptions don't look the firebird only case what ever you do on the sqlquery component is global to all databases.

The code we're talking about is Firebird only though: it's in ibconnection.pas, not in the sqlquery code.

(I'll leave the question about what kind of metadata a updatable view on FB against a computed by field would produce as is, it doesn't really matter anyway...)

Not really my main line of data server was MSSQL from 6 to 2015 firebird in comparison is on the constipated side of the fence if it did not had the wonderful embedded server as a single dll to share with your application I would have never used it to begin with, not when postgreSQL is a few clicks away and MSSQL express literally covers any none enterprise use I had so far on windows environments with out problems. 

PS. Still haven't read the link you provided about calculated fields on firebird, probably will not before Monday either.

Ok no problem. It's not as if we're rushing to fix something here anyway - just chatting along

Just to (hopefully) clarify or perhaps painfully harp on: I'm talking about a firebird specific field type called a "computed by" field... which probably has equivalent/similar manifestations in other dbs, but I'm *only* talking about the Firebird defintion. Likewise I'm excluding fields for which a user may have select but not insert/update grants etc.