[fpc-pascal] Documentation for sqldb - More Questions

[Joost van der Sluis]

On Tue, 2007-07-03 at 17:55 +1000, John wrote:
> I have a basic editable connection working now, but there remain a 
> number of questions:
> 
> 1)  When I tried editing in a dbGrid, I had trouble with the field 
> length.  Looking through the code, I can't see anywhere where the length 
> of a string is checked against the length of the field, and longer 
> strings appear to overflow the field / record buffer.  Should this be so 
> ?  I can fix the problem by doing the edits with a dbEdit control with a 
> specified maximum length, but I can't find anything in the dbGrid 
> component to do this.  (I presume it would really be the TColumn 
> component) 

This is more a Lazarus-issue.

> My real question is, should it be the data control be doing it, or 
> should the sql components truncate a string that is too long ?

The data-controls. Or you can leave it to the SQL-Server. The official
SQL-specs say that the string should be truncated without any error. But
not all SQL-servers obey that rule...

> 2)  I am now trying a simple master slave form, with the slave table 
> having a "parent" field which links to the primary key  (a sequence 
> generated number) in the master  table.  I can make this work with  an 
> After Scroll Event on the master table thus:
> 
>   with sqlAC {TSQLQuery component selecting from slave table} do begin
>     active := false;
>     Params[0].AsInteger := sqlPars.Fields[0].AsInteger; {sqlPars is on 
> the master table}
>     active := true;
>     end;
> 
> Is there anything less drastic than closing and reopening the sql that 
> will refresh the query with the new parameter values ?
> 
> I tried to do this by setting the datasource on the above SQL, (and 
> masking the AfterScroll event), but kept getting "field not not found" 
> errors.  Should this work ?  (I had the parameter name matching the 
> field name I was trying to link to.  The field is only called "ref", so 
> there is not a lot of room for typos, and yes I checked for the correct 
> case!)

There is some master-slave system build in. But it effectively does the
same as you did. Hoe you should use it exactly, I don't know.

> 3)  Recently Joost van der Sluis wrote:
> > You could solve this problem by setting the update/delete/insert queries yourself. (and set parsesql to false)
> When I set ParseSQL to False, the object inspector in Lazarus says 
> "updating is only possible if ParseSQL is true" - unless I make it 
> readonly, which defeats the purpose.

That's a but, I think. It should check if there are any
update/delete/insert queries provided. If that is the case, you can make
a query updateable, even if ParseSQL is false.

> Looking through the code, I surmise that when the query is opened, 
> InternalOpen calls Prepare which in turn calls ParseSQL, but that 
> ParseSQL exits after only getting the statement type if ParseSQL is 
> false.  If the statement is a "select" statement and if it is 
> updateable, then InternalOpen initialises the update queries.  If SQL 
> text is supplied, it is assigned to the queries, otherwise they are left 
> blank.  When ApplyUpdates is called, this calls ApplyRecUpdate for each 
> record, and this then either runs the supplied SQL, or, if the supplied 
> SQL is null, generates it own SQL (using the stuff stored by Parse SQL) . 
> 
> Is this basically correct ?  (If so, you might see some of this expanded 
> a bit into some documentation)

Yes, except...

> If so, I guess leaving ParseSQL=true will waste some processing time, 
> but not actually stop it working.

... sometimes ParseSQL also changes the query a bit, mostly to help
filtering work. In some cases, the query isn't parsed correctly, and the
changes make the query invalid. If that's the case, you'll get syntax
errors in your query, while you think that there's nothing wrong. In
that case you have to set parsesql to false. 
Second thing is that it also tries to obtain the primary key. This could
also lead to trouble. (as in your case) 

> Any hint about the structure of the code for the update queries ?  I 
> guess I can work them out looking at what ApplyRecUpdate is trying to 
> generate, but an example would make life easier.

update table tblPeople set name=:name, birthdate=:birthdate,
email=:email where PeopleID=:PeopleID

> 4)  I still haven't been able to set a breakpoint in, or trace into any 
> of the sqldb code.  Is there any good reason for this ?  I can do so in 
> other library units, "buttons" for example.  (If I could work this out, 
> I might be able to answer a more of the other questions for myself)

That's because sqldb is part of Freepascal, while buttons is part of
Lazarus. By default, fpc is distributed without debug-info, but Lazarus
does have debug-info. 

So you have to recompile fcl-db with debug info. If you have the full
fpc-sources, go to fpc/packages/fcl-db and execute 'make clean all
OPT='-gl' '
After you have done that you have to install the freshly created .ppu's.
You could copy them over the old ones yourself. Or you could use 'make
install' if the system is configured right.

Joost.