Documentation for sqldb

classic Classic list List threaded Threaded
34 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Documentation for sqldb

John-416
Is there any documentation for the SQLDB components ?  I have put a fair
bit of effort in to looking round the FPC and Lazarus documentation and
wiki areas, and while there are a few helpful hints here and there,  I
have not been able to find any sort of  overview of how the components
are supposed to work, particularly once you try to update data.  While I
can make some guesses from looking through the source, it is really hard
to guess from that how the writer intended them to work, and the best
way to use them.

thanks,
John

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb

Joost van der Sluis
On Wed, 2007-06-20 at 22:32 +1000, John wrote:
> Is there any documentation for the SQLDB components ?  I have put a fair
> bit of effort in to looking round the FPC and Lazarus documentation and
> wiki areas, and while there are a few helpful hints here and there,  I
> have not been able to find any sort of  overview of how the components
> are supposed to work, particularly once you try to update data.  While I
> can make some guesses from looking through the source, it is really hard
> to guess from that how the writer intended them to work, and the best
> way to use them.

That's indeed very difficult to understand without documentation. And
indeed, there isn't any.

IN principle you can set ReadOnly to false and ParseSQL to true. That
way sqldb tries to parse your query. If it's a simple 'select * from
table' the TSQLQuery will be updateable. It automatically generates
update/delete and insert queries. For the 'where' clause is uses  by
default the primary key of the table. (That's a setting, upWhereKeyOnly)

For example: 'delete * from table where pk=:old_pk'

If you edit some data, those changes will be stored in an updatebuffer.
With TSQLQuery.CancelUpdates all those changes are lost. But if you
call .ApplyUpdates, it will execute one query for every changed record.
(That could be a insert, update or delete query)

If you have a more complex query (sqldb can handle more complex queries
then the one above, but not everything, offcourse) you can provide your
own update/insert and delete queries.

You can use parameters in those queries. The new value for a field is
stored in a parameter with the same name as the field. If you need the
old value from a field, you have to use the prefix 'old_' For example:

'update table set field1=:field1, field2=:field2, field3=:field3 where
(field1=:old_field1) and (field2=:old_field2) and (field3=:old_field3)'

Those are the basics. If you have questions, ask them here. And if you
have any time, please document it somewhere on the wiki. ;)

Joost

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb

Joao Morais
Joost van der Sluis wrote:

> Those are the basics. If you have questions, ask them here.

I have two!

- please send me some hints to configure a query as fast as possible --
read only and unidirectional. I will open the query, read everything and
close it.

- what about RowsAffected?

Thanks!

--
Joao Morais
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb

Joost van der Sluis
On Wed, 2007-06-20 at 14:43 -0300, Joao Morais wrote:
> Joost van der Sluis wrote:
>
> > Those are the basics. If you have questions, ask them here.
>
> I have two!
>
> - please send me some hints to configure a query as fast as possible --
> read only and unidirectional. I will open the query, read everything and
> close it.

Set ParseSQL to false. (This wil automatically set readonly to true) It
won't be unidirectional, but I made an unidirectional TSQLQuery once,
and coudn't measure any speed difference. The difference was to small.
And maybe you could tweak packetrecords. Set it to -1 so that all data
is fetched at once. That's faster if you really need all data
immediately. (Consumes more memory, though)

> - what about RowsAffected?

You mean how many rows are affected by the last query you've run? I saw
request for that earlier.

But imho, you never need it. You always should know how many rows are
affected before you execute a query. It could be a debug-tool,
though.Maybe I could implement it. Maybe for one specific connection, or
maybe even in a general form.

Joost.

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb

Joao Morais
Joost van der Sluis wrote:

>> - please send me some hints to configure a query as fast as possible --
>> read only and unidirectional. I will open the query, read everything and
>> close it.
>
> Set ParseSQL to false. (This wil automatically set readonly to true) It
> won't be unidirectional, but I made an unidirectional TSQLQuery once,
> and coudn't measure any speed difference. The difference was to small.
> And maybe you could tweak packetrecords. Set it to -1 so that all data
> is fetched at once. That's faster if you really need all data
> immediately. (Consumes more memory, though)

Thanks for the hints.

>> - what about RowsAffected?
>
> You mean how many rows are affected by the last query you've run? I saw
> request for that earlier.
>
> But imho, you never need it. You always should know how many rows are
> affected before you execute a query. It could be a debug-tool,
> though.Maybe I could implement it. Maybe for one specific connection, or
> maybe even in a general form.

This feature will be used in an opf framework to ensure that I didn't
have a conflict. Something like this:

update <list> where id = :id and updatecount = :lastupdatecount

