Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

error due to computed field use reported at compilation of a query - s.b. at runtime [CORE5499] #5768

Open
firebird-automations opened this issue Mar 8, 2017 · 2 comments
Open

error due to computed field use reported at compilation of a query - s.b. at runtime [CORE5499] #5768

firebird-automations opened this issue Mar 8, 2017 · 2 comments
Labels
affect-version: 2.5.7 component: engine priority: minor type: improvement

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Ray Holme (rholme)

If the underlying table has a trigger updating (erroneously) a computed field ( == alternate name for another field), the reported error from my C code is

ISC error code: 335544359
SQLCODE: -151 doing prepare query from typedef
- This column cannot be updated because it is derived from an SQL function or expression.

While this is true, it would have been much more helpful to report the error at runtime for the query when it tried to do the update.

This is a new feature to 2.5.7 as the error is NOT caught by 2.5.2.

I like the engine catching it but the message is VERY confusing - it took many hours to find out what was really happening.

Either a better message should be given (it is a TABLE-COLUMN problem even though the offending column is NOT in the query).
or
Catch the problem later at runtime.

--- The query was
insert into visit_animals (visit_id, animal_id) values (?, ?)

--- the error was a logging trigger in visit_animals that
update visits set synch_time = 'NOW' where visit_id = new.visit_id

--- and the field synch time is in visits but
computed by datetime.

--- Fixing the trigger to use datetime - removed the error (mine).
@firebird-automations
Copy link
Collaborator Author

Commented by: Ray Holme (rholme)

For the record, further experiments uncovered.

a) restoring a DB with this problem in it emits no error
(not surprised, just hoping)

b) re-asserting the trigger with the original (wrong) statement does catch the error

c) fixing the trigge(s) to NOT use the computed alias name fixes the problem wih prepare

---
What would be ideal is for there to be a "checkDb" tool that runs through the named DB (must invoke as sysdba)
that found all user tables with triggers and for each of the three trigger types (insert, update, delete) that are used
compiled a query of that type against the DB (errors are caught at prepare time) - printing out any errors found
This tool would be run anytime the user upgrades a Firebird release, catching any user errors not caught by an earlier release but now caught. Note that you do not need to compile (prepare) a statement unless the particular operation has that kind of trigger.

@firebird-automations
Copy link
Collaborator Author

Commented by: Ray Holme (rholme)

The only other way to be sure that procedures and triggers are release clean that I can think of is to remove them all and reassert in the new release.
Not trivial as sometimes things are convoluted in procedure land, but can be scripted.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 2.5.7 component: engine priority: minor type: improvement
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations