On Fri, 2007-06-29 at 18:35 +1000, John & Marg Sunderland wrote:
> Following earlier advice, I constructed a simple query ("select * from
> organiser.durn_type"), set readonly to false and tried to modify some
> data and commit it.
>
> (At this point I should note that I am doing this from Lazarus 0.9.22
> with the supplied fpc, 2.0.4 I think, and I am connecting to a
> postgresql database v 8.1.4 on the same PC, all on Window$ XP SP2.)
>
> I have no trouble opening the query to display the data in a grid
> (readonly or not) but when I made some changes, ApplyUpdates gave a
> database error: "syntax error at end of input at character 127"
>
> I first tried to trace the execution (in Lazarus IDE), but was unable to
> trace into the db or sqldb modules - "step into" ran straight to the
> error, and breakpoints reverted to disabled once the program started to
> run. Is there any reason why this should be so ? Or should I ask in
> the Lazarus discussion areas ?
>
> I then turned on lots of logging in Postgresql, and found the error to
> be in a statement:
>
> 2007-06-29 14:50:16 LOG: statement: EXECUTE <unnamed> [PREPARE:
> prepare prepst0 as select * from organiser.durn_type]
> 2007-06-29 14:50:16 LOG: statement: prepare prepst1 as
> select ic.relname as indexname, tc.relname as tablename, ia.attname,
> i.indisprimary, i.indisunique
> from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic,
> pg_index i
> where( (i.indrelid = tc.oid)
> and (ta.attrelid = tc.oid)
> and (ia.attrelid = i.indexrelid)
> and (ic.oid = i.indexrelid)
> and (ta.attnum = i.indkey[ia.attnum-1])
> and (upper(tc.relname)='ORGANISER.DURN_TYPE') )
> order by ic.relname;
This is what is done: to create the update/insert/delete it has to
detect what the primary-key is. For that purpose it executes the second
query.
> I then ran this in an SQL window from PGAdmin, and it ran, but returned
> no rows. I then removed the "ORGANISER." prefix on the table, and it
> ran and returned some rows. So, I removed the "organiser." schema
> prefix from the query in Lazarus, and it worked ! YAY! (The database
> connection is logged in under that schema) However, is it definite that
> I can't specify a schema prefix for a table, and should I document it as
> a restriction, or does this rate as a bug ? I can see from looking at
> the pg_class table that the above query is never going to work - relname
> is a simple object name, and the schema owner is an oid in another column.
To fix this the table-name has to be parsed, to get the schema-prefix.
That's not an easy fix.
You could solve this problem by setting the update/delete/insert queries
yourself. (and set parsesql to false) Or you could set
UsePrimaryKeyAsKey to false, and set updatemode to UpWhereAll, or make
one of the fields a key-field.
> Finally, as this table is a set of fairly static codes, and therefore
> would not expect a lot of activity, I tried constructing the editing
> form as a dialog box, only applying updates and committing when the OK
> button is used to close the form, and canceling the updates otherwise.
> Again, does this make sense ? It seems to work, except that: When I
> only change one line and close (committing) it is fine, but if I change
> several lines, I get a string of errors "EVariantTypeCastError - could
> not convert type (String) to type (OleStr). " It still works - I
> presume the errors must be "handled", as I do not see them if I run from
> outside the IDE, and the changes are saved.
This is normal. The exception is handled, but the debugger used by
lazarus, can't detect this. But check that your changes are really
saved.
Joost
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