If RowsAffected is 0 I have a conflict. I can use a select just before
the update but I will create an useless overhead, since the database
provides this information.

When you say implement it, you don't mean 2.2 I think. If so, would you
have a workaround to point out?

Thanks.

--
Joao Morais

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb

Henry Vermaak
In reply to this post by Joost van der Sluis
On 20/06/07, Joost van der Sluis <[hidden email]> wrote:
>
> You mean how many rows are affected by the last query you've run? I saw
> request for that earlier.
>
> But imho, you never need it. You always should know how many rows are
> affected before you execute a query. It could be a debug-tool,
> though.Maybe I could implement it. Maybe for one specific connection, or
> maybe even in a general form.

you can't always know, for instance if you do a conditional update:
update programmers set overtime = 'yes' where work_hours > 80

i've never used it in a program myself, but I've noted that there's an
api function for this in mysql and there's the sql function
ROW_COUNT() (in 5.0.1).  i suspect there are similar functions for
different flavours...

>
> Joost.

henry
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb

Adriaan van Os-2
Henry Vermaak wrote:
> On 20/06/07, Joost van der Sluis <[hidden email]> wrote:
>>
>> You mean how many rows are affected by the last query you've run? I saw
>> request for that earlier.
>>
>> But imho, you never need it. You always should know how many rows are
>> affected before you execute a query.

Of course not, what absurd nonsense.

It could be a debug-tool,

>> though.Maybe I could implement it. Maybe for one specific connection, or
>> maybe even in a general form.
>
> you can't always know, for instance if you do a conditional update:
> update programmers set overtime = 'yes' where work_hours > 80
>
> i've never used it in a program myself, but I've noted that there's an
> api function for this in mysql and there's the sql function
> ROW_COUNT() (in 5.0.1).  i suspect there are similar functions for
> different flavours...

MySQL has mysql_affected_rows, Postgres has PQcmdTuples.

Regards,

Adriaan van Os
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

heap problem or fpc_ansistr_decr_ref in FPC for ARM

josepascual (almudi)
In reply to this post by Henry Vermaak
Hi Everyone, (gdb) list
I have made FPC (fpc-2.1.4_aka_2.2.0_beta) for ARM with DEBUG=1 so I can
debug problem:

I have a program which works okey in same version for i386 but for arm
I'm trying to debug it to see what it's happening

I have run program in GDB and I received it:

Program received signal SIGSEGV, Segmentation fault.
TRY_CONCAT_FREE_CHUNK_FORWARD (MC=0x40213170) at heap.inc:679
679     in heap.inc

and Backtrace is:

(gdb) print MC
$17 = (PMEMCHUNK_VAR) 0x40213170
(gdb) print MC_TMP
$18 = (PMEMCHUNK_VAR) 0x0
(gdb) print SIZEMAK
No symbol "SIZEMAK" in current context.
(gdb) print SIZEMASK
$19 = -16
(gdb) PRINT MC.SIZE
$20 = 690513264
(gdb) bt
#0  TRY_CONCAT_FREE_CHUNK_FORWARD (MC=0x40213170) at heap.inc:678
#1  0x0002a574 in TRY_CONCAT_FREE_CHUNK (MC=0x40213170) at heap.inc:691
#2  0x0002acc0 in SYSFREEMEM_VAR (PMCV=0x40213170) at heap.inc:1040
#3  0x0002ad0c in SYSFREEMEM (P=0x40213170) at heap.inc:1059
#4  0x00029ad8 in FREEMEM (P=0x40213178) at heap.inc:342
#5  0x0001cd0c in fpc_ansistr_decr_ref (S=0x40213178) at astrings.inc:107
#6  0x00077ee8 in LUAPUSHVARIANT (L=0xdda08, N=void) at LuaUtils.pas:464
#7  0x00069a10 in LUACOPYTABLE (L=0xdda08, IDXFROM=4, IDXTO=2, MTTO=3)
    at lua_WrapUp.pas:51
#8  0x00069dd8 in NEW_TMYTYPE (L=0xdda08) at lua_WrapUp.pas:125
#9  0x000961bc in luaD_precall ()
#10 0x0009f084 in luaV_execute ()
#11 0x00000000 in ?? ()


mc^size of 690513264 is not very regular, is it?
What size is normal for it?

Any Idea?

How Can I patch it?

thank you for your help

Jose Pascual


_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

why {$E+} is in fpcsrc/rtl/inc/objects.pp?

josepascual (almudi)
Hi Everyone,

I have downloaded last snap (20_06_2007) for fpc 2.3.1 and I have make and
make installed for i386,
for ARM compilation stop compiling objects.pp because {$E+} is inside code,

