[fpc-pascal] Documentation for sqldb - further adventures

John & Marg Sunderland jmsund at netspace.net.au
Fri Jun 29 10:35:45 CEST 2007

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;

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.

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.

thanks for any help,

More information about the fpc-pascal mailing list