It does not work for ARM I have commented this switch to comopile.

best regards

Jose Pascual


_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Re: Documentation for sqldb

John-416
In reply to this post by Joost van der Sluis
Joost van der Sluis wrote:

> IN principle you can set ReadOnly to false and ParseSQL to true. That
> way sqldb tries to parse your query. If it's a simple 'select * from
> table' the TSQLQuery will be updateable. It automatically generates
> update/delete and insert queries. For the 'where' clause is uses  by
> default the primary key of the table. (That's a setting, upWhereKeyOnly)
>
> For example: 'delete * from table where pk=:old_pk'
>
> If you edit some data, those changes will be stored in an updatebuffer.
> With TSQLQuery.CancelUpdates all those changes are lost. But if you
> call .ApplyUpdates, it will execute one query for every changed record.
> (That could be a insert, update or delete query)
>  
So if I change some field values and post the record, that changed data
goes into the update buffer - of TBufDataset ?  I should then be able to
accumulate a few changes of these, and then, in code, call
TSQLQuery.ApplyUpdate, and it should be sent to the database ?  Should
this also commit at the database level, or do I have to do this ?  
(I think I tried this, but didn't pursue it very far, because I wasn't
sure if it even should work)

Would it be feasible / sensible to call ApplyUpdate from an AfterPost
event handler ?  

> Those are the basics. If you have questions, ask them here. And if you
> have any time, please document it somewhere on the wiki. ;)
I will, if I get to the point of thinking I understand it enough to not
make a fool of myself!

John

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Re: Documentation for sqldb

Joost van der Sluis
On Fri, 2007-06-22 at 10:43 +1000, John wrote:

> Joost van der Sluis wrote:
>
> > IN principle you can set ReadOnly to false and ParseSQL to true. That
> > way sqldb tries to parse your query. If it's a simple 'select * from
> > table' the TSQLQuery will be updateable. It automatically generates
> > update/delete and insert queries. For the 'where' clause is uses  by
> > default the primary key of the table. (That's a setting, upWhereKeyOnly)
> >
> > For example: 'delete * from table where pk=:old_pk'
> >
> > If you edit some data, those changes will be stored in an updatebuffer.
> > With TSQLQuery.CancelUpdates all those changes are lost. But if you
> > call .ApplyUpdates, it will execute one query for every changed record.
> > (That could be a insert, update or delete query)
> >  
> So if I change some field values and post the record, that changed data
> goes into the update buffer - of TBufDataset ?  I should then be able to
> accumulate a few changes of these, and then, in code, call
> TSQLQuery.ApplyUpdate, and it should be sent to the database ?  Should
> this also commit at the database level, or do I have to do this ?  
> (I think I tried this, but didn't pursue it very far, because I wasn't
> sure if it even should work)

You have still to commit the database-transaction.
(SQLQuery.Transaction.commit(retaining)) And yes, the buffer is handled
by TBufDataset.

> Would it be feasible / sensible to call ApplyUpdate from an AfterPost
> event handler ?  

Yes, you could do that. But that could generate a lot of sql-traffic.
Maybe in an after-scroll is a better idea.

> > Those are the basics. If you have questions, ask them here. And if you
> > have any time, please document it somewhere on the wiki. ;)
> I will, if I get to the point of thinking I understand it enough to not
> make a fool of myself!

Just start it, others will correct mistakes if needed.

Joost

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb - further adventures

John & Marg Sunderland
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,
John
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb - further adventures

Joost van der Sluis
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  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb - More Questions

John-416
In reply to this post by Joost van der Sluis
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)

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 ?

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!)

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.

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)

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

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.

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)

Thanks for your patience,

John


_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb - More Questions

Martin Schreiber
On Tuesday 03 July 2007 09.55, John wrote:
> I have a basic editable connection working now, but there remain a
> number of questions:
>
Have a look on MSEide+MSEgui:

http://www.homepage.bluewin.ch/msegui/

Screenshots:
http://sourceforge.net/project/screenshots.php?group_id=165409

It has lookup buffers, a sophisticated tdbwidgetgrid and many other useful
components for database development and a stable and highly productive IDE.
The users of MSEide+MSEgui affirm that it is production ready.

Please send questions and bug reports to (NNTP):

news://news.grid-sky.com/public.mseide-msegui.talk

Martin
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb - More Questions

michael.vancanneyt


On Tue, 3 Jul 2007, Martin Schreiber wrote:

> On Tuesday 03 July 2007 09.55, John wrote:
> > I have a basic editable connection working now, but there remain a
> > number of questions:
> >
> Have a look on MSEide+MSEgui:
>
> http://www.homepage.bluewin.ch/msegui/
>
> Screenshots:
> http://sourceforge.net/project/screenshots.php?group_id=165409
>
> It has lookup buffers, a sophisticated tdbwidgetgrid and many other useful
> components for database development and a stable and highly productive IDE.
> The users of MSEide+MSEgui affirm that it is production ready.

Why don't you contribute the database code to the FCL ? This way, everybody
benefits from it. It's non-visual code, so it can perfectly be separated
out.

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb - More Questions

Joost van der Sluis
In reply to this post by John-416
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.

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb - More Questions

Martin Schreiber
In reply to this post by michael.vancanneyt
On Tuesday 03 July 2007 11.36, Michael Van Canneyt wrote:
>
> Why don't you contribute the database code to the FCL ? This way, everybody
> benefits from it. It's non-visual code, so it can perfectly be separated
> out.
>
It depends on the completely rewritten tmsebufdataset, if you want to use the
MSEgui DB components you need to to replace TBufDataset with tmsebufdataset
and the SQL connection components need to be patched.
The development speed of MSEide+MSEgui is too fast that I could depend for DB
code on FCL with the difficult and time consuming FPC coordination process
for bug fixes and changes. Another problem is that I am not bound to Delphi
compatibility so I don't think that my code will be accepted by the FPC team.
Feel free to integrate any MSEgui code which you will find useful into
FCL. :-)

Martin
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb - More Questions

michael.vancanneyt


On Tue, 3 Jul 2007, Martin Schreiber wrote:

> On Tuesday 03 July 2007 11.36, Michael Van Canneyt wrote:
> >
> > Why don't you contribute the database code to the FCL ? This way, everybody
> > benefits from it. It's non-visual code, so it can perfectly be separated
> > out.
> >
> It depends on the completely rewritten tmsebufdataset, if you want to use the
> MSEgui DB components you need to to replace TBufDataset with tmsebufdataset
> and the SQL connection components need to be patched.

So in fact, you are duplicating SQLDB. Judging from your website, you even
used the lazarus icons for the components.
(this is ok, just a constatation)

> The development speed of MSEide+MSEgui is too fast that I could depend for DB
> code on FCL with the difficult and time consuming FPC coordination process
> for bug fixes and changes. Another problem is that I am not bound to Delphi
> compatibility so I don't think that my code will be accepted by the FPC team.

Since SQLDB is in no way connected to Delphi, so you're free to do as you
please, as long as it descends from TDataset ?

The coordination process would be non-existent, because I would
give you direct access to the FCL subversion. As long as you follow the
rules for the merging of fixes to fixbranches there is no problem.

In fact there would be less problems, as fixes would be tested sooner/better
and would make it to the fixes branch easier. With the new packaging system
which will be in effect after 2.2, the FCL can be released on a more regular
basis.

> Feel free to integrate any MSEgui code which you will find useful into
> FCL. :-)

I'd rather see that the whole is coordinated a bit. I don't think all this
duplication is a good idea; One solid set of components makes much more
sense: the number of developers available for it's maintenance would double.
The end user would also benefit, as we can give 1 solid set of components,
no doubts possible.

There are not so many people available who understand the inner workings
of TDataset and friends, so all this scattering and dividing is
counterproductive.

It would be much better if you, me, Joost and Bram (who work on SQLDB)
had a single set of components to work with: speed would not be an issue
then, and we could guarantee a working and reliable set of components for
everyone.

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Documentation for sqldb - More Questions

John-416
Michael Van Canneyt wrote:

> I'd rather see that the whole is coordinated a bit. I don't think all this
> duplication is a good idea; One solid set of components makes much more
> sense: the number of developers available for it's maintenance would double.
> The end user would also benefit, as we can give 1 solid set of components,
> no doubts possible.
>
> There are not so many people available who understand the inner workings
> of TDataset and friends, so all this scattering and dividing is
> counterproductive.
>
> It would be much better if you, me, Joost and Bram (who work on SQLDB)
> had a single set of components to work with: speed would not be an issue
> then, and we could guarantee a working and reliable set of components for
> everyone.
>
>  
As a relatively new user, I absolutely agree.  There is a wealth of
components already, and trying to work out which to use where is already
quite daunting, especially when there is little/no documentation to
guide you!  And I am new only to FPC/Lazurus - I have been programming
Pascal since Turbo Pascal 3, and did some reasonably serious database
applications using Delphi 4 C/S into Oracle "on the side" during a stint
as an Oracle DBA.


John Sunderland
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
12